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.
In this short video you will see how to check the cells, and then fix the problem.
Visit the Contextures website to get the sample file.
0:42 Check the Cell Contents
1:40 Check the Formula Bar
2:00 Fix the Numbers
3:08 Get the Sample File
Instructor: Debra Dalgleish, Contextures Inc.
Get Debra’s weekly Excel tips: http://www.contextures.com/signup01
More Excel Tips and Tutorials: http://www.contextures.com/tiptech.html
Subscribe to Contextures YouTube: https://www.youtube.com/user/contextures?sub_confirmation=1
If you import or copy data into Excel such as a bank statement, sometimes the numbers don’t add up correctly. We’ll see how to fix that.
Here is a very small sample of a bank account. We’ve got cheque numbers and the amount of each cheque.
To create a total, I can go to the Home tab, and over at the right, click AutoSum. And usually, that will select any numbers above, but in this case it didn’t, so I will manually select those, and press Enter, and it shows zero. So even though I’ve got hundreds of dollars, it’s showing zero.
I’m going to add a couple of other functions on this worksheet, and just find out what’s going on in these cells.
In this cell, I’m going to get a count of everything that’s in those cells, whether it’s text or numbers.
In here, I’m going to use COUNTA equals COUNTA open bracket. Then I’ll select the cells that have the numbers, close the bracket, and press Enter.
These four cells have something in them. But how many of those have numbers? And to do that here we use COUNTA.
And in this cell, I’m going to use COUNT, and it only counts numbers. So equals COUNT open bracket, select the four cells again, close the bracket, and press Enter.
We have four cells with something in them, but none of those cells have numbers.
If I look at one of these cells and look up in the Formula Bar, I can see the number, but in front of the number there’s an apostrophe, and that indicates that this is text rather than a number. So, whatever we’ve downloaded or copied in from somewhere came in as text.
There is a quick way we can fix this, though. We’re going to select a blank cell, and then use paste special to paste it over these numbers, and it will add a zero to everything, which won’t have any effect on these values, but will change them from text to numbers with that simple step.
I’ll select a blank cell and copy. Then select the cells that I want to fix, and go up to Paste, click the drop-down arrow, and go down to Paste Special. And in here, I want to Add, so I’ll select that. Click OK.
And now, these have all changed to numbers, and we can see a total at the bottom.
I could format these so that they all line up nicely. I could format this one, as well. And now, we have a total that’s correct and nicely formatted.