6 Helpful Excel PPC PivotTable Tips & Tricks- 10 Common Problems Solved!

Pivot-Table-Before-300x158If you work in PPC, you know that Microsoft Excel is fantastic for crunching numbers. PPC often involves analyzing huge data sets, and one tool comes to mind to make this task easier- PivotTables. Arguably the most important tool in Excel, learning how to create a great PivotTable is essential, and once you learn how to really use them you’re life may never be the same again. You’ve got a bunch of raw data and hit Insert PivotTable, but how can you go about getting the most out of this new PivotTable? Follow these 6 Helpful PPC Tips and Tricks Below to Solve 10 Common PPC PivotTable Problems. Starting with the example PivotTable to the right, watch it transform with these easy-to-do steps.






Summarize-Values-By-300x250Problem #1:

You’ve mastered the task of creating a PivotTable and adding your columns and rows, but the values are not being summarized in a way that gives you the information you’re even looking for!

Fix #1:

PivotTable Tools > Analyze > Field Settings > Summarize value field by

Our PivotTable started out displaying the Count of each value. While Count is an important summary, in PPC PivotTables we are often looking at the Sum or Average when analyzing data.

Number-Format-300x210Problem #2: 

You finally got your values summarized correctly, but the formatting is all off. The values are showing tons of decimals, no commas, no units, and are difficult to easily interpret.

Fix #2:

PivotTable Tools > Analyze > Field Settings > Number Format

Our PivotTable is now providing us with a valuable display of the information we are looking for! Costs are shown as currency, Impressions and Conversions are shown as whole values, and other calculations can be shown as a percentage, date, fractions, and more. Setting the correct Number Format can go a long way in making your PPC PivotTable easy to understand.

Custom-Name-300x227Problem #3:

Now that you’ve got your values summarized correctly and formatted appropriately, the column titles are too long and poorly named!

Fix #3:

PivotTable Tools > Analyze > Field Settings > Custom Name

No worries, there’s a fix! Change the Custom Name to whatever you find appropriate. Rather than the default ‘Sum of Cost,’ maybe try ‘Spend’ or ‘Total Cost.’ Keep in mind that the Source Name is the value from your original data that will be pulled when you update your PivotTable. Your PivotTable will not allow you to set your new Custom Name to the same name as the Source Name. If you do want to set your Custom Name to the Source Name, one workaround is to add an extra space at the end of the Custom Name.





Problem #4:

You PPC PivotTable is starting to look like a real work of beauty, but you’re probably looking to add in calculated columns. Your original data may have pulled metrics like CTR and Cost/Convr, but you don’t want to take the Average or Sum of the average CTR, you want to calculate it based on Clicks/Impressions.

Fix #4:

PivotTable Tools > Analyze > Fields, Items, & Sets > Calculated Field…

This is the best way to add in calculated metrics to get the most accurate values. Create formulas by inserting metrics from the Fields list. Name your new Calculated Field with a clear name, click ‘OK’, and your new field will now exist in you Field List.





Problem #5:

You just created some new Calculated Fields and now you’re seeing error messages appear such as ‘#DIV/0.” While this makes sense since we all know you can’t divide a number by zero, these messages can easily lead to confusion especially if the error is appearing across multiple values.

Fix #5:

PivotTable Tools > Analyze > Options > For error values show

Set whatever messaging you’d like to show for errors like these! Feel free to set a value for empty cells as well. While you’re here, also make sure that the ‘Preserve cell formatting on update’ is checked if you’re going to be updating your data and refreshing your PPC PivotTable.


Grand-Totals-270x300Problem #6

You’re PivotTable is starting to actually become easy to understand, but are you interested in adding or changing Grand Totals by Column or Row as an easy was to see the data aggregated?

Fix #6:

PivotTable Tools > Analyze > Options > Totals & Filters > Grand Totals

Grand Totals are a great way to visualize the total data and to better understand the percentage breakdown of subtotals. It’s an easy fix, that’s worth adding.







Problem #7:

You love PivotTables because of how dynamic they can be, but you’re looking for an easy way to quickly slice and view the data by various dimensions.

Fix #7:

PivotTable Tools > Analyze > Insert Slicer

Sometimes it can be way too overwhelming presenting all your data on one sheet. Slicers are a great way to break up the data so that you can view exactly what you’re looking for with the click of a button. They are a great way to break up your data by labels, dates, campaigns, and more!



Problem #8:

You’ve created Slicers and realized how valuable they can be, but you specifically want to slice by time at a more granular level.

Fix #8:

PivotTable Tools > Analyze > Insert Timeline

If your original data is broken down by day, week, month, or year, it might be worth adding in a Timeline. It’s an easy way to easily view your data if you are interested in understanding it over various time periods.




Problem #9:

You’ve pulled all the right data, but you want an easy way to group that data into specific categories.

Fix #9:

Select Data >Right Click > Group, Subtotal

This fix is a great solution that comes in handy more often than you’d think! Whether you want to look at week data by groups of 4 weeks or you want to look at average position data by each position, its a great way to take the data you have and learn something new from it.




Design-300x297Problem #10:

You’ve got all the right data. It’s formatted and you’re PPC PivotTable has become a seriously valuable source of information. However, it’s difficult to distinguish between column headers, data, and breakdowns because of the default design.

Fix #10:

PivotTable Tools > Design > New PivotTable Style…

If you’re short of time, choose one of the many already created designs in Excel. They have tons of options that are great for creating contrast to make reading your PPC PivotTable quick and easy. If you’re looking for something unique, try creating your own design with custom colors and design elements.


Involve these steps into your next PPC PivotTable to create a chart that really provides you with the easy-to-read information you’re looking for. Look at our example PivotTable below, to see how easy it was to make a great looking PivotTable.

Pivot-Table-After-300x149 (1)

Point It About the author
No Comments

Leave a Comment: