Copying formula in Excel

Copy a formula in Excel

While working with statistical calculations in Microsoft Excel, the user uses formulas to calculate the mathematical operations. Implementing these formulas manually for each data is a time-consuming process. Hence Microsoft Excel provides a great feature to copy the formulas for the entire column, down a column, multiple cells etc., without changing the references. In this tutorial, let’s look at how to copy a formula in a step-by-step process.

Copying a Formula Down a column

STEP 1: Enter the data in a spreadsheet.

STEP 2: Select a cell where the formula wants to display. Enter the formula and the result will be displayed in the particular cell (C2).

STEP 3: Select the cell with formula and drag the ‘+’ sign towards C6 to display the results for balance data present in the sheet. The result will display automatically as shown below.

Another method to display the result is to, rather than dragging the ‘+’ sign, double click the ‘+’ sigh present in the bottom right corner. It automatically displays the result for the remaining data. This term is called Fill handle.

Copying a Formula across the row

STEP 1: Enter the data and type the formula in the cell, to display the result in the first cell.

STEP 2: Select the cell A4, drag the ‘+’ sign across the cell range from A4 TO F4. The result will be displayed across the columns.

Absolute Reference

Here the value of the absolute reference never changes. It is used to fix a reference to a cell or range of cells. While copying the formula, the value of the absolute reference won’t change.

STEP 1: Enter the data in the spreadsheet.

Here the data in cell range from A1 to A4 are multiplied with value present in D5. Type the formula in B1 as = A1*$D$1 and press Enter.

STEP 2: Select the cell B1, click on the lower right corner of the cell and drag it to B4. The result will be displayed as per the formulas.

Moving a Formulas to new Location

To move formulas from one location to another location following steps are used.

STEP 1: Enter the data in the cell. Select the cell and type the formula

STEP 2: Select the cell A3. A four-sided arrow will appear. Hold the arrow mark and move the formula to new position such as C3.

The formula is moved to new location C3.

Exactly copying the formula

Exact copy of a formula is made without modifying the cell references.

STEP 1: Enter the data in the spreadsheet. Select the cell and type the formula. Press Enter. The result will display in the required cell.

STEP 2: Select the formula in the formula bar. Click on the shortcut key Ctrl+C. Choose another cell and paste the data by pressing Ctrl+V. The cell contains the exact copy of the formula.

STEP 3: Select a new cell, and click Ctrl+V. The exact copy of the formula is copied to new location.

Summary

From the above tutorial, the various methods to copy the formula are discussed.