Excel formula: Auto Fill (copying formulas)

YOUR OBJECTIVE

You are recording August’s sales in an Excel spreadsheet. There you want to calculate sales tax and gross sales for several product categories. But because exactly the same calculation must be made in several cells, you want to enter the formula just once and then copy it into other cells. Using Excel’s Auto Fill feature, you can easily copy a formula to other cells.In this learning module “ excel formula”, you will discover how to use the Auto Fill feature to copy formulas, and what you should keep in mind when doing so.

 

HOW DOES IT WORK?

You already entered an excel formula into one of your table’s cells. Now you must enter exactly the same excel formula into a number of cells below that one. Using the Auto Fill feature, you can easily transfer the excel formula from one cell to the other cells, thus saving yourself time and eliminating typing as a possible source of error.

First, enter your excel formula in the initial cell where the calculation is to be performed (cell C4 in the example below).

Now you can use the Auto Fill feature to copy the excelformula into the other cells. To do this, click on the cell that contains the excel formula you want to copy. Then move your mouse pointer to the small black square (the fill handle) in lower-right corner of the cell. The mouse pointer will then change to a plus sign.

 

Now depress the left mouse button, and while holding it down, drag the fill handle down until you arrive at the the last cell into which the excel formula is to be copied.

 

When you release the mouse button, you will discover that, using Auto Fill, you’ve copied the formula into the other cells.

 

HINT

Provided that (as in the current example) the cells directly to the left of your excel formula contain values (data or formulas), you can also copy your excel formula downward by double-clicking the fill handle. Excel will then automatically copy the formula as far down as there are cell contents on the left.

It is important to note that when you copy excel formulas in this fashion, the cell references in each additional cell are automatically adjusted to address the locations of its own data cells. Cell references that automatically adjust themselves in this way are called “relative references”. That is, when you use Auto Fill to copy a formula in the downward direction, all cell references in the formula are correspondingly shifted one row down: For example B4 becomes B5 in the next row, and then B6 in the following row, and so on…

 

If you copy a formula not downwards, but rather to the right; the row references are not adjusted, the column references are adjusted instead. That is: “C2″ in the formula becomes “D2″, and so on…

 

HINT

You can use the button (Formulas tab, Formula Auditing group) to switch from data view to formula view. If you do this, instead of displaying the results of the excel formulas in their cells, the formulas themselves are displayed. Clicking this button again deactivates the view.

HINT

Depending on what you are trying to accomplish, it may not always be desirable to automatically adjust cell references when using Auto Fill. This could be the case when a cell reference points to a cell that must in every instance be specifically referenced. In such cases, you will have to work with absolute references. You can find out more about this subject elsewhere, in the Relative and absolute references learning module.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen