Copy hundreds of rows of Excel formulas in three steps
- Date: July 28th, 2009
- Author: Mary Ann Richardson
- Category: Microsoft Excel, Tips
- Tags: Formula, Cell, Microsoft Excel, Microsoft Office, Office Suites, Software, Mary Ann Richardson
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:
- Click in C2 and enter =B2*300%
- Press [Ctrl][Enter].
- 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.
