You can repeat a formula in adjacent cells by using various Copy and Paste command sequences or by dragging the fill handle in the lower right corner of the active cell to drag down columns and across rows. But the fastest method for copying a formula down a column is to double-click the fill handle.
- [Voiceover] We're looking at a worksheet called CopyDataFormuulas. This is a worksheet that has about 700 rows or so. If you've never seen a worksheet before or maybe you haven't seen this in a long time, click on a cell in a column where you know there's data in it, and double click the bottom edge of a cell. It'll take you down to the bottom. If there was an empty cell in there, it would've stopped at that point. This goes down to row 742. I'll just double click the top edge of this cell, we're back up top. I'm about a write a formula here in column I. One of Excel's best shortcuts is the ability to quickly copy a formula down a column.
There's no corresponding shortcut for copying across a row. It's more likely to have the need to copy a formula down a long column than across a wide row. So I'm going to write a formula in cell I2 that calculates a new compensation amount. Everybody in this list is going to get 2.73% increase, so certainly one way to write the formula is equal H2 times this percentage amount in J1. That needs to be an absolute reference because I want to copy this down the column.
I'll press the function key F4 to make that an absolute address. Then after calculating the amount of the increase I'll add it to the original compensation amount. Instead of pressing return here, I'm going to press control+return. The advantage is if I press return, the active cell will move down. If I press control+return, it stays in place. Now rather than using copy-paste techniques, or dragging from the fill handle in the lower right hand corner. By the way, that's not a bad tip if it's just five or six or 10 cells whatever, we can certainly drag from the corner, but we've got 700 rows here.
What if this were thousands of rows? This would be pun, a real drag to drag downward. I'm simply going to double click the lower right hand corner and the data gets copied downward. I think the question would enter your mind, how far did it get copied? A good follow up to this and not so well known, control+period moves the active cell to the bottom of the list. Now if you weren't sure that 742 was the last cell in the list, just scroll a little bit more to confirm that, and you'd see immediately. Control+period will take us back up top.
We can also copy just raw data. We can also copy data that happens to be within Excel's custom lists. For example, I put in these numbers here. I haven't done the months yet, but they're gonna be January through December here. So I'm gonna type Jan. Once again not pressing return, but control+return. Now I'll double click the lower right hand corner, and we see what happens here. That's one of Excel's built-in custom lists. Abbreviations for days of the week also fall into that category as well as full spellings for the months and days of the week.
If it's just raw data, maybe for the time being I want to put in 5% for all these here. I'll just type in 5%, once again control+return, double click the lower right hand corner. For the moment they're all gonna be 5%. We'll end up changing some of those a bit later perhaps. But you can copy, as we saw here, text, you can copy items from a custom list, and you can copy formulas down a column simply by double clicking that fill handle in the lower right hand corner. Excel essentially figures out your adjacent data and knows when to stop.
So that's a built-in feature that makes working with Excel particularly handy in these kinds of situations. Remember there's no corresponding shortcut for quickly copying formulas or data across a row the way there is down a column.
The course then dives into data entry and editing techniques, formatting and drag-and-drop tricks, keyboard shortcuts for working with formulas, data management strategies, and chart tricks. Short on time? Make sure to check out the "Ten Tiny Tips" chapter for a quick productivity boost.
- Entering today's date or time instantly
- Converting formulas to values with a simple right-drag
- Undoing and redoing with keyboard commands
- Creating split screens fast
- Navigating in workbooks quickly
- Selecting noncontiguous ranges
- Entering data more efficiently
- Applying formatting with keyboard shortcuts
- Accelerating copying and moving data with drag and drop
- Creating formulas rapidly
- Using database techniques to work with Excel data
- Working with charts, shapes, and linked images