Create a Pivot Table in Excel 2019: Step-by-Step

Charts & pivot tables · Updated November 18, 2026

To create a PivotTable in Excel 2019, select a cell in a clean data range, choose Insert > PivotTable, confirm the range and where to put the report, then use the PivotTable Fields pane to arrange fields into Rows, Columns, Values, and Filters. A PivotTable summarizes and reorganizes records without changing the source data, making it easier to compare totals, counts, and other summaries.

Before you begin, check that your source data is arranged as a simple table: one header row, one record per row, and a separate column for each type of information. The steps below describe Excel 2019 for Windows. Ribbon labels can differ on Mac, but the basic process and field areas are similar.

What You’ll Need

  • Excel 2019: Open the workbook in the desktop application, rather than Excel for the web, so the menus match these instructions.
  • A structured data range: Include column headings such as Date, Region, Product, and Sales. Avoid blank rows or columns within the data.
  • A clear question: Decide what you want to summarize, such as sales by product, order counts by month, or expenses by department. This helps you choose fields for the report.
  • A saved copy of the workbook: PivotTables do not normally overwrite the source, but saving first is a useful precaution before changing a workbook or its data.

Step-by-Step

  1. Check and prepare the source data. Make sure the first row contains a distinct heading for every column, with no empty heading cells. Keep each column to one kind of data: for example, do not mix dates and text in a Date column or numbers and notes in a Sales column. Each row should represent one record, such as one order line. Remove completely blank rows inside the list, and check that the range does not include unrelated notes or totals.

    For example, a sales list might have the headings Order Date, Region, Product, Units, and Revenue. A row below those headings should contain the details for a single order or order line. Do not add a manually calculated grand-total row to the source; the PivotTable can calculate totals itself.

  2. Select a cell in the data. Click any filled cell within the source range. You usually do not need to highlight every row and column: Excel can detect the connected data range automatically. If the list is next to other data or separated by blank rows, select the precise range before continuing so Excel does not include unrelated cells or omit intended records.

  3. Open the PivotTable command. On the ribbon, choose Insert > PivotTable. Excel opens the Create PivotTable dialog box. In the Table/Range box, review the detected source address. If it is wrong, click the box and select the complete range, including its headers. You can also use a named Excel table as the source.

  4. Choose where the report will go. In the Create PivotTable dialog, select New Worksheet to put the report on a separate sheet. This is a good choice for a first PivotTable because it keeps the summary apart from the raw records. Select Existing Worksheet only if you want to place it on a particular sheet; then specify a blank starting cell in the Location box. Check the source and destination before proceeding.

  5. Create the PivotTable. Click OK. Excel adds a blank PivotTable layout and opens the PivotTable Fields pane. The field list should show the source column headings. If the pane is not visible, click inside the PivotTable and look for the field list on the right; you can also use the contextual PivotTable Analyze tab to show the Field List.

  6. Add a category to Rows. In the PivotTable Fields pane, drag a field such as Region or Product into the Rows area. Excel displays each distinct item in that field as a row label. You can also select the field’s checkbox; Excel places text fields in a default area, which may not be the layout you want. Dragging gives you more control over the result.

  7. Add a measure to Values. Drag a numeric field, such as Revenue or Units, into Values. Excel typically summarizes numeric data by Sum. The report may now show total revenue for each product or region. To include a second measure, drag another numeric field into Values. Excel displays another value column in the PivotTable.

  8. Choose the correct calculation. If Excel counts a field when you expected a sum, open the drop-down beside that field in the Values area, choose Value Field Settings, select Sum, and click OK. The Sum option is available when Excel recognizes the source values as numbers. For a count of records, select Count instead. Use the number format controls in Value Field Settings to make results easier to read, such as formatting revenue as currency.

  9. Arrange fields to answer a question. Add another descriptive field to Columns to compare categories across the top of the report. For example, put Region in Rows, Product in Columns, and Revenue in Values to compare revenue by product and region. Add a field to Filters when you want a report-wide selector, such as filtering the whole report to one year. Move fields between the four areas until the layout communicates the comparison you need.

  10. Filter, sort, and review the results. Use the arrow beside a row or column label to include or exclude items. You can sort labels or values from the same menu. Check the grand totals and a few individual categories against the source data to confirm the report makes sense. If the report is too broad, add a filter or remove unnecessary fields rather than trying to interpret a crowded layout.

  11. Refresh after source changes. A PivotTable does not automatically recalculate its summary every time you edit the original records. After changing or adding source data, click inside the PivotTable and choose PivotTable Analyze > Refresh. To update all PivotTables in the workbook at once, choose Data > Refresh All. If you added records outside the original source range, update the source as described in Troubleshooting before refreshing.

