Friday, January 27, 2012

How to Work With Pivot Tables in Excel 2003


1. Open the Excel 2003 file that contains the data you want to work with.
2. Ensure that each column of data has a header at the top. If you do not have a header row, you can right-click the number on the left side of your data's top row and choose 'Insert' from the pop-up menu. Type headers for each column into the new row. You also need to remove any subtotal cells from the middle of the data field, as the PivotTable can't handle these types of cells.
3. Click the 'Data' menu at the top of the screen and choose 'PivotTable and PivotChart Report.' This launches the PivotTable wizard. Select the 'Next' button on the first screen of wizard.
4. Click the small box next to the 'Range' field and the wizard will minimize. Select the data that you want to include in the PivotTable. Click the top-left cell of your desired data range, then hold down 'Shift' and click the bottom-right cell. Don't include any subtotal cells on the bottom or the right side of the data area. Click the small box again to bring the wizard back up, and choose 'Next.'
5. Choose between placing the PivotTable on a new worksheet, or an existing worksheet. If you choose to use an existing worksheet, you will have to select the cell where you want the PivotTable to start. The table will expand down and to the right of your selected cell. Click 'Finish' to complete the wizard and create your PivotTable. You will see an empty PivotTable along with a field list that contains the names of all your column headers.
6. Click an item in the field list, then drag and drop it to one of the four areas of the PivotTable. The first area sits just above the actual PivotTable and acts as a global filter for the data in the table. Then, there are the two areas that make up the rows and columns of the PivotTable. Finally, there is a large area in the middle of the PivotTable that comprises the main data for the table. Each area can have multiple fields, as they will just stack onto one another automatically.
7. Rearrange the PivotTable by clicking the name of the field in the PivotTable and dragging that name to a different area. You can also drag and drop the field name off the PivotTable to remove it.
8. Click the arrows next to any of the field names on the PivotTable to filter the report. Remove check marks from any item you want to remove from the table, or add check marks to bring any item back onto the table. By using this feature on the page field area above the PivotTable, you can filter the entire table based on the elements in this list.
9. Double-click any data or subtotal cell in the PivotTable to see a list of all the source data that Excel used to create that number. The list will appear on a new worksheet that Excel adds to the workbook.

Blogger news