How to add your own formulas to Pivot Tables

This week’s tip explains how to make a calculated field in a pivot table and is a compulsory read for those of you who include metrics calculated from other fields (such as ROI or CTR) in your pivot table reports.

Pivot tables can create new columns of data that are functions of existing variables. If you create CTR (click through rate) or ROI (return on investment) in your raw data and then build a pivot table, you will come across issues, such as:

  • Columns you create for CTR or ROI will simply sum the raw data and the totals will be incorrect
  • Having extra variables in your raw data makes file sizes larger than they need to be

Instead you should always create these metrics as calculated fields once the pivot table is built.

We have some sample data showing monthly clicks and impressions for each search engine:


Excel Raw Data

Excel Raw Data

  • Create a pivot table as normal


An Excel Pivot Table

An Excel Pivot Table

  • Now we want to create a column showing click through rate…
  • Click within the Pivot table and then click on the Options menu in the Ribbon


  • Next select Formulas from the Tools section and choose Calculated Field

Calculated Field

Calculated Field

  • Here you can name and create your click rate formula using the list of fields provided
  • Bear in mind that the name cannot match the title of an existing field
  • Click OK to finish!


Calculated Field

  • CTR will now appear in the table and you may just have to reformat the field quickly to show it as a percentage

Formula in Pivot Table

  • Don’t worry about the fact the column is labelled as a “Sum”. ¬†Individual click through rates have not been summed, they have been calculated
  • Also, if you change your pivot table now, for example to show these metrics by month, the CTR will automatically update

Leave a Reply




2 + = eleven

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>