My favorite Excel trick is to double-click the fill handle to copy a formula down to all of your rows of data. In Episode 607, Ill take a look at many different details of the double-click trick.
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.
When I do the Excel seminars, by far, the favorite trick is when you double click the Fill handle in order to copy a formula down to all the rows in the data sets.
So, let’s say we have a simple formula that takes the city in =E2&”, “&F2&” “&G2 in order to concatenate the City, State and Zip.
Now, if you need to copy this down rather than grab the fill handle and start to drag, which could be a real pain especially if you have 5000 rows, Excel starts going faster, and faster, and faster.
And you inevitably blow pass the end of the data, just simply double click the fill handle, the fill handle is a little square dot in the lower right-hand corner of the cell and Excel will look to the left, figure out how many rows of data you have today and copy down to exactly that many rows – great way to copy the formula down.
Now, there are some things that can go wrong with double clicking the Fill Handle.
For example, let’s say that I insert a new column here between Address 2 and City, State, Zip and I move my formula to that spot.
Now, if I double click the Fill Handle because the column to the left, the Address 2 column is very sparsely filled in, Excel does not know how far to copy the formula down and it doesn’t copy it all.
One cool trick here is to hide column D, Format Column Hide and now when I double click the Fill Handle because the Address field is completely filled in, Excel copies it down.
Of course, it’s always good to hit Ctrl+.
That will take you to the other end of the selection and make sure that you’re all the way to the end of the data.
Now, some interesting trivia, I guess, about how this works, I always say that Excel generally looks to the left.
So, for example, if we have seven rows to the left of our data and 11 rows to the right of our data, Excel will use the seven rows.
So, when I double click the fill handle, because the data to the left is filled in, that’s how many rows it chooses.
But over here in Columns E and F, I just have the 11 rows in Column F to the right of my formula, when I double click the Fill Handle this time, Excel copies it down to as many rows as are on the right.
So, it can use the column to the right provided the column to the left is blank.
But what’s interesting what most people don’t know is what actually trumps both the left and the right is to have something immediately below your formula.
So here, I’ve already have one formula in the column and Column I goes down to Row 14, if I type a new formula here and double click the Fill Handle, Excel will actually not look at the left, not look at the right but copy the whole thing down to as many rows as I have before in this column.
So double clicking the Fill Handle, great way to get your formula copied down all of the rows of data.
Hey, thanks for stopping by, I’ll see you next time for another netcast from MrExcel.
When I do the Excel seminars, by far, the favorite trick is when you double click the Fill handle in order to copy a formula down to all the rows in the data sets.
So, let’s say we have a simple formula that takes the city in =E2&”, “&F2&” “&G2 in order to concatenate the City, State and Zip.
Now, if you need to copy this down rather than grab the fill handle and start to drag, which could be a real pain especially if you have 5000 rows, Excel starts going faster, and faster, and faster.
And you inevitably blow pass the end of the data, just simply double click the fill handle, the fill handle is a little square dot in the lower right-hand corner of the cell and Excel will look to the left, figure out how many rows of data you have today and copy down to exactly that many rows – great way to copy the formula down.
Now, there are some things that can go wrong with double clicking the Fill Handle.
For example, let’s say that I insert a new column here between Address 2 and City, State, Zip and I move my formula to that spot.
Now, if I double click the Fill Handle because the column to the left, the Address 2 column is very sparsely filled in, Excel does not know how far to copy the formula down and it doesn’t copy it all.
One cool trick here is to hide column D, Format Column Hide and now when I double click the Fill Handle because the Address field is completely filled in, Excel copies it down.
Of course, it’s always good to hit Ctrl+.
That will take you to the other end of the selection and make sure that you’re all the way to the end of the data.
Now, some interesting trivia, I guess, about how this works, I always say that Excel generally looks to the left.
So, for example, if we have seven rows to the left of our data and 11 rows to the right of our data, Excel will use the seven rows.
So, when I double click the fill handle, because the data to the left is filled in, that’s how many rows it chooses.
But over here in Columns E and F, I just have the 11 rows in Column F to the right of my formula, when I double click the Fill Handle this time, Excel copies it down to as many rows as are on the right.
So, it can use the column to the right provided the column to the left is blank.
But what’s interesting what most people don’t know is what actually trumps both the left and the right is to have something immediately below your formula.
So here, I’ve already have one formula in the column and Column I goes down to Row 14, if I type a new formula here and double click the Fill Handle, Excel will actually not look at the left, not look at the right but copy the whole thing down to as many rows as I have before in this column.
So double clicking the Fill Handle, great way to get your formula copied down all of the rows of data.
Hey, thanks for stopping by, I’ll see you next time for another netcast from MrExcel.
