Advertisement
  1. Business
  2. Microsoft Excel
Business

How To Make Use of 5 Advanced Excel Pivot Table Techniques

by
Difficulty:AdvancedLength:MediumLanguages:
This post is part of a series called How to Do Your Excel PivotTables Right (Mastery Guide).
How to Use PivotTables to Analyze Your Excel Data
How to Insert Slicers in Microsoft Excel PivotTables

The problem we all face isn't a lack of data; instead, it's finding meaning in huge amounts of data! That's why I advocate for the use of PivotTables, an amazing feature in Excel to summarize and analyze your data.

PivotTable to Summarize
Seeing the power of PivotTables is as simple as comparing the original data to the PivotTable version; with just a few clicks I was able to see the average item price by state.

As a finance professional, I'm genetically inclined to love spreadsheets. But I also find that I use spreadsheets to organize my creative and freelance work. No matter what you use spreadsheets for, a PivotTable can help you find greater meaning in the data.

In this tutorial, we'll build upon on our starter tutorials on using PivotTables to work better with your data. I'll show you five of my favorite advanced PivotTable techniques. 

Throughout this tutorial, I'll use sample data provided by Microsoft on this page. Use this data to recreate the examples or test the features I'm showcasing.

How to Use Advanced Pivot Table Techniques in Excel (Quick Video)

I love teaching with screencasts, which give you a chance to watch me use the features step-by-step. Check out the quick video below which cover five of my favorite advanced Excel PivotTable features:

 

5 Advanced Excel Pivot Table Techniques

Keep reading for a walkthrough of how to use each of these five features in the written tutorial below, covering: Slicers, Timelines, Tabular View, Calculated Fields, and Recommended PivotTables. Let's get into it.

1. Slicers

Slicers are point and click tools to refine the data included in your Excel PivotTable. Insert a slicer, and you can easily change the data that's included in your PivotTable.

Slicer Example in Excel PivotTable
In this example, I've inserted a slicer for the Item type. After I click on Backpack, the PivotTable shows only that item in the table.

Many times, I'm developing PivotTable reports that will be used by many others. Adding slicers can help my end user customize the report to their liking.

To add a slicer, click within your PivotTable and find the Analyze tab on Excel's ribbon.

Insert Slicers in an advanced Excel PivotTable
Check multiple boxes to include a selection of columns, each as their own slicer.

Hold Control on your keyboard to multi-select items within a slicer, which will include multiple selections from a column as part of your PivotTable data.

2. Timelines

Timelines are a special type of slicer, used to tweak the dates included as part of your PivotTable data. If your data includes dates in it, you really need to try out Timelines as a way to select data from specific time periods.

Insert Timeline into advanced Excel PivotTable
Browse to Analyze > Insert Timeline to add a Timeline, a special type of slicer that controls the data included in your advanced Excel PivotTable based upon the date.

Tip: If this feature isn't working for you, make sure that your original data has the date formatted as a date in the spreadsheet.

To add a Timeline, make sure that you've selected a PivotTable (click within it) and then click on the Insert > Timeline option on Excel's ribbon. On the pop-up window, check the box of your date column (or multiple columns) and press OK to create a timeline.

Adjust Timeline in Excel PivotTable
Click and drag within the timeline window to select a specific range of time to include with the PivotTable.

Once the timeline is inserted, you can click and drag the handles in the timeline to change what's included as a part of the PivotTable.

You can change the way your Timeline works by clicking on the dropdown box in the lower right corner. Instead of the timeline showing specific dates, you can change the timeline to show data based on the quarter or year, for example.

3. Tabular View

Excel's default PivotTable view looks kind of like a waterfall; as you drag more levels of fields into the rows box, Excel creates more "layers" in the data.

The problem is that PivotTables in the standard view are difficult to write formulas on. If you have your data in a PivotTable, but you want to view it more like a traditional spreadsheet, you should use tabular view for your PivotTable.

