How to Fix Excel Numbers That Don’t Add Up

If you download bank statement data into Excel, or copy numbers from a website, those numbers might not add up correctly. The bank data can look like numbers, but Excel sometimes sees those numbers as text -- not real numbers.

You can fix the numbers manually, as shown in the video below, or use a macro to automate the fix.

Fix Numbers That Don’t Add Up

Numbers Don't Add Up

There are a variety of reasons that numbers don't add up in Excel, and this technique fixes one of the more common problems. If it doesn't work on your data, check out some of the other fixes here.

Here is a screen shot of the sample data used in this video – it has cheque numbers and amounts from a fake bank statement. (If your bank only sends you this much information, you should switch to a different bank!)


Watch the Video

In the short video below, you will see how to check the cells, and then fix the problem. This is an update for a video that I did in May 2008 – and people are still watching that one on YouTube!

NOTE: In the Paste Special window, select Values in the Paste section, if you want to keep the current formatting in the selected range.

Fix the Numbers with a Macro

Also, I've updated the macro that automates this fix, to prevent a problem. The old code used the full range that was selected, and that was okay, if you just selected the cells that you wanted to update.

However, if you selected an entire column, and then ran the macro, the Used Range was extended to that entire column.

Selection.PasteSpecial Paste:=xlPasteValues, _

In the revised code, it creates a range from the cells with constants in the selected range, and updates those cells.

Set rng = Selection _
  .SpecialCells(xlCellTypeConstants, 23)

That seems to have eliminated the problem. You can get the entire code from my website, or by downloading the sample file.

Download the Sample File

To test the manual or macro fix, you can download the sample file from my website. Click here to go to the Fix Numbers page on my website, then go to the "Download the Sample File" section. The zipped file is in xlsm format, so enable macros when you open the file.


Fix Numbers That Don’t Add Up

You may also like...

9 Responses

  1. It would be nice to select both Add and Values in the Paste Special dialog box to not lose the destination formatting.

  2. AlexJ says:

    Debra, could we add a helper column that multiplies the text value by 1.0 ?

  3. AlexJ says:

    Another idea! Read the data into Power Query and modify the data type for the column in question, then write to another table.

  4. Ron says:


    I have stored a 1 in a cell. Then select the range. Paste special multiply values.


  1. February 1, 2016

    […] How to Fix Excel Numbers That Don't Add Up […]

Leave a Reply

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