Common Mistakes

  • Missing or duplicate headings: Every source column needs a heading, and headings should be distinct. Blank headers can prevent Excel from creating the report; duplicate names can make the field list confusing. Give each column a clear, unique label before starting.
  • Blank rows breaking the range: A fully blank row inside a list can cause Excel to detect only part of the data. Remove the blank row or select the complete source range manually in the Create PivotTable dialog.
  • Numbers stored as text: Values that look numeric may be left-aligned or marked with a warning indicator if they are stored as text. A PivotTable may count these entries instead of summing them. Convert the source values to numbers, then refresh or recreate the PivotTable.
  • Expecting the report to update by itself: Editing a source cell does not necessarily update the displayed summary. Use Refresh after edits, and confirm that newly added rows are included in the PivotTable source.
  • Putting too many fields into the report: Every field is not needed in every summary. Too many row levels or columns can make the output hard to scan. Start with one category and one measure, then add fields only when they help answer the question.
  • Using the wrong kind of total: A count of revenue entries is not the same as the sum of revenue. Check Value Field Settings and confirm the calculation is appropriate for the question and source data.

Tips

  • Convert the source range to an Excel table first. Select a cell in the data and choose Insert > Table, confirm that My table has headers is selected, and click OK. Give the table a helpful name using the Table Design tab. Using a table as the PivotTable source makes it easier to include records added to the table later; refresh the PivotTable after adding them.
  • Keep the source and summary separate. A new worksheet makes it less likely that the PivotTable will cover source records and gives you room to add a chart or notes beside the report.
  • Use date fields thoughtfully. If Excel recognizes the source as dates, you can group date items in the PivotTable. Right-click a date label, choose Group, select a grouping such as Months or Years, and click OK. If Group is unavailable or the dates appear as separate text values, check that every source entry is a valid Excel date and that there are no blanks or text mixed into the date column.
  • Rename value headings for readers. Click a value heading in the PivotTable and type a clearer label, such as Total Revenue. Avoid using a name that exactly matches a source heading, because Excel may require a distinct caption.
  • Make formatting easier to maintain. Click the PivotTable, open the Design tab under PivotTable Tools, and choose a style. For numeric values, use Value Field Settings to set an appropriate number format so currency or decimal places are consistent.
  • Save a useful report layout. Give the worksheet a descriptive tab name, and consider adding a short note describing the source period or the meaning of the metric. This helps someone opening the workbook later understand what the summary covers.

Troubleshooting

The field list does not appear. Click inside the PivotTable. If the pane still is not shown, select PivotTable Analyze > Field List. If you do not see the contextual PivotTable Analyze tab, confirm that the selected cell is inside the PivotTable rather than elsewhere on the sheet.

The PivotTable shows Count instead of Sum. Excel may have detected one or more entries in that source column as text, or the column may contain a mix of numbers and text. Inspect and convert the source values to numbers, check for errors or nonnumeric entries, then refresh. You can also try Value Field Settings > Sum; if Sum is unavailable, correct the source data first.

New records are missing. Click inside the PivotTable and choose PivotTable Analyze > Change Data Source. Select the full range, including the new rows, and click OK; then refresh the PivotTable. If the source is an Excel table, make sure the new rows were added directly below the table so the table expands, and refresh the report.

Dates will not group. Check the entire source date column for blanks, text entries, or invalid dates. Correct those cells, refresh, and try grouping again by right-clicking a date label and choosing Group. If the source contains dates stored as text, convert them to real Excel dates before grouping.

The source range warning appears or Excel rejects the data. Confirm that the selected range includes a single row of headers and at least one row of records, and that no header is blank. Remove merged cells from the source area and avoid selecting entire worksheet columns if unrelated content is present. Reopen Insert > PivotTable and select the intended range explicitly.

The report looks different after refreshing. Refreshing can update totals and may change which items appear if source records were added or removed. Check the filters, the selected source range, and the field arrangement. If you have changed the source column structure, verify that the field list still contains the expected headings.

Frequently Asked Questions

Can I create a PivotTable in Excel 2019 without selecting the whole range?
Yes. Click any cell inside a continuous data range and choose Insert > PivotTable. Excel usually detects the connected range. Review the Table/Range box before clicking OK, especially if other data is nearby.

Does a PivotTable change or delete my original data?
No. A PivotTable is a separate summary based on the source range. Editing the report does not normally rewrite the original records. Keep the source data intact if you need to change the summary later.

Why does my PivotTable not update when I edit a source cell?
Excel 2019 generally requires you to refresh the report. Click inside the PivotTable and choose PivotTable Analyze > Refresh, or use Data > Refresh All to update reports throughout the workbook.

Can I make a PivotTable from multiple worksheets?
Yes, but combining the data into one clean table is usually the simplest approach. Excel 2019 can also use the Data Model for more advanced analysis, but the setup is different from the basic Table/Range process in this guide.

How do I remove a PivotTable?
Click inside it, select the whole PivotTable from PivotTable Analyze > Select > Entire PivotTable, then press Delete. Check that the selection highlights only the report before deleting. This removes the PivotTable, not the source records.

Can I use the same source data for another summary?
Yes. Create another PivotTable from the same source range and arrange its fields differently. Separate reports let you answer different questions, such as revenue by region in one report and order counts by product in another.

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.