How to Figure Percentage Using Excel

1. Click the 'Start' button on the taskbar, point to 'All Programs' and then click 'Microsoft Excel.'
2. Click in an empty cell on the spreadsheet document in which you want the percentage value to be displayed (for example, B12).
3. Type '=(total_value/number_of_values)*100' in cell B12 to get the percentage. Here, replace total_value with the total sum of the numbers whose percentage you want to calculate and number_of_values with the number of individual values. For example, to calculate the percentage of 12, 22, and 32, type '=(66/3)*100'. Here, 12 22 32=66 and there are 3 individual values (12,22,32).
4. Type '=((second_number-first_number)/ABS(first_number))*100' in an empty cell on the spreadsheet were you want to calculate the difference between two numbers as a percentage. Here, replace first_number with the first of the two numbers whose difference you want as percentage and second_number with the second number. For example, to calculate the difference between 32 and 42 as percentage, type '=((42-32)/ABS(32))*100' in the cell.

Read more ►

How to Adjust Page Breaks in Excel 2007

1. Open the worksheet you want to print.
2. Click 'View' then 'Page Break Preview.'
3. Click on the page break and drag the break to the place you want the break to be.

Read more ►

How to Set Two Print Areas Using an Excel 2007 Worksheet

Printer Method
1. Select your first desired print range by highlighting the cells within your worksheet; for example A1:H15.
2. Hold down the 'Ctrl' key while highlighting the second set of cells to print--for example N5:V15--resulting in two separate highlighted areas.
3. Select the 'Page Layout' tab, click on 'Print Area' and select 'Set Print Area.'
4. Press 'Ctrl P,' a shortcut that brings up the Print Dialog Box.
5. Verify the output printer displayed is the correct printer and click the 'Properties' button.
6. Look through your different tabs and options for a setting asking you to specify how many pages per sheet you want to print and change it from '1 page per sheet' to '2 pages per sheet.' These options vary by printer based on the make and model and may not be available on your printer.
7. Click the 'OK' button to close the properties box and return to the Print Dialog Box. If your printer does not have this capability, use the camera method.
Camera Method
8. Right-click on the tab of your current sheet ('Sheet1' by default) located at the bottom of the page and select 'Insert' from the pop-up menu. Click on 'Worksheet' and then click 'OK' to insert a new blank sheet into your document. Click the Office button in the upper left-hand corner of Excel and select 'Excel Options.'
9. Choose 'Customize' from the menu on the left and click the drop-down menu under 'Choose commands from' to change the selection from 'Popular Commands' to 'All Commands.' Scroll through the options until you find 'Camera;' click on it and choose 'Add' to add it to the quick-access ribbon at the top of the page. Click 'OK' to save your changes.
10. Return to the sheet where the data you need to print is located. Select the first range of cells to be printed and then click the Camera tool in the quick access ribbon at the top of the page. Switch to your blank worksheet and click to select where you want the upper left-hand corner of your image placed. The new sheet will contain the selected range as a graphic.

Read more ►

How to Insert the Same Drop

1. Enter the list items in a column on the spreadsheet where you will not need to enter other data, such as on the far right or bottom of the page. Enter the items in the order you want them to appear in the list, one item per cell. Make sure there are no blank cells between the items.
2. Select all of the cells into which you want to insert the drop-down list. If the cells are contiguous in one row or column, you can select them by clicking the first cell and dragging your mouse down or across the row or column. If they are in noncontiguous cells, press and hold the 'Ctrl' key while you click on each cell into which you want to insert the drop-down list.
3. Go to the 'Data' menu in Excel 2003 and select 'Validation.' In Excel 2007 or 2010, go to the 'Data' tab, click the 'Data Validation' button. The 'Data Validation' dialog box will open.
4. Make sure you are on the 'Settings' tab. Select 'List' in the 'Allow' box. Click inside the 'Source' box and then select the range of cells containing the list you created in the first step.
5. Make sure 'In-cell Dropdown' is checked. Clear the 'Ignore Blank' check box if you want to require users to enter data into the cell. Click 'OK' to insert the drop-down list into all of the selected cells.

Read more ►

How to Center in a Row on Excel for Microsoft 2003

