Heidi asks a question that is a new twist on an old problem how to fill in the blank cells in a range with an outline numbering system. Episode 451 shows how to adapt the technique discussed in Learn Excel to solve this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question comes from Heidi.
Heidi was in one of my excel seminars that I did at a controllers conference out in Las Vegas.
Heidi has an outline where she basically has to fill in the sub items, so she has 1.1 and 1.2 and 1.3 and then needs to number the items in the middle.
The problem is that she has a different number of items for each topic For example 1.1. There's three sub items 1.2 Today there happens to be five sub items you never know how many items you're going to get.
She basically starts out with a sheet that looks like this that has the major items filled in and then she needs to fill in all of the sub items.
The solution to solving this uses a trick that I use a lot when I'm converting a pivot table to values and I need to fill in the outline view with a little bit of a twist.
I'm going to select all of the cells including the headings that we have and the blank cells we want to fill in and then use "Edit" "Go to" On the "Go to" dialog box in the lower left-hand corner. I'm going to click "Special" and then click "Blanks".
Click OK.
and excel chooses just the blank cells in the range.
I'm going to enter formula =0.01+ the cell immediately above this cell and now that here's the real trick.
In order to get excel to fill in all of the cells in the selection you have to type Ctrl enter instead of enter.
Very quickly fills in all of the numbers that we have.
Now these are alive formulas you probably want to convert them into static values.
So of course you would use Ctrl C or copy and then "Edit" "Paste special" "Values" to convert those formulas to values.
Hey, thanks to Heidi for asking that question.
Another idea if you work for a large company where you have many divisions and they get together once a year for Controllers conference or an accounting conference to review results and you want to do some continuing education. Give me a call.
I'd love to come out, do a quick one or two hours seminar for all of the accountants from the various divisions to show them a lot of excel tips.
You can stop by the website MrExel.com.
Click on the "bring a seminar to your city" link, and there's all the details there or just give us a call so we can get out to be at your site to do an excel seminar.
Today's question comes from Heidi.
Heidi was in one of my excel seminars that I did at a controllers conference out in Las Vegas.
Heidi has an outline where she basically has to fill in the sub items, so she has 1.1 and 1.2 and 1.3 and then needs to number the items in the middle.
The problem is that she has a different number of items for each topic For example 1.1. There's three sub items 1.2 Today there happens to be five sub items you never know how many items you're going to get.
She basically starts out with a sheet that looks like this that has the major items filled in and then she needs to fill in all of the sub items.
The solution to solving this uses a trick that I use a lot when I'm converting a pivot table to values and I need to fill in the outline view with a little bit of a twist.
I'm going to select all of the cells including the headings that we have and the blank cells we want to fill in and then use "Edit" "Go to" On the "Go to" dialog box in the lower left-hand corner. I'm going to click "Special" and then click "Blanks".
Click OK.
and excel chooses just the blank cells in the range.
I'm going to enter formula =0.01+ the cell immediately above this cell and now that here's the real trick.
In order to get excel to fill in all of the cells in the selection you have to type Ctrl enter instead of enter.
Very quickly fills in all of the numbers that we have.
Now these are alive formulas you probably want to convert them into static values.
So of course you would use Ctrl C or copy and then "Edit" "Paste special" "Values" to convert those formulas to values.
Hey, thanks to Heidi for asking that question.
Another idea if you work for a large company where you have many divisions and they get together once a year for Controllers conference or an accounting conference to review results and you want to do some continuing education. Give me a call.
I'd love to come out, do a quick one or two hours seminar for all of the accountants from the various divisions to show them a lot of excel tips.
You can stop by the website MrExel.com.
Click on the "bring a seminar to your city" link, and there's all the details there or just give us a call so we can get out to be at your site to do an excel seminar.
