Pivot Table in Excel Not Working? Fix Common Causes

Charts & pivot tables · Updated November 20, 2026

If a PivotTable in Excel is not working, first select a cell inside it and choose PivotTable Analyze > Refresh; if the problem is missing new rows, update its source range or convert the source data to an Excel Table, then refresh again. Most problems come from stale data, an incorrect source range, filters, blank or inconsistent source values, or a value field summarized in an unexpected way.

Start by identifying what is wrong

PivotTables summarize a source dataset, so a symptom in the report often points to a specific part of that process. A report that omits recent records may need a refresh or a larger source range. A total that looks too small may be counting records instead of summing amounts. A blank result may be caused by a filter, empty source cells, or a field that has been placed in the wrong area.

Before changing anything, note what you expected to see and compare it with the source data. Check whether the source actually contains the record or value in question, and whether the PivotTable is meant to include it. If possible, save a copy of the workbook before making structural changes, especially when the PivotTable feeds other reports or charts.

  • Missing records: check the source range and refresh status.
  • Unexpected totals: check the value field’s calculation and the source values’ data types.
  • Missing categories: check filters, spelling differences, and blank cells in the source field.
  • Grouping errors: check whether the underlying dates or numbers are genuine values rather than text.

Refresh the PivotTable and its source data

A PivotTable does not always update immediately when cells in its source are edited. In Excel for Microsoft 365 on Windows, click inside the PivotTable and select PivotTable Analyze > Refresh. To update every PivotTable and data connection in the workbook, select Data > Refresh All. Refreshing recalculates the report from its current source; it does not necessarily expand a fixed source range to include rows added outside that range.

If you want the report to refresh when the workbook opens, click inside the PivotTable, open PivotTable Analyze > Options, and look on the Data tab of the PivotTable Options dialog for Refresh data when opening the file. This is useful for a workbook whose source is updated regularly, but it does not replace checking that the source itself is current and accessible.

For a PivotTable connected to an external file, database, or other connection, refresh can fail if the source has moved, the connection is unavailable, or access credentials are required. Check the message Excel displays and, if needed, go to Data > Queries & Connections to inspect available connections. Do not assume that a successful refresh means every external source is complete; confirm that the expected records appear.

Excel for Mac has PivotTable refresh controls too, though tab names and dialog placement can vary slightly by release. Excel for the web supports many PivotTable viewing and refresh tasks, but some creation, editing, and connection-management options are more limited than in the desktop application. If a particular command is missing in the browser, open the workbook in desktop Excel.

Check the PivotTable source range

When new rows are absent even after refreshing, the PivotTable may still point to an older, fixed range. Select a cell in the report and choose PivotTable Analyze > Change Data Source. Inspect the range shown and make sure it includes the new records and all required columns. Avoid including unrelated notes or totals beneath the data, since those can become unwanted records or disrupt the field structure.

A durable solution is to turn the source list into an Excel Table. Select a cell in the source data and choose Insert > Table, or press Ctrl+T on Windows and confirm that the table has headers if it does. On Mac, Command+T commonly opens the table dialog. When you add rows directly below a Table, Excel expands it; a PivotTable based on that Table can then include those rows after a refresh. If the PivotTable was originally built from a fixed cell range, changing the source to the Table may be necessary once.

Make sure the source has one clear header row, with a distinct name at the top of every column. PivotTables rely on headers to create field names. Blank headers, merged header cells, duplicate or confusing names, and completely blank rows or columns can make the source difficult to interpret. Correct the source layout first, then update the data source or recreate the PivotTable if Excel cannot recognize the revised structure.

Also check that the new records are genuinely part of the same continuous dataset. A pasted block separated from the source by an empty row may not be included in a detected range. Similarly, a new column added outside the original source range will not automatically appear as a PivotTable field unless the source is expanded.

Fix incorrect totals and value calculations

A PivotTable can show a count where you expected a sum. This commonly happens when a value column contains numbers stored as text, or when the field contains a mixture of numbers, text, and blanks. In the PivotTable, open the drop-down for the field in the Values area and choose Value Field Settings. Under Summarize Values By, select the intended calculation, such as Sum, Count, or Average. The options depend on the field and the type of data available.

Then inspect the source column. A number that looks numeric may be text because it was imported with an apostrophe, spaces, or inconsistent formatting. Correct the source values and refresh the PivotTable. Changing a cell’s number format alone may not convert text into a number. If the source includes legitimate blanks or text entries, decide whether they should be cleaned, left blank, or treated as separate categories rather than silently changing the report’s meaning.

Check the value field’s number format separately from its calculation. In Value Field Settings, use Number Format to apply currency, percentage, or decimal formatting. This changes how results display, not the underlying calculation. A total that appears incorrect may simply be rounded or displayed without decimals, so compare the displayed value with the source and the selected summary method.