Tabular Form in Excel
Choose Design > Report Layout > Show in Tabular Form to work with your PivotTable in a standard spreadsheet style.

Why should you use Tabular View? Putting your PivotTable data in the classic, table style view will allow you write formulas on the data more easily, or paste it in a separate report.

Tabular View in Excel
Use the Tabular View as a way to make your PivotTable look more like a standard rows and columns view.

For me, it's easier to use the tabular view in Excel most of the time. It looks more like a standard spreadsheet view and feels easier to write formulas and work with the data inside of it. I could also take this view and paste it into a new tab more easily.

4. Calculated Fields

Calculated fields are a way to add a column to your PivotTable that isn't in your original data. You can use standard math operations to create entirely new fields to work with. Take two existing columns and use math to create entirely new ones.

Let's say that we have sales data in a spreadsheet. We have the number of items sold, and the selling price for each item. This is the perfect time to use a calculated field to calculate the total of the order.

To get started with calculated fields, start off by clicking inside of a PivotTable and find then click Analyze on the ribbon. Click on the Fields, Items & Sets menu, and then choose Calculated Field. 

Insert Calculated Field in Excel PivotTable
Use Analyze > Fields, Items & Sets > Calculated Field to insert a calculated field inside of your PivotTable.

In the new pop up window, start off by giving your calculated field a name. in my case, I'll name it Total Order. A total order price is the quantity times the price of each unit. Then, I'll double click on the first field name (quantity) in the list of fields in this window.

Calculated Field in Excel
To calculate the total order cost, I've multiplied my existing Unit Price field times the Quantity field.

After I've added that field name, I'll add the multiplication sign, , and then double click on the total quantity. Let's go ahead and press OK. 

Now, Excel has updated my advanced PivotTable with the new calculated field. You'll also see the list of PivotTables in the list of fields, so you can drag and drop it anywhere in the report as you need it.

If you don't want to use math on two columns, you can also type your own arithmetic values in the calculated field. For example, if I wanted to simply add 5% sales tax for each order, I could write the following calculated field:

Calculated Field with Numeric value in Excel
I multiplied the total order cost times 1.05 to calculate a cost including sales tax; you can use numeric values along with existing fields.

Basically, calculated fields can contain any of the standard math operators, such as addition, subtraction, multiplication and division. Use these calculated fields when you don't want to update the original data itself.

5. Recommended PivotTables

The Recommended PivotTables feature is so good that it feels like cheating. Instead of spending time dragging and dropping your fields, I find myself starting with one of the Recommended configurations.

Recommended PivotTables Icon
Go to Insert > Recommended PivotTables to try out this feature.

This feature is so easy to use that there's not much to say. You can use it to make advanced Pivot Tables in Excel quickly. Simply highlight your data, browse to the Insert tab on Excel's ribbon, and choose Recommended PivotTables.

The pop-up window features a litany of options for creating a PivotTable from your original data. Click through the thumbnails on the left side of this window to view the Recommended PivotTable options Excel generated.

Recommended PivotTables in Excel
The recommended PivotTable feature offers a variety of one click options to analyze your data.

Even though this is an advanced feature that not many users think about, it's also a great tool for starting with PivotTables. There's nothing stopping you from modifying the PivotTable by changing the fields on your own, but this is a time-saving starting point.

I also like this feature as a way to explore data. If I don't know what I'm looking for when I'm starting to explore data, Excel's Recommended PivotTables are often more insightful than I am!

Recap and Keep Learning (With More Excel Tutorials)

This advanced Excel tutorial helped you dive deeper into PivotTables, one of my favorite features to analyze and review an Excel spreadsheet. I use PivotTables to find meaning in large sets of data, which I can make good decisions from and take action.  

These tutorials will help you advance your Excel and PivotTable skills to the next level. Check them out:

What do you still want to learn about PivotTables? Let me know about your ideas or questions in the comments below this tutorial.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.