• Skip to main content
  • Skip to primary sidebar

Technipages

Tutorials and fixes for smartphone, gadget, and computer problems

  • Topics
    • Android
    • Browsers
    • Gaming
    • Hardware
    • Internet
    • iPhone
    • Linux
    • macOS
    • Office
    • Reviews
    • Software
    • Windows
    • Definitions
  • Product Reviews
  • Downloads
  • About

Excel Calculations Are Wrong

February 27, 2023 by Mitch Bartlett 18 Comments

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

  1. Go to “File” > “Options” > “Formulas“.
  2. In the “Calculation options” area, ensure “Automatic” is selected.

Excel Automatic Calculation option


Fix 2 – Check Cell Format

Highlight the problematic cell(s), then select the Home tab in the Number section and ensure that “Text” is not selected

Excel Number setting

 


Fix 3 – 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.

excel-hidden-row
An example of a hidden row causing confusion with math. At first it looks like the sum should be shown as 1050. A hidden row bumps it to 1100.

You can reveal hidden by highlighting the rows or columns the hidden values are in between, right-click, then choose “Unhide“.

excel-unhide


Fix 4 – 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.

excel-decimals-hidden

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.

excel-decimal-places

excel-decimal-places-revealed


Fix 5 – Check Circular References

Select the problematic cell then ensure that you don’t see “Circular References” at the bottom-left corner of the page. You can also select “Formulas” then check the “Error Checking” opttion to check for Circular References in your sheet.

You can learn more about Circular References in Excel on Microsoft’s website.


I hope this guide has helped you figure out why Excel is not calculating correctly. Share your experience in the comments section.

You Might Also Like

  • iPhone Location Maps Header
    iPhone & iPad: Current Location is Wrong
  • Fix Office "Something went wrong" Error 1058-13
    Fix Office "Something went wrong" Error 1058-13
  • Windows 11 Time is Wrong - Causes/Fixes
    Windows 11 Time is Wrong - Causes/Fixes
  • How to Fix Wrong Time On Android
    How to Fix Wrong Time On Android
  • 3D printing header
    3D Printing Basics: Signs your Print Head or Bed Temperature…
  • fix whatsapp something went wrong
    Fix WhatsApp Error Something Went Wrong

Filed Under: Office

Reader Interactions

Comments

  1. Sally Lowery says

    June 21, 2023 at 3:13 pm

    None of the suggested solutions worked for me. I had to use the ROUND function to the formula to get rid of the additional 0.00000000000000109 that was included in the sum.

  2. Lee says

    May 4, 2023 at 7:50 am

    Hi,
    3+3=6 right? So why am I getting 7??!! aaaahhhh
    F16 G16 H16
    Monthly Jan Feb
    ÂŁ3 ÂŁ3 ÂŁ7
    Formula:
    ÂŁ3 =F16 =F16+G16

    Maths is not my forte and I’m pretty sure this is not the best formula anyway as I am ultimately I am trying to do an accumulative thing per month where it adds ÂŁ3 to each month, so Feb should be ÂŁ6, March would be ÂŁ9. I have a feeling when I send this message it will mess up my table format so I might have resend a better way.
    Any help would be very much appreciated. Thank you.

  3. Andre'a says

    October 19, 2022 at 11:43 pm

    All I want to do is, from 1062.87 subtract 135.54 which obviously equals 927.33.
    Why do I end up with 927.329999999999000000000000000000?

    I’ve formatted for 30 decimals and have no hidden rows.

    I notice that there are no responses to the comments. Bummer.

  4. sami C. says

    September 1, 2022 at 12:58 pm

    none of this is helping me..

    in one column i have rows 1-10 with a formula in it– that gives me results from another data set– this answer that comes back in each row is correct. Then i need to SUM the column that these numbers are in, and when i do that — it comes back 2 lower than what the actual math is. The automatic is on, i have no hidden rows, i have no extra decimals… it just will not do what it is supposed to and it is frustrating

  5. Pratik Shah says

    May 4, 2022 at 9:44 am

    Thanks a lot !! The Fix-1 has helped me.

    Fix 1 – Ensure Workbook Calculation is Enabled.
    Go to “File” > “Options” > “Formulas“.
    In the “Calculation options” area, ensure “Automatic” is selected.

  6. Ken Siebert says

    April 4, 2022 at 11:05 am

    Check the format type of the cell. My formula (just adding up 4 cells) worked in one cell, but in the cell directly above it, it gave me a result that was nowhere near the actual answer. Turned out that the cell formatting was set to “Custom”. I switched it to “General” and it worked fine after that.

  7. Tom Proios says

    February 2, 2022 at 3:31 pm

    I am having the same issue with excel causing issues in like the 10th or 12 decimal place causing balance sheets not to balance. This is straight dollars and cents in a check book fashon, no interest calculations or the like.

    1204.080000000000000000
    +260.420000000000000000
    -521.970000000000000000
    =942.53000000000200000000

    I do a lot of calculations for engineering functions and while the error is small, it exists and is unacceptable.

    Apparently the programmers failed first grade arithmetic.

  8. William Pinson says

    June 11, 2021 at 7:27 pm

    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.

  9. Susan says

    May 15, 2021 at 1:21 am

    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

  10. Kelly Johnson says

    April 8, 2021 at 12:57 pm

    =IF($L235>0,($M235+$N235)*L235,0)
    This should = 14,618,126.88
    Excel is showing $14,618,113.24
    ???

  11. rahul says

    March 28, 2021 at 1:10 pm

    How is =PV(0.29166667,360,-1200) = 4109? Should have been 267000.

  12. Diane Trelenberg says

    January 4, 2021 at 12:23 pm

    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

    ???????

  13. Jeff says

    November 11, 2020 at 3:50 pm

    Thanks for the tip. I was trying to figure this out for 2 hours until you pointed out there were actually hidden columns.

  14. hope says

    September 30, 2020 at 2:57 am

    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

  15. Polly says

    May 2, 2020 at 11:12 pm

    Brilliant – THANK YOU!

  16. Val says

    April 30, 2020 at 1:47 am

    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

  17. David says

    April 29, 2020 at 5:08 pm

    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.

  18. Teya L Watson says

    February 19, 2020 at 3:32 pm

    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.

Did this help? Let us know!

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Recent Posts

  • How to Send HD Pictures and Videos on WhatsApp
  • How to Share Files Using Google Nearby Share
  • Android Battery Will Not Charge Fix
  • Turning on Triple Touch Zoom on Android
  • How to Connect Outlook 2023 to Gmail
  • Netflix: Change Password
  • Step-by-Step Guide: How to Use WhatsApp on Your Computer
  • Android: Enable or Disable Background Data

Who’s Behind Technipages?

Baby and Daddy My name is Mitch Bartlett. I've been working in technology for over 20 years in a wide range of tech jobs from Tech Support to Software Testing. I started this site as a technical guide for myself and it has grown into what I hope is a useful reference for all.

© Copyright 2023 Guiding Tech Media · All Rights Reserved · Privacy