Problem from my Atlanta Power Excel seminar for the Gulf South Council of the IMA
Two columns of numbers. The AutoSum at the bottom of the second column stops working sometimes.
The problem happens if you are inserting rows and sometimes leave out a number
One best practice is to use a blank row between the AutoSum and the numbers.
Always insert the new rows above that blank row.
Pro Tip: Make the blank row small and change the fill color
To download this workbook: https://www.mrexcel.com/download-center/2018/05/problem-with-two-autosum.xlsx
List of upcoming seminars: Excel Seminar Schedule
Two columns of numbers. The AutoSum at the bottom of the second column stops working sometimes.
The problem happens if you are inserting rows and sometimes leave out a number
One best practice is to use a blank row between the AutoSum and the numbers.
Always insert the new rows above that blank row.
Pro Tip: Make the blank row small and change the fill color
To download this workbook: https://www.mrexcel.com/download-center/2018/05/problem-with-two-autosum.xlsx
List of upcoming seminars: Excel Seminar Schedule
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2199: Problem With Two AutoSum.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Couple weeks ago I was down in Atlanta, Georgia, for the Gulf South Council meeting of the IMA.
A lot of great questions came up in that one, including this one that stumped me.
Someone said that they had two columns of AutoSum-- so these two cells here, press the AutoSum button (Alt+=)-- and that as they were working, they would insert rows, insert rows, insert rows, and then suddenly, Column 2 would quit working.
Now, that was bizarre-- like, why would that ever happen?
But then, you know, as I was talking through it, you know, I started to replicate what was going on.
So we come here, we do Alt+IR to insert a row, type the next item and 100.
That 800 changes to 900, that's perfect; 200, 1600 changes to 1800, that's perfect.
Alright, but then insert another row and this one has a value.
Beautiful-- the thousand changes.
But Column 2, for whatever reason, has no value at all.
And then, we insert a new row-- alright, see, that's where the problem comes in.
Now Column 1 is going to work.
I want to enter 123, and the number changes.
But over here, because I didn't type anything for Jicama in C11, when I type the value here, 222, BAM-- it's not working, right?
So, what we're running into there, is Excel is extending that formula.
If you have three or more cells that the formula is referring to, and you insert a fourth cell, they'll extend it; but they only extend it if you add a number.
And, well, we didn't add a number here, that causes all sorts of problems.
So, my suggestion is that, we're going to insert a blank row here, and I'm going to change the color of this blank row-- you don't have to change the color of the blank row, but I like to change the color of the blank row.
We, like, go to a nice light gray and then put your total here, alright?
In this total, when we use the AutoSum, it's going to include the blank row.
Copy that across.
And I-- this is a pro tip here-- I even make for my row height, like, tiny, like, maybe 3, alright?
So it just looks like we're just putting, you know, an accounting underline there almost.
And then you have to make sure to always insert the new rows above the blank row.
So, "Iceberg", "111", "222", great.
Insert a new row: Jicama, spelled wrong, "111", great, nothing.
And then insert a new row: Kale, "111", "222", BAM!
And it keeps working.
Alright.
So, if you're going to have those blank cells, just a better overall approach all the time, is to include a blank row in your AutoSum, and then train the people using the spreadsheet to always insert the extra new rows above the blank row, and your AutoSum will keep going on.
Tips like that are in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Alright.
Episode recap: we have two columns of numbers; the AutoSum at the bottom of the second column stops working, intermittently; once it breaks, it's broken forever; it's when you're inserting rows and we've figured out that it's when you sometimes leave out a number; so the best practice is to always leave a blank row between the AutoSum and the numbers; and then insert new rows above that blank row; you can make the blank row a little bit smaller, change the color, it'll actually look like an underline.
Now, to download the workbook from today's video, in case you want to work along, is the URL down there in the YouTube description.
And hey, if you want a fun day, check out my upcoming list of seminars.
I'll put that link down in the YouTube description, as well.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Couple weeks ago I was down in Atlanta, Georgia, for the Gulf South Council meeting of the IMA.
A lot of great questions came up in that one, including this one that stumped me.
Someone said that they had two columns of AutoSum-- so these two cells here, press the AutoSum button (Alt+=)-- and that as they were working, they would insert rows, insert rows, insert rows, and then suddenly, Column 2 would quit working.
Now, that was bizarre-- like, why would that ever happen?
But then, you know, as I was talking through it, you know, I started to replicate what was going on.
So we come here, we do Alt+IR to insert a row, type the next item and 100.
That 800 changes to 900, that's perfect; 200, 1600 changes to 1800, that's perfect.
Alright, but then insert another row and this one has a value.
Beautiful-- the thousand changes.
But Column 2, for whatever reason, has no value at all.
And then, we insert a new row-- alright, see, that's where the problem comes in.
Now Column 1 is going to work.
I want to enter 123, and the number changes.
But over here, because I didn't type anything for Jicama in C11, when I type the value here, 222, BAM-- it's not working, right?
So, what we're running into there, is Excel is extending that formula.
If you have three or more cells that the formula is referring to, and you insert a fourth cell, they'll extend it; but they only extend it if you add a number.
And, well, we didn't add a number here, that causes all sorts of problems.
So, my suggestion is that, we're going to insert a blank row here, and I'm going to change the color of this blank row-- you don't have to change the color of the blank row, but I like to change the color of the blank row.
We, like, go to a nice light gray and then put your total here, alright?
In this total, when we use the AutoSum, it's going to include the blank row.
Copy that across.
And I-- this is a pro tip here-- I even make for my row height, like, tiny, like, maybe 3, alright?
So it just looks like we're just putting, you know, an accounting underline there almost.
And then you have to make sure to always insert the new rows above the blank row.
So, "Iceberg", "111", "222", great.
Insert a new row: Jicama, spelled wrong, "111", great, nothing.
And then insert a new row: Kale, "111", "222", BAM!
And it keeps working.
Alright.
So, if you're going to have those blank cells, just a better overall approach all the time, is to include a blank row in your AutoSum, and then train the people using the spreadsheet to always insert the extra new rows above the blank row, and your AutoSum will keep going on.
Tips like that are in my new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Alright.
Episode recap: we have two columns of numbers; the AutoSum at the bottom of the second column stops working, intermittently; once it breaks, it's broken forever; it's when you're inserting rows and we've figured out that it's when you sometimes leave out a number; so the best practice is to always leave a blank row between the AutoSum and the numbers; and then insert new rows above that blank row; you can make the blank row a little bit smaller, change the color, it'll actually look like an underline.
Now, to download the workbook from today's video, in case you want to work along, is the URL down there in the YouTube description.
And hey, if you want a fun day, check out my upcoming list of seminars.
I'll put that link down in the YouTube description, as well.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
