Pivot Tables importing number values as text from and only displaying zeros

Question

I have tried multiple ways to use pivot table functions with a spreadsheet I created. Formatting in the spreadsheet is not text. I have tried text to columns fix, numerical formatting, helper cells multiplying by 1, recreating the pivot table from new, and even recreating the data spreadsheet from new. My data spreadsheet has roughly 35000 lines of data and my pivot tables are used only to sort that data so that I can run calculations on them via created macros. I ran these numbers last year and even just yesterday it was working. Noticed I was missing a line of data, added it, made sure it was formatted the same as all the others. Attempted to update the (refresh) the pivot table, and then nothing but zeros. Double clicking shows the number is saved as text but the originating spreadsheet it is not in text format. I verified that the pivot table is set to preserve original formatting and checked that security settings are not blocking access. I had a second person who is better at excel than I am and he too was perplexed by what he saw. At one point the numbers showed up then immediately went to zeros, again showing the numbers as text. I have run a virus scan as well because of the behavior, and nothing showed up. I am perplexed and now wondering if there wasn't some update that may be causing a problem. I have run this on Windows 11 and Windows 10, on two different systems. Same issue in all cases. In the future I may move this data to PowerBi but I am hesitant to waste the time with that if Excel isn't even working right.

Answer 1

It sounds like you're experiencing issues with your Pivot Table displaying numbers as text, which can often happen due to how Excel interprets data types. Here are some troubleshooting steps you can consider:

1. Check Data Types: Ensure that the data in your source spreadsheet is indeed formatted as numbers. Sometimes, even if it appears to be formatted correctly, Excel may still interpret it as text. You can use the VALUE function in a helper column to convert text to numbers.
2. Remove Leading/Trailing Spaces: If there are any leading or trailing spaces in your data, it can cause Excel to treat numbers as text. You can use the TRIM function to clean up the data.
3. Refresh the Pivot Table: After making changes to the source data, make sure to refresh the Pivot Table. You can do this by right-clicking on the Pivot Table and selecting "Refresh."
4. Recreate the Pivot Table: If refreshing doesn’t work, try creating a new Pivot Table from scratch with the updated data. Sometimes, the Pivot Table can retain old formatting or settings that cause issues.
5. Check for Filters: Ensure that there are no filters applied to the Pivot Table that might be hiding data.
6. Data Source Range: Verify that the data source range for the Pivot Table includes all the relevant data. If the range is incorrect, it may not capture the new data you added.
7. Excel Updates: Since you mentioned that this issue started after adding a new line of data, check if there have been any recent updates to Excel that might affect functionality. Sometimes, reverting to a previous version can help if a new update caused the issue.
8. Check for Errors: If the data is being interpreted as text, double-clicking on the cells showing zeros may reveal an error message or indicate that the data is not being recognized correctly.

If these steps do not resolve the issue, consider checking the Excel forums or Microsoft support for additional guidance, as there may be specific bugs or issues related to your version of Excel or Windows.

Answer 2

Hi Suay,

Thank you for sharing your question. I understand your pivot tables are showing zeros all of a sudden, and the numbers are showing up as text when you click, even though they should be numbers. And yeah, you've tried a bunch of fixes already, like Text-to-Columns, the VALUE thing, and even remade the whole pivot table and dataset. It still messes up on both Windows 10 and 11. I am here to help.

Usually, it boils down to

Hidden text stuff or spaces: Excel might see the cells as text if there are hidden characters.

Pivot cache problems: Big sets and refreshing too much might mess up how it sees the data.

Excel updates: Updates can change how it handles data types, and it could interpret mixed data wrong.

Data source range problems: If it is pulling from ranges that includes blank rows/ mixed formats, Excel defaults to text.

Here's what you can try to fix it

Force numbers back in your data: Add a column and use =VALUE(A2) or =--A2on your number column.

Copy and paste the values back in. Make sure it's formatted as a number and test with ISNUMBER() before refreshing.

Get rid of hidden spaces: Try =TRIM(CLEAN(A2)) on the column, then replace the current values.

Clear the pivot cache: Take out the current pivot table. Go to Insert > PivotTable, select the cleaned-up range, and create a new one.

 

I hope this helps.

Best Regards,

Noel