Excel Pivot Table Repeat Item Labels

17-09-2021 admin

Repeat the Row Labels. A new feature in Excel 2010 lets you repeat those row labels, so they appear on every row in the pivot table. To turn on that feature for all the fields, select the Repeat All Item Labels on the Ribbon’s Design tab. Here’s the pivot table in Outline form, with repeating row labels. Repeating Labels for a Single Field. Repeat All Item Labels In An Excel Pivot Table MyExcelOnline. Excel Details: The best layout to use is either the Outline or Tabular layouts.You can then select to Repeat All Item Labels which will fill in any gaps and allow you to take the data of the Pivot Table to a new location for further analysis. 5 - Repeat row labels. Tabular form is my favorite layout for pivot. It looks clear and easy on eyes. Do you know that you can enable “Repeat item labels” option to make the tabular layout even better. Bonus tip: When you repeat item labels, you can use Pivot Tables in other formulas (like SUMIFS) easily. Now go to the Layout & Print tab on the same dialog box, and tell Excel to display the pivot tables labels in separate columns, without grouping: You need to display your row labels in tabular form. Finally (and still in the same dialog box) tick the box to repeat item labels (at last!).

In this post, we’ll cover a handy technique for manually filling data labels down through an Excel range. This can also be referred to as repeating the labels, or, as flattening the data. Let’s get into it.

Note: An alternative would be to use a Get & Transform query, as discussed in this post.

Flattening Data

Many of Excel’s features, such as PivotTables, Charting, AutoFilter, and the Subtotal feature, were designed to work with flat 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. It is simple to see the difference visually. So, this screenshot shows data that is not flat:

You can see labels are not repeated, and there are cells with missing values. 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.

In contrast, flat data contains repeats the labels as needed. This screenshot shows flat data:

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

Sometimes we obtain data from an accounting system or from a client that is not flat. We want to use it with Excel, and really wish we could easily flatten it. 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.

We’ll start with a summary, and then work through the details.

Summary

Select the range that you want to flatten – typically, a column of labels
Highlight the empty cells only – hit F5 (GoTo) and select Special > Blanks
Type equals (=) and then the Up Arrow to enter a formula with a direct cell reference to the first data label
Instead of hitting enter, hold down Control and hit Enter
To replace the formulas with values, select the whole column, and then Copy / Paste Special > Values

Details

Here, we’ll walk through each step, and … I brought screenshots!

Step 1:

First, select the range that you’d like to flatten. This is typically a column of labels you want to repeat, represented by B39:B62 in the screenshot below:

Step 2:

Next, we need to select only the empty cells within the range. We can simply use the Go To command for this. Hit the F5 key on your keyboard to bring up the Go To dialog, as shown below:

Next, hit the Special button to bring up the Go To Special dialog. Select the Blanks option, as shown below:

Click OK, and Excel will select only the empty/blank cells within the original range, as shown below:

Excel Vba Pivot Table Repeat Row Labels

Step 3:

Now, we need to write a formula that pulls the value from the cell above. This is easily accomplished by typing an equal sign (=), and then hitting the Up Arrow key on your keyboard. 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. The resulting formula is shown below:

Step 4:

Now, we need to fill this formula down through all selected (blank) cells. 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, you’ll need to write the formula again, and press Ctrl+Enter instead of Enter.

The Ctrl+Enter shortcut tells Excel to perform two tasks at once. Enter the formula, and, fill it down through all selected cells.

The result of this command is shown below:

Step 5:

Excel Pivot Table Cannot Select Repeat Item Labels

Now, all that remains is to replace the formulas with their values. First, select the whole column. Excel doesn’t let us perform the next step with multiple ranges selected, so, we need to select a single column range.

Now, we just copy the range using any method you prefer (Ribbon, right-click, keyboard shortcut). Then, we do a Paste Special. In the Paste Special dialog box that pops up, we select Values, as shown below:

When we click OK, we are done. We repeat these steps on the Account column, and bam…..flat data, as shown below:

Notes

You can typically perform this task on multiple columns at the same time, but, it only works if the first row has values for all selected columns, so, just be sure to review and double-check your work.

Conclusion

I first heard about this approach by John Walkenbach…thanks John!