Excel Pivot Table Rows and Filters: How They Work

Charts & pivot tables · Updated November 19, 2026

In an Excel PivotTable, the Rows area determines which categories appear down the left side, while the Filters area adds a report-wide dropdown for limiting which source records are included. Drag a field into Rows to organize the summary; drag a field into Filters when you want to choose which items the whole PivotTable displays.

Rows and Filters: The Core Difference

PivotTables divide their layout into areas that give fields different jobs. Rows creates the labels that structure the summary vertically. Filters lets you narrow the report by a field without making that field another level of row labels. Neither area changes the source data itself: each controls how the PivotTable summarizes and displays that data.

For example, a sales PivotTable might put Region in Rows and Sales in Values. It then shows a line for each region and a total for each one. If you put Year in Filters, you can select 2025 or 2026 from a dropdown above the report, and the region totals update to match that selection. Year does not appear as another row label.

  • Rows: Creates categories or nested categories in the body of the report.
  • Filters: Adds a dropdown above the report to include or exclude items for the PivotTable as a whole.
  • Values: Calculates a summary, such as a sum, count, or average, for the records represented by the row and column categories.

A field’s placement is a design choice, not a change to what the field means in the source table. The same Region field can be used to organize detail by region, or another suitable field can be used as a report filter when the goal is to select a subset of the data.

Create a PivotTable and Place Fields

Start with a clean, tabular source range: one header row, one record per row, and a distinct heading for each 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 useful because the table can expand as records are added.

  1. Click a cell in the source data, then choose Insert > PivotTable.
  2. Check that Excel has selected the right table or range. Choose whether the PivotTable should go on a new worksheet or an existing one, then select OK.
  3. In the PivotTable Fields pane, find the field names that match the source headers.
  4. Drag a category field, such as Region, into Rows. Drag a measure, such as Sales, into Values.
  5. Drag a field such as Year into Filters if you want a report-wide selection control.

You can also select checkboxes in the field list; Excel places fields in an area automatically, so check the layout and move a field if needed. To show the pane again in Excel for Microsoft 365 on Windows, select the PivotTable and choose PivotTable Analyze > Field List. In Excel for Mac, select the PivotTable and use the PivotTable tab or ribbon controls to show the field list; labels and placement can vary by release. Excel for the web supports PivotTables, though some commands and advanced layout options differ from desktop Excel.

What Happens When a Field Goes in Rows

A single field in Rows produces a category for each distinct item in that field. If Region contains East, West, and Central, the PivotTable can show those three row labels. The values beside each label summarize the source records belonging to that category. A numeric field in Values commonly defaults to Sum, while a text field commonly defaults to Count; check the calculation because mixed data or blanks can affect the result.

Adding more than one field to Rows creates a hierarchy. For instance, put Region above Salesperson to group each salesperson under a region. The order matters: Region first makes it the outer level, while Salesperson first makes salesperson the outer level. Use the expand and collapse controls beside row labels to show or hide lower levels when the layout supports them.

Row fields are appropriate when readers need to compare categories or inspect detail. They can make a report longer, however. Adding customer, product, and transaction-level fields all to Rows may produce a very large PivotTable, so include only the detail needed for the question at hand. Fields that are not needed as visible labels may be better placed in Filters or left out.

How the Filters Area Works

A field in Filters appears as a report filter above the PivotTable. Open its dropdown and choose the item or items to include. Selecting a particular year, for example, limits the PivotTable’s displayed summary to records for that year. Choose the all-items option to return to the full report. Depending on the dropdown, you may need to select Select Multiple Items before checking several choices.

Report filters are useful when one selection should apply to the entire summary and the field does not need to appear in the row or column labels. Examples include selecting one department, sales channel, or reporting period. You can add more than one field to Filters, then make a separate selection in each dropdown. The combined choices narrow the report together.

A report filter is not the only way to filter a PivotTable. The dropdown beside a row label can filter that row field, while a column-label dropdown filters a column field. Those controls sit within the report’s layout; the Filters area sits above it. The distinction is useful: choose Rows when categories should be visible, a row-label filter when you want to limit a visible category field, and Filters when you want a separate report-level selector.

Worked Example: Sales by Region and Year

Imagine a source table with columns for Date, Region, Salesperson, Product, and Sales. To report sales by region for a chosen year, place Region in Rows, Sales in Values, and Date in Filters. The PivotTable will summarize sales for each region, while the Date dropdown lets you limit the report to selected dates or date groupings available in the table.

For a clearer year-by-year comparison, put Year or a grouped Date field in Rows or Columns instead of Filters. The report can then show multiple years at once, with region totals under each year or years beside each region. This is a different question from filtering: a filter selects which records to show, while a row or column field displays the selected categories for comparison.

