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 might look like numbers, but Excel might see 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!

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. 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/

Leave a Reply

Your email address will not be published.

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