KJONGSys | Tech Blog | Pivot Tables

5 Advanced Excel Pivot Table Techniques

KJONGSys Support 0 Comments

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 Pivot Tables. Let’s get into it.

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

KJONGSys | Tech Blog | Pivot Tables

Many times, I’m developing Pivot Table 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 Pivot Table and find the Analyze tab on Excel’s ribbon.

KJONGSys | Tech Blog | Pivot Tables

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

Timelines are a special type of slicer, used to tweak the dates included as part of your Pivot Table 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.

KJONGSys | Tech Blog | Pivot Tables

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 Pivot Table (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.

KJONGSys | Tech Blog | Pivot Tables

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 Pivot Table.

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.

Excel’s default Pivot Table 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 Pivot Tables in the standard view are difficult to write formulas on. If you have your data in a Pivot Table, but you want to view it more like a traditional spreadsheet, you should use tabular view for your Pivot Table.

KJONGSys | Tech Blog | Pivot Tables

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

KJONGSys | Tech Blog | Pivot Tables

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.

Calculated fields are a way to add a column to your Pivot Table 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 Pivot Table and find then click Analyze on the ribbon. Click on the Fields, Items & Sets menu, and then choose Calculated Field. 

KJONGSys | Tech Blog | Pivot Tables

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.

KJONGSys | Tech Blog | Pivot Tables

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 Pivot Table with the new calculated field. You’ll also see the list of Pivot Tables 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:

KJONGSys | Tech Blog | Pivot Tables

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.

The Recommended Pivot Tables 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.

KJONGSys | Tech Blog | Pivot Tables

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 Pivot Tables.

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

KJONGSys | Tech Blog | Pivot Tables

Even though this is an advanced feature that not many users think about, it’s also a great tool for starting with Pivot Tables. There’s nothing stopping you from modifying the Pivot Table 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 Pivot Tables are often more insightful than I am!

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

What do you still want to learn about Pivot Tables? Let us know about your ideas or questions in the comments below this tutorial or get in touch with us if you need assistance.

Leave a Reply

Your email address will not be published. Required fields are marked *