Excel Advanced Pivot Table Techniques for Serious Data Analysts

Tip #1: Adjust Fields List Settings

The PivotTable Fields List is the drag-and-drop interface on the right side of Excel that allows you to populate the logic that drives the Pivot Table.

This panel can be customized in a variety of ways.  By clicking the Tools (gear) icon you can rearrange the positions of the field list and the corresponding drop zones.

Each of these arrangement options has its strengths and weaknesses.  Experiment with the options to see which fits your needs best.

You can also sort the items in the Field List either alphabetically or in Data Source (the order listed in the source table) order.  Alphabetically is best when working with unfamiliar data sets containing many fields.

A Search field is available for ease of locating fields.

Tip #2: Create Multiple Reports with One Click

One of the features new users to Pivot Tables soon learn is the ability to filter the Pivot Table by a “global” field.  In other words, filter the entire table by a single, overall criterion such as “Customer Name” or by “Department”.

Suppose you want to create a separate report for each item in the global filter zone?  Imagine performing the following steps:

1. Select the unfiltered Pivot Table
2. Copy the Pivot Table
3. Create a new worksheet
4. Paste the Pivot Table
5. Set the global filter to the desired field item
6. Return to the original, unfiltered Pivot Table
7. Repeat steps 1 thru 6

Now imagine performing those steps 200 times.  You would quickly tire of the process.

What if you could create every one of those filtered Pivot Table reports in just 1 click? (actually, 3-4 click, but who’s complaining?)

With the desired field placed in the Filters zone, select Pivot Table Analyze (tab) -> PivotTable (group) -> arrow next to the Options button -> “Show Report Filter Pages”.

This displays a dialog box listing all the Pivot Table’s global filters.

Select the appropriate field and click OK.  Then sit back and enjoy the show.

Imagine seeing 200 Pivot Tables appear before your eyes in a matter of seconds.  These would take hours (or days) to create manually.

Tip #3: Double-Click to View Details

This one is a “boss-pleaser” feature.  Suppose your boss examines the following pivot tables and asks this question:

“Show me the full details for every record that comprises the $209,280 (Lucas Basics – Europe).”

You could perform the following steps:

1. Return to the original data
2. Filter the “Company Name” field by “Lucas Basics”
3. Filter the “Region” by “Europe”
4. Select the results
5. Click Copy
6. Create a new worksheet
7. Paste the copied results into the new worksheet

…OR…

…you could just double-click in the number “$209,280” and call it a job well done.

This double-click action creates a new sheet and places the complete records from the table that contribute to the original value.

If this question was just a casual curiosity, you can delete the sheet without harm and you are back to where you were moments ago.

Tip #4: Add Data Bars to Pivot Tables

Data Bars are created by the Conditional Formatting tool and are a great way to create in-cell bar charts.

“But how do we create them in a Pivot Table?”

It’s not much different than creating Data Bars in a regular table but there is an extra step.

Using the above Pivot Table as the example:

1. Add the “Sales USD” to the Values This displays the expected numbers (in column B).
2. Add the “Sales USD” a second time to the Values
3. Select any number in the second set of “Sales USD” and click Home (tab) -> Styles (group) -> Conditional Formatting -> Data Bars -> pick your desired color.

You will see a Data Bar on the single cell you originally selected.

Here comes the extra step.

Click the small button to the right of the cell containing the Data Bar to reveal the Formatting Options list.

If you want EVERY number in that column (including the totals) to display a Data Bar, select “All cells showing “Sales USD” values.

If you only want to display Data Bars for the row-level items (no totals), select “All cells showing “Sales USD” values for “Product Description”.

Tweaking the Results

Since we don’t need to see the numbers twice, we can hide the numbers in the Data Bar column by selecting Home (tab) -> Styles (group) -> Conditional Formatting -> Manage Rules.

Select the Data Bars rule and click “Edit Rule”.

In the Edit Formatting Rule dialog box, place a check in the option labeled “Show Bar Only” and click OK.

If you don’t want to display a title above the Data Bars, you can manually change the name in the cell to a single space.

Word of Warning!!!

If you refresh the Pivot Table, you run the risk of collapsing the column containing Data Bars.

To prevent this from happening, set the Data Bar’s column to your desired width, then select the Pivot Table Options (upper-left).

In the PivotTable Options dialog box, on the “Layout & Format” tab, uncheck the option labeled “Autofit column widths on update” in the “Layout” section.

Tip #5: Create Calculated Fields

