Pivot Tables in Microsoft Excel: How to Create and Use

A PivotTable in Microsoft Excel summarizes and reorganizes a larger data set so you can compare totals, counts, and other measures without writing formulas for every result. In Microsoft 365 for Windows, select a cell in your data, choose Insert > PivotTable, select where to put it, and arrange fields in the PivotTable Fields pane.

What a PivotTable Does

A PivotTable is an interactive summary of structured source data. Rather than changing or duplicating the original records, it groups them into a report that can answer questions such as how much each region sold, how many orders each product received, or which months had the highest expenses. You can rearrange the report by moving fields between areas, changing the calculation, or applying filters.

Imagine a sales table with one row per transaction and columns for Date, Region, Salesperson, Product, and Amount. A PivotTable can show total Amount by Region, then break each region down by Product. Moving Product from Rows to Columns changes the layout while keeping the underlying records intact. The report is a view of the data, not a separate source of truth.

The name refers to the ability to pivot the perspective: the same records can be summarized in different arrangements. PivotTables are useful for exploration and recurring reports, but they do not automatically correct poor source data or explain why a number changed. You still need to check the source and choose a summary that fits the question.

Prepare the Source Data

A clean, rectangular data set makes a PivotTable easier to create and maintain. Put one distinct field name at the top of each column, keep each record on its own row, and avoid blank rows or columns within the data. Do not merge cells in the header or use multi-row headings. Each column should contain one kind of information; for example, keep dates in a Date column and amounts in an Amount column.

Give every column a meaningful, unique heading, such as Order Date or Net Sales.
Remove completely blank rows inside the data and check that values intended as numbers or dates are stored in the correct type.
Keep totals and explanatory notes outside the source range. A grand total row included as a record can be counted again in the PivotTable.
Consider converting the range to an Excel Table with Insert > Table. Tables expand when you add rows, and a PivotTable based on a Table can use the expanded source after you refresh it.

For instance, if you add April transactions beneath a Table named SalesData, the table can include those new rows automatically. A PivotTable based on that table still needs refreshing before its displayed results reflect the additions. If the source is an ordinary fixed range, newly added rows may fall outside the original source range and need to be included by changing the source.

Create a PivotTable in Excel

In Excel for Microsoft 365 on Windows, click a cell inside the source data or select the complete range. Choose Insert > PivotTable. Excel normally identifies the current region as the table or range. Confirm the source, choose New Worksheet or Existing Worksheet, and select OK. If you choose an existing worksheet, specify an empty destination cell so the report does not overwrite other content.

In the PivotTable Fields pane, find the source column names.
Drag a field into Rows to list its distinct values vertically. For the example, put Region in Rows.
Drag Amount into Values to calculate a summary for each region.
Optionally put Date in Columns to compare periods across the top, or put a field in Filters to filter the whole report.
Review the grand totals and the value calculation before interpreting the results.

You can also select fields using their checkboxes; Excel places them in areas automatically, but you may need to move them to get the layout you want. To change a report, drag a field from one area to another or remove it from its area. The PivotTable Fields pane can be shown again from the PivotTable ribbon when the pane is hidden.

On Excel for Mac, the basic workflow is similar: select the source and use Insert > PivotTable, then arrange fields in the PivotTable Builder. Button names and the dialog layout can vary slightly by release. Excel for the web also supports creating and working with PivotTables, though some advanced desktop options may not be available there.

Arrange Fields and Choose a Calculation

The four main field areas determine what the report displays. Rows and Columns create the report’s structure. Values contain the numbers or counts being summarized. Filters let you limit the report to selected items. For a sales report, Region in Rows and Amount in Values produces one total per region. Adding Product under Region in Rows creates nested product detail within each region.

Excel often uses Sum for numeric fields and Count for text fields, but the choice can depend on the source values. If Amount appears as Count rather than Sum, check whether the source contains text, blanks, or numbers stored as text. To change a calculation, open the menu for the value field in the Values area, choose Value Field Settings, and select a summary such as Sum, Count, Average, Max, or Min. Available options depend on the field and the PivotTable type.

Value Field Settings also offers Show Values As options, such as a percentage of a total or a difference from another item. These change how values are displayed; they do not change the source records. Number Format in the settings can format the report as currency, a percentage, or another appropriate number style. Formatting the value field is usually more reliable than applying a format to individual cells, because the PivotTable can rebuild its cells when refreshed.

Filter, Group, and Explore Results

Use a field’s drop-down filter in the report to show selected row or column items. A field placed in Filters creates a report-level filter above the PivotTable. For an easier interactive control, select the PivotTable and use PivotTable Analyze > Insert Slicer, then choose a categorical field such as Region or Product. A slicer displays buttons for filtering and makes the active selection visible. For date fields, Insert Timeline can provide a visual way to filter by time when supported for that PivotTable.

