Rapid Analysis Using Microsoft Excel Pivot Tables

Pivot tables are a way to analyze large amounts of data that’s been downloaded into or created in Excel. The best attributes of the Excel pivot table function are the speed of the analysis, and the flexibility to choose specific choices of data to zero in on the analysis and flip back and forth between possible options. Microsoft has, through numerous iterations of Excel, making pivot tables very easy and intuitive.

Preparing Your Data

Excel arranges your information in rows and columns, and it’s important that no blank rows or blank columns exist in your data–they inhibit the ability for the pivot table function to see the entire dataset. A small amount of data cleanup may also be necessary for your successful use of pivot tables: Each column heading should be unique, and column headings should be formatted differently than the data in the column. As an example, you can simply add bold to your headings to make it easy for Excel to read them correctly as headers rather than more data.

Building a Pivot Table

SELECT PIVOT TABLE: Position your cursor anywhere in the body or the headers of your data. When you select the Insert ribbon of your toolbar in Excel, the first icon at left is Pivot Table. While there may be some helpful suggestions from the Recommended Pivot Table icon functionality, learning how to construct one from the beginning will help you create more and better pivot tables in the future.
VERIFY RANGE: When you select the Pivot Table function, you’ll be shown the range that Excel assumes you are attempting to analyze. Confirm that range before moving forward, to make sure a hidden blank row or column isn’t keeping the software from reading all your data. Once you’re satisfied with that, select New Worksheet, as this will allow you to analyze your data without modifying it accidentally
FIELD SELECTIONS: In the new worksheet, at the top left, you will see the placeholder build box that shows where your pivot table will begin. On the right-hand side of the worksheet, you will have all the possible fields available to choose from for your analysis. These field names are the same as your column names in your main worksheet, and the first one is the header for your row names.
BUILDING THE PIVOT TABLE: Begin by choosing the first checkbox in the pivot table fields list. That header will show up in the Rows box at the bottom of the fields function table, and simultaneously show all the unique fields of row headers in your placeholder box at left, which has now turned into a one-column table.
SUMMING: Go to your field list at right once more and select a numerical column that you need to sum. It will place these name of these summed values associated with those rows in the Values box below, and the summed amounts in the column next to their appropriate field names in the pivot table at top left.
FORMATTING: Pivot tables do not bring formatting with them, such as currency formatting. In order to add back in appropriate formatting, simply right click on any of the items in the column, and select format cells or number format to select your required formatting. This will change both the cell you’re in and all the cells in that column to your selected new format.
COMPARE AND SUMMARIZE: When there are specific sets of data that you wish to compare or summarize, if you select that field in the field list, you will see it show up in the rows box by default. This will generate a subtotal for each that you will see in the pivot table. If, for instance, you’ve generated a regional sum and wish to break it down into what the dollar amount of sales for each specialty item is, as well, that action will generate the subtotals you’re looking for. If you would like to compare one specialty item to another within that region, you can drag and drop the item header from the Rows box in the Pivot Table Fields window to the Columns box. The iterations for comparing and summarizing in your pivot table are limited only by the number of fields you’re working with

Tips, Tricks and Shortcuts for Pivot Tables

Pivot tables don’t refresh formula results automatically. Changes to the data will not reflect in the pivot table. Whenever you plan to analyze your data, use Refresh or Refresh All in the Pivot Table Analyzer ribbon to ensure that Excel is reflecting any data changes.
If you are creating pivot tables for a document that is continually updated, with new rows added on a regular basis, for instance, Excel will not necessarily change your pivot table to reflect the new information. You can do this by selecting Change Data Source in the Pivot Table Analyze ribbon and changing the current source to the new range.
If the need to drill down on a specific sum in the pivot table is required, a double click on it in the pivot table will produce a new worksheet with a table that shows all row data that were summarized up to the aggregate sum.
If you want to make subtotals in your pivot table hidden but available at will, you can select on your subtotal row label that has a minus sign beside it. In the Pivot Table Analyze ribbon, you will find a Collapse Field button that hide all the subtotals of that particular field throughout the pivot table. To get them back, you will find an Expand Field button just above it.
At the far right hand of your ribbon when you’re in your pivot table, you will see the Design ribbon. There you can find colorful templates, as well as a myriad of options for the report layout for your pivot table.

The Power of Filters and Sorting

One of the most powerful attributes of data manipulation that reside in pivot tables includes filters that can be used to show very specific information of your choosing. Any item which shows a dropdown arrow by the text in the table enables you to filter on that information in order to pinpoint a time span, a region, or other information of your choosing. You can also filter using the Filter box within the Pivot Table Field window by dragging and dropping the field you wish to use to filter the table.

For more information to help your organization simplify tomorrow, contact us today.