From the course: Excel 2016: Tips and Tricks

Copy data or formulas down a column instantly

From the course: Excel 2016: Tips and Tricks

Start my 1-month free trial

Copy data or formulas down a column instantly

- [Voiceover] We're looking at a worksheet called Employees. There's a formula in cell L-2, it's calculating new compensation for the number in cell K-2. And we wanna copy this formula down the column. Notice there's an absolute reference here to cell N-1, that's the percent of increase that everybody's going to get. We wanna copy this down the column. Trying to do this by way of copy and paste is gonna be very lengthy and probably inaccurate too. Dragging from the lower right-hand corner is fine. If we had only eight or 10 entries that would work beautifully. But we've got about 600 or so. I'll click on the cell to the left, double-click the bottom edge there, down to row 742. So how do we copy this quickly down the column? Simply double-click that fill handle in the lower right-hand corner where we see the formula. It gets copied all the way down the column. But are you sure? Then press Ctrl + period. Takes you down to the bottom. If you're not sure that's the bottom, scroll a little bit beyond it, you'll see. Ctrl + period, brings us right back up top. So, double-clicking the lower right-hand corner allows us to copy a formula downward, and, as we would hope, it adjusts the cell references properly. It's up to you, of course, to recognize that we did need to have an absolute address here so on creating the formula, you would make sure that that cell, N-1, is an absolute reference, by typing in the dollar signs or pressing the Function key F4. Now, we can also use this fill-in feature for data. And sometimes, in an unusual way. What we really need to have filled in here are the months of the year. So I'm simply going to type January. I won't press Enter, I'll press Ctrl + Enter so the act of cell doesn't move, and I'll simply double-click the lower right-hand corner. Now, if this were normal text, it would simply repeat January over and over and over again. I'll just double-click. There it is. If we need to fill in a rate, maybe it's not sure yet, we might type in a word. Could be any word. If it's not a special word that's recognized by way of special custom lists, we could make an entry here, double click, and it just says pending. We'll fill it in later maybe. Same thing with tax rate over here, maybe it's a number, everybody except for a few are going to have a 5% tax rate here. For the moment we'll just fill that in with that number, 5%, Ctrl + Enter. Double-click simply repeats it down the column. And once again, if you're not sure about how deep that column is, Ctrl + period, Excel figures out the extent of your adjacent data, only copies it down that far. Ctrl + period. So, an indispensable shortcut in Excel, double-clicking the lower right-hand corner. Primarily of value with formulas, but as we see, you can also use this with pure numbers, you can use it with special entries like days of the week and months of the year, or we can use it with simple text. It really helps us fill in data quickly and easily.

Contents