Join Dennis Taylor for an in-depth discussion in this video Copying data or formulas down a column instantly, part of Excel for Mac 2011: Tips and Tricks.
- In this worksheet, called CopyDataFormulas, we're about to write a formula in Column I. Everybody's going to get a new compensation amount based on on a 3.73% increase that we see in Cell J1. So, one way to write the formula would be simply =H2, type it or click on it, *, that's the asterisk, cell J1, click on it, we need to make sure this is an absolute address so as we copy it down the column, the J1 entry should not change. I'll simply press command-T to make that an absolute address.
We could've typed the dollar signs, but that's faster. +H2, there are other ways to write the formula, but all of them involve the same cells. In different orders, perhaps, or in parentheses with the one, and so on. So, instead of pressing return here I'll simply press control-return, because I don't want the active cell to move downward. So this keeps it in place, control-return. And now, copying the formula downward. I could use copy paste techniques, that would take quite a while. I could point to the lower right hand corner, that's called the fill handle, I could click and drag downward.
And that wouldn't be too bad, but it's over 700 rows I've got here, so it would take some time. And what if it were 70,000 rows, or 700,000 rows? That would take a really long time. I'm going to double-click the lower right hand corner and the formula gets copied downward. I think almost anybody would want to know how far did this get copied? Press control-. a few times, first of all, pressing it once takes you to the bottom of the list. Now, if you knew ahead of time that you had 742 rows, you don't have to scroll any farther, but you could do that if you wished.
If you press control-. again, the active cell moves to the top of the range, the range is still selected, control-. is very valuable when you're copying information down a column and you need to know how far the formula, or in some cases, text or a value, got copied. Not only can we copy formulas down a column, we can also copy data, and perhaps in some unusual ways. Maybe these code numbers here refer to some items for each of our 12 months. So, in cell M2 here, I'm going to type January.
And I don't even have to complete the entry, but I'll point to the lower right hand corner and double-click, and we see what happens here. And it turns out that the rate is going to go up every month, we don't know what this rate means exactly let's say, but 1.0025, and then the next rate is going to be 1.0035, and every month it's going to go up by that same interval that we see there. So in this case, I'll highlight both of these. The interval between those is .001, I'll simply double-click here, copy this down the column, and we see what's happened, they've all been incremented as well.
Double-clicking is a great tool for copying data, it's one of Excel's best shortcuts. Unfortunately, there's no corresponding shortcut for copying data quickly across a row. But more often than not, the powerful use of this is in large lists where you're putting in formulas as we saw over here in Column I. A viable feature, simply double-click to copy a formula down the column. One note here too is, be a little bit sensitive to other data, if you have no data to the left of this, this will not work. Let me backtrack a little bit here and introduce a new column to the left of Column I.
And I'm going to delete the data from here downward. First of all, with the active cell here, I'm holding down the shift key, I'll double-click the bottom edge here, highlighting all that, and I'll press delete. Now I'm going to double-click. What happens? Nothing. Excel doesn't recognize anything on either side. Now, if I had data over here, it could be anything, I'll double-click here, Excel looks leftward first, if it doesn't see anything, than it looks rightward. And it follows the data there. So ideally, the use of this is how we first saw it when the information was in a column and there was data adjacent to it on the left side.
A viable tool for copying data.
Learn the top shortcuts, find out how to most efficiently navigate and control the display, and discover the best ways to select, enter, and format data. The course also includes ways to leverage drag-and-drop features, shortcuts for formulas and operations, data management efficiency techniques, guidelines for working with charts efficiently, and a selection of quick tips.
- Converting formulas to values with a simple drag
- Entering today's date or time instantly
- Accessing Ribbon commands from the keyboard
- Creating split screens fast
- Navigating and zooming quickly
- Entering data more efficiently
- Performing calculations without formulas
- Applying formatting with keyboard shortcuts
- Quickly cleaning up extra spaces and deleting duplicate entries