From the course: Excel 2016: Advanced Formulas and Functions

Copying column formulas instantly

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Copying column formulas instantly

“

- If you're working with lists of data and you're writing formulas, it's not uncommon to essentially write one formula and then copy it down a column. In cell E2 is a formula. I'll double click this cell. Using a function called DATEDIF, we're tabulating the difference between two days. And as I record this in early December of 2015, today's date. Compare with the date here, is almost 14, but not quite 14 years away. So the DATEDIF function actually calculates years of service in the same way that we would calculate birthdays or anniversary dates, coming up with the answer 13. And I want to do this for every cell down the column. Momentarily here, I'm going to double click the bottom edge of one of these cells, like this one here. Double click. To show you this data goes down to row 742. Double click the top edge, back up top. So we want to copy this formula down to row 742. Now, copying and pasting is not the way to go here. Because it's a much lengthier process than is necessary. We can drag from the lower right hand corner, and many times when you're writing formulas if you only have to drag down 10 or 20 rows, this is certainly fast enough. But I'm going to double click. Certainly one of Excel's best short cuts. And the formula goes all the way down. Or does it? How do you know? We could scroll up and down to verify that, but it's much faster to press CTRL + . Now, if we knew ahead of time that 742 was the bottom, and let's say we did in this case. We're all set. If we're not, we'll scroll just a little bit beyond that and we see what's happening. When you press CTRL + . , the active cell just moves around the corners of a selected range. And when we do copy a formula by double clicking, it keeps the data highlighted. So it's a great short cut. Unfortunately, we have no corresponding short cut to quickly copy formulas across a row. Here's on more example. I'm going to write a formula here in cell I2 that calculates a new salary. And everybody's going to get the same salary increase out of cell K2. =G2 times this amount here. We want this to be an absolute address so we'll either have typed in the dollar signs or simply press the function key F4 to make sure that that absolute address gets copied all the way down the column. Plus and then cell G2. Now, in completing the formula, I would normally press enter, but if I do, the active cell will move downward. Nothing terribly wrong with that, but by pressing CTRL + Enter, the formula is complete and the active cell stays in place. As it does here. I'll double click. Formula gets copied down the column. Once again CTRL + . just to make sure. Yep, down to row 742. CTRL + ., right back up top. So every one of these formulas uses that reference to K1 because it's an absolute address. And copying the formula down the column was painless. Extremely fast with that double clicking technique.

Contents