Blog
How to Create a Pivot Table in Excel, Step by Step
A pivot table in Excel turns a long list of transactions, one row per order or shipment, into a summary by customer, channel or month, without writing a single formula. To create one, click inside your data, choose Insert > PivotTable, confirm the range, then drag fields into Rows, Columns and Values. That part takes a minute. What takes longer is the cleanup when a total quietly comes out wrong because of a blank row, a number stored as text or a refresh nobody ran. This guide covers both: the build, and the reasons pivot numbers drift from the source.
Prepare the source data first
Microsoft's guide to creating a PivotTable lists the conditions the source needs. Data should be organized in columns with a single header row, with no blank rows or columns, and each column should hold one data type. Dates mixed with text, or an amount column with "TBD" in a few cells, are the usual culprits behind odd results.
Format the source as a table with Ctrl+T before you build anything. When the source is a table, new rows are included the next time the pivot refreshes and new columns appear in the field list. A pivot built on a plain range ignores rows added below it until you change the data source.
A pivot table never changes your data. It works from a snapshot called the cache, so you can rearrange it freely without touching the shipment list.
How to create a pivot table in Excel
- Click any cell in the source data.
- On the Insert tab, click PivotTable and choose From Table/Range.
- Check that Table/Range covers all the data.
- Choose New Worksheet or Existing Worksheet for the location and click OK.

If you're not sure what layout you want, try Insert > Recommended PivotTables. Excel analyzes the data and previews a few summaries; pick one and it lands on a new sheet. In Excel for the web, Recommended PivotTables are available to Microsoft 365 subscribers.
Place fields in Rows, Columns, Values and Filters
Check a field in the PivotTable Fields pane and Excel puts it in a default area: non-numeric fields go to Rows, dates to Columns, and numbers to Values. Drag a field between areas to change that.
- Rows: the vertical breakdown, such as customer or product category.
- Columns: the horizontal comparison, such as month or channel.
- Values: the numbers to summarize, such as revenue or units.
- Filters: conditions for the whole table, such as year or sales rep.

Two or more fields in Rows create a hierarchy with subtotals at each level, as above. If you'll paste the result into a report, choose Design > Report Layout > Show in Tabular Form and Subtotals > Do Not Show Subtotals to get a flat, conventional table.
Change how values are summarized
Numbers in Values default to Sum. For average order value or the number of orders, click the field's arrow in the Values area and choose Value Field Settings. On Summarize Values By you can pick Sum, Count, Average, Max, Min and more, and Number Format applies thousands separators to the whole field.

The Show Values As tab turns numbers into ratios. % of Grand Total shows each customer's share of revenue; % of Column Total shows the channel mix within each month. Drag the same revenue field into Values twice, leave one as Sum and set the other to % of Grand Total, and you get dollars and share side by side.
Group dates by month or quarter
In current versions, putting a date field in Rows or Columns groups it by year, quarter and month automatically. For a different grouping, right-click a date and choose Group, then pick Months, Quarters or Years. If Group is grayed out, the date column contains text dates or blanks; fix them in the source and refresh.
Slicers and timelines
PivotTable Analyze > Insert Slicer adds buttons for a field, such as channel or category, that filter the table with one click. Insert Timeline does the same for dates with a draggable bar. In a meeting, "what about online only?" gets answered faster with a slicer than with the Filters area.
PivotCharts
Select a cell in the pivot and choose PivotTable Analyze > PivotChart. The chart is tied to the pivot, so changing fields or clicking a slicer updates both. Clustered columns or lines suit monthly revenue by channel; for share, a 100% stacked column stays readable with more categories than a pie. Hide the field buttons on the chart under PivotChart Analyze > Field Buttons.
Refresh and change the data source
Pivot tables traditionally don't recalculate when the source changes. In July 2025 Microsoft announced PivotTable Auto Refresh, rolling out gradually in Microsoft 365. Where it's available, it is on by default for new pivots and toggled from the PivotTable Analyze tab. It works only with data inside the same workbook, not with external sources like Power Query or databases.
Otherwise, refresh manually: right-click inside the pivot and choose Refresh, or use PivotTable Analyze > Refresh > Refresh All (Ctrl+Alt+F5) for every pivot in the workbook. To refresh whenever the file opens, turn on Refresh data when opening the file under PivotTable Options > Data.
If rows were added outside the original range, refreshing isn't enough. Use PivotTable Analyze > Change Data Source to reset the range.