If the date dropdown lists individual dates rather than years, you can group dates in the PivotTable when Excel permits it. Grouping is not available in every source or setup, including some PivotTables based on the Data Model. You can also add a separate Year column to the source data before creating or refreshing the PivotTable. Choose the option that makes the report easiest for its readers to understand.

Filters Compared with Slicers and Label Filters

A report filter is compact and takes little space, but it can be easy to overlook because it is a dropdown above the report. A slicer is a visible panel of buttons for filtering a PivotTable field. Select the PivotTable, then use PivotTable Analyze > Insert Slicer and choose the field. Slicers make the available choices more obvious and are helpful on dashboards, especially when a reader needs to change filters repeatedly.

For dates, a timeline can provide a visual way to filter date-based PivotTable data where the feature is supported. Slicers and timelines are controls for filtering; they do not replace Rows or Values. A field placed in Rows still determines the visible grouping, regardless of whether a slicer is also used to narrow the displayed data.

Label filters and value filters answer different questions. A label filter keeps or excludes categories based on their names, such as showing only product labels that begin with a certain word. A value filter limits categories based on their summarized results, such as displaying regions with sales above a chosen amount. These options are accessed from the relevant row or column field dropdown, not by placing the field in the Filters area.

Common Problems and How to Fix Them

  • The field list is missing: Select a cell inside the PivotTable and reopen the Field List from the PivotTable Analyze tab. Clicking outside the PivotTable can hide contextual PivotTable tabs.
  • A field appears as Count instead of Sum: In the Values area, open the field’s menu, choose Value Field Settings, and select the intended summary. Check the source column for text-formatted numbers or nonnumeric entries if Sum is unavailable or produces an unexpected result.
  • New records do not appear: Refresh the PivotTable using PivotTable Analyze > Refresh. If the added rows are outside a fixed source range, update the source or use an Excel Table as the source so its expanding range is easier to maintain.
  • The filter selection seems to do nothing: Confirm that the selected field has multiple items in the current source data and that the relevant records are included in the PivotTable source. Also check other report filters, which may already exclude the items you expect to see.
  • Row labels look nested or indented: Multiple fields in Rows create levels. Reorder them in the PivotTable Fields pane, or remove a field if you want a flatter summary.

Refreshing updates the PivotTable from its source; it does not necessarily change the source range to include records added beyond that range. If the source is an Excel Table, new rows are generally included when the PivotTable is refreshed. If it is a fixed range, verify the source through PivotTable Analyze > Change Data Source and extend it when necessary.

Choosing a Layout That Answers the Question

Before placing fields, decide whether the reader needs to compare categories, choose a subset, or see both. If the goal is comparison, put the categories in Rows or Columns. If the goal is to let someone select one reporting context, put that field in Filters. If readers must understand the current selection at a glance, consider a slicer rather than a small dropdown.

  • Use Rows for a short list of categories that should be visible in the report.
  • Use nested row fields when readers need a clear parent-to-detail structure, such as Region then Salesperson.
  • Use Filters for fields that control the whole report but do not need to take up row space.
  • Use a slicer when quick, visible filtering matters more than a compact layout.
  • Test the report with several filter selections and confirm the totals and labels still make sense.

Keep the source data intact and treat the PivotTable as a view of it. If the source is changed, refresh the PivotTable; if the report will be shared, make the filter choices and any hidden or collapsed row levels clear. A carefully chosen layout helps prevent readers from mistaking a filtered total for a total across all records.

Frequently Asked Questions

What is the difference between Rows and Filters in an Excel PivotTable?
Rows displays categories as labels in the PivotTable and can create nested levels. Filters adds a dropdown above the report to limit which records the PivotTable summarizes without displaying that field as a row category.

How do I add a filter to a PivotTable?
Select the PivotTable, open the PivotTable Fields pane, and drag the desired field into Filters. Use the dropdown above the PivotTable to choose which items to include.

Can I put more than one field in the Rows area?
Yes. Multiple row fields create a hierarchy in the order shown in the Rows area. For example, Region above Salesperson groups people under their region; changing the order changes the nesting.

Can I filter a PivotTable without using the Filters area?
Yes. Use a row- or column-label dropdown to filter that field, or insert a slicer for a visible button-based control. These methods filter the PivotTable without adding a report-filter dropdown above it.

Why does my PivotTable filter not show new data?
Refresh the PivotTable first. If the new records are outside its source range, update the source range or base the PivotTable on an Excel Table that expands as data is added.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.