An Excel PivotTable layout is the arrangement of fields in four areas—Rows, Columns, Values, and Filters—that determines how source data is grouped and summarized. To create one in Excel for Microsoft 365 on Windows, select a cell in a clean data range, choose Insert > PivotTable, and then drag fields from the PivotTable Fields pane into those four areas.
What a PivotTable layout controls
A PivotTable does not rearrange or rewrite the original records. It builds a separate summary view from them. The fields you place in its areas decide which categories appear down the side, which appear across the top, what calculation appears in the body, and which overall filters readers can use. Changing a field’s position can change the summary without changing the source data.
Imagine a source table with columns named Date, Region, Product, Salesperson, and Sales. If Region is in Rows, Product is in Columns, and Sales is in Values, the report can compare sales by product within each region. Putting Date in Filters adds a control above the report for narrowing it to a selected date or period. The same source data can support many different layouts.
In Excel, the word “field” generally means a source column. A field can be used more than once—for example, Sales can appear in Values twice to show both its sum and its average. The four areas are not four separate worksheets; they are zones in the field list that define the PivotTable’s structure.
What the four field areas do
- Rows: Lists categories vertically. Region, salesperson, or product names are common row fields. If you place multiple fields here, Excel nests them, creating levels such as Region followed by Salesperson.
- Columns: Places categories horizontally across the report. A field such as Product can create a separate column for each product category, making comparisons easy when there are only a manageable number of categories.
- Values: Displays calculations based on source fields, such as Sum of Sales, Count of Orders, or Average of Duration. Excel summarizes numeric fields with Sum by default in many cases; fields containing text or unsuitable mixed values commonly default to Count.
- Filters: Adds a report-level filter above the PivotTable. Selecting a region, year, or other item limits the report to matching records. A filter is useful when readers need to switch the report’s focus without adding another row or column grouping.
Where a field appears matters. Putting Product in Rows gives a vertical list; putting it in Columns creates headings across the top. Neither position changes the underlying information, but one may be easier to read depending on how many distinct products there are and how the report will be used.
Build a layout from a source table
Start with a well-structured range: one header row, one record per row, and one type of information per column. Avoid blank header cells, merged cells, and completely blank rows or columns inside the data. Converting the range to an Excel Table first is often helpful: select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows. A table can expand as records are added, and the PivotTable can use its name as a source.
- Select a cell in the source range or table.
- Choose Insert > PivotTable. Confirm the table or range shown in the dialog, choose where the PivotTable should go, and select OK. A new worksheet is a convenient choice while learning or when the summary needs room.
- In the PivotTable Fields pane, select or drag fields into Rows, Columns, Values, and Filters. For example, put Region in Rows, Product in Columns, and Sales in Values.
- Check the Values area. If the result is not the calculation you need, open the field’s menu, choose Value Field Settings, and select a suitable option such as Sum, Count, or Average.
- Apply number formatting and adjust the report’s visual layout. If the source data changes later, refresh the PivotTable.
To reopen the field list after closing it, click inside the PivotTable and choose PivotTable Analyze > Field List. On Windows, the PivotTable Analyze and Design tabs appear when a PivotTable is selected. Excel for Mac offers the same basic field-area concept, although the ribbon and pane presentation can differ slightly. Excel for the web also supports creating and working with PivotTables, but some controls and advanced options may differ from the desktop version.
Choose a layout that answers the question
Good layouts begin with the question, not with a desire to use every available field. For a regional sales comparison, put Region in Rows and Sales in Values. To compare products within regions, add Product to Columns. If the report has dozens or hundreds of product categories, placing Product in Rows instead may prevent an excessively wide table.
- Use Rows for the main category people should scan or rank.
- Use Columns for a smaller set of categories that readers need to compare side by side.
- Use Values for measures that can be aggregated meaningfully, such as revenue, units, or record counts.
- Use Filters for optional scope choices, such as a reporting year or business unit, especially when those choices should not dominate the table.
For example, with Region in Rows, Product in Columns, and Sum of Sales in Values, the intersections show sales for each region-product combination. Row and column totals help reveal the overall contribution of each category. Adding Salesperson under Region in Rows creates a hierarchy: expand or collapse a region to show or hide the people within it. This is useful for exploration, but a deeper hierarchy may be harder to read in a report intended for a quick overview.
A common misconception is that a field in Filters has the same effect as a field in Rows. Both can limit what is visible, but they communicate different things. A row field displays its categories as part of the report’s structure. A report filter acts as a control over which records are included, usually without listing every category as a row.
Change the report’s visual layout
Field placement controls the summary’s logic; the Design tab controls several aspects of its presentation. Click the PivotTable, then use Design > Report Layout to select Compact Form, Outline Form, or Tabular Form. Compact Form places multiple row fields in one column and is often the default. Outline Form gives row fields separate columns with a more hierarchical appearance. Tabular Form places each row field in its own column, which can be easier to read or reuse in a flat-looking report.
These options do not create a different analysis. They change how row labels are displayed. In particular, a report in Compact Form can make it less obvious which column belongs to which row field when there are several nested levels. Tabular Form may be clearer for readers who want distinct category columns, though it does not turn the PivotTable into a normal data table.
The Design tab also includes controls for subtotals and grand totals. Choose Design > Subtotals to position or hide subtotals, and Design > Grand Totals to control totals for rows and columns. Totals can make a summary easier to interpret, but they can also be distracting when the report contains many nested levels or when the total is not meaningful for the measure.
For a polished result, use Value Field Settings to give a calculation a clear custom name, and use Number Format there to format the values as currency, percentages, or whole numbers as appropriate. Applying number formatting through the value field settings is generally preferable to formatting cells manually: a refresh or layout change is less likely to leave the field displaying inconsistent formatting.
Summarize, filter, and compare values
The Values area is where the PivotTable performs its main calculation. A numeric Sales field may appear as Sum of Sales, while a text field such as Salesperson is more likely to appear as Count of Salesperson. If the calculation is unexpected, open the value field’s menu and choose Value Field Settings. Confirm that the chosen summary matches the question: Count answers how many nonblank entries are represented, while Sum adds numeric values and Average calculates a mean.
Value Field Settings also includes options for showing values as comparisons, such as a percentage of a total or a difference from another item, where the selected field and layout support that calculation. These are display calculations: they change how the summarized values are presented, not the source records. Choose the base field and item carefully for comparisons, and check that the resulting percentages or differences answer the intended question.
Filters can be applied at more than one level. The Filters area creates a report filter, while fields in Rows or Columns can also be filtered from their label drop-downs. You can sort categories and filter to selected items or use available label and value filters. For instance, a value filter can help show regions whose summarized sales meet a threshold. Such filters operate on the PivotTable’s summarized view, so confirm whether you mean to filter source records before aggregation or categories after summarization.
Refreshes, source changes, and common problems
A PivotTable is a summary based on a source range or table; it does not necessarily update immediately when source cells change. Click inside it and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update workbook connections and PivotTables. If you add rows below a plain fixed source range, those new rows may fall outside the PivotTable’s source. Using an Excel Table as the source helps the source range expand as records are added, but the PivotTable still needs a refresh to incorporate changed records.
- Sum appears as Count: Check the source column for text, blanks, or values stored inconsistently. Correct the source data if it should be numeric, then refresh and set the summary to Sum if appropriate.
- A new category is missing: Confirm that the record is inside the source table or range, then refresh the PivotTable.
- Repeated labels look blank: Some report layouts display a category label once for a group rather than repeating it on every line. Try Design > Report Layout > Repeat All Item Labels when that option is available for the current layout.
- The report is too wide: Move a high-cardinality field from Columns to Rows or Filters, or filter the displayed categories.
- The source headers are unclear: Rename headers to unique, descriptive names and refresh. Duplicate or blank headers can prevent a clean field list or make fields difficult to identify.
Another practical issue is that refreshing can change the visible categories or the order of items as the source changes. If a report is copied into a presentation or used as a recurring deliverable, verify its filters, totals, and number formats after refresh. A PivotTable is a flexible analysis tool, but its output should still be checked before it is treated as a final report.
PivotTable layout versus a regular table
A regular Excel table stores and displays records; a PivotTable summarizes records. In a regular table, each row might be one sale with its date, region, product, and amount. In a PivotTable, those thousands of records can become a small cross-tabulation of totals by region and product. This makes PivotTables useful for exploration and recurring summaries, while the source table remains the place to edit individual records.
A PivotTable is not always the best output format. If someone needs a fixed, presentation-ready report with manually arranged labels, or needs to type directly into individual summary cells, a normal worksheet layout may be more suitable. PivotTable cells are generated from the summary, so typing into a result cell is not a way to edit the corresponding source record. Keep source data and analysis separate, and use a PivotTable when changing groupings or summaries is valuable.
Frequently Asked Questions
How do I change the layout of an Excel PivotTable?
Click the PivotTable, then drag fields between Rows, Columns, Values, and Filters in the PivotTable Fields pane. To change the visual arrangement of row fields, use Design > Report Layout and choose Compact, Outline, or Tabular Form.
What should go in Rows and Columns?
Put the main category you want to scan in Rows. Put a smaller comparison category in Columns when side-by-side headings are useful. If columns become too numerous, move that field to Rows or Filters instead.
Why does my PivotTable show Count instead of Sum?
Excel may choose Count when the source field contains text or values it cannot consistently treat as numbers. Inspect the source column, correct values stored as text if needed, refresh, and select Sum in Value Field Settings.
How do I make a PivotTable repeat row labels?
Select the PivotTable and choose Design > Report Layout > Repeat All Item Labels, if available for the chosen report layout. This fills repeated category labels down the report and can make a tabular-style output easier to read.
Does a PivotTable update automatically when its source changes?
Do not assume that it does. Refresh it with PivotTable Analyze > Refresh or Data > Refresh All. If records are added, make sure they are included in the source; using an Excel Table as the source helps it expand to include new rows.
Related
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Trendline Tutorial: Add, Format, and Choose OneTo add a trendline in Excel for Microsoft 365 on Windows, select a chart, then choose Chart Design…
- Pivot Table Average Not Working? Fix the Source DataIf a PivotTable average is missing, showing Count, or giving an unexpected result, check the value field’s summary…
- Doughnut Chart in Excel: Create, Format, and Read ItTo make a doughnut chart in Excel for Microsoft 365 on Windows, select your category labels and values,…
- Creating a Power Pivot in Excel: Steps and TipsTo create a Power Pivot in Excel for Microsoft 365 on Windows, enable the Power Pivot add-in if…
- Excel Exponential Trendline Formula and How to Use ItThe exponential trendline model is y = a ebx, where a is the value predicted when x is…
- Excel Line Chart Examples: How to Choose and FormatExcel line chart examples include monthly sales trends, several product lines compared over time, actual results against a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.