This video will cover cell references in Excel and copying formulas in Excel. These topics go hand and hand, and understanding them will make your work with Excel formulas and functions easier and more productive.

First let’s talk about copying formulas. In my spreadsheet I have sales figures for my salespeople for January and February. I’ve already put in a simple sum formula to sum the January and February sales for the first salesperson on the list. Instead of typing 34 plus 87, I used the cell names, B5 plus C5 in the formula. These are called cell references. I want to sum the sales for the other people in my list, but I don’t want to type the formula into each of the cells. Because my formula uses cell references, I can copy the formula down to get the sales figures for each of my other sales people. I’m going to press ESC to get out of the editing mode of my formula. And now I can copy my formula down.

I could copy the formula using the keyboard shortcut Ctrl + C, or using the Copy button on the ribbon, and paste it into the next cell. But Excel has an even better feature for copying formulas down. This feature is called the Fill Handle.

Select the cell that contains the formula. Then, if you look in the bottom right corner of the box around the cell, you’ll see a little black box. It’s called the fill handle. Hover your mouse over the fill handle, and your cursor will turn into a black plus sign. Click and drag down, and Excel will the formula down.

Now I’m going to undo what I just did by pressing Ctrl + Z on my keyboard. That’s the keyboard shortcut to undo. Now here’s an even better trick. Instead of clicking and dragging on the fill handle, you can double click on the fill handle. When you double click on the fill handle, Excel will fill the formula down as far as you have continuous content in the column just to the left of the column with the formula.

Notice that after you fill down, at the bottom right corner of the area you filled, there is a little icon. If you click on this, it will give you extra fill options.

Now we’ll talk about cell references. If I double click on one of the cells that I filled the formula in, I can see the contents of the formula. You can see that the cell references in the formula changed. My original formula, in row 5 used cell references B5 plus C5. This formula uses the cell references B9 plus C9. That’s because when you copy a formula down, your cell references change relative to where you are in the spreadsheet. So what this formula really says is add the cell to the left of the current cell and the cell two cells to the left of the current cell. When the cell reference changes relative to where you are in the spreadsheet, it is called a relative cell reference.

I’ll press ESC to get out of the editing mode for this formula.

Now let’s look at another example. This time I have the amount of a sale, and I want to calculate the sales tax on that sale. In cell E16 I have entered the local sales tax amount. In cell B16 I’ll enter =A16*E16 to calculate the sales tax amount, and press Enter. Now look what happens when I fill the formula down. I get the correct answer in the first cell, but the cells that I copied the formula into all show zero. The dash is an alternate way to show that a cell’s value is zero.

If I double click on one of the cells that shows zero, I can see why. Looking at the cell references used in this formula, the formula uses the correct sales amount, but for the tax cell, it uses a cell below the actual tax rate. That is because these are relative cell references. They change relative to where I copy the formula in the workbook. I need to make my formula always use E16 as the sales tax cell reference. To do this, I need to make it an absolute cell reference in my first formula. An absolute cell reference in a formula or function means always use this cell, no matter where I copy it in my worksheet. This is sometimes called anchoring or locking a cell.

I’ll press ESC to get out of the editing mode for this formula, and I’ll go back to my original formula. I’ve double clicked on it so I can edit it. To make E16 into an absolute cell reference, you need to add dollar signs before the column letter and row number. The easiest way to do this is to put your cursor on or next to the cell reference, and then press F4 on your keyboard. Now you can see, Excel has added a dollar sign before E and before 16, the column letter and the row number. On a side note, if I keep pressing F4, Excel will toggle through all of the different options. You can have an absolute column, but relative row, or a relative column and an absolute row. In this case, I want to have both an absolute column and an absolute row. Now I’ll press Enter to finish my formula. If I double click on the fill handle to fill the formula down, you can see that now I get the correct answers. If I double click on one of the formulas to look at the cell references used, you can see that the sale amount changed relative to where I am in the worksheet, but the sales tax cell did not change.

Thank you for watching.