Getting Started with Pivot Tables: Avoid the Easy Mistakes
Published: September 9, 2014
Author: Oliver Eldredge
Pivot tables provide fast access to data that can dramatically shorten the amount of time that you spend analyzing anything from account structure to campaign performance. Unfortunately for those who are just getting started with them, they can create problems and frustrations just as easily as they can eliminate them.
This blog post will help you eliminate five of the most common issues that you can run into while getting started with pivot tables.
1) Keep Your Data Current
Pivot tables don’t keep themselves updated in the same way that formulas do. By default in Excel, formulas will update themselves when the data that they are referencing gets changed. To keep a pivot table up-to-date, you will have to manually refresh it and keep the definition of its data source current.
The classic mistake that you can make is to update the data source that your pivot depends on without actually updating the pivot. The result of this mistake is that your pivot table will display data that is inaccurate.
To keep your pivot table updated, you can use the Refresh and Change Data Source buttons on the Analyze ribbon. The refresh button will cause the pivot table to reevaluate the data that you used to create it, so it’s useful when you’ve made changes to existing data that your pivot table is using.
The Change Data Source button is used when you’ve made a change to the data that you want to analyze. The most common situation here is that you’ve added data (let’s say yesterday’s spend) to the bottom of the worksheet that you’re using, and then didn’t update the data source for the pivot table. The result of this error is that you’ll end up analyzing an incomplete data set.
2) Use Calculated Metrics
Pivot tables will generally add up, or count up data by default. This is bad news if you want to include calculated metrics like CTR, CPC, CPA, or anything else in your table. You can control how the pivot table handles the data that you’re including in it by clicking on the small triangle located to the right of the metric in question in the summation values area. By selecting the Field Value Settings option from the dropdown, you can make some changes that will let you add, multiply, count, average, or otherwise analyze your data. You cannot get a custom calculation from here, though.
In order to get a metric calculated on the fly, you will have to use the Fields, Items, & Sets button found under the Analyze ribbon. From here you can select Calculated Fields, and then you will be prompted to define new fields for your pivot table. This is the correct method for adding calculated metrics to a pivot table.
A common mistake is to include the calculated metrics in the data set that your pivot table is using, and then find yourself in a situation where your pivot table is displaying the sum of every KWs CPC in a campaign, instead of the actual CPC that that campaign had. A tempting, but absolutely incorrect way to solve this problem is to try to use select Average from Field Value Settings. This will put you in a situation where you are reporting on, or analyzing, an average of several calculated metrics, instead of the actual calculated metric itself.
3) Format for the Timeline
The timeline feature for pivot charts provides you with an easy way to slice and dice data based on dates. Since it’s working with dates, it makes sense that it requires your data to be formatted as a date in order to work correctly.
It’s easy to be frustrated when the timeline feature won’t work even though you’ve labeled your data with months or quarters on your own. Double-check to make sure that the data that you see as a “date” isn’t actually a text string that describes a date to you (but not Excel).
In summary: Excel is really great at solving problems, and sometimes it’s really great at creating them too. It’s important to understand the subtleties that can be the difference between a workbook that helps you save time versus a workbook that causes problems and wastes your time.