How to Insert a Calculated Field Simply

Running a pivot table on your data is a great way to see everything at a high level. Here is an example report that I pulled from my PPC account. The one column that had incorrect data was the column called “Count of Conversion Rate”. The reason why this data was off was because the pivot table could not get the true conversion rate for what I was looking for. To fix this error in data, I will need to create a calculated item in excel to show the true conversion rate percentage. Let’s start this now!

 

Step 1: Select the Data

 

Click anywhere in the pivot table. By doing this, you will have the ability to click on the analyze section. From there, you want to head over to the fields, items, and sets tool section. From there, you wan to click on the section that says “Insert Calculated Field”. See the picture below to see where we are right now.

 

As a side note, you will see in column G what I was talking about with the incorrect data reading.

 

Insert Calculated Field

 

Step 2: Create the Formula

 

For this example, I want to focus on the conversions against the total amount of clicks for my PPC campaign. Since I am looking for that, I am going to set up my formula to look like this. This is where you can create any formula that you want, so feel free to do whatever you would like. For me, I am just tryin to pull the conversion rate for the data that I have, so I am going to put this in.

 

Insert Calculated Field

 

Step 3: Deploy

 

With the formula set, I am going to go live with it. Again, I want this to be a percentage, but the default makes this column into a whole number. I have to go into the settings on this column to change the default type to a percentage. See the picture below for what I am talking about.

 

How to Change the Format

 

Step 4: Done

 

At this point, we just changed the format to a percentage, so we should be seeing this now.

 

Pivot Table with Custom Field Added

 

Conclusion

This was just the tip of the iceberg on what we can do with the data. Since we are in a pivot table, we can manipulate this data to show any sort of trends. I could break out the pivot table to show by keyword, ad group, device, CTR, and so much more, but I will just leave it at that.