How to Add a Footer in Excel 2007

1. Click the 'Insert' tab in Microsoft Excel.
2. Click 'Header Footer' in the 'Text' group.
3. Click the bottom scroll arrow until 'Click to add footer' is visible and click it.
4. Type the text you wish to display. To enter dynamic elements, click the appropriate button in the 'Header Footer Elements' group of the current Design tab. Available elements include 'Page Number,' 'Number of Pages,' 'Current Data,' 'Current Time,' 'File Path,' 'File Name,' 'Sheet Name' and 'Picture,' which allows you to add a picture to the footer.
5. Click the 'Home' tab to access text formatting features of the 'Font' and 'Alignment' groups, such as font type, font size, bold, underline, colors and centering.
6. Click the main spreadsheet window to exit the footer.
7. Click the 'View' tab and click 'Normal' to return to the normal view mode.

Read more ►

How to Make Negative Numbers = 0 in Excel

In Formulas
1. Write a formula to evaluate data. For example, if you're subtracting A1 from B1 and showing the results in C1, type '=(B1-A1)', minus the quotes, into cell C1.
2. Add 'MAX' to the formula to designate the maximum answer allowed: '=MAX(B1-A1)'. This is only part of the formula; if you press 'Enter' at this point, you will get an error message.
3. Insert the amount for the maximum number allowed as the formula result -- in this case, zero: '=MAX(0,B1-A1)'. Any results that would normally be negative become 0, not just as display text, but as value. If the formula result of B1-A1 is -2, once you use the MAX function, it actually becomes zero, and if you add 5 to it, the result will be 5, not 3.
Formatting
4. Enter all data as usual, including negative numbers, or open a worksheet containing the data you want to use. Select all the cells you want to display only as positive numbers or zeros.
5. Click on the number format drop-down and choose 'More Number Formats....'
6. Choose 'Custom' in the left-hand pane.
7. Type '##;'0';0', without the outer quotation marks, into the field labeled 'Type:', overwriting any symbols already in that field. Click 'OK' to return to the spreadsheet, where all negative numbers will now display instead as '0' while retaining their actual values. If a cell value is -2, it will display as 0, but if you add 5 to it, it will become 3, not 5.

Read more ►

How to Create a Weekly 24 Hour Calendar With Excel

1. Open a blank worksheet in Excel. Select cells A1 through H1 by clicking on A1, holding down the mouse button and dragging the mouse over to H1. Go to the Cells section of the Home tab, click the 'Format' drop-down arrow and select 'Row Height.' Change the row height to '26.25' and click 'OK.'
2. Type the word 'Time' in cell A1. In cell B1, type 'Monday.' Click B1 to select it, then click on the fill handle, which is a small black square in the bottom right corner. Drag the fill handle across to cell H1 to fill with in the other days of the week.
3. Type '12:00 AM' in cell A2. Select A2 and grab the fill handle. Drag it down to cell A25 to fill each cell with an hour, giving you 24 hours on your calendar.
4. Select cells A2 through H25. Right-click and select 'Format Cells.' On the Alignment tab, select center for both vertical and horizontal. On the Border tab, select a thin or dotted line in the Line Style box, then select 'Outline' and 'Inside' in the Presets section. Click 'OK' to apply these changes.
5. Select cells A1 through H1. Right-click and choose 'Format Cells.' On the Alignment tab, select center for both vertical and horizontal. On the Font tab, change the font if you want, and select a larger, legible size (16 through 20 work well). On the Border tab, select the thickest line in the Line Style box, then select 'Outline' and 'Inside' in the Presets section. On the Fill tab, select a fill color or pattern. Click 'OK' to apply these changes.
6. Change the column width in cells A1 through H1 so that the text fits properly. Place your mouse on the line between the A and B column headers in the gray area just above the worksheet. When you see a thick line with arrows pointing right and left, double-click to adjust the column to the width of the text. Repeat with the remaining columns.

Read more ►

How to Insert Page Breaks on a Pivot Table

