OmniDocRefresh a Pivot Table and Update Its Data Source
Learn how to refresh a pivot table after adding rows, change its source range, and fix stale or wrong totals caused by an outdated data source.
Pivot tables don't watch your data. They store a link to a fixed range of cells, and they show whatever that range contained the last time the table was refreshed. If you pasted twenty new rows under your sales list and the pivot still shows last month's totals, the table is doing exactly what it was built to do. The fix is a pivot table Excel data refresh followed by a check of the source range the table points to.
This guide is for anyone who keeps data in an Excel workbook and summarizes it with a pivot table, whether that is a monthly report, a budget tracker or a list of customer orders. You will learn how to refresh the table, change its data source so new rows are included, and track down figures that look stale or wrong. The steps use general spreadsheet operations, so they apply to most versions of Excel and similar desktop spreadsheet apps.
If your source data begins as a PDF, OmniDoc can help you get it into cells first. The sections below cover that case too.
How do you refresh a pivot table and update its data source? The short answer
Refresh the pivot table first to pull in changed values, then change the source range if new rows fall outside it. Refreshing alone won't include rows the range never covered.
| Your situation | What to do | Why |
|---|---|---|
| Existing rows changed values | Refresh the pivot table | It re-reads the current cells in its source range |
| New rows were added below the data | Extend the source range, or convert the data to a table | The original range stops at the last row you selected |
| Several pivot tables use one source | Update the source once, then refresh each pivot | Each pivot keeps its own link to the range |
| The source data came from a PDF | Export to Excel, check the numbers, then refresh | A pivot table can only read data that sits in cells |
Before you start: check the source data
A few checks prevent most of the confusion before you touch the pivot table.
- Confirm you are editing the right copy. If the workbook exists in two places, changes made in one copy never reach the pivot in the other. Work in the file that contains the pivot table.
- Check the header row. Each column needs one unique, non-blank name. Blank or duplicate headers cause fields to merge or appear with strange names, and a renamed header can break the link to a field.
- Look for numbers stored as text. Text values usually sit left-aligned in their cells. A column of text numbers can be counted rather than summed, so totals come out wrong even after a refresh.
- Review any filters on the pivot. A filter set to show only some items hides new rows, even when the refresh works. Note which filters are active before you troubleshoot.
- Save a copy of the workbook. Changing a source range is easy to undo, but a copy protects you if a range edit removes data you still need.
Refreshing the pivot table and changing its source, step by step
- Open the workbook that contains the pivot table. Working in the same file ensures the pivot reads the data you are looking at.
- Click any cell inside the pivot table. Selecting a cell in the pivot tells the spreadsheet app which table you mean to refresh.
- Run the refresh for the pivot table. This re-reads the source range and updates the values. If the workbook has several pivot tables, refresh all of them so every summary reads the same data.
- Open the pivot table's data source settings and read the current range. Compare the range address with your data. If the source is A1:F12 but you added rows 13 through 20, the range is too short, and a refresh will not add those rows.
- Enter the full range, including the header row. For example, change A1:F12 to A1:F20. Keeping the header row in the range keeps the field names stable.
- Or convert the source to a table, then point the pivot at the table. Select the data, turn it into a table, and set the pivot's source to the table name. Rows added inside or directly under the table are included on later refreshes, so you stop editing the range each time.
- Refresh again and check the field list. If a field has disappeared or changed name, the header in the source was edited, so fix the header and refresh once more.
- Save the workbook. The saved file holds the refreshed pivot table and its updated source settings.
If your figures come from a PDF, OmniDoc's PDF editor can export content to Excel (.xlsx), and it also offers OCR for scanned PDFs. Export the table, open the output in your spreadsheet, and compare several rows against the PDF before you build or refresh the pivot. Re-export whenever the PDF changes, because the export does not update the pivot table on its own. OCR can misread characters, especially digits in dense tables, so check any figure that matters before you rely on it.
Check the result
After the refresh, check the pivot against the source data. These checks take a few minutes and catch the problems that cause stale or wrong totals.
| Check | How to see it | If it's wrong |
|---|---|---|
| Row count | Count the data rows in the source and compare with what the pivot summarizes | The range is too short: extend it or convert the source to a table |
| Grand total | Sum one numeric column in the source with a formula and compare it with the pivot's grand total | An active filter is hiding items, or some numbers are stored as text |
| Field names | Look at the field list and confirm each column appears once | A header was renamed, blank or duplicated: fix the header and refresh |
| Newest entries | Find a row you added last and confirm its values appear in the pivot | The range stops above that row, or it sits outside the table |
| Changed values | Pick one cell you edited and check that the pivot reflects the change | The refresh didn't run on this copy: refresh the correct workbook |
Comparing the grand total with a formula over the source is the quickest test. If the numbers match, the pivot is reading the full range. If they don't, work down the table above in order, starting with the range and the filters.
Troubleshooting a pivot table that won't update
When the pivot seems stuck, work through these causes in order.
- Calculation is set to manual. Some workbooks are set so formulas and summaries update only when you tell them to. Switch to automatic calculation, or run a full recalculation, then refresh the pivot.
- The source sheet was renamed or moved. If the range points to a sheet that no longer exists, the pivot can show errors. Update the source settings to the new sheet name and range.
- The source was deleted or shortened. Deleting rows inside the source range shrinks it. Restore the data, or set the range to cover the rows that remain.
- Text numbers are being counted. Convert the column to real numbers, then refresh. Totals will change once the values are numeric.
❓ FAQ
1. Why does my pivot table not refresh when I change the cells in the source?
The pivot keeps the values from its last refresh until you run one again. If the workbook is set to manual calculation, updates may also wait for a recalculation. Refresh the pivot directly, and check the calculation setting if the numbers still don't move.
2. How do I change the data source of a pivot table without rebuilding it?
Open the pivot table's data source settings and enter the new range or table name. The layout, field placement and most formatting stay in place, so you rarely need to rebuild the table from scratch.
3. Does refreshing a pivot table change my source data?
No. A refresh only reads the source range and updates the summary. It never edits the cells you summarize, so you can refresh as often as you need.
4. Why is my pivot table pulling the wrong data when the totals look close?
Usually a filter is hiding some items, the range misses a few rows at the end, or some numbers are stored as text and are being counted instead of summed. Compare the grand total with a formula over the source, then work through the checks in the table above.
📝 Summary
A pivot table shows what its source range contained at the last refresh, so a pivot table Excel data refresh should be paired with a check of the range itself. Refresh the table, extend the source or convert it to a table, and verify the grand total against the original data. For figures that start as a PDF, export and check them before you build the pivot, because OCR and conversion can introduce errors.
