Instructions for Creating an Excel Pivot Table - Using Excel 2010

The Excel Pivot Table facilitates data analysis through organization of information contained in an Excel spreadsheet.   Following are instructions for creating a Pivot Table.

Step 1:  Download or enter data into an Excel Spreadsheet and Save.

For example:  We ran a Trial Balance query for all funds and downloaded the results into an Excel spreadsheet.  If you block out the data to be included in the Pivot Table first, it will self populate in the following screen.  Otherwise, you will need to choose it in the following second screen.

Step 2:  Go into Insert → PivotTable.

Step 3:  Verify that the cells selected include all cells with data, and then Click “OK”.

Step 4:  This is the classic view and may not be the one that Excel 2007 brings up.

 

If you do not see this, click anywhere in the box and then right click and go to Pivot Table Options.

 

 

Select Display and then Classic display.  It is easier to select the fields.

Step 5:  Construct the Table

              For this example, Drag:

                   “Account” to Row Labels

                   “Fin Stat Code” to Row Labels

                   “Ledger” to Column Labels

“Sum Total Amt” to Values

 

Step 6: Deselecting Subtotals and Formatting Numbers

To deselect all subtotals click a cell inside the pivot table, then at the top of the screen a read tab will appear call “PivotTable Tools” then click on the “Design” tab right below it.

 

To the left of the screen, click on the “Subtotals” box and then in the dropdown menu click on the “Do Not Show Subtotals” button.

To format the number data, click in the data section of the pivot table. Then click on the “Options” tab at the top of the screen.

 

In the “Active Field” section, click on the “Field Settings” button, and the “Value Field Settings” window will open.

At the bottom of the window click on the “Numer Format” button and the “Format Cells” window will open.

 

In the “Category” section click on the selection for “Number” and then in the section to the right click on the box for “Use 1000 Separator (,)” and then click on the last number selection.

Then click OK on the “Format Cells” window and click OK on the “Value Field Settings” window.

7. Your pivot table is complete.

To change the pivot table, click inside one of the cells.  At the top of your screen a red tab should appear along with the “pivot table field list”.  “Design” is where to change it.