1. Open the Excel file that contains the pivot table you want to format. Then select 'View,' 'Toolbars' and 'PivotTable' from the toolbar to open the 'PivotTable' toolbar if it's hidden.
2. Select 'PivotTable' and 'Table Options' from the 'PivotTable' toolbar. The 'PivotTable Options' dialog box will open. Add a checkmark to 'Repeat item labels on each printed page' and to 'Set print titles.' Then click 'OK.'
3. Go to the 'Pivot Table Field List' task pane. You can open the task pane if it's closed by clicking on the 'Show Field List' button in the 'PivotTable' toolbar.
4. Customize how each page is categorized by selecting 'Page Area' from the 'Add To' drop-down list in the task pane. Select the field you want to use to separate each page. Then drag it from the task pane into the 'Page Area' in the top-left section of your table (by the 'Row Area').
5. Choose 'PivotTable,' 'Select' and 'Entire Table' from the 'PivotTable' toolbar. Then click on 'File,' 'Print Area' and 'Set Print Area' from the menu. This will set which area of the page will be printed.
6. Select 'File' and 'Page Setup' from the menu and click on the 'Sheet' tab. Click within in the 'Rows to repeat at top' text box. Then go to the worksheet (while the dialog box is open) and select the rows you want to repeat on each page. Click within the 'Columns to repeat at left' text box. Then go to the worksheet and select the columns you want to repeat. Click 'OK' to close the 'Page Setup' dialog box when you are finished.
7. Click on 'View' and 'Page Break Preview' in the toolbar to switch to page break view. The current page breaks in your pivot table report will be revealed at the blue lines. You can insert page breaks manually on your page or after each row type.
8. Insert a manual page break by right-clicking on any row where the page break will be added. Then select 'Insert Page Break' from the pop-up.
9. Insert a page break after each row type by double-clicking on the row type heading on your spreadsheet. The 'PivotTable Field' dialog box will open. Click on the 'Layout' button to open the 'PivotTable Field Layout' dialog box. Then add a check mark to 'Insert page break after each item' and click 'OK.'
10. Click on 'OK' to close the 'PivotTable Field' dialog box.
11. Click on 'View' and 'Normal' in the toolbar to return to normal view. Then save your changes.

Read more ►

How to Check for Circular References in Excel

1. Start Microsoft Excel 2007 and open a workbook from your files that contains formulas that aren't properly calculating a result.
2. Study the formula that isn't calculating a result. Check to see if a cell reference in the formula is referring to the cell that the formula resides in. If it is, then you have a circular reference.
3. Select the 'Formulas' tab to display the 'Formula' ribbon. This contains all the formula options for an Excel workbook.
4. Click the arrow to the right of the 'Error Checking' button in the 'Formula Auditing' section of the 'Formula' ribbon.
5. Point to 'Circular References' from the 'Error Checking' menu to display the list of formulas that contain a circular reference in your open Excel workbook. Click on the formula you want to fix, and it will become selected in the worksheet.
6. Correct the formula in the 'Formula Bar' of the Excel spreadsheet. You will need to either move the formula to another location or change the reference within the formula to a different cell reference.
7. Press the 'Enter' key to enter the new formula into the worksheet. Continue to check for and fix all of the circular references within the spreadsheet.

Read more ►

How to Get Document Recovery Task Pane in Excel 2007

1. Open Microsoft Excel 2007 on your computer and then select the 'Microsoft Office' button. Click the 'Open' button.
2. Select any Excel file and then click the 'Open' button. Press the 'Ctrl,' 'Alt' and 'Delete' keys at the same time to bring up the Task Manager.
3. Click the 'Applications' tab and then select the Excel 2007 file you have open. Click the 'End Task' option.
4. Open the Excel 2007 program again and the Document Recovery task pane will appear. Click the 'Recovered' button from the status bar to hide the Document Recovery task pane.
5. Click the 'Recovered' option again at any time to make the Document Recovery task pane reappear.

Read more ►

How to Display Statistical Worksheet Functions in Excel

1. Click the Microsoft 'Office' button in the top left corner of Excel.
2. Click the 'Excel Options' button.
3. Click 'Add-Ins' in the list. The window to the right will display all the inactive add-ins that you can add. You do not need to click any of these at this point.
4. Click 'Excel Add-Ins' under the Manage section at the bottom and click 'Go.' You will get a pop-up window with available add-ins.
5. Check the 'Analysis ToolPak' box and click 'OK.' If you get a message that it is not installed and are asked if you want to install it, click 'Yes' to install it.
6. Click the 'Data' tab on the ribbon to check whether you have the statistical add-in. You should now have an 'Analysis' group under the 'Data' tab.

