How to Fix Excel Numbers That Don’t Add Up
Articles Blog

How to Fix Excel Numbers That Don’t Add Up


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. Usually, that will select any numbers
above, but in this case it didn’t, I will manually select those, and
press Enter, and it shows zero. 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 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. 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. So 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. 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
they all line up nicely. I could format this one as well. And now we have a total that’s
correct and nicely formatted. For more Excel tips and tutorials. And to download the sample
file for this video, please visit my Contextures website
at www.contextures.com

44 thoughts on “How to Fix Excel Numbers That Don’t Add Up

  1. Thank you! I couldn't figure this out and also took some time to find your video not knowing what to ask in search.

  2. Thank you very much .. you made my day I was trying to sort out this for last two days nothing worked but it did.. thanks a lot!!!!!!

Leave a Reply

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

Back To Top