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 http://blog.contextures.com/

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!)

numbertext01

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, _
     Operation:=xlPasteSpecialOperationAdd

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 http://blog.contextures.com/

9 comments to How to Fix Excel Numbers That Don't Add Up

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>