Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
There are many times when you create a formula where you also need to copy that formula into many cells below it in the same column. In column J are some salaries. There are about 750 of them. In cell L3, right here, is a formula that's calculating a new salary, and we want to use the same formula for every cell downward in column L corresponding with all the column J entries. Everybody is going to be getting a 3.73% increase. We've got an absolute address in there to take care of that. We round the results to the nearest whole dollar.
Standard way to do this is to drag from the lower right-hand corner. Possibly you use the right mouse button and do a copy. That's usually not as efficient, although it can work as well. If we start to drag this, and if you pardon the pun for the second, this could be a real drag. This is 700 rows, but what if it is 70,000 or 170,000? We've got a lot of rows in Excel worksheets; it's going to be a lot faster if we simply double-click as I am doing here. And right away you would be concerned, and you would be wondering how far did this get copied? And a quick way to find out is to press Ctrl+Period and then press it again.
Ctrl+Period simply moves the active cell around a highlighted range. The logic behind this is we want this to be copied downward as long as there is data in adjacent columns to the left. There can be a gap here and there. This still works. So if I were to press Ctrl+Z to undo this for example, I'll take out this one momentarily. What happens when we double-click? It does go all the way down the column. Now that didn't happen in prior versions. So that might surprise you if you are familiar with this shortcut. However, if there is an empty row in here, suppose for example there is an empty row right here.
I am going to put it on purpose, right there row 16. Now we'll double-click and see what happens. So it works along with your other adjacent data and if there is a gap in the immediate column to the left it will still copy the information below that. But if you have a completely empty row in the midst of this, it will not work perhaps the way you might be expecting it to. But there is no question this can be a huge, huge time-saver here. Again, double-click the lower right-hand corner.
I am doing this with formulas, but it could just as easily be done with text or with values. And let's say I probably wouldn't really put in a tax rate here, but just to illustrate the point here, if I put in 10%, and strictly value, double-click here. This too gets copied all the way down the column. So whether it's text, whether it's a value, or whether it's a formula, the information will be copied repeatedly. And of course the real value here is a formula like we see here, and it does get adjusted properly. In other words the formula gets copied in a relative way.
There is no question this is a time-saver. I wish there were a similar feature for copying information across a row, but there isn't.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 73613 Viewers
80 Video lessons · 129290 Viewers
52 Video lessons · 63665 Viewers
59 Video lessons · 49424 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.