Tools for Information Literacy

Pivot table practice

According to Microsoft

A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data.

Another observer put it even more succinctly.

Pivot tables are summaries of your data based on categories that you select.

Still using your downloaded workbook ...

Create a Pivot Table from the 13-pivot table data Worksheet

The 13-pivot table data worksheet is a good candidate for a PivotTable.

• There are a lot of text and categories in this sheet, and it would be clunky to use sorts and filters to summarize.
• It is possible that the text has at least some redundancy and errors.
• There are a lot of blank cells as well.

When you create PivotTables, you do not want the data to be messy, and you would want to first "clean" the data.

• However, cleaning data is a big job in itself, so we will instead use Pivot tables to help us identify some of the problems in this worksheet, if there are any.

Step by step ...

1. In the 13-pivot table data worksheet, select the data in columns A through R, and rows 1 through 204.
2. On the ribbon, select Insert, and then click the PivotTable Button. A create PivotTable dialog box will open. Verify that the Table/Range shown in the Select a table or range Table/Range box is '13-pivot table data'!$A$1:$R$204
3. Verify that New Worksheet is selected and click okay. A new sheet will be created. Rename the sheet 14-PivotTable Report.

Verify that you now have a blank PivotTable report located in the cells area on the left and that you have a Pivot Table Fields pane on the right.

back to top

Set up the data in your Pivot Table

1. Inside of the Pivot Table Fields Pane, click the TOA Amount box at the top of the box. This will add TOA Amount to the value area and also to your pivot table.
2. Since TOA Amount is a column of dollar values, it is useful to SUM it.
3. Next, drag the Budget Activity Short Title field into the Rows Area.
4. Then drag the Organization into the rows area above the Budget Activity Short Title. If you inadvertently drag it below, you can then drag it above after it is in the area.
5. Finally, drag the State/Country Title into the Rows Area into the third position. Here is what you should see now:

You now have a nice summary of important parts of your data.

back to top

Add a filter

1. Drag Facility Category Title field into the Filters Area. Notice that you now have a filter added to the top of your Pivot Table Titled Facility Category Title.
2. If you were cleaning this data, you would need to go back to the data and correct this. It is important to understand that your pivot table will not dynamically update this correction. Any time you must go change source data, you will need to refresh your PivotTable, just like you would refresh a web page to see the correction.
3. To finish this task, scroll down to the last field in the data and choose Medical Centers & Hospitals. Your Pivot Table will now give you a report of all of the records that have Medical Centers & Hospitals in the Facility Category Title field.

back to top