How to Use Excel's ZTEST Function

1. Learn the syntax for the ZTEST function. It is: ZTEST(array,u0,sigma) where array of data to test the given value against, u0, is the given value to test and sigma is the known standard deviation. ZTEST returns the #N/A error value if array is empty.
2. Calculate ZEST when sigma is supplied as follows: ZTEST(array,u0) = 1 - NORMSDIST(x - u0)/(sigma/square root of n)).
3. Substitute the sample standard deviation if sigma is omitted as follows:
ZTEST(array,u0) = 1 - NORMSDIST((x - u0)/(s/square root of n)).
4. Note that x is AVERAGE(array)), s is the known standard deviation and n is the number of observation in COUNT(array). Furthermore, if AVERAGE(array)
5. Determine the two-tailed probability. This is the probability that the sample mean is further from the given population mean than AVERAGE(array) and is expressed as follows:2 * MIN(ZTEST(array,u0,sigma),1 - ZTEST(array,uo,sigma)) where u0 is the given population mean.
6. Use Excel 2003 and later for improved accuracy of NORMDIST. Previous versions of Excel used a single computation for all values of z, providing accuracy to about seven decimal places.

Read more ►

How to Use Excel to Make a Queue

1. Open a new Excel 2010 spreadsheet. Click the 'File' tab at the top of the screen and choose 'Options' on the left side of the screen. Select 'Customize Ribbon' from the left side of the new window. Find the 'Developer' listing on the list on the far right side of the window. Click the check box next to 'Developer' to add a check and click 'OK.'
2. Select cell 'A1' and type in 'Name.' Select cell 'B1' and type in 'Time.' Click the 'B' at the top of the second column. Right-click anywhere in the column and choose 'Format Cells.' Select 'Time' from the list on the left side of the window. Select the option that says '*1:30:55 PM' and click 'OK.'
3. Click the 'Developer' tab at the top of the screen. Click the 'Insert' button in the 'Controls' area of the ribbon. Click the 'Button' icon in the top-left corner of the small field of icons that opens up. Move your mouse over the spreadsheet. Click and hold the mouse button and drag it down and to the right. Release the mouse button to create a button on your spreadsheet. Type 'Add' into the 'Macro Name' field and click the 'New' button. The VBA console will open up automatically. Click the 'Minimize' button to get this window out of the way for now.
4. Click the 'Insert' button again and repeat the process. This time, name the macro 'Remove.' The VBA console will come up automatically again.
5. Click on the line between 'Sub Add()' and 'End Sub' in the VBA console. Enter the following text into the console:Rows('2:2').Insert shift:=xlDownRange('C1').CopyRange('A2').PasteSpecialActiveCell.Offset(0, 1) = NowRange('A:B').Sort Key1:=Range('B1'), order1:=xlAscending, Header:=xlYesThis code will take a name from cell 'C1' and add it to the queue.
6. Click on the line between 'Sub Remove()' and 'End Sub.' Enter the following text into the console:ActiveCell.EntireRow.DeleteThis code will delete the selected row from the queue. Click the 'X' in the top-right corner of the console to close it and go back to your Excel spreadsheet.
7. Click the 'Design Mode' button on the toolbar. Right-click the first button you created and click 'Edit Text.' Delete the text in the box and type in 'Add.' Do the same thing for the second box and type in 'Remove.'
8. Type in the name of the first person or thing you want to enter into the queue into cell 'C1.' Click the 'Add' button. The name will appear in column 'A' and the current time will appear in column 'B.' When you want to add another person to the queue, change the name in cell 'C1' and click 'Add.' When you want to remove an entry from the queue, just click on the name in column 'A' and click the 'Remove' button.

Read more ►

How to Password

Microsoft Excel 2010
1. Open Microsoft Excel 2010.
2. Click 'File' on the top menu bar. Choose 'Info.'
3. Select the option to 'Protect Document.' Choose 'Encrypt with Password' from the list.
4. Enter a password. Click 'OK.' Retype the password. Click 'OK.'
Microsoft Excel 2007
5. Open Microsoft Excel 2007.
6. Click the 'Microsoft Office' button. Select 'Prepare' from the list. Choose 'Encrypt' from the menu.
7. Enter a password. Click 'OK.' Retype the password. Click 'OK.'
Microsoft Excel 2003
8. Open Microsoft Excel 2003.
9. Click 'Tools' on the top menu bar. Choose 'Options' and then 'Security.'
10. Enter a password in the box labeled 'Password to Open.' Click 'OK.' Retype the password. Click 'OK.'

Read more ►

How to Create a Check Box in Excel 2007

1. Open Microsoft Excel 2007.
2. Click the 'Office' button located in the left corner of the window represented by the Microsoft Office Logo.
3. Click 'Excel Options' located along the bottom of the menu on the right.
4. Click the tab titled 'Popular' and select the check box next to 'Show Developer tab in the Ribbon.' Click 'OK' when you have finished to exit.
5. Click the 'Developer' tab along the Office menu. Click 'Insert' and select the check box from the set of options.
6. Click-and-drag your mouse to draw the check box to your desired size.

