Episode #70: New advanced PivotTable class and a PivotTable calculated field trick for percentages

This post originally appeared on the KeyCuts blog.

I've been planning a few advanced Excel classes with Skillshare and excited to launch my first one today called Advanced PivotTable Techniques for Analyzing and Presenting Data Faster. I use PivotTables on and off depending on the task at hand. In preparation for this class, I had the opportunity to research and learn some advanced techniques that I personally didn't even know. I then pulled out the skills I think analysts would need the most (80/20 baby!) to be productive in their jobs and put them into this fast-paced 1-hour advanced PivotTable class. As a small teaser, I go through a calculated field technique for calculating percentages in your PivotTables below. To see some of my beginner Excel classes, take a look here. I'll be creating more bite-sized content on Instagram as well.

Click below to learn more and sign up for my advanced PivotTable class:

Credit card customer attrition data

This example is actually from the workbook used in the class project of my Advanced PivotTable class. This is the Google Sheet that shows the problem we're trying to solve. Let's take a quick look at the data:

It's a list of credit card customers and some demographic information about them. The most important column to note is the Attrition column because it indicates whether that specific customer churned or attrited (had to look up the past tense of attrition). This type of customer data would be great to summarize and analyze in a PivotTable like so:

You can get some summary stats about your customers, but what about the Attrition? If you throw that column into the PivotTable, you'll get something like this:

Not very helpful because our Attrition column consists of "Yes" and "No" as values. What I really care about is finding the Attrition % no matter how I set up my PivotTable. You could do something like this where you drag the Attrition column into the columns of the PivotTable. This would get you the Attrition % but it's a manual calculation and you can't see the Attrition % by different columns and properties in your PivotTable:

The minute you change up the PivotTable, column E will potentially get overwritten and you'll have to re-write the Attrition % for the cut of the data you care about. In order to get the Attrition % you may be thinking the calculated field is the way to go. That's partially right, so let's explore that option.

Adding a calculated field in Google Sheets for Attrition %

Adding a calculated field to your PivotTable in Google Sheets is similar to Excel. You have to go through the right sidebar instead of the ribbon:

A little known fact about PivotTables in Google Sheets or Excel (something I go over in my Advanced PivotTables class) is that you can add IF() statements to calculated fields. If we try to create a calculated field for Attrition %, however, we don't have the right data type to create this percentage. Additionally, the columns you put into the calculated field are summed. I tried experimenting with a few variations, but ultimately I couldn't find a formula to create a calculated field given the data we have:

The issue is that we want to divide the customers who have attrited by the total number of customers based on the current PivotTable fields. Even with the IF() formula, we can't take the Attrition column by itself to calculate the percentage. This is where we have to do a little augmentation to our dataset. I don't particularly like this solution because it's not a sustainable solution (e.g. you have new data coming in every day). But it works for one-off analyses.

Solution: Create a calculated field off columns with numbers in them

Watch the video tutorial of the solution below:

There are two new columns to add to the dataset: COUNT and Attrition Flag. The COUNT column is literally an entire column of 1s and the Attrition Flag is an IF() function that outputs 1 or 0 depending on the "Yes" or "No" in the Attrition column:

The key insight here is to create columns that output a number so that you can use those columns in the calculated field. Back in our PivotTable, you should change your source data to include columns F and G in the raw data worksheet and the calculated field formula looks like this:

I believe that by default, Google Sheets and Excel automatically sums the columns you add in the calculated field formula. Our Attrition Flag is a bunch of 1s and 0s and the COUNT is always 1, so this formula is kind of like:

=SUM('Attrition Flag')/SUM(COUNT)

This means you'll get the right Attrition % no matter how you create your PivotTable:

One final plug

--> Learn more advanced PivotTable techniques like this in my Advanced PivotTables class on Skillshare <--

Other Podcasts & Blog Posts

No other podcasts/blog posts mentioned in this episode!

Subscribe: Apple Podcasts | Android | Google Podcasts | Stitcher | TuneIn | Spotify | RSS