👉 How to Create a Table in Excel: Ctrl+T, Totals, References
Worked example: monthly revenue by channel from shipment data
At Northgate Supply, the sales operations analyst builds the monthly revenue summary for the leadership meeting. The ERP export Shipments_2026.xlsx has seven columns, Ship Date, Customer, Channel, Category, Item, Units and Revenue, with about 4,800 rows from January through September. The request: revenue by channel by month, plus the top 10 customers and their share.
Step 1: check the source. The status bar showed a different Count and Numerical Count for the Revenue column: 23 cells were numbers stored as text. A few ship dates were text in "03.05.2026" format. After converting both, the analyst made the data a table named Shipments.
Step 2: build the channel-by-month pivot. On a new sheet: Channel in Rows, Ship Date in Columns, Revenue in Values. Excel grouped the dates by year and quarter, so a right-click on the column header and Group set it to months only. Number format went to thousands separators with no decimals.
Step 3: build the top-customer pivot. A second pivot from the same table put Customer in Rows and Revenue in Values twice, the second set to % of Grand Total. From the Customer arrow, Value Filters > Top 10 kept the ten largest accounts, led by Brightway Retail, Crestview Foods and Lakeview Distribution. Together they made up 61% of revenue.
Step 4: connect a slicer. A Category slicer was linked to both pivots through Report Connections. When someone asked about frozen products, one click answered it.
Step 5: next month. October rows go at the bottom of the table and Refresh All updates both pivots. Because the source is a table, nobody has to change the data source.
When a pivot table looks wrong
It shows Count instead of Sum. Microsoft notes that if Excel interprets the data as text, values are displayed as a COUNT. One text value or many blanks in the column is enough. Fix the source, then change the field back to Sum in Value Field Settings.
Totals don't match the source. The pivot hasn't been refreshed, or the source range doesn't include the new rows. Refresh first; if it still differs, check Change Data Source.
"The PivotTable field name is not valid." A header cell is empty or merged. Give every column a header and unmerge.
Clicking a pivot cell writes GETPIVOTDATA. That's Excel's default when a formula references a pivot. Turn it off from the arrow next to PivotTable Analyze > Options by unchecking Generate GetPivotData.
Column widths jump on every refresh. In PivotTable Options > Layout & Format, clear Autofit column widths on update.
Differences between Excel versions
The Windows versions of Microsoft 365, Excel 2024, 2021 and 2019 share the same menus. Excel for Mac builds pivots from Insert > PivotTable with the same field pane. Excel for the web creates and edits pivots too, choosing the destination in a side pane. Microsoft 365 also offers the PIVOTBY function and GROUPBY, which build pivot-style summaries as formulas that recalculate the moment the source changes.
FAQ
Q. Can one pivot table use data from several sheets?
Yes. Make each sheet a table, check Add this data to the Data Model when creating the pivot, and define relationships between the tables. If the sheets share the same columns, appending them into one table is simpler.
Q. How do I turn a pivot into a static table?
Copy the whole pivot and use Paste Special > Values. The result won't refresh, so use it only for the version you send.
Q. How do I delete a pivot table?
Select the entire pivot and press Delete. If it sits alone on a sheet, delete the sheet. Nearby data and charts aren't affected.
Q. Can I add a calculated column?
Yes. PivotTable Analyze > Fields, Items & Sets > Calculated Field lets you write a formula like =Revenue/Units. Calculated fields work on the summed values, which is right for ratios such as average price per unit.
How inline AI builds the monthly summary
With the shipment export open, you ask inline AI for "revenue by channel by month, and the top 10 customers with their share." inline AI fixes text numbers and dates first, then builds both pivots and a shared slicer. Companies that also run ARP have shipments and revenue in one place, so the same summary comes straight from live data without an export.
Hand your documents and your company’s work to inline AI.
Keep a file open, say what you need, and AI edits it or drafts a new one. Add the company AI OS and AI runs the rest of the work, ERP included.
Book a demo