Think of a calculated field as an additional field in your data (like “Tax”) that is calculated based on the structure of the Pivot Table.

In other words, if you were only showing sales for each “Company Name” and wanted to display the total tax for each, instead of creating a “Tax” column in the data with the requisite calculation (this could require hundreds or thousands of row-level calculations), we could instead wait to see how the Pivot Table is grouped and only calculate taxes for each item in the group.  (sorry for such a long sentence)

In this example, three calculations are better than thousands.

To do this, perform the following steps:

1. Select the Pivot Table
2. Select PivotTable Analyze (tab) -> “Fields, Items, & Sets” -> “Calculated Field…”

In the Insert Calculated Field dialog box, enter a name for the new column and write the formula as you would most normal Excel formulas, and click OK.

With a bit of number formatting and column heading customization, you have your new calculated column.

A couple of interesting features of Calculated Columns:

• You can reuse the new calculation from the PivotTables Field List in as many Pivot Tables as you like.
• If you update the Calculated Column (ex: change the tax rate), every Pivot Table that uses the calculation is updated to reflect the new value.
• Changing the base row or column selection creates all-new calculations.

Tip #6: Calculate the Difference Between Columns

Suppose you have a Pivot Table that displays sales for products across two months.

If you wanted to calculate the increase or decrease in sales from June to July, you can perform the following steps:

1. Add the “Sales USD” field a second time to the Values zone.

2. Right-click on one of the values in the second instance of sales and select Show Values As -> “Difference From…”.

3. In the “Show Values As…” dialog box, set the “Base Field” to Months and the “Base Item” to Previous.

The result (after customizing the heading and hiding the empty column) appears as follows.

Tip #7: Smart Custom Number Formatting

Using the results of Tip #6 above, suppose we wanted to display up and down arrows in place of the values, just to indicate an increase or decrease from the previous month.

1. Select a cell in the “Difference” column.
2. Right-click the cell and select “Number Format…”.
3. On the Number tab of the Format Cells dialog box, select
4. Erase the word “General” or whatever text happens to occupy the “Type:” option.

Using the “Windows Key-Period” key combination to open the symbols window, browse or search for “arrow”.

NOTE: If you are not running a Windows 10 environment, you can copy-paste symbols from the Clipboard.

To add a splash of color to the arrows, we’ll add color codes to the Custom Number Format; [Red] for negative values and [Color10] to give the positive values a subdued green color.

If all you are interested in is whether you made or lost money from the previous month, this is easier to recognize than scrutinizing each value.

Tip #8: Create Custom Groupings

Suppose we wish to create three groups of products: “Mens”, “Womens”, and “Other”.

This will be performed in 3 passes; one for each group.

1. Select all rows containing “mens” items.
2. Right-click the selection and click “Group…”.
3. Rename the newly created group “Mens”.

Repeat the process for the rows containing women’s items.

Finally, select the rows for “other” items.  If the needed rows are separated by other rows of unrelated data, use the CTRL key to select multiple, non-adjacent items.

If you would rather display the “Others” group at the bottom of the list, you can click the cell labeled “Others”, then grab the edge of the cell selection border and drag the group to a new position such as the bottom of the list.

One of the great things about this grouping is if you were to create a hierarchy of categories, each would be sub-divided by this new group logic.

Tip #9: Group Date Fields

Beginning users of Pivot Tables quickly learn that when you add a date-based field to a Pivot Table, the Auto-Grouping feature attempts to group the data by “Year/Quarter/Month”, “Month/Day”, or something along these lines.

What if you wanted to display records grouped in sets of 5-day blocks?  This is easier than you might think.

Using the below Pivot Table showing sales by day…

Right-click a date and select “Group…”.

Ensure that “Days” is the only selected item and set the “Number of Days” option to 5.

Observe the newly “grouped by 5 days” results.

Tip #10: Add a Timeline

We can give the user an easy-to-use Slicer-style interface for selecting date ranges.  This is done by way of the Timeline Slicer.

To add a Timeline Sliver, select the Pivot Table and click PivotTable Analyze (tab) -> Filter (group) -> Insert Timeline.

Select the field in your data that contains dates you wish to filter by.  Note: only date-based fields will be displayed in the Insert Timeline dialog box.

Size and position the newly inserted Timeline Slicer as needed.

You can use the slider bars to reduce or expand the range of qualifying records based on years, quarters, months, or days.

Practice Workbook

Feel free to Download the Workbook HERE.

LinkedIn Twitter YouTube

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

More About Leila Join 400,000+ professionals in our courses