Let’s take a look. Here we have the same pivot table we looked at earlier. Sales are summarized by Region, and we are using a calculated item, Eastern, to summarize the regions East and South. Before we make any changes, let’s add Product as a Row Label above the Region field. Now we have multiple instances of the calculated item Eastern. As you can see, the Name of a calculated item appears directly in the pivot table. If you want to update this name, just type a new name directly in the table. For example, we could temporarily rename Eastern to New Eastern, and it will be updated globally throughout the pivot table. If we check the Insert Calculated Item dialog box, we’ll see the name has been updated there as well. However, if you try to change the name of a calculated item in this dialog box, you’ll see the Modify button disappear. And if you click OK, you’ll get a new Calculated item with the same formula. We’ll delete this extra calculated item, and rename New Eastern back to Eastern. Let’s now look at how to change a calculated item’s formula. But first, let’s make Region a Column Label to make the pivot table more compact. The formula for a calculated item can also be edited directly in a pivot table, but it behaves differently. For example, if you select the first instance of the calculated item Eastern, you’ll see the formula East + South. And we see the same formula at each location. Let’s edit the first instance of this formula by removing South. As you can see, the formula is easily updated. However, note that the change we made is local. When you edit the formula for a calculated item directly in a pivot table, the change is a local override only - the other instances of the calculated item remain unchanged. We can verify this behavior by checking the Insert Calculated Item dialog box. Here, we see the original formula. Let’s make a simple change to this formula, and click OK. Back in the pivot table, the formula has been updated everywhere except where we made the local change. Once you make a local change, it will stay that way, even if you update the formula later.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
