5 Pivot Table Pointers for PPC Newbies

When faced with a myriad of digital marketing data, a PPC newbie will likely feel fairly overwhelmed—I sure did.  But stress not, Excel pivot tables are here to help!  Whether crafting a report for a client or analyzing what ad text outperforms the rest, pivot tables can help you present and analyze the data in a tidy and logical manner.  

The following pointers will teach you how to dominate your data using one of Microsoft Excel’s most powerful features. Plus, I've included gifs galore to provide a more detailed walkthrough of the pointers! Click on each gif to see a larger version. 

1) Use ‘Calculated Fields’ to compute key metrics

A calculated field utilizes values from other fields to compute any PPC metric you can imagine.  This functionality is essential when creating reports that include click-through rates, conversion rates, or average CPCs, as pivot tables cannot accurately report these numbers due to averaging errors.  As a general rule, any percentages or averages (e.g. CTR, conversion rate, CPA, average CPC...) will necessitate the use of formulas created via the Calculated Field dialog box.

Begin by clicking any cell within the table to access the PivotTable Tools menu bar. On the Analyze tab (Options tab in older versions of Excel), find and click Fields, Items, & Sets > Calculated Field.  Next, enter a name and a formula, then click OK.  Finally, drag the newly created field to the Values area to add it to your table.

2) Save time with ‘Change Data Source’

Need to add additional rows or columns to your dataset and pivot table?  Save time and brainpower with the Change Data Source feature, which allows you to easily expand the source data to include more information.  Click anywhere in the pivot table to show the PivotTable Tools menu bar and then select Analyze > Change Data Source.  A dialog box will appear with the current table range highlighted.  From here, just click and drag to select the new source, then hit OK.

3) Edit and update with ‘Refresh’

It’s alive, it's alive!  Transform your pivot table workbook into a living document with Excel’s Refresh button.  This handy command allows you to add new data to an existing pivot table, making routine updates a breeze.

To incorporate your latest dataset edits, simply click anywhere in the pivot table to access the PivotTable Tools tab, then choose Analyze > Refresh.  Alternatively, you can right-click on any cell in the table and select Refresh.

4) Fine-tune with ‘PivotTable Options’

Dying to get rid of those dreaded #DIV0! error messages?  Look no further than the PivotTable Options dialog box.  To access this, simply right-click any cell in the table and select PivotTable Options. In the Layout & Format tab, check the box next to “For error values show:” and enter whatever you wish to see in place of #DIV0!.  Peruse and adjust the other options accordingly to perfect your pivots.

5) Don’t be afraid to move stuff around

At first glance, it might not be clear how to best organize a pivot table and the data within it.  That said, Excel pivots are extremely easy to manipulate and rearrange, so never be afraid to shift stuff around.  Add or remove a field, drag this or that to a different area—move things around until the metrics make sense.  If you’re really desperate, Excel 2013 even has a Recommended PivotTables button, which suggests different ways to organize the data you’ve selected.

--

Take it from me—the above pivot table pointers will help you work more efficiently, discover new ways to manipulate data, and become a Microsoft Excel pro. When in doubt, always remember the sage advice of the one and only Ross Geller: PIVOT!!!

Want more Excel tips and tricks? Make sure to check out Anthony’s post on game-changing Excel formulas! Are you a pivot table pro? What are your Excel secrets? Tweet us @Perfect_Search or email us at [email protected].