🔵 You can add two types of custom formulas to an Excel pivot table - Calculated Items and Calculated Fields. Watch this video to see the difference between Pivot Table Calculated Items and Calculated Fields. You'll see how to add each type of formula, and create a list of the formulas in the pivot table.
✅ Get the Excel file from this page on my site:
contextures.com/calculatedfieldcalculateditem.html
⏰ Video Timeline ⏰
0:00 Intro
0:30 Pivot Table Formulas
1:04 Calculated Item
2:50 Calculated Field
4:06 List Formulas
5:02 Get the Sample File
🔶 Instructor: Debra Dalgleish, Contextures Inc.
💡 Get Debra's monthly Excel tips: contextures.com/signup01
Subscribe to Contextures YouTube: youtube.com/user/contextures?sub_confirmation=1
#ContexturesExcelTips
VIDEO TRANSCRIPT
After you create a pivot table in Excel, you can add custom formulas, either calculated fields or calculated items.
In this video we'll see when to use either type of formula and what you can do with those formulas, such as combining several items into one, or adding something to the total with a special calculation. This is Debra Dalgleish from Contextures.com.
The two types of formulas in a pivot table are calculated field and calculated item.
To create either of those, we would select a cell in the pivot table, go up to the Analyze tab, click Fields, Items, & Sets, and then we have a choice Calculated Field or Item.
The calculated field lets us work with any of the fields in the pivot table and the calculated items lets you work with any items in a specific field.
So we'll try a calculated item first, and what we're going to do is work with this Order Status and instead of having Canceled plus 3 different types below it, we're going to combine all of these 3 into something called Sold.
Here, I'll click Calculated Item, and the first thing we would do is give this a name, and I'm going to call it Sold. Then we have to create our formula, and it right now just shows equals zero.
We can see all the fields in our pivot table here. Because I have order status selected, that's the one that it has highlighted in this list of fields, and it's showing all the items, but I could do something with any of the other fields if I prefer.
If I wanted to work with the regions, I could click it and see its items.
But we'll go back to Order Status, and what we want to do is get the total for Backorder, plus Pending, plus Shipped.
And to do that I'm going to double click on Backorder, and that puts it up into the formula, so I didn't have to delete what was there. It just deleted that for me and put Backorder in.
Then we'll do a plus and Pending. I'll double click it. Plus Shipped.
So we're going to get the total of those 3, and I'll click Add and OK.
Now what's happened is, it still has Canceled, our other 3 items, and Sold.
So we're getting double the amount for each of these items.
So the last step here would be to hide the 3 items that are being included in Sold.
And now we just have Canceled and Sold. So that's a very simple Calculated Item.
The other type of formula in a pivot table is a Calculated Field.
Well, this is the same data, I've now listed all the sales reps here, how many total units they sold, and the total amount of their sales.
We're going to give everyone a bonus, and it's going to be based on what total they sold.
So I've got a cell selected in this pivot table, go to Analyze, Fields, Items, & Sets.
And this time we've got a value field selected, so it's not offering to let us make a calculated item for that.
So Calculated Field, and we're going to name this, and we'll call it Bonus.
And now we have fields, but there's no list of items. This time we're strictly working with the fields.
We want to do something with this Total, so I'll double click and it puts it in there and for our bonus they're going to get 3% of their sales.
So I'll do an asterisk to multiply, and .03 would be our multiplier.
Click OK. And there's the bonus that each person will get, based on 3% of their total sales.
And the last thing we'll take a look at, while we're creating formulas in a pivot table, is getting a list of all those formulas that we've added.
So this is helpful if you're taking over a pivot table that someone else built or if you built something a while ago and you can't remember what's in it.
Just select a cell in a pivot table, go back to Fields, Items, & Sets on the Analyze tab.
Then go to List Formulas and it puts a new sheet in the workbook.
And we've got Calculated Fields. If you have any, it might be blank.
We've got Calculated Item, and it lists any, or if you don't have any, you just have the heading there.
It also shows Solve Order.
And there's a note at the bottom about how that works, and I've got videos that show more about the Solve Order.
And the name of the calculated field or item.
Then it prints out the formula.
- Excel Pivot Table Calculated Items and Calculated Fields ( Download)
- Excel PivotTable Calculated Items + the EASY way to Distinguish them from Calc. Fields ( Download)
- How to add a calculated field to a pivot table ( Download)
- Excel Pivot Table Calculated Fields and Calculated Items | How to add a calculated field to a pivot ( Download)
- Excel Pivot Table Calculated Fields and Calculated Items | How to add a calculated field to a pivot ( Download)
- Add New Fields in a PivotTable using Calculated Fields ( Download)
- Excel Pivot Table Calculated Fields and Calculated Items | How to add a calculated field to a pivot ( Download)
- Pivot Table Calculations Basics: Calculated Fields, Items & Sets ( Download)
- Excel Advanced - Calculated Items in a PivotTable - using percentages ( Download)
- How to add a calculated field to a Pivot Table in Excel ( Download)
- Add Calculated Fields with IF Statements to a Pivot Table ( Download)
- Pivot Table Calculated Fields & Calculated Items | How to add calculated field to a pivot | Part 15 ( Download)
- Pivot Table: Calculated Fields vs Calculated Items ( Download)
- Excel Pivot Table Growth % calculated field & calculated items. Add calculations to pivot fields ( Download)
- Calculated Field and Calculated Item in Pivot Table ( Download)
