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.
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).
When you click on the PivotTable, you should see two contextual tabs for PivotTable Tools: Analyze and Design.
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.
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.
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.
7. Press OK, and look at the field you just created incorporating a formula from two other fields. Pretty cool!
Notes
- PivotTable Fields: You will see this calculated field appear in the Values section of your PivotTable Fields.
- 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.
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
