In the screen shot below, The category names are in the left column, and the products for each category are listed below the headings.

This is useful if the the lists are long, and you cant see the headings as you scroll down.

You can also do lookups from the pivot table, if the names are filled in.

You can also set this as one of your preferences, in the PPP default settings window.

This can also be referred to as repeating the labels, or, as flattening the data.

Flat data is described as data which contains values in all cells within the table.

All information about the record is derived from the values in the row, and, not from its position within the table.

Thus, we must determine information about a record based on the position of the row within the table.

For example, we know that row 39 is for Bayshore Water, but, we only know that row 40 is for Bayshore Water based on its position within the table.

All information about a record is contained within the row, and is not derived by its position within the table.

We want to use it with Excel, and really wish we could easily flatten it.

Excel Pivot Table Repeat Item Labels Series Of Built

Since this is Excel, there are of course several ways to approach this task, but one of the easiest ways is by using a series of built-in commands.

This is typically a column of labels you want to repeat, represented by B39:B62 in the screenshot below.

Hit the F5 key on your keyboard to bring up the Go To dialog, as shown below.

There are other ways, but to me, this is the easiest way to write the formula.

Now, resist the urge to hit the Enter key Do NOT hit Enter yet.

This is done by holding down the Control key, and then pressing Enter immediately after writing the formula.

If you have written the formula, and have already pressed Enter, then, youll need to write the formula again, and press CtrlEnter instead of Enter.

Enter the formula, and, fill it down through all selected cells.

Excel doesnt let us perform the next step with multiple ranges selected, so, we need to select a single column range.

In the Paste Special dialog box that pops up, we select Values, as shown below.

We repeat these steps on the Account column, and bam.flat data, as shown below.

I would use the approach above if I needed to flatten data occasionally.

However, if I needed to flatten data files often, then I would probably handle this task with a get transform query.

Gamification ensures it is the most fun you can have learning Excel:).

Excel For Mac Pivot Table Repeat Item Labels