1. Select the row of text that you want to center by left clicking on the first cell and then dragging the cursor to the right.
2. Right-click and then select 'Format Cells' from the pop-up window.
3. Select the 'Alignment' tab.
4. Click on the arrow next to the 'Horizontal' or 'Vertical' text alignment tab. If you want the text centered both horizontally and vertically, then choose both options.
5. Click on 'Center Across Selection.'
6. Click on 'OK.'

Read more ►

How to Create Free Receipts

1. Open a Microsoft Excel spreadsheet. Select 'New'. Select a free sales receipt template from the list of available spreadsheet templates.
2. Fill in or delete the specified fields for company information, such as company name, address, company logo, and receipt number.
3. Enter in the sales information for each item, which includes quantity, item number, item description, and the unit price.
4. Enter a value in the discount cell if a discount applies and the discount is a flat number. If the discount is in the form or a percentage off, then enter '=xy*z' in the cell where x = the column letter of the item, y = the row number of the item, and z = the percentage off in the form of a decimal (e.g. 20 percent is 0.20).
5. Enter the sales tax in decimal form (e.g. 8 percent is 0.08) into the sales tax cell on the sales receipt.

Read more ►

How to Convert Numbers Into Letters in MS Excel

1. Open your spreadsheet in Microsoft Excel.
2. Click 'Tools' in the top menu bar, select 'Macro,' and choose the 'Visual Basic Editor' option. If you use Excel 2007 or later, click the 'Developer' tab and click the 'Visual Basic' button instead.
3. Click 'Insert' in the top menu bar of the Visual Basic Editor and select 'Module.'
4. Copy and paste all of the code shown below:Option ExplicitFunction SpellNumber(ByVal MyNumber)Dim Dollars, Cents, TempDim DecimalPlace, CountReDim Place(9) As StringPlace(2) = ' Thousand 'Place(3) = ' Million 'Place(4) = ' Billion 'Place(5) = ' Trillion 'MyNumber = Trim(Str(MyNumber))DecimalPlace = InStr(MyNumber, '.')If DecimalPlace > 0 ThenCents = GetTens(Left(Mid(MyNumber, DecimalPlace 1) _'00', 2))MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))End IfCount = 1Do While MyNumber
''Temp = GetHundreds(Right(MyNumber, 3))If Temp
'' Then Dollars = Temp Place(Count) DollarsIf Len(MyNumber) > 3 ThenMyNumber = Left(MyNumber, Len(MyNumber) - 3)ElseMyNumber = ''End IfCount = Count 1LoopSelect Case DollarsCase ''Dollars = 'No Dollars'Case 'One'Dollars = 'One Dollar'Case ElseDollars = Dollars ' Dollars'End SelectSelect Case CentsCase ''Cents = ' and No Cents'Case 'One'Cents = ' and One Cent'Case ElseCents = ' and ' Cents ' Cents'End SelectSpellNumber = Dollars CentsEnd FunctionFunction GetHundreds(ByVal MyNumber)Dim Result As StringIf Val(MyNumber) = 0 Then Exit FunctionMyNumber = Right('000' MyNumber, 3)If Mid(MyNumber, 1, 1)
'0' ThenResult = GetDigit(Mid(MyNumber, 1, 1)) ' Hundred 'End IfIf Mid(MyNumber, 2, 1)
'0' ThenResult = Result GetTens(Mid(MyNumber, 2))ElseResult = Result GetDigit(Mid(MyNumber, 3))End IfGetHundreds = ResultEnd FunctionFunction GetTens(TensText)Dim Result As StringResult = ''If Val(Left(TensText, 1)) = 1 ThenSelect Case Val(TensText)Case 10: Result = 'Ten'Case 11: Result = 'Eleven'Case 12: Result = 'Twelve'Case 13: Result = 'Thirteen'Case 14: Result = 'Fourteen'Case 15: Result = 'Fifteen'Case 16: Result = 'Sixteen'Case 17: Result = 'Seventeen'Case 18: Result = 'Eighteen'Case 19: Result = 'Nineteen'Case ElseEnd SelectElseSelect Case Val(Left(TensText, 1))Case 2: Result = 'Twenty 'Case 3: Result = 'Thirty 'Case 4: Result = 'Forty 'Case 5: Result = 'Fifty 'Case 6: Result = 'Sixty 'Case 7: Result = 'Seventy 'Case 8: Result = 'Eighty 'Case 9: Result = 'Ninety 'Case ElseEnd SelectResult = Result GetDigit _(Right(TensText, 1))End IfGetTens = ResultEnd FunctionFunction GetDigit(Digit)Select Case Val(Digit)Case 1: GetDigit = 'One'Case 2: GetDigit = 'Two'Case 3: GetDigit = 'Three'Case 4: GetDigit = 'Four'Case 5: GetDigit = 'Five'Case 6: GetDigit = 'Six'Case 7: GetDigit = 'Seven'Case 8: GetDigit = 'Eight'Case 9: GetDigit = 'Nine'Case Else: GetDigit = ''End SelectEnd Function
5. Return to your spreadsheet and single-click the cell containing a number you want converted into text.
6. Type '=SpellNumber(X)' (without quotation marks) into the cell, replacing 'X' with the number originally contained in the cell. You can also refer to the value of another cell, instead of a specific number (e.g., '=SpellNumber(B5)' to use the value in cell B5).
7. Press the 'Enter' key to convert the number value into letters. If you entered '7' in the previous step, for example, the value in the cell now displays as 'Seven.'

