Basic Pivot Table: Create and Use One in Excel

A basic pivot table is an interactive summary of source data: in Excel for Microsoft 365 on Windows, select a clean data range, choose Insert > PivotTable, and place fields in Rows, Columns, Values, or Filters. For example, it can turn a list of sales transactions into sales totals by region and quarter without changing the original records.

What a basic pivot table does

A pivot table groups records and calculates a summary, such as a sum, count, or average. Instead of writing formulas for each category, you tell Excel which fields define the groups and which values to summarize. You can then rearrange those fields to ask a different question of the same data.

It is called a pivot table because you can pivot, or reorient, the summary. Put Region in Rows and Quarter in Columns to compare regions across quarters. Swap those placements to make quarters the row labels instead. The underlying source data stays intact; the pivot table displays a separate summary.

A pivot table is useful for questions such as:

How much did each product sell in each region?
How many orders came from each sales channel?
What was the average delivery time by month?
Which categories account for the largest share of total costs?

It does not automatically determine what a result means. The fields, summary calculation, and source data all affect the answer, so check that the layout matches the question you are asking.

A small example

Suppose a worksheet contains transaction records with columns named Region, Quarter, and Sales. Each row represents one transaction. A basic pivot table can put Region in Rows, Quarter in Columns, and Sales in Values. Excel then adds the sales figures for each region-quarter combination and displays row, column, and overall totals.

For example, imagine the source has these six records: East has sales of 120 in Q1 and 80 in Q2; West has 150 in Q1 and 50 in Q2; North has 100 in each quarter. The summary shows 200 for each region, 370 for Q1, 230 for Q2, and 600 as the grand total. Those totals can be checked against the six original amounts.

If you drag Product into Rows as well, Excel can show products nested under each region. If you move Quarter from Columns to Filters, you can select a quarter to display without changing the source records. These changes alter the view, not the transaction data.

Prepare the source data

Good source data is the foundation of a reliable pivot table. Use one header row followed by records, with one type of information in each column. Avoid blank rows and columns inside the list, merged cells, and repeated header rows. Each column should have a meaningful, unique heading, such as Order Date, Region, Product, and Sales.

Keep each row at a consistent level of detail. If each row is one order line, do not also insert a region subtotal row among the records; the pivot table would treat that subtotal as another record and may count it twice. Store dates as real Excel dates and amounts as numbers, rather than as text containing currency symbols typed into the cell.

For a source that will grow, format it as an Excel Table before creating the pivot table. Select a cell in the list, choose Insert > Table, confirm the range, and make sure My table has headers is selected when appropriate. A pivot table based on an Excel Table can pick up newly added rows after you refresh it. A pivot table based on a fixed cell range may need its source range changed when records are added.

Create a pivot table in Excel

Click a cell in the source data. If you did not format the data as an Excel Table, check that the complete range is contiguous and includes the headers.
Choose Insert > PivotTable. Excel identifies the Table/Range or table name to use as the source.
In the dialog, verify the source and choose New Worksheet for a separate summary sheet, or Existing Worksheet and a destination cell if you want the pivot table beside other content.
Choose OK. Excel creates an empty pivot table and opens the PivotTable Fields pane.
Move fields into Rows, Columns, Values, or Filters. You can select a field or drag it to the area where you want it; the result updates as you arrange the fields.

On Excel for Mac, the broad workflow is also to select the source and use Insert > PivotTable, though dialog and pane placement can differ. These instructions describe the Windows desktop version. In either version, review the selected source before confirming; an incorrect or incomplete range can leave records out of the summary.

Arrange fields and choose calculations

The four areas in the PivotTable Fields pane control different parts of the report:

Rows lists categories vertically, such as Region or Product.
Columns spreads categories across the top, such as Year or Quarter.
Values contains the numbers or records Excel calculates, such as Sales or Order ID.
Filters adds a report-level selector that can limit the displayed results.

Excel commonly sums a numeric field placed in Values and counts a text field. If a field that should be numeric is being counted, some source values may be stored as text or the field may contain nonnumeric entries. Correct the source data, refresh the pivot table, and check the calculation again.

To change how a value is summarized, open the drop-down beside the field in Values and choose Value Field Settings. Depending on the field, options include Sum, Count, Average, Maximum, and Minimum. The Number Format button in that dialog controls how values appear, for example as currency or percentages; formatting changes the display, not the underlying calculation.

Use a layout that answers one clear question. Too many fields can make a report difficult to scan. For example, Region in Rows, Month in Columns, and Sum of Sales in Values is a useful starting point for comparing monthly sales across regions. Add Product as a second row field only when the product-level detail is relevant.

