Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to permanently tabulate Pivot Table report and how to repeats all item labels. Let’s get started.
In the caption below, we have a Pivot Table report showing Sum of Total by States and Account Manager. Notice that, we have Row Labels in cell A3 where the Pivot Table starts from, which is not descriptive and polite to send this kind of report to end users. Since we have State and Account Manager fields in the Rows axis, we should be able to see State and Account Manager in lieu of Row Labels. The default Report Layout is Show in Compact Form
What we must do if we want to Show Report in Tabular Form and Repeat All Item Labels (when we have two or more field in the rows axis) is as follows:
- In the Report Layout drop-down of the Layout group of the Design contextual ribbon tab, click on Show in Tabular Form.
- 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.
Therefore, we want to permanently ensure that our Pivot Table Report is displayed in tabular form and all item labels are repeatedly:
- Open a new workbook and execute ALT + FTD
In the above caption, we can thus: Make changes to the default layout of PivotTables, click on Edit Default Layout
- In the Report Layout drop-down of the Edit Default Layout dialogue box, select Show in Tabular Form and check Repeat All Item Labels
- Click OK twice and close the workbook (no need to save)
- Open the workbook with data to summarize in Pivot Table
- Then, I dragged and dropped Total into the Values axis and State and Account Manager into the Rows axis respectively
In the caption above, we can see that the Pivot Table report in displayed in tabular form with corresponding field names; and all items in the State field are repeated.
CONCLUSION
What we are performed above has eliminated having to always show our report in tabular form and repeat all item labels. Definitely, this will ensure that we save time and focus on other things.