Change Excel Pivot Table Subtotal Text

When you add two or more fields to the Row area in a pivot table, the outer fields will automatically display subtotals. By default, the subtotal row has a label that stars with the item name, followed by "Total"

In the pivot table shown below, the Category and Product fields are in the Row Labels area, and there is a subtotal for each item in the outer field – Category. You can see the Bars Total and Cookies Total labels.

pivotsubtotaltext01

Instead of leaving the default subtotal labels, you type a new label in any subtotal row. The text that you enter will apply to all the subtotals in that field.

Type a New Subtotal Label

When you type a new subtotal label, you can include the item name, or omit it. For example, if you select the Bars Total label in cell A9, and type "Subtotal", all of the items will change to that label. There is no item name in any subtotal label.

pivotsubtotaltext02

Include the Item Name

If you include the item name, such as Subtotal – Bars, when typing the new text, each subtotal will include its own item name.

pivotsubtotaltext03

When you type the item name, be sure to type it exactly as it appears in the pivot table. If you misspell the item name, such as "Bar" instead of "Bars", that word will be included in all the other subtotal labels.

pivotsubtotaltext04

Text Before and After Item Name

You can add text before and/or after the item name in the subtotal label. In the next example, the Bars subtotal label was changed to, "This is the Bars Subtotal". The other products show the same text before and after their item names.

pivotsubtotaltext07 

Get Creative with Subtotals

In addition to text, you can use other characters in the subtotal labels. In the example below, I typed a couple of space characters, then equal signs and a greater than symbol, to create an arrow.

pivotsubtotaltext05 

In the next example, I pressed the Alt key, and typed 16 on the numeric keypad, to create a right-pointing triangle. That certainly draws attention to the subtotals!

pivotsubtotaltext06 

NOTE: If you’re using a keyboard that doesn’t have a separate numeric keypad, you might have to press the Fn key, and the Alt key, then type 16 on the numbers that appear on the JKL keys.

So, you can get creative with those subtotal labels. Just remember that they can’t be changed individually – a change to one will be applied to all of them.

_____________________

2 thoughts on “Change Excel Pivot Table Subtotal Text”

  1. Hi, I just saw your blog – very helpful.
    I’m working on some pivot tables at the moment and I was wondering can you create a formula which to use the range of customise field ( filtered field ) in a pivot table ie. the formula is very simple : =sum(100000/pack sold)*complaints the problem is when I try to create a subtotal formula for a specific type of complaint, as the complaints vary by type. Could you please advise me if that’s possible.

    Thanks
    Pam

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.