To display a PivotTable’s row fields in separate columns in Excel for Microsoft 365 on Windows, select the PivotTable and choose PivotTable Design > Report Layout > Show in Tabular Form. If you mean showing a field’s categories across the top of the table, drag that field into the Columns area in the PivotTable Fields pane instead; these are two different settings.
What “display data in columns” can mean
The request to show PivotTable data in columns usually refers to one of two layouts. First, you may want each row field to have its own worksheet column, rather than having several fields nested in one column. Tabular Form does that. Second, you may want categories such as years, regions, or product types to appear as headings across the top. That is controlled by the Columns area in the field list.
These choices affect the shape of the report, not the underlying source data. A PivotTable summarizes records and lets you rearrange fields without moving or changing the original rows. Understanding which kind of column display you need makes it easier to choose the right setting and avoid changing the wrong part of the layout.
Switch row fields to separate columns
By default, a new PivotTable often uses Compact Form. In that layout, multiple fields placed in the Rows area appear together in one worksheet column, with indentation showing their hierarchy. To give each row field its own column:
- Click any cell inside the PivotTable to show its contextual ribbon tabs.
- Open PivotTable Design > Report Layout.
- Select Show in Tabular Form.
- If the report should show a category label on every detail row, choose Report Layout > Repeat All Item Labels.
For example, if Region and Salesperson are both in Rows, Compact Form may show them in one column, with each salesperson indented beneath a region. Tabular Form puts Region and Salesperson in separate columns. Repeating item labels fills the Region column on each salesperson row instead of leaving it blank below the first entry.
In Excel for Mac, select the PivotTable and use the contextual PivotTable Design tab, then choose Report Layout and Show in Tabular Form. Ribbon names and placement can vary slightly by release. Excel for the web has fewer PivotTable layout controls than the desktop application; if the Report Layout option is unavailable, open the workbook in desktop Excel to change the report layout.
Put categories across the top
Use the Columns area when you want a field’s unique items to become column headings. Click inside the PivotTable so the PivotTable Fields pane appears. In the pane, drag the desired field from the field list into Columns. You can also select its checkbox and then move it into that area if Excel initially places it somewhere else.
Suppose your source has Date, Region, Product, and Sales fields. Place Product in Rows, Sales in Values, and Year (or a grouped Date field) in Columns to compare product totals by year. Each year becomes a separate column, while the values show the sum of Sales for each product and year. Place Region in Columns instead if the aim is to compare regions side by side.
A PivotTable can have several fields in Columns, but every extra field can create more headings and make the report wider. For a compact comparison, use a small number of meaningful categories. If you have many dates, group them by year or quarter rather than placing every individual date across the top.
Understand the four field areas
The PivotTable Fields pane divides fields among four areas. Moving fields between these areas changes how the summary is arranged:
- Rows: Groups records vertically, such as products or customer names.
- Columns: Splits the summary into headings across the top, such as years or regions.
- Values: Calculates summaries, such as sum, count, or average of a numeric field.
- Filters: Adds a report-level filter that can limit which records are included.
Fields in Rows and Columns are labels; a field in Values is summarized data. For instance, putting Sales in Columns does not usually create a useful set of sales totals by itself. To calculate sales, put Sales in Values. Put a category such as Month or Department in Columns to decide how those totals are divided. If a numeric field appears as Count instead of Sum, check whether its source cells contain text, blanks, or inconsistent values, and review the value field’s settings.
Choose the right report layout
Excel provides Compact, Outline, and Tabular report layouts under PivotTable Design > Report Layout. Compact Form saves horizontal space by combining row fields into one column. It is useful for a short, hierarchical summary. Tabular Form gives each row field a separate column and is often easier to scan, filter, copy, or use as a flat report. Outline Form also separates row fields, but presents them in a more hierarchical style.
Changing the report layout does not move a field from Rows to Columns. It changes how row fields are drawn in the worksheet. Conversely, moving a field to the Columns area changes the cross-tabulation itself. Pick Tabular Form when the problem is that row labels share one column; move a category to Columns when you want category headings across the top.
Tabular Form can make a PivotTable resemble a conventional table, but it is still a PivotTable, not a converted range of ordinary records. It can contain grouped summaries, subtotals, and grand totals. If another person needs a static, flat list for a system import, check whether those summary rows should be removed and whether a copied values-only version is more appropriate.
Subtotals, blank labels, and readability
Separate columns do not automatically mean that every row has a repeated label or that subtotals are gone. To fill labels down, select PivotTable Design > Report Layout > Repeat All Item Labels. This is particularly useful when users will filter, sort, copy, or export the visible report. Repeated labels can also make a printed report easier to follow.
To change where subtotals appear, use PivotTable Design > Subtotals and select an available display option, such as showing subtotals at the top or bottom of the group, or not showing them. The exact usefulness depends on the layout and the fields in Rows. Grand totals have their own control under PivotTable Design > Grand Totals. Review these settings after changing layouts because totals can add rows or columns that are not detail records.
For example, a sales report with Region and Salesperson in Rows may show a regional subtotal as well as individual salesperson totals. If you need a readable management summary, the subtotal is valuable. If you need one row per salesperson for further processing, turn off subtotals and consider repeating region labels. Choose the layout based on what the reader needs to do with the report, not just how it looks at first glance.
Common display problems and fixes
- All row labels still appear in one column: Confirm that you selected Show in Tabular Form, not a Columns-area change. Check that the fields you want separated are actually in Rows.
- Repeated rows show blank category cells: Select Repeat All Item Labels. Blank cells in a compact hierarchy can be intentional, not missing source data.
- The report became very wide: Too many distinct items may be in Columns. Move a field to Rows or Filters, or group dates into a higher-level period.
- Values show Count rather than Sum: Excel may be treating some source values as text or finding nonnumeric entries. Inspect the source column, correct inconsistent entries, and refresh the PivotTable.
- New source records do not appear: Refresh the PivotTable, and confirm its source range includes the added records. A formatted Excel Table used as the source can expand as records are added, but the PivotTable still needs refreshing.
Refreshing updates the summary from its source; it does not necessarily fix a source range that excludes new data. If the source is an ordinary fixed range, update the PivotTable’s data source when necessary. After refreshing, recheck formatting and labels, especially if new categories have appeared.
Formatting and sharing a column-based PivotTable
Once the layout is correct, adjust column widths and number formats so the report is practical to read. You can apply a value format through the value field’s settings so that currency, percentages, or dates display consistently. Applying a worksheet number format directly can work, but field-level formatting is generally more dependable when the PivotTable refreshes.
PivotTable styles are available on the contextual PivotTable Design tab. A style can improve contrast between headings, detail rows, and totals, but it does not change the field arrangement. For printing, check page orientation, scaling, and whether the wide column layout fits on the page. A PivotTable with many column items may be easier to explore on screen than to print on a single page.
When sharing a workbook, remember that PivotTable filters and field buttons can affect what others see. Keep the source data and the summary’s purpose clear, and avoid assuming a displayed blank is a zero. Depending on the layout and source, a blank may indicate no matching records, an empty source value, or a suppressed repeated label.
Frequently Asked Questions
How do I show row labels in separate columns in an Excel PivotTable?
Click the PivotTable, then choose PivotTable Design > Report Layout > Show in Tabular Form. To repeat category names on every row, also choose Repeat All Item Labels.
How do I display months or years as PivotTable columns?
Drag the date field, or a grouped date field, into the Columns area in the PivotTable Fields pane. Add the measure you want to compare, such as Sales, to Values.
Why does my PivotTable show the same field in one column?
It may be using Compact Form, which combines multiple row fields in a single column. Switch to Tabular Form to give each field in Rows its own column.
Can I make a PivotTable show every label instead of blanks?
Yes. Use PivotTable Design > Report Layout > Repeat All Item Labels. This fills repeated group labels in the displayed report; it does not alter blank cells in the source data.
Does changing the layout change the source data?
No. Report Layout changes how the PivotTable displays its summary. Moving fields among Rows, Columns, Values, and Filters changes the summary arrangement, but does not rewrite the original records.
Related
- Excel Often Not Responding? Causes, Fixes, and Prevention
- Free Yearly Expense Tracking Spreadsheet: Setup Guide
- Excel ODE Solver: How to Solve Differential Equations
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Org Chart in Excel or Word? Choosing the Right ToolFor a straightforward org chart that is mainly a document, use Word; for a chart that sits alongside…
- Edit a Pivot Table in Google Sheets: Steps and TipsTo edit a pivot table in Google Sheets, click any cell inside it to open the Pivot table…
- Excel Pivot Table Org Chart: Options and WorkaroundsAn Excel PivotTable cannot turn employee records into a graphical organization chart with connected boxes. It can summarize…
- Packed Bubble Chart in Excel: Options and AlternativesExcel for Microsoft 365 does not include a native packed bubble chart. You can make a standard bubble…
- Excel Pivot Table Sort by Sum ColumnTo sort an Excel pivot table by its Sum column, right-click any value in that column, choose Sort,…
- Excel Graph: How to Move the X-Axis to the BottomTo move an Excel chart’s x-axis to the bottom, select the vertical (y) axis, open Format Axis, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.