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.
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, _ 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.