Clearblog618

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.

• Use the RepeatAllLabels property of the PivotTable object.Options are xlRepeatLabels and xlDoNotRepeatLabels. Dim ws As Worksheet Set ws = ActiveSheet Dim wb As Workbook Set wb = ActiveWorkbook Dim PTcache As PivotCache Dim PT As PivotTable 'Define the cache for the PivotTable Set PTcache =wb.PivotCaches.Create(SourceType:=xlDatabase,.
• Currently, I am working on a code that iterates through three sheets of data that are identical in formatting and then creates three pivot tables (one for each sheet) that has an identical format. I want to toggle the Repeat Labels format on within the VBA code, but cannot seem to do 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:

Right-click the row or column label you want to repeat, and click Field Settings. Click the Layout & Print tab, and check the Repeat item labels box. Make sure Show item labels in tabular form is.

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

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:

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:

Excel For Mac Pivot Table Repeat Item Labels

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!

Excel For Mac Pivot Table Repeat Item Labels Free

Resources

• 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