Custom Calculations in PivotTables

The Core Difference: Fields vs. Items

Before we start building, it's crucial to understand the difference between a Calculated Field and a Calculated Item. They sound similar, but they do very different things.

• A Calculated Field is a new field (column) that performs a calculation using other fields. Think of it as operating horizontally across your data. For example, Average Price = Sales / Units.
• A Calculated Item is a new item (row) within an existing field that performs a calculation on other items in that same field. Think of it as operating vertically within a category. For example, East + West = Total US Sales.

This short video provides an excellent visual explanation of this core concept.

1. Calculated Fields: Creating New Metrics

You'll use a Calculated Field whenever you need a formula that references other numeric columns in your PivotTable. This is perfect for creating Key Performance Indicators (KPIs). For example, in your recruitment work, you might calculate a consultant's commission based on their total placements.

Let's walk through creating a simple Calculated Field for a sales bonus.

The steps are always the same:

1. Click anywhere inside your PivotTable.
2. Navigate to the PivotTable Analyze tab.
3. Click Fields, Items, & Sets, then select Calculated Field.
4. In the dialog box, give your new field a name (e.g., "Commission").
5. Enter your formula. You can double-click fields from the list to add them to your formula. For example: ='Sales' * 0.05.
6. Click Add, then OK.

Your new Calculated Field will appear in the PivotTable Fields list and can be used like any other value field.

You can also use Excel functions like IF within your formulas for more complex logic. For instance, you could calculate a bonus that only applies if sales are above a certain threshold.

2. Calculated Items: Creating Custom Groups

You'll use a Calculated Item when you want to perform calculations on specific items within a single category field. This is useful for creating custom groupings or comparisons that don't exist in your source data. For example, in an incident report, you might want to create a new category called "High Priority Incidents" that sums up "Urgent" and "High" priority items.

Let's see how to combine several items into one new summary item.

Key Steps and an Important Warning:

1. Click on one of the row or column labels within the field you want to modify (e.g., click on a country name if you're grouping countries).
2. Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Item.
3. Name your new item (e.g., "Active Pipeline").
4. Create your formula by referencing other items in that same field. For example: ='Sourced' + 'Screening' + 'Interview'.
5. Click Add, then OK.

Crucial Tip: As the video demonstrated, Excel adds the new Calculated Item but leaves the original ones. This leads to double-counting in your Grand Totals. You must manually hide the original items (using the filter dropdown on the Row/Column Label) so your totals remain accurate.

There are also some limitations to be aware of when using Calculated Items.

Your Turn: Add Custom Metrics

It's time for you to apply these concepts. Please continue using the sample-data-for-pivot-table.xlsx file.

Task:

1. Create a new, simple PivotTable with Category in Rows and Sales in Values.
2. Create a Calculated Field:
   • Add a new Calculated Field named Commission.
   • The formula should calculate a 2% commission on all sales: ='Sales' * 0.02.
   • Verify that a new "Sum of Commission" column appears in your PivotTable.
3. Create a Calculated Item:
   • Now, click on one of the category names (e.g., "Computers") in your PivotTable.
   • Add a new Calculated Item named Tech Hardware.
   • The formula should sum two of the existing categories: ='Computers' + 'Projectors & Screens'.
4. Clean up your report:
   • Notice that your Grand Total is now incorrect because it's double-counting.
   • Use the filter dropdown on the Row Labels to hide the original "Computers" and "Projectors & Screens" items.
   • Verify that your Grand Total is now correct.

Bonus Tip: Auditing Your Formulas

If you inherit a workbook or need to troubleshoot a complex report, you might need to see all the custom formulas used in a PivotTable. Excel has a built-in feature for this.

This simple command creates a new worksheet detailing every Calculated Field and Item, which is incredibly useful for quality checking your work.

Conclusion

You have now mastered the art of customizing PivotTable calculations. This is a significant step up from basic summaries and is a skill you will use constantly in any MIS role. You are no longer limited by your source data; you can create the specific metrics your business needs on the fly.

Key Takeaways:

• Calculated Fields create new columns based on formulas that use other fields (e.g., Sales - COGS).
• Calculated Items create new rows within a category based on formulas that use other items in that same category (e.g., North America = USA + Canada).
• When using Calculated Items, you must often hide the original items to prevent double-counting in totals.
• You can use the List Formulas command to audit all custom calculations in a PivotTable.

This lesson concludes our module on summarizing data with PivotTables. You've gone from creating a basic PivotTable to building a fully interactive and customized analytical tool.

In our next module, we will focus on Visualizing Data with Charts. We'll start by learning how to create essential charts like bar, column, and line charts from the summary data you are now an expert at preparing.