Are you staring at your Microsoft Excel spreadsheet wondering why the calculations are not adding up properly? A row or column may clearly add up in your head, but it’s just not accurate in your spreadsheet. Here are a few things to check if your Excel spreadsheet is calculating wrong.
Fix 1 – Ensure Workbook Calculation is Enabled.
- Go to “File” > “Options” > “Formulas“.
- In the “Calculation options” area, ensure “Automatic” is selected.
Fix 2 – Hidden Rows or Columns
If columns or rows are hidden, they still will calculate in the sheet. You will need to find and unhide these rows if they are causing your spreadsheet to calculate unexpected totals.
You can find hidden rows or columns by locating the small boxes between columns or rows on the left or top panes.
You can reveal hidden by highlighting the rows or columns the hidden values are in between, right-click, then choose “Unhide“.
Fix 3 – Hidden Decimals
If values that were entered with decimals were entered, Excel may round those values up in a single cell, but not for the overall total.
Check to see if decimals are hidden by highlighting all cells with numbers, then selecting “Format Cells“. From there, you can look under the “Number” category, to see how many decimal places are shown. Increase the Decimal places to 30 to see everything.
William Pinson says
I am running into a weird and very frustrating problem with Excel 365 that’s part of the annual subscription Office.
I am doing a VLOOKUP and the value returned for some cells, but not all, are simply wrong. To troubleshoot, I added a “1*” in front of the formula and ran Evaulate so I can step through it and see what’s being returned. The value returned for the VLOOKUP is actually right, e.g. resulting in “=1*4.31”, but then evaluating that results in 6.78. It seems that the incorrect value is the value from the cell immediately above.
I can take the same spreadsheet in OpenOffice Calc, and re-fill the column of VLOOKUP functions, and all values are evaluated correctly. So OpenOffice handles the exact same syntax correctly, but Excel does not. Yet the spreadsheet was created in Excel.
For now, I guess I just have to use OpenOffice.
Susan says
Hi, Could you help me figure out why excel is adding an invisible .0000000001 to my addition calculation? I’ve expanded the decimal places and it’s showing the below. See below example:
A column is manual input and B column is a formula that adds the prior top number in column B to the number in column A. I have no idea how excel got 589.829999999973000?
A column B column
23,513.340000000000000
0.20000000000000000000 23,513.540000000000000
23,513.540000000000000
17,076.29000000000000000000 40,589.830000000000000
(40,000.00000000000000000000) 589.829999999973000
589.829999999973000
589.829999999973000
589.829999999973000
589.829999999973000
589.829999999973000
589.829999999973000
4,378,941.36000000000000000000 4,379,531.190000000000000
4,379,531.190000000000000
Kelly Johnson says
=IF($L235>0,($M235+$N235)*L235,0)
This should = 14,618,126.88
Excel is showing $14,618,113.24
???
rahul says
How is =PV(0.29166667,360,-1200) = 4109? Should have been 267000.
Diane Trelenberg says
I am trying to subtract 17.63, 17.46, 50.44, 16.95 from 2285.61
The answer should be 2182.3 but it is giving me 9039.96
???????
Jeff says
Thanks for the tip. I was trying to figure this out for 2 hours until you pointed out there were actually hidden columns.
hope says
Hi i’am having problems with getting the correct sums,
i have to add four 8 hours which would give me a total of 32 hours but it is giving me a togtal of 8, even by using auto sum . please help
Polly says
Brilliant – THANK YOU!
Val says
I understand if its some kind of big numbers or difficult calculations, but in my case, I’m trying to subtract 96,00-95,04 and it gives me 0,959999999999994
instead of 0,96. anyone can calculate it in their mind, 1,00 – 0,04 equals 0,96. right? But in excel it says 0,959999999999994
David says
I found this page looking for problems with MS Excel FV (future value) function failing to give results from their own examples. I put in all the numbers they tell me to put into the FV function and it gives a different FV than what their example said. That is true for all of their examples for FV. It is stunning that Excel doesn’t give me confidence in their results.
Teya L Watson says
Thank you. The last fix helped me. It was the decimals. Even though it was already at 2, increasing it and then decreasing it back down to 2 fixed it.