Excel may group a date field into years, quarters, and months when it is placed in a PivotTable. You can also select date items, right-click, and choose Group to set grouping intervals where the feature is available. Grouping requires usable date values; text that merely looks like a date can prevent the expected grouping. To undo a grouping, right-click a grouped item and choose Ungroup.

Sorting the labels can reveal the largest or smallest categories, while a value filter can show items meeting a threshold, such as regions with sales above a chosen amount. These tools help narrow a report, but the displayed result depends on the active filters. Before sharing a finding, check the filter indicators and confirm that the report includes the intended dates, products, and locations.

Refresh Data and Keep Reports Current

A PivotTable generally stores a summary based on its source at the time it was last refreshed. When source values change or rows are added, right-click the PivotTable and choose Refresh, or use PivotTable Analyze > Refresh. To refresh all PivotTables and other workbook connections, use Data > Refresh All. Refreshing updates the report from its source; it does not necessarily expand a fixed source range to include new rows.

To check or change the source, select the PivotTable and use PivotTable Analyze > Change Data Source. A PivotTable based on an Excel Table is often easier to maintain as records are appended, but it still needs a refresh. In PivotTable Options, the Data tab includes an option to refresh data when opening the file. This can be useful for recurring workbooks, though refresh behavior can depend on connections, permissions, and whether the source is available.

Refreshing can also affect layout and formatting. In PivotTable Options, settings such as preserving cell formatting on update can help keep the appearance consistent. If a workbook draws on an external connection or a Data Model, the refresh process may require access to the source or credentials; a refresh error is not necessarily a problem with the PivotTable layout itself.

Common Problems and Practical Fixes

Sum appears as Count: Inspect the source field for text values, spaces, or errors mixed with numbers. Correct the source data, refresh, and choose Sum in Value Field Settings.
New records are missing: Refresh the PivotTable and confirm the source includes the new rows. A fixed range may need to be expanded; an Excel Table can make the source range easier to extend.
Dates will not group: Check for blanks, text dates, or invalid entries in the date column. Make the source values real Excel dates, refresh, and try grouping again.
Duplicate-looking categories appear: Values such as North and North with an extra space are different text. Standardize spelling and remove unwanted spaces in the source, then refresh.
The report shows unexpected totals: Check whether a source total row was included, whether filters are active, and whether the Values field is set to the intended calculation.

One common misconception is that changing a number directly in the PivotTable edits the original transaction. It does not: PivotTable cells are generated from the summary, and many cannot be edited like ordinary worksheet cells. Make corrections in the source data and refresh. If someone needs a static report, copy the PivotTable results and use Paste Special > Values in another location, understanding that the copied figures will no longer update with the source.

PivotTables Compared with Formulas and Charts

PivotTables are a strong choice when you need to explore categories, rearrange summaries, or produce a report from many rows without building a grid of formulas. Formulas are preferable when a result must follow a specific custom calculation, feed a carefully controlled model, or update cell by cell in a layout that should not change. Excel Tables with formulas can also make row-level calculations easier to audit.

A PivotChart can visualize a PivotTable and usually responds to its filters and slicers. To create one, select the PivotTable and choose PivotTable Analyze > PivotChart. Use a PivotChart when readers need a visual comparison as well as totals. It is not a substitute for validating the underlying numbers: a chart inherits the PivotTable’s filters, grouping, and source limitations.

For very large or related data sets, Excel’s Data Model and Power Pivot features can support relationships and more advanced measures, depending on the Excel edition and platform. A standard PivotTable based on one flat table is simpler to maintain and is sufficient for many everyday summaries. Choose the more advanced model when the data relationships or calculations require it, rather than adding complexity without a clear need.

Frequently Asked Questions

Are PivotTables available in Excel for free?
PivotTables are an Excel feature, but whether you can use them depends on your Excel access and edition. Excel for the web includes PivotTable capabilities, while desktop Microsoft 365 provides a broader set of analysis and configuration options. Check the features available in the particular app and account you use.

Do PivotTables change my original data?
No. A PivotTable summarizes its source and displays the results separately. Edit the source records to correct them, then refresh the PivotTable. Typing into a PivotTable result cell is not a reliable way to change the underlying transaction.

Why does my PivotTable not update automatically?
PivotTables usually need a refresh after source changes. Use Refresh or Data > Refresh All. You can also enable refresh when opening the workbook in PivotTable Options, but this depends on the source being accessible and the refresh succeeding.

Can I make a PivotTable from multiple worksheets?
Yes, but the best method depends on how the data is organized. For related tables, the Excel Data Model can connect tables through relationships and support a PivotTable. For similarly structured sheets, combine the records into one clean table when practical; older consolidation approaches may have limitations compared with a properly structured source.

Can I use a PivotTable on a phone?
Excel mobile apps can display and interact with workbook content, but available PivotTable editing features may be more limited than in desktop Excel. For building a report or changing advanced settings, use Excel for Microsoft 365 on Windows or another desktop version with the required feature.

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.