My favorite trick is to double click the fill handle to copy a formula. But, this requires the column to the left to have no blanks cells. Today, Suzanne from Chicago gives us a great trick of how to overcome a sparse column to the left of the formula. Episode 545 shows you how.
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:
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's tip comes from Suzanne in Chicago; I met Suzanne in one of my Power Excel seminars.
Now we already have a bunch of seminar schedule for the fall.
Now of course the big one is the data analyst boot camp in Chicago, that's September 18 through the 20th, it's a three days excel and access hosted by myself and Mike Alexander.
If you're interested in Excel and Access it’s a great opportunity.
We also have a number of half-day seminars already were scheduled for Birmingham Alabama, Milwaukee Wisconsin, Madison Wisconsin, Springfield Illinois, and Springfield Missouri.
We're working on adding Boise to that list a couple of hour and a half breakout sessions in Virginia Beach and Roanoke.
Come if you're interested in any of those just go to the home page of MrExcel and click the upcoming seminars link plus, I'd love to come to your city if you have an accounting group, the IMA, IIA, and AICPA.
I'd love to come out and do a seminar for your group or even a large company if you have 30 to 50 people and get them together in a room; we all come out and do a seminar for you.
Let me show you this tip, now my favorite trick in Excel is when we need to copy a formula, we double click the fill handle and Excel copies the formula all the way down.
Unfortunately, that trick is relying on the column to the left, so let's say that we have a formula here maybe I want to join city state and zip into a single column.
So I'll use =E2&” “&” “&G2, that will join those three columns basically concatenate them.
When I double click the fill handle, unfortunately because the column to the left is only sparsely populated, it's where we put the apartment number or the suite number, it doesn't get copied down all the way.
Well I'm going to undo that, here's a great trick that I learned if you hide this column format column hide, now Excel will rely on the column that is two columns to the left.
Basically it's looking at the visible column immediately to the left, so here we're in column D double-click the filling handle, it'll look over to column B of course every customer has one line of their address filled in so the data gets copied all the way down, then we can use format column unhide to unhide the column.
Now this works great whether you have data to the right or to the left, in this particular case we did happen to have data that was to the right so another solution would have been to insert a blank column between column C and D, now when we double-click the filling and handle since there's no data to the left it will look to the right, and because the city happens to be filled in, it will also fill it in.
So it's your call whether you want to hide column C or temporarily insert a new column D, either way basically allows you to use that double click the fill handle much faster than dragging the fill handle and overshooting the end of the data going back and forth, what I call the fill handle dance.
Hey thanks for stopping by, we'll see you next time for another netsast from MrExcel.
Today's tip comes from Suzanne in Chicago; I met Suzanne in one of my Power Excel seminars.
Now we already have a bunch of seminar schedule for the fall.
Now of course the big one is the data analyst boot camp in Chicago, that's September 18 through the 20th, it's a three days excel and access hosted by myself and Mike Alexander.
If you're interested in Excel and Access it’s a great opportunity.
We also have a number of half-day seminars already were scheduled for Birmingham Alabama, Milwaukee Wisconsin, Madison Wisconsin, Springfield Illinois, and Springfield Missouri.
We're working on adding Boise to that list a couple of hour and a half breakout sessions in Virginia Beach and Roanoke.
Come if you're interested in any of those just go to the home page of MrExcel and click the upcoming seminars link plus, I'd love to come to your city if you have an accounting group, the IMA, IIA, and AICPA.
I'd love to come out and do a seminar for your group or even a large company if you have 30 to 50 people and get them together in a room; we all come out and do a seminar for you.
Let me show you this tip, now my favorite trick in Excel is when we need to copy a formula, we double click the fill handle and Excel copies the formula all the way down.
Unfortunately, that trick is relying on the column to the left, so let's say that we have a formula here maybe I want to join city state and zip into a single column.
So I'll use =E2&” “&” “&G2, that will join those three columns basically concatenate them.
When I double click the fill handle, unfortunately because the column to the left is only sparsely populated, it's where we put the apartment number or the suite number, it doesn't get copied down all the way.
Well I'm going to undo that, here's a great trick that I learned if you hide this column format column hide, now Excel will rely on the column that is two columns to the left.
Basically it's looking at the visible column immediately to the left, so here we're in column D double-click the filling handle, it'll look over to column B of course every customer has one line of their address filled in so the data gets copied all the way down, then we can use format column unhide to unhide the column.
Now this works great whether you have data to the right or to the left, in this particular case we did happen to have data that was to the right so another solution would have been to insert a blank column between column C and D, now when we double-click the filling and handle since there's no data to the left it will look to the right, and because the city happens to be filled in, it will also fill it in.
So it's your call whether you want to hide column C or temporarily insert a new column D, either way basically allows you to use that double click the fill handle much faster than dragging the fill handle and overshooting the end of the data going back and forth, what I call the fill handle dance.
Hey thanks for stopping by, we'll see you next time for another netsast from MrExcel.
