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. In this short video you will see how to check the cells, and then fix the problem.

✅ Visit my Contextures website to get the sample file.

⏰ Video Timeline ⏰
0:00 Introduction
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.
#ContexturesExcelTips

VIDEO TRANSCRIPT

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.

[Phil__]

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

[ericli]

I was trying to fix this literally all night with =CLEAN, =TRIM, C/P as Text, nothing. Then your video saved my life. I cannot thank you enough!

[mohammadahmed]

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

[jmblestari]

Thankyou so much. i asked my daughters and they showed me a hard way and took a longer time by deleting all the space in the number one by one . With your help i the surprised them with a simpler way in a jiff. Glad i found u.

[samuelkliment]

If someone is still struggling even after watching this video, my advice is change the decimal sign – dot for comma or vice versa

[kansasabernathy]

Thanks for this video. Just helped me troubleshoot a file someone else sent me.

[mikoto.kayano]

Thank you very much! This helped me a lot. I was wondering why the sum never made any sense. It's because I copied it from another cell. Dumb 'ol me. THANK YOU

[Aracnah]

THANK YOU SO MUCH!

For my problem, it was a "space" behind the number that I didn´t notice.

[USCITIZENOHIO]

I'm unable to rectify...excel Online....no paste special menu..but I DID get to use/understand the COUNTA and COUNT functions and what they are about...NICE to expand knowledge on Excel. Thank you for your simple explanation!

[agnessapurba]

Oh my God, thank you so much much fo helping me. I'd got so stressfull of this.... may God bless you dearrrr

[crazynassim]

I LOOVE YOU YOU SAVED ME HOURS OF CALCULATING BY HAND IN MY JOB

[lindareyna]

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

[laurenreynolds]

THANK YOU! This is the ONLY video that actually solved my issue. Everything else just said "oh you must have made your equations manual instead of automatic"...NOPE! Sure didn't...and I have been ready to throw my computer out of the window for the last hour as I stare at the setting that is clearly marked "Automatic"

[thedon]

Thanks a lot! Just came across this for the first time ever! :)

[kaolor]

Thank you Contextures Inc.

It's a really helpful tip.

[BCCLLQP]

This was such a frustrating error, and your fix was perfect! Thanks.

[dashcharivamaraa]

This solved my problem. Thank you very much!

[keuaychanthangone]

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

[harryb]

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

[atibafranklyn]

This was such a life saver, WOW, So happy. Saved me a ton of work.