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 ►

How to Multiply Using Excel 2007

1. Determine the cell references that will be multiplied together. The cell reference refers to the labels on the columns and rows where the cell is located and will include a letter and a number.
2. Choose the cell where the multiplication results will be returned by clicking within that cell.
3. Click in the Formula Bar (denoted by 'Fx' at the top of the screen). Type = , then the cell reference of the first cell, * and finally the cell reference of the second cell. A correct formula will appear as: =G10*X10, according to Home Learn.
4. Press 'Enter' on the keyboard to multiply the cells together. The correct answer will now appear in the cell.

Read more ►

How to Center Numbers in a Cell in Excel

1. Select the cell with the numbers you want to center.
2. Click the 'Home' tab.
3. Click the 'Center' button, located in the Alignment section.

Read more ►

How to Remove Duplicate Rows From an Excel Spreadsheet

1.
Click on the 'File' menu and then the 'Open' option to open the spreadsheet with the duplicate rows. The example highlights the duplicate rows for easier viewing.
2. Select the entire spreadsheet by clicking on the top left cell of the column header and then scrolling to the last row. Press the 'Shift' key and click on the lowest right cell with information. The rows are highlighted.
3.
Click on the 'Data' menu, 'Filter' and then 'Advanced Filter.' The Advanced Filter dialog box appears.
4. Choose 'Filter the List, In Place' and 'Unique Records Only.' Then click the 'OK' button. Excel hides the duplicated records.
5. With the filtered records still selected, press the 'Ctrl' and 'C' keys to copy the rows. The list is highlighted with bounding outlines.
6.
Click on the 'View' menu, 'Toolbars' and then 'Clipboard.' The Clipboard window appears with your copied data in the first icon.
7. Click on the 'Data' menu, 'Filter' and then 'Show All' to display the original list.
8. With the original list still selected, press the 'Delete' key. The list is deleted.
9.
Click the first icon in the 'Clipboard' window. The list appears with no duplicates. The example highlights the filtered rows for easy viewing.
10. Click the 'Save' button to save the spreadsheet.

Read more ►