Software

Copy hundreds of rows of Excel formulas in three steps

Copying formulas in Excel is a relatively simple operation - unless you need to copy down through dozens or even hundreds of cells. Here's a foolproof little trick that will knock out the task for you.

Excel provides a number of ways to copy formulas. You can use Excel’s Auto Fill feature or, as an alternative, select the cells you want to copy to, enter the formula in the active cell, and then press [Ctrl][Enter]. These methods are fine for copying formulas down a column of cells -- unless the column extends down hundreds of rows.

For example, say Column A in your worksheet lists 100 products and Column B lists the wholesale price of each. You would like to enter a formula that calculates the retail price for each product and displays the results in Column C. Selecting 100 cells is a time-consuming and error-prone process, but you can copy the formula down Column C without having to select the cells. Follow these steps for foolproof cell copying:

  1. Click in C2 and enter =B2*300%
  2. Press [Ctrl][Enter].
  3. Double-click the fill handle (the small black square in the lower-right corner of the cell).

Excel will stop copying the formula when it reaches a row with a blank cell in Column B. To ensure that you copied the formula correctly, click in C1 and then press [Ctrl][Shift][End].


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

35 comments
balgowda
balgowda

how we use formula for 300+20.2 to 30+020.2

modeler4
modeler4

Aren't these articles supposed to mention what version of MS Excel this covers????

vsacco1745
vsacco1745

I discovered this double-click by accident about 2 years ago. I teach Excel and past this along to my trainees. None of their experienced co-workers knew about this short cut. Checked all my text books. Could not find this shortcut. I wonder how many of your experieced readers knew about it? Also is there any text book which describes this short cut? vsacco1745@aol.com

njnb
njnb

Call me an old fuddy-duddy if you like but as 'formula' is a Latin word I had always believed that the plural form is 'formulae'.

zdnet.20.muffin
zdnet.20.muffin

I don't understand what the control enter is for. Mine seems to work by just double clicking the fill handle. Am I missing something? Neat trick. Thanks.

ayshey
ayshey

thanks for the useful tool

jbenton
jbenton

step 2 - just press enter after double clicking the fill handle, press ctrl-. (control plus full stop) which jumps to the 'next corner' of a range (or to where the copying down finished in this case). If necessary the fill handle can be used to drag the formula past a gap in your data before repeating the double-click-the-fill-handle process outlined above

amit_sriv_010
amit_sriv_010

How to Link of a cell to Another sheet's cell Pls replay My question with example.

pastorel
pastorel

The tip is excellent indeed because it reminds us that double-clicking the fill handle does the job (no need to [Ctrl][Enter], just [Enter] as usual in step 2)

vrakeshis
vrakeshis

Very Very Handy - Excellent Tip ! Thanks a lot !

mlusk
mlusk

Handy thing to know, but I don't understand the point of step 2, pressing [Ctrl][Enter]. It works without doing that.

david.hanshumaker
david.hanshumaker

I frequently copy expressions down many rows, and thought I was doing it efficiently - until I read this tip. This tip was excellent.

dhays
dhays

What I need is a way to copy a whole sheet, leaving the links to another sheet behind, but leaving the formulas inplace so the graphs copy as well. Copying and pasting just the data, leaves the graphs out. I end up using the create a copy of the whole sheet method and passing it along and letting them get the error message that the this sheet contains links, and whether to update or not. With me holding the original on my PC, the reviewer cannot update, and it is not needed or desired that they do. It is for reference for the month, next month a newer version with updated information is provided, with the same links. I realize this is almost a year after most of the comments were posted, however, since it was just reissued, maybe more will be reading and responding.

TTfight
TTfight

Hmmm...I have to confirm. Doesn't seem to be a need for the CTRL+ENTER. Is their some sort of subtle difference?

brownt
brownt

Thanks for the tip, I love it. I don't know what happened, but I followed the instructions (1,2,3), it did not work until I skipped #2 and went to #3. It worked perfectly. Then I tried #2, perfect. Are these steps out of order.

cb.kaltelco
cb.kaltelco

Why wouldn't you just place your formula in the first cell, select the range required and hit ctrl D?

SheRex
SheRex

When I created a test file exactly as used in the example and using that formula it worked fine. However, when I tried it in another file in nonadnacent cells, it didn't work. What am I missing?

TygerZoyd
TygerZoyd

@jbenton: Perhaps you intended your solution to 'read' differently, but I believe a clarification is needed for your "even easier" solution in order for it to work. Why would you have a user 'just press enter' on Step 2 when doing this completes the formula entry (from Step 1) and moves the cursor away from the formula (in the direction specified by how the user has customized the Enter key action)? I tested this and for me, after I entered the formula and pressed Enter, the cursor moved 1 column to the RIGHT so that proceeding to double-click the fill handle auto-fills THAT column with the value from the top cell -- obviously NOT the desired effect/function. The author of the article stated to press Ctrl-Enter for Step 2, which keeps the cursor in-place in the current cell where the formula was just entered so that Step 3 (double-clicking the fill handle) will work properly and have the desired effect. With your method of 'just press enter', the user has to click back on the cell where they just entered the formula in order to proceed (for Step 3 to work). The rest of your instructions are sound and helpful, but if the user never makes it to that point then they can't use this solution effectively.

grzollar
grzollar

