8 Excel PivotTable mistakes that end up giving you wrong numbers

You know how you build a PivotTable in Excel and then quickly eyeball the numbers to check whether they add up? It can be frustrating when they don't, especially when you're not sure what's causing the problem. The issue could be with your data, your formulas, or even the way Excel processes the information. Whatever the cause, any of these pitfalls can lead to incorrect numbers, which defeats the purpose of using PivotTables in the first place. After all, they're meant to make data analysis faster and more reliable.

Fortunately, understanding how Excel handles your data and the common mistakes that can throw off your results can help you produce accurate reports every time.

Mixing the data types in your value columns

One text value is all it takes to throw off your totals

If you eyeball the totals in your PivotTable and the numbers look smaller than expected, there's a good chance your value column contains a mix of text and numbers. Excel normally summarizes numeric values with the basic SUM function, but if it detects text entries in the field, it will summarize the data differently, and it may even switch to the COUNT function instead.

The fix is simple once you know what to look for. Check your source column for accidental text entries, such as a stray space or a number stored as text, and clean them up before refreshing the PivotTable. You should also keep the data type consistent within each column. For example, don't mix dates and text in the same column or numbers and text in a field that's supposed to contain only numeric values.

Failing to refresh the data

A changed source doesn’t always mean a changed table

A common misconception is that PivotTables update automatically whenever the source data changes. In reality, PivotTables work from a cache, which is essentially a snapshot of your data taken since the last refresh. That means you can make as many changes as you want to the source data, but the PivotTable won't reflect them until you refresh it.

If you add new information to your source, you need to manually select Refresh or Refresh All for the PivotTable to display the updated numbers. In many cases, refreshing is the only way to ensure your table stays accurate and consistent. Make it a habit to refresh your PivotTables before presenting or sharing any reports.

Messy source data

PivotTables love neat data a little too much

For a PivotTable to work correctly, your source data needs to be in a clean, tabular format. That means it shouldn't contain blank rows or columns, and it should have a single header row with unique, non-blank column names. Merged cells are another common culprit, as they can interfere with the way PivotTables interpret your data.

Before building your next PivotTable, take a few seconds to scan your source range. Make sure there are no blank rows, missing headers, merged cells, or stray data that could prevent Excel from reading the dataset correctly.

Having some numbers stored as text

Looking like a number doesn’t mean it’ll behave like one

If some of your numbers are stored as text, Excel won't summarize them the way you expect, and your totals can end up being wildly inaccurate without any obvious error message. I once noticed a discrepancy of millions of dollars in a PivotTable, only to discover that some of the values in my source data were stored as text. All I had to do was change the cells' number format to Number under Excel’s number formats and refresh the PivotTable.

The next time your totals look suspiciously low, check whether your values are stored as text before you start questioning your formulas. It's a simple fix that can save you a lot of unnecessary troubleshooting.

Hidden date components in time fields

There might be invisible values hiding inside your timestamps

Just as numbers can be stored in the wrong format, time values can also contain hidden date components that affect your calculations. Excel stores dates as whole numbers and times as decimal fractions of a day. As a result, a cell might display "1:00 AM" while still containing an underlying date value, such as "1/1/1900."

You might not notice until you perform calculations like adjusting for a different time zone, only to find that the results don't make sense. If your time-based calculations seem inexplicably wrong, it's worth checking whether hidden date values are affecting the results.

Renaming your source headers

Rename with care or expect broken pivot fields

This mistake is quite easy to make. You fix a typo or rename a column header in your source data, thinking you're only making it clearer. However, PivotTables rely on those header names to identify their fields. If a header changes, the PivotTable may no longer be able to find the original field, and you could see a "field name is not valid" or "field cannot be found" error the next time you refresh.

If you need to rename your headers, be prepared to update or rebuild the affected PivotTable fields afterward.

Overlapping PivotTables

Your PivotTables need room to grow

Sometimes, your source data isn't what's causing the wrong numbers. If two PivotTables are placed too close together, refreshing one can cause it to expand into the other. When that happens, Excel may stop the refresh altogether, leaving you with outdated numbers and little indication of why they haven't changed.

To avoid this, leave enough empty space around your PivotTables, especially if you expect your source data to grow over time.

Before you trust the numbers, fix your process

A PivotTable is only as accurate as the data and setup behind it. Keeping your source data clean, using consistent formatting, and refreshing your PivotTable whenever your data changes all go a long way toward ensuring your reports are accurate.

Spending a few extra minutes checking these details before you analyze or share your data can save you from drawing the wrong conclusions based on incorrect numbers.