Read more ►

How to Use Countif on Excel 2003

1. Open Excel 2003 and select a workbook. Click 'File' on the menu bar. Select 'Open.' Browse your computer file and locate the workbook. Click the workbook and select the 'Open' button. The workbook opens.
2. Click on a cell to enter the Countif function. Enter the following formula in the cell: '=Countif.' Notice the Excel hint that appears beneath the formula. Hints serve as a mini guide to assist you as you enter the formula. Click the 'Insert Function' button on the formula bar. The Function Arguments dialog box appears.
3. Click the box with the red arrow in the 'Range' field. The Function Arguments dialog box becomes minimized. Highlight the range that you want to count. When you stop highlighting, the Function Arguments dialog box reappears. Enter your criteria in the Criteria box. In this example, you will count all values that are greater than 1000. So to notate greater than, enter the '>' symbol. In this criteria, enter the following: '>1000.' Click 'OK.' Excel displays the results of the Countif function.

Read more ►

How to Create a Curve Graph in Excel or Word

1. Input your x-values into the first column, starting at cell A1.
2. Input your y-values into the second column, starting at cell B1.
3. Select 'Insert' from the toolbar and choose the 'Scatter' button. Choose 'Scatter With Smooth Lines' from the drop-down menu.
4. Select the chart by left clicking on it. Go up to the top toolbar and select 'Layout' underneath 'Chart Tools.' Select 'Primary Horizontal Axis' from the 'Axes' drop-down menu (press the down arrow beneath 'Axes' for the menu). Select 'More Options,' and change the minimum and maximum x-values by selecting the top two radio buttons. Enter a value that corresponds with your data. For example, if your spread of data is from -10 to 9.1, make your minimum x-value -10 and your maximum y-value 10.
5. Repeat Step 4 for the 'Primary Vertical Axis,' entering your y-values in place of the x-values.
6. Press close.
7. Select the graph by placing your cursor on it and left clicking on the mouse. Hit Ctrl C to copy the graph. Hit Ctrl P in Word to paste the graph into the document.

Read more ►

How to Remove a Password on Excel

Office 2003
1. Launch Excel from the desktop icon or the “Start” menu of your computer. Go to the “File” menu and select “Open.” Navigate to the folder that contains the password-protected Excel workbook and double-click the file to open it.
2. Enter the password that you created for the Excel workbook, when prompted. Press “Enter” to open the workbook.
3. Go to the “File” menu in Excel and choose “Save As.” Open the “Tools” menu in the “Save As” dialog box and select “General Options.”
4. Highlight the asterisks in the “Password to open” box. Press the “Delete” key and click “OK” to close the password box.
5. Click the “Save” button. Confirm that you want to replace the existing Excel workbook, and the file is saved with the password removed.
Office 2007
6. Launch Excel. Click on the “Open” folder icon, navigate to the file on the hard drive and double-click the file name to open it.
7. Type in the password that you set for the workbook and press “Enter.” The workbook should open on the screen.
8. Click on the button with the Microsoft Office icon in the upper-left corner. Hover over “Prepare” and choose “Encrypt document” from the options that appear.
9. Highlight the asterisks in the “Encrypt document” box that pops up. Press “Delete” and click “OK.”
10. Press the “Save” button at the top of the screen to save the file. Confirm that you want to write over the original file, if prompted, and the file is saved without the password.

Read more ►

How to Use Excel Formula Functions

1. Launch Microsoft Excel. You can begin with a blank document or open an existing document for which you want to use formula functions. If you do not have MS Excel, use the link in Resources for a free trial.
2. Populate the spreadsheet with the data appropriate to one or more of the Excel functions that you want to use. Click once inside the cell in which you want to use a formula. Click once on the 'Insert Function' button, which is represented by the symbol 'fx' and is located immediately to the left of the 'Formula Bar.' This will launch a separate window in which you may choose the appropriate formula.
3. Use the 'Category' drop-down menu on the 'Insert Function' window to choose from a list of formula types. This will narrow the results of available formulas, but you can also choose the 'All' listing to display all available function formulas. If you are not certain which formula to use, you can type a description into the 'Search' field and click once on the 'Go' button. Click once on the function that you want to use and once on the 'OK' button. This will usually launch a separate window, if further detail is required for Excel to run the function.
4. Enter the appropriate information into any of the required fields on the 'Function Arguments' window. The names and ranges of selected criteria will display to the right of any required field, and will be red if using the entry will result in an error, or green if accurate. The anticipated result of any formula will appear to the bottom of the 'Function Arguments' window. Once the appropriate criteria have been met, click the 'OK' button once. The result will appear in the selected field.