Read, filter, and refine the summary

Pivot tables include controls for narrowing and organizing their results. A field placed in Filters provides a selector above the report. Row and column labels also have drop-down filters, which can show selected categories or sort labels and values. Use these controls to focus on a subset without deleting source records.

To sort by a result, select a value in the relevant column and use the sort options available from the right-click menu or the ribbon. A value filter can show categories above or below a threshold, which is helpful when looking for the largest contributors. Check the active filters before interpreting a total: a report may be showing only one region or a selection of products.

For dates, Excel may group entries into periods such as months, quarters, or years when the source contains valid dates. If grouping is available, right-click a date label and choose Group, then select the periods you need. Mixed text and date values, blank dates, or invalid dates can prevent useful grouping. If the report needs a different date category, a separate Year or Month column in the source can also make the intended grouping explicit.

Grand totals and subtotals are useful checks, but they are not always appropriate for every report. Use the PivotTable Design tab to adjust report layout, subtotals, and grand totals. Number formats and clear field names also help readers distinguish a count of records from a sum of dollars.

Refresh the pivot table when data changes

A pivot table is a summary built from a source, not a live view that recalculates after every edit. After changing source values, right-click inside the pivot table and choose Refresh, or use PivotTable Analyze > Refresh. To refresh pivot tables in the workbook together, choose Data > Refresh All.

When records are appended to an Excel Table used as the source, refresh the pivot table to include them. With a fixed range source, new rows outside that range are not included just because the worksheet now contains them. Use PivotTable Analyze > Change Data Source to select an expanded range, or use an Excel Table as the source for a list that is expected to grow.

Refresh can also update cached results based on the current source. It does not repair incorrect source values or automatically decide that a new field should be added to the report. After a refresh, verify totals and confirm that the desired fields and filters are still in place.

Common problems and how to fix them

The PivotTable command or field names look wrong: check that the source selection includes the header row and all intended records. Blank or duplicate headers can cause problems; give each column a distinct, nonblank name.
Numbers appear as Count instead of Sum: inspect the source column for numbers stored as text, blanks, or mixed content. Convert values to numbers where appropriate, then refresh and set the Value Field Settings to Sum.
New records are missing: refresh first. If the source was a fixed range, expand it through Change Data Source; if it is an Excel Table, confirm the records were added within that table.
Dates will not group: look for blanks, text dates, and inconsistent entries in the date column. Correct the source values and refresh before trying to group again.
The grand total seems too large: check for duplicated source records, embedded subtotal rows, and filters that make the visible detail differ from the full source. A pivot table summarizes what it receives; it cannot know whether a record is accidental.

For a useful accuracy check, compare the pivot table grand total with a total calculated from the same intended source records, and temporarily remove filters if necessary. If the numbers disagree, verify the selected range, field calculation, and source values before relying on the report.

Pivot tables compared with formulas

Formulas such as SUMIF or COUNTIF can calculate targeted summaries and are convenient when a worksheet needs a fixed result in a particular cell. A pivot table is often quicker when the categories or questions may change: you can move fields, filter categories, and switch between calculations without building a separate formula for each grouping.

Neither approach is automatically more accurate. Formulas make the calculation logic visible in cells, while pivot tables make grouping and exploration easier. For a small, stable report, a formula may be simpler. For recurring analysis across many categories, a pivot table is often easier to rearrange. You can also use both: a pivot table for exploration and formulas or charts for a carefully designed final report.

Frequently Asked Questions

What is the simplest way to make a pivot table in Excel?
Select a cell in a clean data list, choose Insert > PivotTable, confirm the source and destination, and choose OK. Drag a category field to Rows and a numeric field to Values to create a basic summary.

Do I need formulas to create a pivot table?
No. Excel performs the selected summary calculation when you place fields in the PivotTable Fields areas. Formulas can still be useful for preparing source data or building a separate report.

Can a pivot table update automatically when I change data?
Not usually as you edit individual source cells. Refresh the pivot table after changes. New rows are easier to include when the source is an Excel Table, but the report still needs a refresh to update its summary.

Why does my pivot table show Count of Sales?
Excel may see one or more entries in the Sales column as text or otherwise not consistently numeric. Correct the source values, refresh, then choose Sum in Value Field Settings if Sum is the intended calculation.

Can I make a pivot table from multiple worksheets?
Yes, but combining data from multiple sheets requires more setup than the basic single-range method. A common approach is to combine consistently structured data into one Excel Table or use Excel’s Data Model and related tools. For a first pivot table, one clean source table is simpler to inspect and maintain.

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.