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

Great video for this common problem!

Thanks Deborah… Paste Special Add — great tip.

A neat little tip, thanks for uploading this video.

A neat little tip, thanks for uploading this video.

Classics!

Thanks!

Classics!

Thanks!

useful GrandMaster … I though you going to use text to columns

Thank you! very helpful and to the point.

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

Very helpful , thank you.

helpful. thanks

Ótimo!! Great!! Thanks!!!

thanks a lot

it didn’t work for me 🙁 whyyy

This would drive me nuts every time it happened. Thank you for being a lifesaver.

Very Helpful

you sir deserves a hat

How can I put the squares back ?

Very helpful video Thanks alot dear.

Keep going good work 🙂

Thanks

Thank you. Worked like a charm.

Superb Thank you very much

I ve Tried to use =sum() with some formulated cells but it doesnt work, every time it gives 0 value , i need help

Very helpful ..Thnx

YOU JUST SAVED MY LIF

Thx for sharing this wonderful lesson. I was stuck, but this video helped me got out of the problem!

Excellent!

I had years in my table. I did this and numbers changed to something completely different numbers.

Thanks for the video. It was driving me crazy !!!

Awesome, it works! I thought that my Excel was out of date

Thank you!

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

it didn't work gg

mine are numbers , but i still find zero instead of the right amount

Useful

Hello Phyllis!!

Nice and clear. Thank you!

You are an absolute LEGEND!

2:14 it wil aaaaaaaaadddd

Wow, this problem was destroying me for so long but you fixed it so easily! Thank you so very much!

THANK YOU VERY MUCH, SAVED THE DAY 🙂

Nope, it doesn't work.

FINALLY!

Thanks. You are life saving.