Read more ►

How to Unprotect Specific Areas in Excel

1. Launch Microsoft Excel 2010 and open the spreadsheet that's protected.
2. Click the 'Review' tab on the toolbar and click 'Unprotect Sheet' from the 'Changes' group. Enter the password if prompted.
3. Highlight the cell or cells that are to become unprotected.
4. Hold 'CTRL' and 'Shift' and press the letter 'F' on the keyboard and then release all of the keys to bring up the 'Format Cells' dialog box.
5. Click the 'Protection' tab and clear the check from the 'Locked' check box. Click 'OK' to save the changes. Repeat for additional cells or ranges as necessary.
6. Click the 'Review' tab and click 'Protect Sheet' under the 'Changes' group to protect the sheet.

Read more ►

How to Create Percentages in Microsoft Excel 2003

1. Write out your fraction. The numerator, or top number, will be the amount you want to determine as a percent of the total. The denominator, or bottom number, will be the total amount. For example, if you have 18 marbles, five red and 13 green, and you want to determine the percent of green marbles, then 13 is the numerator and 18 the denominator.
2. Type the numerator in cell A1 of your Excel spreadsheet. Type the denominator in cell B1. Using our example, you would type 13 in cell A1 and 18 in cell B1.
3. Type the following equation in cell C1 to get a percentage from your equation:=(A1/B1)*100

Read more ►

How Do I Print Out Row Numbers for My Excel Spreadsheet?

Microsoft Office Excel 2007
1. Select the worksheet whose row numbers you want to print. If you want to have row numbers print on multiple worksheets, you must repeat the process for each worksheet.
2. Click 'Page Layout' from the options across the top of the screen.
3. Locate the 'Sheet Options' subgroup, which will be the fourth subgroup from the left.
4. Click the box next to 'Print' under 'Headings' to turn on the row and column titles for printing for the worksheet.
Microsoft Office Excel 2003
5. Select the worksheet whose row numbers you want to print. If you want to have row numbers print on multiple worksheets, you must repeat the process for each worksheet.
6. Click 'File' from the menus at the top.
7. Select 'Page Setup' from the drop-down menu, and then click 'Sheet Tab.'
8. Check the box next to 'Row and Column Headings,' and then click 'OK.'

Read more ►

How to Use the CORREL Function in Microsoft Excel

1. Start Microsoft Excel. You can do this by clicking on Start, highlighting Programs, and clicking Microsoft Excel, or double-clicking the Microsoft Excel icon on your computer's desktop.
2. Before we work with data, it's important to understand an important fact about the correlation coefficient, the value that represents the strength of the relationship between two random variables. The correlation coefficient ranges from -1 to 1, with -1 indicating a perfect negative correlation and 1 indicating a perfect positive correlation.
3. Enter the data. I'll use cells A1 to A25 and B1 to B25 for this purpose. The A column represents each student's homework average and the B column refers to each student's exam average.
4. Enter the following values, starting with cell A1 and ending with cell A25: 89, 92, 88, 76, 90, 68, 100, 87, 93, 77, 81, 80, 94, 90, 83, 79, 73, 83, 91, 84, 88, 90, 93, 80, 91.
5. Now enter the following values, starting with cell B1 and ending with cell B25: 76, 83, 80, 84, 81, 90, 92, 78, 70, 93, 82, 90, 98, 75, 67, 72, 90, 82, 77, 81, 87, 63, 92, 71, 82.
6. Now we can calculate the correlation coefficient. In cell A27, type =CORREL(A1:A25, B1:B25). A1 to A25 and B1 to B25 is where the data appears. Hit ENTER.
7. The result is -.07965, indicating a very slight weak relationship between the two variables.

Read more ►