Read more ►

How to Alphabetize a Listing in Excel 2007

1. Click on the Start button on the lower left-hand side of your screen. Point to All Programs at the bottom, to Microsoft Office, and then click on 'Microsoft Office Excel 2007.'
2. Click on the top-left circular Microsoft button and click on the second option, Open. Search for the excel spreadsheet you want to sort alphabetically on the left hand panel and double-click the folder it is located in with the main, right-hand panel. Double-click on the file name and click on Open.
3. Determine if the column of text you want to sort alphabetically corresponds to the any or all of the columns of text within that one spreadsheet. For example, a column of student names would correspond to student exam grades, essay grades, and overall grade point average (GPA).
4. Highlight all of the columns that correspond to each other. Do not highlight the column titles, such as 'Name' or 'Averages.'
5. Click on the Data button, the fifth option on the ribbon.
6. Click on the Sort option near the middle of the ribbon.
7. Click on the first arrow on the left-hand side next to 'Sort by' and choose the column you want to be sorted alphabetically. Click on the last arrow on the right-hand side and choose A-Z sorting or Z-A sorting.
8. Click on 'Add level' if you want to sort by other data after that one alphabetical column from the last step. You will want to include all of the columns associated to the first column you sorted by. For example, if you sort student names alphabetically you will want to sort all of the grades with the names. Click on the drop-down arrow right next to 'Then By' and choose the next column. Click on the last arrow on the right-most side and choose how you want the data sorted; if it's numeric data, it will say 'Smallest to Largest' or 'Largest to Smallest.'
9. Repeat step 8 to sort however many cells you wish.

Read more ►

How to Create Multiple Hyperlinks in Excel

1. Open Microsoft Excel. Click the 'File' tab. Click 'Open.' Browse to the spreadsheet to add hyperlinks into and double-click its file name. The spreadsheet opens in the Excel workspace.
2. Scroll to or click the cell with the first set of data to hyperlink. Right-click the cell and click 'Hyperlink' to open the 'Insert Hyperlink' window.
3. Type the Web address to launch when clicking the hyperlink into the 'Address' box at the bottom of the 'Insert Hyperlink' window. Click the 'OK' button. The cell text becomes blue with an underline signifying a hyperlink.
4. Scroll to or click the next cell to hyperlink and repeat the 'Insert Hyperlink' process.
5. Create multiple hyperlinks going to the same destination by highlighting a cell, right-clicking and clicking 'Insert Hyperlink.' After typing in the website in the 'Address' box, highlight the typed address, right-click it and click 'Copy.' Click the 'OK' button to close the 'Insert Hyperlink' window and add the hyperlink.
6. Click the next set of data to hyperlink with the same address. Repeat the 'Insert Hyperlink' process, but instead of typing into the 'Address' box, right-click the box and click 'Paste' to paste in the address. Click 'OK.'

Read more ►

How to Use Microsoft Office Home Student for Small Business

1. Use Word 2010 to process most of the documents that your small business requires you to handle. Microsoft Word is the standard application for writing and storing digital documents. The application allows you to read, write, edit and create files with the DOC and DOCX extensions. Word 2010 allows you to create easy-to-read tables and paste images into the text of the document. The 2010 version features an improved Navigation Panel, providing a visual outline of the documents so you can browse and sort what you need quickly.
2. Use Excel 2010 to analyze, organize, share and manage the data of your small business. You can use the many mathematical, financial, statistical and other formulas to collect and process such data as sales statistics and information about your clients and suppliers. You also can calculate the profit margin of your business and use other ratios, including the return on your investment and what proportion of your cash flow goes to repaying bank loans.
3. Use PowerPoint 2010 to create professional-looking presentations. PowerPoint can put your arguments in a concise and easy-to-understand visual image for your perspective customers.
4. Organize your information in OneNote 2010. Create a virtual notebook that can save text, images, video and audio. You can use OneNote to schedule important meetings and write down any thoughts or ideas without worrying that they will be lost if you write them down on a piece of paper.

Read more ►

How to Create a Histogram in PowerPoint

1. Arrange your data into columns on paper to organize yourself before creating a histogram in PowerPoint. For example, list input data in one column and the range in the second column. Identify the frequency of the data occurring within each of the ranges of the second column.
2. Open PowerPoint to create a new presentation so you can build your chart based on the information you've organized on paper. Right-click on the slide. Choose the 'Layout' option. Click the 'Title and Content' layout. Click the 'Click to add title' text box, and type 'Histogram.'
3. Click the 'Insert Chart' button. Choose the first 'Clustered Column' option. Click 'OK.' Because you will show only two columns of information, drag the right corner of the range so it ends at Column B. Press the 'Ctrl' and 'A' keys and then press the 'Delete' key to delete the default data and add your own. For example, in column A, enter the following data:Range60802040
4. In column B, enter the following data:Frequency3321
5. Click the 'Microsoft Office' button in the Excel window, and click the 'Close' option to return to the PowerPoint window.
6. Add a text box to list the percentage legend to the right of your column chart. From the 'Insert' menu, click the 'Text Box' information and enter this text:100%80%60%40%20%0%
7. Add a cumulative percentage point line. For example, under the 'Insert' menu, choose the 'Shapes' option and select a line. Draw a line from each column to the next column to show the increasing cumulative percentage for the bin range. Insert a text box to label the percentage complete. For example, on the 60 value column, 30% of the data is represented. On the 80 value column, 60% of the data is represented. On the 20 value column, 80% of the data is represented. On the 40 value column, 100% of the data is represented.

