Copying column formulas instantly

- When you're working with large lists of data in Excel, it's not uncommon to write a formula and then need to copy that formula down the entire column. In this particular worksheet called CopyColumnFormulas, there's a formula in cell F2 actually using a function called DATEDIF. This is covered elsewhere in the course. It tells us how many years have elapsed since the hire date here. And it does it in the same way that we would calculate birthdays. The actual year hasn't been reached until we reach that day of the year.

So, in this particular case we've got the form in place, let's say it's late in the year 2014. This person has been here 15 years. We'd like to copy this formula down the column. Standard edit copy paste techniques are very lengthy in this particular case, and dragging the lower right hand corner, which is a common way, certainly works fine for small amounts of data. But in this particular example we've got over 700 rows. Imagine if it were 7,000 or 70,000 rows of data. The fastest way to copy a formula down a column, nearly always is to double-click the lower right-hand corner of the cell.

You can also do this, by the way, with simple data, too, if we needed the same word or phrase all the way down the column or just a pure number. We could also double-click. But this is a formula, and we expect to see different results in these cells because we've got different hire dates. Simply double-click the lower right-hand corner. It's called the fill handle, and we see all of our results popping into place. I think what you would want to know almost immediately is, "Did this get copied all the way down the column?" But rather than scrolling, which is relatively easy, simply press ctrl + .

and that moves the active cell to the bottom of the list. You might want to scroll slightly below that just to see it and make sure. ctrl + . simply moves the active cell around the corners of a selected range. And when you double-click, not only are you copying the formula but you're also highlighting the range. So, every time I double-click to copy a formula like we see here, I always press ctrl + . just to make sure this got copied all the way down the column. Now, I'm going to press ctrl + z or cmd + z, either way, to undo this action, and point out a slight problem occasionally.

What if the data entry for one of these particular people just wasn't recorded? I'm going to temporarily just move this off to the side. I can see that clearly there, but I wouldn't necessarily see that if it's in row 200 or 500 or whatever. What happens now if I double-click right here? The display stops. Now, if you know that there could be empty cells in column E, but still you'd like to copy this downward, let me again undo, cmd + z. I'm going to hide column E. Keep in mind, there is an empty cell in E9.

Right-click and hide column E. Now, if I come back and double-click the fill handle, the cell won't look at the empty column, or the column that has an empty cell in it, it will look to the first visible column to the left and follow it downward until it sees an empty cell. So this time I'll double-click. We will get a funny answer in there, because that's the cell that has the missing date. Nevertheless, as I press ctrl + . here, we will see that the formula got copied all the way down to row 742.

Clicking ctrl + . again, bring the active cell up here. At that point we probably want to redisplay the hidden column. Simply drag across here. Either double-click the boundary or right-click and unhide. So let's say eventually we would put in our value here. Now what I'll do is simply copy this over, or move it over, right to there. And we'll simply copy this down one cell and we're all set. But anytime you do have an empty cell in an adjacent column to the left, the double-clicking method will stop if it sees an empty cell.

If there's any thought or any suspicion there might be one, simply highlight the column to the left. But always check out after double-clicking whether the formula got copied all the way to the bottom. Similarly, off to the right here, using a formula we might have seen in another movie, calculating a new salary here, =, and there are a couple different ways to right this formula, but one way would be simply to take this amount * the data from cell L2. To make that an absolute reference, we can press cmd + t. Simply do it that way, and then + this cell.

A slight improvement here in speed is simply to press ctrl + return instead of the simple return. This keeps the active cell in place. And now we can double-click the lower right-hand corner. And once again, immediately press ctrl + . and we see what's happened there. So, copying a formula down a column is very fast by double-clicking the lower right-hand corner. It's too bad we don't have a corresponding shortcut for copying across a row, but that's not nearly as commonly needed as the feature we saw here, a great tool for copying formulas in Excel.