Another common source of confusion is a report that counts rows rather than adding a measure. For example, a sales field with clean numeric entries should normally use Sum to show total sales; Count reports how many nonblank entries it contains. If multiple fields are in the Values area, verify each field’s name and settings, since Excel may label similar summaries in ways that are easy to overlook.

Look for filters, hidden items, and layout choices

A filter can make valid source data appear to be missing. Review the drop-downs beside row, column, and report-filter fields, and check whether a slicer or timeline is filtering the report. To remove a field filter, open its drop-down and choose Clear Filter From followed by the field name when that command is available. To clear filters across a report, use the filtering controls shown for the PivotTable or slicer. A filter may be intentional, so confirm the report’s purpose before clearing it.

Also inspect the field arrangement. In the PivotTable Fields pane, fields in Filters, Columns, Rows, and Values affect the report differently. A text field placed in Values may be counted instead of displayed as row labels. A numeric field placed in Rows may produce categories rather than a total. Drag fields to the appropriate area and check whether the desired labels and values now appear.

Layout settings can make a PivotTable look broken when the data is still present. Expand or collapse row groups, inspect subtotals and grand totals, and check whether a narrow column is hiding labels. The Design tab contains layout and style controls; choosing a different report layout can make nested fields easier to read. If a PivotChart seems out of sync, check the PivotTable filters and refresh the underlying report, since the chart is linked to PivotTable data.

Resolve date grouping and source-data issues

If Excel will not group dates by month or year, first confirm that every nonblank entry in the date field is a real Excel date value. Text that merely looks like a date, blank cells, or error values can prevent grouping. Check the source column for inconsistent formats and correct the data before refreshing. Then right-click a date item in the PivotTable and select Group; choose the intervals you want, such as Months and Years, if those options are offered.

Grouping is different from changing how a date is displayed. A date format can show a valid date as a month and year, but it does not combine records into grouped categories. Conversely, grouping dates can create separate month categories across different years if Year is not included. For example, grouping only by Month can combine January 2024 and January 2025 under January, which may be unsuitable for a year-over-year comparison.

Inconsistent category names can also split what looks like one group into several. In a source column, values such as North, north, and North with a trailing space may appear as separate items. Standardize labels in the source data, refresh, and review filters for old items that are no longer present. Do not delete or overwrite source records merely to make a report look tidy unless you have confirmed that the changes are appropriate.

Use a safe troubleshooting sequence

When the cause is not obvious, work through the checks in a consistent order. This reduces the chance of changing several settings and losing track of the fix.

  1. Save the workbook, then confirm the expected records and headers exist in the source.
  2. Check whether the report has active filters, slicers, or collapsed groups.
  3. Select the PivotTable and choose PivotTable Analyze > Refresh.
  4. If rows or columns remain missing, inspect PivotTable Analyze > Change Data Source.
  5. Review field placement, Value Field Settings, and source data types.
  6. For date grouping problems, correct blank, text, or error entries before trying to group again.
  7. Refresh once more and compare a few displayed results with the underlying records.

If a PivotTable is damaged or its field list behaves unexpectedly, try creating a new PivotTable from a clean copy of the source data rather than repeatedly editing the existing one. This can distinguish a source-data issue from a problem specific to the original report. Keep the old report until the replacement has been checked, particularly if it contains custom formatting, filters, calculated fields, or connected charts.

Frequently Asked Questions

Why is my PivotTable not updating when I change the source data?
Select a PivotTable cell and choose PivotTable Analyze > Refresh. If you added rows, check whether the source range includes them; a fixed range does not necessarily expand. An Excel Table is a practical source when records are added over time.

Why does my PivotTable show Count instead of Sum?
The value field may be set to Count, or some source entries may be stored as text. Open Value Field Settings and select Sum if appropriate, then check that the source column contains consistent numeric values and refresh the report.

Why are some rows missing from my PivotTable?
Check the source range, report filters, slicers, and field filters. Also confirm that the records are in the source dataset and that new rows were added inside the range or its Excel Table.

Why can’t I group dates in an Excel PivotTable?
One or more entries may be blank, stored as text, or contain an error rather than a valid Excel date. Correct the source column, refresh the report, then try the Group command again.

Does refreshing a PivotTable include new columns automatically?
Not if the new column falls outside the PivotTable’s source range. Use PivotTable Analyze > Change Data Source to include it, or base the report on an Excel Table and ensure the new column is part of that Table.

Can I fix a PivotTable in Excel for the web?
You can refresh and work with many PivotTable features in the browser, but some editing and connection options vary. If the necessary command is unavailable, open the workbook in Excel for Microsoft 365 desktop and use its PivotTable Analyze controls.

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.