Read more ►

How to Extend Margins in Excel

1. Open the Microsoft Excel 2010 spreadsheet that you want to alter.
2. Click the 'Page Layout' tab at the top of the screen. Locate the 'Page Setup' area on the ribbon and click the 'Margins' button found there. Click 'Custom Margins' from the menu that appears.
3. Click the up and down arrows next to each of the four major margins to adjust the margin for that side of the paper. You can also adjust the margins for the header and footer, making them larger or making them so small that they disappear.
4. Click the 'Print Preview' button once you have extended your margins, to ensure that the final product has the look that you want. If the margins are not correct, click the 'Page Layout' tab again and choose 'Custom Margins' again to go back to the margins screen.

Read more ►

How to Convert Excel Field to Boolean Type

1. Click 'File.'
2. Click 'Options.' This opens the 'Excel Options' window.
3. Click 'Customize ribbon.'
4. Check the box next to 'Developer' in the pane on the right. Click 'OK.'
5. Click 'Developer' from the menu bar.
6. Click 'Insert' from the 'Controls' tab.
7. Click the icon for a check box from the 'form options' section.
8. Click on the cell to which you want the add the boolean value.

Read more ►

How to Set Up a Default Email Program With MS Excel

Internet Explorer
1. Open Internet Explorer.
2. Click 'Tools' then 'Internet Options.'
3. Click the tab labeled 'Programs.'
4. Select your e-mail program under 'E-mail.'
5. Click 'OK.'
Control Pannel
6. Click the 'Start' menu.
7. Select 'Control Panel.'
8. Select 'Network and Internet.'
9. Click 'Internet Options.' Click the tab labeled 'Programs.'
10. Select your e-mail program under 'E-mail.' Click 'OK.'

Read more ►

How to Create a Summary Workbook Excel 2003

1. Type a description of the information that you want to display as a summary on Sheet 1. If you have an income statement on Sheet 2, for example, you can type 'Net Income' in cell A1.
2. Go to the cell you want to show on your summary page. In the above example, you would go to the cell that has net income on Sheet 2.
3. Click 'Copy.'
4. Go back to the summary sheet and click where you want to display the link. In the example, go to cell A2.
5. Click 'Paste' then 'Paste Special.' Choose 'Paste Link.'
6. Repeat these steps for all of the information that you want to include on your summary page.

Read more ►

How to Subtract Dates in Microsoft Excel 2003

1. Start Microsoft Excel 2003 and open the workbook containing the dates you would like to subtract.
2. Click an empty cell where you would like the results to appear.
3. Identify the cells containing the start date (for example, A2) and end date (for example, B2). Type without quotes: '=B2-A2' and substitute your actual cell references for the examples used here. Press Enter.

Read more ►

How to Calculate Weeks Days Between Two Given Dates in Excel 2003

1. Left-click on the cell you want the result to formulate within.
2. Type in the formula in the cell you selected in step 1 to calculate the amount of days. For example, if the two dates are A2 ('6/9/2004') and A3 ('8/21/2004'), you would subtract A3 from A2 to find out the days and weeks in between. The formula would look like this: ' =(A3 - A2) '
3. Press the 'Enter' key on your keyboard to formulate the result. For example, if you enter the formula using the data in step 2, you would end up with 72 days between the two dates.
4. Select another cell where you wish to input your formula to convert the number of days to weeks.
5. Enter the formula into the newly selected cell beginning with the ' = ' sign and clicking on the cell you selected in step 1. Add a '/' sign followed by the number '7.' This will take the result you found in step 1 and divide it by 7 to find the amount of weeks between the two dates.
6. Press the 'Enter' key on your keyboard to find the number of weeks between the two dates.

Read more ►

How to Change From Sum to Count in a Pivot Table

1. Open the PivotTable report in your Microsoft Excel application. Click any cell in the data area so that it’s highlighted.
2. Click the “Field Settings” option on the PivotTable toolbar. The “Sum” option will be selected by default in the PivotTable Field dialog box.
3. Click the “Count” option in the “Summarize by” box. The data will now be summarized by 'count' instead of 'sum.'
4. Click the “Options” button to change any other field settings, such as adding a custom calculation.
5. Click the “OK” button in the PivotTable Field dialog box to save all of your changes.

Read more ►