Copy hundreds of rows of Excel formulas in three steps

Excel provides a number of ways to copy formulas. You can use Excel’s Autofill feature or, as an alternative, select the cells you wish 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-hand 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.