Read more ►

How to Make a Frequency Chart

1.
Create a dataset of student grades; include student names and the grades 50, 60, 70, 80, 90 and 100. Enter or sort the grades in ascending order.
2.
Create a bin range of 50, 60, 70, 80, 90 and 100 and enter or sort it in ascending order.
3.
Go to 'Data Analysis.' In older versions of Excel, go to 'Tools,' 'Data Analysis.'
4.
Click on 'Histogram,' then click 'OK.' A frequency chart is also considered a Histogram.
5.
Select the 'Input Range' of the histogram. Click on the box beside the field.
6.
Highlight the 'Input Range' which is the 'Grade' column. Click on the box again to return to the histogram dialog box.
7.
Select the 'Bin Range' of the histogram. Click on the box beside the field and highlight the 'Bin Range' column.
8.
Click on the box beside the field to return to the 'Histogram' dialog box.
9.
Click on the 'Labels' check-box and the 'Output Range' button. The Labels check-box tells Excel that there are labels in the dataset. The Output Range tells Excel where to place that information that will feed the frequency chart or histogram.
10. Select the 'Output Range' using the same steps as the selection of the Input and Bin Ranges. However, select only one cell. In this case, cell E14 was selected. Return to the 'Histogram' dialog box.
11. Click the 'Chart Output' box which will generate the frequency (histogram) chart.
12. Click 'OK' to generate the frequency chart.

Read more ►

How to Turn Off Calculations in Excel 2010

1. Open Microsoft Excel and click 'Formulas' in the Ribbon at the top of the Excel window.
2. Click 'Calculation Options' on the far right side.
3. Select 'Manual' to disable auto-calculation. If you want Excel to recalculate your spreadsheet while auto-calculation is disabled, you will need to click 'Calculate Now,' also located under 'Calculation Options.' When you are ready, you can re-enable auto-calculation.

Read more ►

How to Do a Scattergraph in Excel 2003

1. Click 'Start,' then 'All Programs,' then 'Microsoft Office,' then 'Microsoft Excel 2003.'
2. Click 'File,' then 'Open.' Locate the Excel document that contains the data in it which you wish to make a scatter chart from.
3. Highlight the cells that contain the data.
4. Click 'Insert,' then 'Chart' to launch the Insert Chart wizard.
5. Select 'XY (Scatter)' foam the list of options then click 'Next.' You can 'Press and Hold to View Sample' to see what the graph will look like. Click 'Next' then name the axes and the chart. Click 'Finish' when you have finished creating the graph.

Read more ►

How to Do a VLOOKUP With Two Spreadsheets in Excel

