Excel: PivotTables and Calculated Fields

Formulas and PivotTables. Many of us have gotten caught up creating formulas outside of a PivotTable by referencing cells within a pivot table, only to be disappointed when we update our PivotTable and cells shift back and forth. There are several ways of dealing with this issue. One way is to create a Calculated Field within your PivotTable. This is something we cover briefly in Excel PivotTables sessions, but it is definitely worth a review.

Fields, Items and Sets drop down with Calculated Field circled

Exercise File

If you would like to follow along, here is an exercise file:

This document contains two sheets: one sheet contains the PivotTable, and the other contains the source data, showcasing a couple years of dessert sales. Right now, you have income and expense information in the values of the PivotTable. Since we have those figures handy in our data, it would be nice to figure out net income.

For this Byte, I am assuming you are comfortable with PivotTables in Excel. If you are not, please come attend my PivotTables, Charts, and Pictures sessions! Sessions are listed in myTraining and are updated regularly.

PivotTable Contextual Tabs

We are going to be working a lot with the PivotTable Tools contextual tabs. Like any other contextual tab in Microsoft, remember that you need to click on the element to see the corresponding contextual tab(s).

PivotTable Tools COntextual Tabs

When you click on the PivotTable, you should see two contextual tabs for PivotTable Tools: Analyze and Design.

Click onto the pivot table to activate Pivot Table TOols tab

Be sure you can see these tabs before moving on to the next step.

Inserting a Calculated Field

Remember, our end goal is to create a Net Income field that will subtract the Expenses from the Revenue.

1. With the PivotTable Tools activated, go to the Analyze tab, Calculations Group.

2. Select the dropdown for Fields, Items & Sets and select Calculated Field.

Fields, Items and Sets drop down, Calculated Field circled

3. In the popup screen that appears, you will see all the PivotTable fields listed at the bottom of the screen. At the top is an opportunity to name the field and a space for a formula.

Insert Calculated Field Pop up

4. Click in to the Name box and change Field1 to Net Income.

5. Click in to the Formula box, and double click on Revenue in the Fields area. Type a “-” on your number pad, and double click on Expense. We are building a formula that will read: = Revenue – Expense.

6. Press Add.

Insert Calculated Field pop up, filled out as described in text.

7. Press OK, and look at the field you just created incorporating a formula from two other fields. Pretty cool!

Pivot table with new Calculated Field column

Notes

  • PivotTable Fields: You will see this calculated field appear in the Values section of your PivotTable Fields.
Sum of Net Income visible in the Values box of Pivot Table Fields list.
  • Calculations from Calculations: If you are creating another Calculated Field, you will see the Net Income field we just created as a field option for use in future formulas.
Calculated field visible in a new Calculated Field pop up screen for further calculations

Thoughts?

What do you think? Can you incorporate Calculated Fields into your PivotTables? I would love to hear from you!

Open Labs

Did you know that Applications Training has open labs every other week? Whether you have a Banner question or a Microsoft question, feel free to stop by! Open lab times are listed in myTraining under the title Open Lab Assistance on Banner 9, Time Entry, Reporting, & Microsoft/Adobe.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Julie Scott

Julie Scott with Power User Certificate