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.
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. 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. True if item labels are repeated for the specified PivotField; otherwise, False. The setting of the RepeatLabels property corresponds to the Repeat item labels check box on the Layout & Print tab of the Field Settings dialog box for a field in a PivotTable. To specify whether to repeat item labels for all PivotFields in a PivotTable in a single. In the Report Layout drop-down of the Layout group of the Design contextual ribbon tab, click on Repeat All Item Labels; The above step requires six (6) mouse clicks. Now, if we regularly prepare Pivot Table Reports, we must perform the six (6) clicks repeatedly, such a routine might be uninteresting.
Note: An alternative would be to use a Get & Transform query, as discussed in this post.
Flattening Data
Currently my pivot table contains column headers like this: But I would like the first layer of headers to repeat itself, so that it looks like this: Is there an option for Pivot Tables to do this? I tried going into Design Report Layout Repeat all item labels, but that only appears to repeat row headers, not columns. Hello all, Having a strange issue with my Pivot Table. I have selected 'Repeat All Item Labels' but as you can see, they're still being grouped/merged. Any ideas would be great:) 308883.
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:
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.
Mac Excel Pivot Table Repeat Item Labels
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
- 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
Printing a Pivot Table Report - Excel
As applicable to Excel 2007
Excel Pivot Table Tutorial:
12. Printing a PivotTable report, Repeat Row Labels, Set Print Titles, Insert Page Breaks, Print Area, Print Layout.
Refer complete Tutorial on working with Pivot Tables using VBA:
To print an Excel Pivot Table report, the worksheet print features and those for Pivot Table reports are required to be used together.
Print Options in the 'Pivot Table Options' dialog box:
To launch the dialog box, click 'Options' tab under the 'PivotTable Tools' tab on the ribbon, then click 'Options' in the 'PivotTable' group and click 'Options' again which opens the dialog box. In the 'Print' section of the 'Printing' tab of this dialog box, you have three options for printing a Pivot Table:
1. Print expand/collapse buttons when displayed on Pivot Table:
If you have chosen to show expand/collapse buttons in your Pivot Table, then you can also print these buttons by selecting the check box of 'Print expand/collapse buttons when displayed on PivotTable' in the 'Print Section' of the 'Printing' tab.
2. Repeat row labels on each printed page:
To repeat the outer row label items on the printout of each page of the Pivot Table, select the check box of 'Repeat row labels on each printed page' in the 'Print Section' of the 'Printing' tab. This is applicable when there is more than one row label item in the Pivot Table report.
3. Set print titles:
To repeat field titles including Row Labels, Value Field & Report Field names, on the printout of each page of the Pivot Table, select the check box of 'Set print titles' in the 'Print Section' of the 'Printing' tab.
Example showing different Printing options for a Pivot Table:
Refer Image 1a which shows a Pivot Table Report, being split into the next page after row no. 29.
Image 1b shows the print of the second page from row no. 30 onwards, when 'Repeat row labels on each printed page' is not selected and the outer row label items of 'Compact', 'Europe' & 'France' do not show.
Image 1c shows the second page when 'Repeat row labels on each printed page' check box is selected (and 'Set print titles' check box is not selected) and the outer row label items of 'Compact', 'Europe' & 'France' show in printing.
Refer Image 1d which is the print of the second page when 'Repeat row labels on each printed page' is not selected and 'Set print titles' is selected. The outer row label items of 'Compact', 'Europe' & 'France' do not show but the Row Labels of 'Car Models', 'Region', 'Country' & 'City' and the Value field 'Sales' as also the Report Filter field of 'Year' all show in printing.
Refer Image 1e which is the print of the second page when both 'Repeat row labels on each printed page' and 'Set print titles' are selected. The outer row label items of 'Compact', 'Europe' & 'France' show, and the Row Labels of 'Car Models', 'Region', 'Country' & 'City' and the Value field 'Sales' as also the Report Filter field of 'Year' all show in printing.
Insert page break after each item (in the 'Field Settings' dialog box):
To print a Pivot Table report with specified parts on separate pages, you can set a page break after an item of any outer row label. In the Pivot Table report, right-click on the outer row label area (whose items you want to appear on separate pages) in a Pivot Table report, select 'Field Settings', this will open the dialog box of 'Field Settings', click on 'Layout & Print' tab, and then in the 'Print' section select check box of 'Insert page break after each item'. This will print each item of the outer row label which is right-clicked, on a separate page. Note: You can also open the dialog box of 'Field Settings' by clicking 'Options' tab under the 'PivotTable Tools' tab on the ribbon, and in the 'Active Field' group click 'Field Settings'. Refer Image 2 (where the 'Region' row label has been selected and 'Insert page break after each item' enabled) which shows the print view of page breaks with horizontal broken lines after each Region of Europe and North America.
Print Titles (in the 'Page Layout' tab):
You can also repeat specific rows at the top or columns on the left while printing a Pivot Table report, irrespective of the 'Printing' settings in the 'PivotTable Options' dialog box of the Pivot Table. To do this, in the 'Page Layout' tab on the ribbon, in the 'Page Setup' group, click 'Print Titles', this will open the dialog box of 'Page Setup', go to the 'Sheet' tab, and in the heading 'Print Titles' enter values in the boxes of 'Rows to repeat at top' or 'Columns to repeat at left'. Note: Ensure these boxes are cleared to set 'Repeat row labels on each printed page' and 'Set print titles' in the dialog box of 'PivotTable Options' explained above.
--------------------------------------------------------------------------------------------------------------------------------
Steps to Print a Pivot Table Report
1. Set Print Area
In case you have multiple Pivot Table reports in a single worksheet, set the print area of the specific Pivot Table report you wish to print - under the 'Page Layout' tab on the ribbon, click on 'Print Area' in the 'Page Setup' group. You can set the area to include the entire Pivot Table which should be pre-selected. You can select the entire Pivot Table by clicking the 'Options' tab under the 'PivotTable Tools' tab on the ribbon, then click 'Select' in the 'Actions' group and finally click 'Entire PivotTable'.
Excel Pivot Table Repeat Item Labels Not Working
2. Determine Page Layout
For a Page Layout View, under the 'View' tab on the ribbon, click on 'Page Layout' in the 'Workbook Views' group. This enables to view the document as it will appear on the printed page, to see where pages begin and end, and to view any headers or footers on the page. You can set margins, switch page orientation, choose paper size, use rulers to determine width and height, set headers & footers, apply scaling for printing, print titles, and so on.
3. Print Settings specific for a Pivot Table report
As explained above, printing options specific to a Pivot Table report are available in the 'PivotTable Options' dialog box and the 'Field Settings' dialog box. Below are the print options and settings to be determined likewise:
Print expand/collapse buttons when displayed on Pivot Table;
Repeat row labels on each printed page;
Set print titles; and
Insert page break after each item.
4. Print Titles (in the 'Page Layout' tab)
As explained above, print titles can also be set in the 'Page Layout' tab on the ribbon.
5. Preview Page Breaks
For a Page Break View, under the 'View' tab on the ribbon, click on 'Page Break Preview' in the 'Workbook Views' group. This view gives a preview of where pages will break when the report is printed. You can make any overall changes where you want the pages to break.
6. Print Preview
For a Print Review, click the Microsoft Office Button on the top left of the ribbon, point your mouse to 'Print' and click on 'Print Preview' in the list of commands. This shows the final layout of the page as it will be printed. Preview and make changes to pages before printing.