1. Select the cell in which you want the returned value.
2. Click on the formula bar, the long empty box at the top of the screen.
3. Type =VLOOKUP(“text you want to match”,Be sure to include the double quotes.
4. Click the tab of the other spreadsheet, the one holding the searchable table.
5. Highlight the table.
6. Click back to the original spreadsheet. Now the location will be in the VLOOKUP function.
7. Click the formula bar again, add a comma after the table location and type in which column you want the value returned from. For example, if you want to return two columns to the right of the search column, type 3.
8. Type a closing parenthesis and press “Enter.”

Read more ►

How to Add Radio Buttons to Microsoft Excel for Data Collection

1. Define the mutually exclusive data to be collected. For example, if the spreadsheet is used to track clothing by size, the option buttons might be for small, medium and large sizes. This prevents mistyping information.Select the 'Developer' tab; from the 'Controls group,' press 'Insert > Option Button.' Place the cursor into the position where the upper left corner of the button and label are desired, and then drag a square, creating the frame. Do the same for the remaining option buttons to be created. Excel will name these 'Option Button 1,' '2,' and '3.'
2. Select 'Option Button 1,' and in the 'Developer' tab, 'Controls' group, select 'Properties.' A new window opens allowing the option button to be customized. From this pane, the font, color, status, control, caption and important functions can be controlled. Define the caption, select fonts, colors and size, and then create the functions for the button. Do the same for the remaining option buttons to be grouped.
3. Select the option buttons. Using the 'Drawing Tools>Format' tab in the 'Align' group, use the 'Align' button to position the option buttons as desired, then use the 'Group' button to create the Option Button Group.
4. Add the appropriate commands and cell links as needed for the option buttons, and then protect the cells over which the buttons are located. When the spreadsheet is completed, protect the Worksheet in the 'Review' tab, 'Changes' group, and the option buttons will be functional.

Read more ►

How to Insert Auto Numbering in a Header for Excel 2003

1. Run the Microsoft Excel 2003 application, and open the workbook that you want to edit.
2. Click 'View' in the menu bar, and then click 'Header and Footer' in the drop-down list.
3. Click the 'Custom Header' button.
4. Click the section of the header that you would like to add automatic page numbering to.
5. Click the '#' icon to add automatic numbering to the specified section.
6. Type any additional text that you want to display in the header, and then click 'OK' twice to save your header changes.

Read more ►

How to Use Multiple Regression in Excel

Excel for Multiple Regression
1. Enter the data you will use to conduct your regression analysis into an Excel spreadsheet. You can enter the data by hand or import a data file from another source, such as an ASCII file or another spreadsheet, into Excel.
2. Unlock the Data Analysis tool from the add-ins menu and install it. Open Excel, click “Tools” and select “add-ins” from the drop-down menu that appears. A smaller window opens that displays a set of options. Check the box next to “Analysis ToolPak” and click “OK.” The Data Analysis option appears in your Tools menu, ready for use. If you are using Excel 2007, you can access the Data Analysis add-in by clicking the Microsoft Office button in the top left corner of an open Excel workbook. Click the button and then click “Excel Options.” A new window opens, displaying a set of options on the left side. Choose “Add-ins,” select “Analysis ToolPak” and click “OK.”
3. Click the Tools menu in Excel and select Data Analysis (in Excel 2007, click the “Data” tab and click the Data Analysis button). A window opens that displays a menu of analysis tools. Scroll to “Regression” and click “OK.”
4. Enter the values for dependent variable (Y) and independent variables (X) by clicking on the applicable cells and columns in your Excel data sheet. After selecting the ranges of data to be entered for analysis, click 'OK.' Excel runs the procedure and display your results on a new worksheet.
5. Examine your summary output, starting with the regression statistics at the top of your output. Note the value of R-square, which tells you what percentage of the variability in the dependent variable (for example, average salaries) is explained by your regression model. Then note the values of the coefficients and corresponding t-statistics and significance levels. A t-statistic of 2 or greater indicates statistical significance, meaning that the relationship between that independent variable and the dependent variable is likely not due to random chance.

Read more ►

How to Compare Two Columns in Excel 2007

1. Activate Excel and open a new worksheet. Set up three different columns entitled 'A,' 'B' and 'C'. Allow for five data entries within each column so that you are working with a 3x5 grid.
2. Enter the data for the two known columns in 'A' and 'C'. Leave column 'B' blank for the time being, and make sure not to enter zeros in the 'B' column. Column 'B' needs to remain blank because this is where the formula will be entered and function.
3. Select the first cell in column 'B' and enter the following formula therein; =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),'',A1)
4. Navigate to 'Fill' from the 'Edit' menu and click 'Down'. The duplicate data will appear in column 'B.' Rinse and repeat as needed with new data sets.

Read more ►