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: