Sean Mannion

Senior Budget Analyst

Newton Public Schools

Pivot Tables – How To

  1. Highlight all of the data that you would like to include in your pivot table. Every column of data you would like to include in your pivot table must have a title or header in the first row.
  1. Click on “Data” in the top menu and then on “PivotTable and PivotChart Report…” For Excel 2010, Click on “Insert” and then “PivotTable”.
  1. The first screen to appear asks two questions. In almost all cases, click “Next.” For Excel 2010, there is only one screen.
  1. The second screen to appear will confirm the data range that you have selected. You have the option to manually enter a different data range at this point. In most cases, simply click “Next.”
  1. The third and last screen will ask you where you would like to have the Pivot Table located. In most cases, click “Finish”. This will create the pivot table in a new excel worksheet tab.
  1. You will now be on the Pivot Table worksheet tab. In the upper left-hand corner of the worksheet you will see the basic framework for the pivot table. On the right side or somewhere in the middle, you will see a “Pivot Table Field List”. The fields in this list correspond to the column headers of the original data you selected. These fields are now available for you to click and drag into the desired positions of the Pivot Table.
  1. Items that you would like to use as your “Data” should be dragged into the large middle section of the Pivot Table. Items that you would like to use as non-data items should be dragged into a Column, Row or Page position. Data items are fields which you would like to manipulate by some type of mathematic calculation. Non-data items simply list the various possibilities within a particular field.
  1. “Data” items can be edited by placing the cursor in either the row or column of the item you would like to edit and right clicking to select “Field Settings” or by selecting “Field Settings” from the Pivot Table toolbar (click on “PivotTable” and then “Field Settings”).
  1. Column, Row and Page items can be edited by either double clicking on the grey icon of the particular field, or by using the “Drop Down” arrow on the grey field icon to filter the selection.
  1. If items are added or changed within the original worksheet, the Pivot Table can be updated, or refreshed, at any time by clicking on the red “!” on the Pivot Table toolbar.
  1. A separate worksheet can be created to show the backup for any particular number in the Pivot Table by double clicking on that particular number.

Helpful Tips for Pivot Tables

  1. Don’t be afraid of “messing up” a Pivot Table by moving around a particular row or column. This is what Pivot Tables are made for. One table can be quickly customized into various report formats. The goal is to be comfortable and fluid in structuring one “Pivot” into various formats.
  1. If you have a particular Pivot Table you would like to save, but you do not want to save it attached to the original data, simply copy the whole Pivot Table worksheet and Paste it into another sheet or file as “Paste Special” and then “Values”. Repeat this and use “Paste Special” and “Formats” to save the formatting. Another way to do this is to “Move or Copy” the worksheet into a new workbook and then copy it and paste it as values.
  1. When dragging and dropping data fields into columns or rows, a small icon will appear next to the cursor that shows a miniature picture of the Pivot Table. In this picture, the area where the data field will be dropped will be colored blue.

Helpful Tips for Excel

  1. If you can say what you want Excel to do in a logical and concise way, Excel can do it.
  1. Don’t be afraid to use the “Help” function or Google. This is the best way to figure out how to execute a particular function or to solve a problem. Topics can be easily searched and are conveniently indexed.
  1. Try not to be lazy about looking for new ways to solve current problems. Investing extra time now in creating a new or more efficient spreadsheet model is well worth the time it will save you later.

2013 MASBO Annual Institute