1. Go to the sheet you want to show the cell in. 2. type the equal sign 3. click on the sheet with the cell you want to link 4. click in the cell, the other sheet will appear 5. type enter.

ellen
ellen

If you are on Sheet1 and you want to link to a cell in Sheet2, so that the contents of the cell in Sheet2 appears in Sheet1, do this: Go to the cell in Sheet1, type = (equal sign), then click on Sheet2 tab, then click on the cell you want, then press enter. THis will return you to Sheet1 and show a formula that looks like =Sheet2!B2. Basically you just precede the cell you want with the sheetname and an exclamation mark.

jbenton
jbenton

if you want to use the value from the other sheet's cell then when writing you're formula just use the mouse to select the sheet/cell when needed eg =100+(select required cell now)*2 if you want to jump to the other cell then use a hyperlink: select the cell you want to link from and initiate a hyperlink by pressing ctrl-K then select "place in this document" from the Link to: menu on left hand side then select the sheet from the list available and type in the cell reference in the box above

bjr9979
bjr9979

This tip doesn't work for more complicated formulas. A better way for me is to copy the original formula, type where you want it to go in the name box and then paste it. For example if my formula is in cell M1 I select that cell and press Ctrl+C. Then if I want to copy it to M2 to M15 I put M2:M15 in the name box and press Ctrl+V. Also only three steps and much more flexible than the original tip.

balgowda
balgowda

i would like to change no of stations by changeing the formula, can i know how to change 300+20.200 to 30+020.200. Thanks and regards

Merlirin
Merlirin

What ctrl-Enter does is keep you in the same cell (and not go down to the cell below) - it actually save you having to arrow back up to the previous cell. I alway show the "double-click on fill handle" method of copying formulas to my students - it is a lfe saver for large documents.

jbenton
jbenton

you can achieve this if you copy the whole sheet (along with the chart if on a different sheet) to a new workbook, then select Edit, Links and click the Break Link option on the right and confirm this will convert all linked references to their values, leaving all other formulae intact you can even copy just a chart and do this (providing the series don't cover too much data) hope this helps

mw00110011
mw00110011

Control+Enter just completes the formula entry and leaves the selection in the current cell. Just pressing Enter alone will move focus to another cell, determined by the "Move selection after Enter / Direction" option in Tools > Options > Edit tab. Since you must ultimately resort to the mouse to accomplish this fill process, you could as well hit Enter, probably jump to the next cell, then have to reposition to the desired cell to use the double-click fill option.

jbenton
jbenton

To use the crtl-D trick you first need to select the whole range to be filled (similarly if you were to use ctrl-enter, or copy and paste) Double-clicking the fill handle will fill down from the current cell until the adjacent cell is blank - so no need to find a cell 100's or more rows away but NB that your adjacent column can't have any gaps in its data for this to work It's also worth noting that if you select several cells vertically this will fill as a series (the other methods won't). Selecting several cells horizontally will fill all those columns downwards until an adjacent gap is found Dragging the fill handle will also fill a range (vertically or horizontally) but will have different effects if you use different mouse buttons or hold the ctrl or shift keys; shift will insert cells, ctrl will toggle series filling, right mouse will offer a menu when released It's worth getting familiar with and it'll impress colleagues if nothing else

jbenton
jbenton

you can select a range of adjacent cells (see below) btw, what were you expecting to happen?

jbenton
jbenton

Thanks for the correction As you say it's possible to configure which direction the selection moves after pressing return I've had mine set to not move at all for so long now that I'd forgotton that the control key IS necessary to over-ride this movement

Laurie_G
Laurie_G

Go to the source data / cell, copy. Go to the destination cell / data top left corner, right click, paste special, paste link. If you've got lots of links and get in a mess, a quick way to jump from source to destination: Turn off in cell edit (options, edit, edit directly in cell - uncheck). When you double click on a linked (destination) cell, bang, takes you to the source data. It also works on closed workbooks, as long as the link is valid, otherwise it will tell you what the missing link is. This saves having to clear all those arrows that the Auditing tools leave. Not tried on 2007 yet.

Merlirin
Merlirin

The thing I found fascinating about this tip was that it was a replacement for a method of entering formulas which I was not familiar with previously. The Select cells/Ctrl-Enter method might work well for people who have trouble with double-clicking on the fill handle (I am a trainer and a number of my students find this challenging). It is always nice to discover an alternative method of doing something.

jbenton
jbenton

sorry, never experienced that, can't make it happen and can't think why it might and i don't think there's a button to add to a toolbar either to make reversion easier any circumstances under which it always happens?

dhays
dhays

That looked to be a good solution. Thanks for your suggestion. Do you have a solution to another minor problem that I experience with Excel? This one involves copying data from one sheet to another in the same workbook. When a set of cells are copied from one to another or when a set of cells are deleted, Excel automatically (sometimes) changes the "word wrap" formatting from off to on. I suppose I could just leave that information out of the sheet altogether, but not really wanting to do that, I have found no way of stopping Excel (2003) from doing it. It is easily corrected, just a nuisance to have to. It doesn't happen every time, seems to be random, but probably isn't, something might be triggering the format change. One of the "experts" on this set of websites had no answer either.

SheRex
SheRex

that this could be done across multiple noncontiguous cells (i.e. A4*G4 with the formula in K4)

Editor's Picks