How to repeat row labels for group in pivot table?

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. The good news: there is a way to go back to the original view. The bad news: you have to repeat this step for every pivot table you ever create. To return to a normal layout of the pivot table, follow these steps: 1. Select any cell inside the pivot table. The PivotTable Tools tabs appear in the Ribbon. Go to Design tab of the ribbon.

In Excel, when you create a pivot table, the row labels are displayed as a compact layout, all the headings are listed in one column. Sometimes, you need to convert the compact layout to outline form to make the table more clearly. But in tphe outline layout, the headings will be displayed at the top of the group. And how could you repeat the row labels for group in pivot table?

• Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
• More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
• Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
• Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
• Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
• Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
• More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Repeat row labels for all fields group in Pivot table

Supposing you have a pivot table, and now you want to repeat the row labels for all fields group as following screenshots shown.

Do with following steps:

1. Click any cell in your pivot table, and click Design under PivotTable Tools tab, and then click Report Layout > Show in Outline Form to display the pivot table as outline form, see screenshots:

2. After expanding the row labels, go on clicking Repeat All Item Labels under Report Layout, see screenshot:

3. And then, the row labels have been repeated for all fields group in pivot table as following screenshot shown:

Repeat row labels for single field group in pivot table

Except repeating the row labels for the entire pivot table, you can also apply the feature to a specific field in the pivot table only.

1. Firstly, you need to expand the row labels as outline form as above steps shows, and click one row label which you want to repeat in your pivot table.

2. Then right click and choose Field Settings from the context menu, see screenshot:

3. In the Field Settings dialog box, click Layout & Print tab, then check Repeat item labels, see screenshot:

4. And then click OK to close the dialog, and now, you can see the row labels which you have specified are repeated only.

Related articles:

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• To post as a guest, your comment is unpublished.
  any keyboard short cut for repeating label items in pivot table !

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:

How To Repeat Item Labels In Pivot Table

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

1. Select the range that you want to flatten – typically, a column of labels
2. Highlight the empty cells only – hit F5 (GoTo) and select Special > Blanks
3. Type equals (=) and then the Up Arrow to enter a formula with a direct cell reference to the first data label
4. Instead of hitting enter, hold down Control and hit Enter
5. 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:

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:

Excel For Mac Pivot Table Repeat Item Labels Pdf

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:

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!

As with just about everything in Excel, there are many different ways to accomplish any given task. 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.

Hope this approach helps you flatten data files quickly!

Resources

Excel For Mac Pivot Table Repeat Item Labels Greyed Out

• If you’d like to give this technique a try, you can practice by using the Excel file: Flat
• Excel U post: Get & Transform Query: An Alternative to Manually Flattening Data