Friday, October 28, 2011

How to Run a Pivot Table


1. Click and hold the mouse button over the top-left cell in the data that you want to create a table out of. Drag the mouse to the bottom-right cell of the data and release the mouse button.
2. Select the 'Insert' tab at the top of the screen. Then click the 'PivotTable' button located at the left side of the toolbar. Click 'OK' in the window that opens up. You will be taken to a new page that contains your PivotTable.
3. Place a check mark next to the fields in the field list that you want to compare. The field list is the window to the right of the Excel sheet. It contains one long space for your different fields, and four smaller ones that correspond to parts of the PivotTable. When you place the check marks, Excel will move the field into one of the smaller boxes and your table will start to take shape.
4. Drag and Drop the fields into the boxes where you want them. The 'Row Labels' box will put the data in a column on the left side of the PivotTable. The 'Column Labels' box will put the data in a row across the top. The 'Values' box will put the data into the middle of the PivotTable, sorting it based on the row and column labels. The 'Report Filter' box will allow you to filter the entire PivotTable by the fields in the box. Use the labels to set up your data, and put the data that you want to analyze in the 'Values' box.
5. Click on the field in the 'Value' list and choose 'Value Field Settings.' You can now change how the PivotTable will report the data form that field. It defaults to a count, essentially a '1' if there is data and a '0' if there isn't. You can change it to 'Sum,' where it will add the values together, or several other mathematical functions.
6. Use the 'Refresh' button in the toolbar if your original data has changed. Excel essentially takes a snapshot of the data when it creates the PivotTable. If you change the data, you have to refresh the PivotTable before you will see the changes. Likewise, if you need to change the columns in your original data field, press the 'Change Data Source' button and you can choose a new data set.
7. Click on the blue question mark in the upper-right corner to access the Microsoft Online Help for Excel 2010. From there you can learn about all the other features and functions of PivotTables. While the majority of what you will do in PivotTables will involve simply move fields around, as you get more advanced there are a ton of options to make more complicated tables.

Blogger news