PivotTable Step-by-step Guide: Quick & Efficient Data Organization

January 17, 2014 Excel  No comments

PivotTables are used to summarize data in a visual way, so people know what is going on with their numbers. It’s a good way to organize large lists of data, so you can understand the data better and produces reports about it. They save you time and energy and ensure accuracy with automatic calculations. Follow these steps as it takes you from beginning to end of the PivotTable process.

Set Up/Build a PivotTable

First you will need to organize your data: you can’t have any blank rows or columns, the columns and rows need to be labeled with unique names, and automatic subtotals and filters need to be removed. Once your data is organized, you can create your PivotTable. To do this, highlight the data that you want included, then go to the “Insert” tab and in the “Tables Command Group” click on “PivotTable.” A dialogue box will appear and you can verify that you selected the appropriate data. You will be moved to the new sheet tab where the PivotTable will be on the left and the “Field List” will be on the right. You can then drag your fields to one of the four areas: Report Filter, Column Labels, Row Labels, and Values. If the field is a text or date field, it’s usually placed in the Row Labels or Column Labels areas. If the field is numeric data, it’s usually place in the Values area. The Column Labels will display as a column across the page and the Row Labels will display as rows down the page. The Report Filter acts as a filter for the whole PivotTable and is displayed in the top two left columns. The Values will be calculated automatically as a summation for numeric values, but for text values, it will automatically be summarized with a count calculation. To view your data in different ways, move the fields among the 4 areas in the “Field List” and the table will change automatically. You do not have to have a field in each area. Also, changing the data source doesn’t automatically update the PivotTable. To refresh the PivotTable use Alt + F5 (refreshes a single PivotTable) or Alt + Ctrl +F5 (refreshes all PivotTables in a workbook).

Manipulate the Date

With PivotTables, readability is key, so you want to make your data look good. Once you have the PivotTable structure in place, you can manipulate the data by formatting, sorting, and filtering it. If you want to show less data in the PivotTable, you can drag a field out of the area section or uncheck it. You can add or remove them as you want.

To format your PivotTable, right-click on any number and select “Format Cells” and then select the “Number” tab on the left. From there you can set the commas or decimals to make the PivotTable easier to read. The format you choose will be applied to all the numbers on the Pivot Table.

Sorting data in the PivotTable can make it easier to understand. Go to the “Options” tab, then click on the “Sort” button. For numeric values, you can sort from largest to smallest or smallest to largest; for text values you can sort alphabetically; and for dates you can sort from oldest to newest or newest to oldest. But, make sure to always double check sorted data to ensure that the information is moving with its source.

When you use the filter, you can view specific data. By narrowing down the visible data, it’s easier for the reader to focus on the important data that you want them to focus on. To use a filter on a row or column, click on the drop-down arrow next to the Row or Column Labels cell, from there you’ll be able to uncheck the boxes of the data that you don’t want shown on the PivotTable. To use the value filter, click on the drop-down arrow next to the Value cell, go down to “Value Filters” and you’ll be able to filter by greater/less than a certain value, top 10, equal, etc.

Report on the Data

When reporting data, there are a few options you can choose from including drill down, automatic reports, duplicating PivotTables, and PivotTable web. Sometimes, it might be helpful to drill down into the PivotTable to show the numbers that make up the summary numbers. To do this, double click on the value that you want to drill down on. Excel will put a duplicate of the source data that was used to create that value on a new worksheet. Another way to do this is to right click on the value and select “Show Details.” If you want to see the details behind the whole PivotTable, double-click on the “Grand Total” and the data source will be duplicated on a new worksheet.

With automatic reports, a formatted PivotTable is created on a separate sheet tab for each category of data in the Report Filter area. To do this, click on the “Options” tab, then in the PivotTabe Commmand Group, click on the “Options” drop-down arrow, and click on “Show Report Filter Pages.” This will create multiple reports automatically.

For reporting, duplicate PivotTables can be used to display the data in a combination of different ways. When you copy and paste a PivotTable, the duplicate holds a copy of the source data, so it can be used to create different views of the data. There are several ways to do this, first highlight the PivotTable, then right-click and select “Copy” or type “Ctrl” + “C” and the data will be copied. To paste, go to a new worksheet, click on a cell, then right-click and select “Paste” or “Ctrl + “P.” To produce a static copy of the PivotTable (one that doesn’t hold the source data), when pasting, right-click and select “Paste Special” and choose “Values Only.”

To share your PivotTable as a web page, go to “File,” then select “Save As.” In the dialog box, under “Save as type” select “Web Page” and click “Publish.”

Another way to customize your report is with report layouts. To do this, go to the “Design” tab and from the “Layout Command Group” select “Report Layout.” From the drop-down menu, you can choose from the following options: Compact Form (reduces space), Outline Form (indents), and Tabular Form (spreadsheet-like).

We here at Desktop Training Academy hope you found this PivotTable step-by-step guide useful. If you’re looking for more in-depth, hands-on PivotTable Training take a look at our upcoming live PivotTables Training:

PivotTables Basics

PivotTables Intermediate

PivotTables Advanced

For true Excel mastery, you can also look at the Excel Certification.

Have an excel question or comment? Leave your comments below.

Like DTA? Subscribe to our blog to receive weekly updates and special offers.

Spread the word and share the knowledge with our social share options below!

Vince – Desktop Training Academy

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>