How to display row labels side by side in a pivot table

Key points of this article

Let the row labels occupy one column each. The core is to switch the report layout to a table format. The classification and summary need to be set separately.
When each row needs to have a complete upper-level label, turn on the duplicate item label and paste it for easy sorting and filtering.
Batch expansion or collapse can be performed by field as a whole, only the required layer is collapsed, and the remaining layers remain readable.
After switching layouts, randomly check whether the same record is in the same row, and check subtotals, totals and empty rows.
Before copying to the report, confirm that the data has been refreshed to avoid using old results as the current version.

When making a pivot table, row labels are indented by default: two- and three-level categories will be squeezed into the same column, the superior name only appears once, and the subordinate items are moved one space to the right. This type of layout is suitable for quick browsing on the screen, but it will be laborious if you want to copy it to a report, export it to colleagues, or need to sort and filter each record. What really needs to be changed at this time is the report layout, not the source data.

This article is written for those who need to organize the perspective results into a neat table. After reading, you will be able to distinguish the difference between compressed form, outline form and table form, know how to switch layouts, deal with repeated tags and blank lines, and learn to expand and collapse fields in batches. Finally, use several checks to confirm that the results are correct.

How to keep rows of data in the same row in a PivotTable? One sentence conclusion

Directly change the report layout to table format, each row field will occupy its own column, and the labels of the same record will be arranged in the same row. Subtotals and totals are separate settings, and you don't need to change them for layout purposes.

your situation	Recommended practices	reason
There are two or more row labels, and I want each to occupy one column.	Report layout selected as table format	Each layer of fields is independently organized into columns for easy copying and sorting.
Just want to make the results more compact and save horizontal space	Keep compressed form	Multi-level labels are indented in the same column and occupy fewer lines.
It is necessary to collapse the details by level and only view the summary	Use outline form, expand or collapse	Keep the layer buttons to facilitate retracting and unfolding layer by layer
Each row is required to be a complete record	table format and enable the duplicate items label	Each line has the name of the superior, which can be filtered independently after pasting.
Getting used to field drag and drop in earlier versions	Enable classic layout in pivot table options	Fields can be dragged and dropped directly in the grid, and the operation is closer to the old version

Before you start: Confirm these points first

Confirm that the field order is correct. Layout switching will not change the order of row fields. If the levels of "Region" and "Product" are reversed, you must first drag and adjust them in the field list, otherwise the table arrangement will also be reversed.
Distinguish between layout and summary. The subtotal determines whether there are subtotal rows under each group, and has no direct relationship with whether the labels are in the same row. If the two are set separately and mixed together for adjustment, it is easy to make changes more chaotic.
Make clear the purpose of the results. If you want to copy to other tables or make reports, it is recommended to use table format; if you only view it on the screen, the compressed format saves space. Different uses, different choices.
Confirm that the source data has been updated. After the layout is switched, the pivot table will retain the existing settings, but the new rows or fields added to the original data will not appear automatically, and the pivot table needs to be refreshed first.
Backup before switching. After the layout is changed, the arrangement position of the cells will change accordingly. If this table is also referenced by other formulas, it is recommended to copy a worksheet before operating.

Steps to switch to table format in Excel PivotTable

Select any cell in the pivot table and let the program identify the range of the pivot table.
Open the report layout related settings of the pivot table, find the tabular layout option and select it.
Check that the subtotals are still displayed. If you do not want subtotals under each group, choose not to display them in the subtotal settings.
If you want each row to have a complete parent label, enable the duplicate item label in the report layout.
Check whether there are extra blank lines between items. If so, cancel the blank line setting between items in the report layout.
Scroll through the entire table and confirm that each row field is separated into independent columns and the same record is in the same row.

Batch expansion, collapse and field adjustment

When there are many fields, clicking one by one is inefficient. You can operate by fields as a whole.

Expand or collapse entire fields in batches. Right-click on any row label and select the option to expand or collapse the entire field. All items under the field will be expanded or collapsed at once. You can also directly click the plus or minus button next to the row label to view it item by item.
Collapse only a certain layer. Right-click the row label of the corresponding field to collapse only this layer, leaving other levels as they are. This is suitable for scenarios where you first look at the broad categories and then look at the detailed items.
Batch drag fields. Drag a field to the row area in the field list, and multiple fields will form a hierarchy in the order they are placed. The order can still be adjusted again after dragging and dropping.
Batch select fields. The field list can only be checked or dragged one by one at a time, and there is no unified one-time selection method. It is recommended to clarify the order of row fields first, and then adjust the layout uniformly to avoid repeated changes.

Check each item after completion

After the layout adjustment is completed, do not copy and use it directly. Check the following table first.

Check items	How to see	What to do if there is a problem
Whether each row field occupies one column	Check whether the text at each level is in a separate column	Confirm that the layout has been selected as table format. If it has not been switched, select it again.
Whether the same record is in the same row	Randomly select two or three rows and compare them with the original data one by one.	If a row lacks a superior name, turn on the duplicate item label
Are subtotals and grand totals as expected?	Compare each set of summary values with the summation results of the original data	Modify the subtotal or total settings, do not change the numbers manually
Are there extra blank lines between items?	Scroll to see blank lines between groups	Cancel the blank line setting between items
Whether the data is the current version	Compare row counts and totals of source data	Recheck after refreshing the PivotTable

After confirming that it is correct, if you want to hand it over to others or put it into a report, it is recommended to paste the results as numerical values to avoid changes in other cells affecting the display.

❓ Frequently Asked Questions (FAQ)

1. Why did you switch between table format, row labels or indentation?

Usually only some settings have been changed, or the pivot table is still in a compressed form. Re-open the report layout options, confirm that the table format is selected, and then check whether any fields have been mistakenly placed in the column area.

2. What is the relationship between subtotals and row labels on the same row?

Subtotals only determine whether there are subtotal rows under each group, and have no direct relationship with whether the labels are in the same row. When subtotals are turned off, the arrangement of row labels is still determined by the report layout.

3. How to display multiple columns side by side in a pivot table?

Put the fields that need to be arranged horizontally into the column area, and each item in the column field will be expanded into columns in turn. If you want multiple numeric fields to be displayed side by side, you can put them all into the numeric area and they will be displayed horizontally on the same line.

4. What is the use of classic layout? Do you want to turn it on?

The classic layout allows dragging and dropping fields directly in the grid, which is suitable for those who are used to the old version. The placement of the perspective table may change after opening it. It is recommended to try it on a copy first and confirm it is correct before using it in the official form.

📝 Summary

How to keep rows of data in the same row in a PivotTable report? The key is to switch the report layout to table format, then enable repeat labels and cancel empty rows as needed. Subtotals must be set individually, and expansion and collapse must be performed in batches by field. After switching, check randomly and make sure it is correct before copying and using.

Open OmniDoc and work with your spreadsheet file