One of the most important concepts to understand in Excel is cell referencing. Whether you’re creating simple calculations, analyzing data, or writing complex formulas, your ability to use cell references correctly can improve your Performance and accuracy.  Many Excel users learn how to write formulas but struggle to understand why some formulas work correctly when copied across rows or columns while others produce unexpected results. Most times, it could be that you used the wrong formula or that the type of cell reference you used was wrong.

Excel provides three types of cell references, which are:

• Relative References
• Absolute References
• Mixed References

What is a Cell Reference?

A cell reference is the address of a cell in a worksheet that combines a Column letter and a row number. E.g [A1, B3, C6]. It tells Excel exactly where to find the value needed for a formula. 

Let’s say you have an open spreadsheet, and you want to replicate what is in sheet 1 to sheet 2, or you want some part of the data to show in sheet 2. The common method will be to copy and paste. This may work fine for data that may never be changed or edited, but what if we decide to make changes in sheet 1? Let’s say we change many numbers or letters. Due to the fact that we copied and pasted, those changes made in Sheet 1 will not be reflected in Sheet 2. Which means you will have to start making the same changes in Sheet 2 or in as many other sheets that have been created using the same data. But there is a far better way to do that, and this is where Cell referencing comes in. 

The Better Approach:

Open a new sheet. From the cell you want the copied data to start from (for me it’s cell B2),  type  =  and press Enter.

Go to the Sheet that has your data. In the Formula bar, you should see something like this:

This is Excel’s way of acknowledging that it understands the sheet in which you want it to reference. And in my case, the name of my sheet is Sheet1 

Now place your cursor on the cell where you want to start copying from, and drag across to the cell where you want it to end. Notice in the Formula bar that B3:H13 has been added, signifying that Excel recognises the cell range I’m referencing.

Now press Enter. Your data has now been copied to your designated sheet, and you should have something like this:

To confirm if it works properly, go to the sheet where your original data is (in my case, Sheet 1). Make any changes you want to the selected range you copied, then confirm that the changes are reflected in your designated sheet.

Relative Referencing

This is a type of reference that changes automatically when a formula is copied or filled into another cell. 

Let’s say you have a table like this:

And you want to get the total for each item. The formula for this will be ‘Price * Qty’, so we start with the first item, Pen, which will give us 50 as the total using this formula =B4*C4.

Now, to get the total for the next item, which is Pencil, we could also multiply the Price by the Qty and do the same for the next few items. But what if we have hundreds of rows? How then do we make our work faster and better, rather than doing this one at a time? 

It’s simple. Drag your mouse to the fill handle and drag it below the remaining empty cells in the total column to automatically apply the formula for Pen to the rest of the items, or you could just double-tap it, and it will give you the same result. 

The fill handle is the small square located at the bottom-right corner of any cell you select:

Once you have done that, you should have a total for every item like this:

Absolute Referencing

Unlike Relative referencing, Absolute referencing does not change when a formula is copied or filled to another cell. 

Let’s say you have a table like this:

And you want to find the Tax for every product. The Sales Tax is 10%, which means the tax rate on every product is 10% of the price. And to get the Tax, we need to multiply each Product’s Price by the Sales Tax. 

Once we enter our formula for the first Product(Oreos) ‘=E4*B4’, we should have a correct value of ‘0.7’, but here comes the twist. If we drag down or double-tap using the fill handle, we’ll get an error for the rest of the Products. 

What is happening is that once we drag down with the fill handle, the formula starts moving across rows to get the value for the Tax. But what Excel doesn’t understand is that for the sales Tax, there are no other values besides the one in B4, which is 10%. Excel includes the cells below 10%, which are all empty.

Since 10% is the only value we want multiplied by every Product’s price, we have to tell Excel not to move across rows or columns, just to remain constant. And the way to do this is with Absolute referencing.

To apply Absolute referencing to a cell, clear the formula we just did and do it again, but this time, before you hit enter, while your cursor is on B4, press the F4 key on your keyboard. You should see a $ before and after the letter B. Now what this does is that the $ is used for locking a row or a column, and in Excel, rows are represented by numbers while columns are represented by letters. So the dollar sign before the Letter prevents the column from moving, while the dollar sign before the number prevents the row from moving. And since we don’t want either side to move. We’ll leave it like that and press Enter.

You can now drag or double-tap the fill handle to apply the formula to the rest of the cells for Tax.  You should now have the correct values rather than the error we had before.

Mixed Referencing

Mixed referencing is a combination of relative and absolute referencing because it allows either the row or the column to change while the other is fixed using the dollar sign ($), depending on where it is placed. 

Final Take

The next time you write a formula, take a Minute to think about how it will behave when dragged across your sheet. Don’t waste time manually rewriting formulas row by row. Instead, use relative references to let data flow, absolute references to lock down constants, and mixed references to handle big data tables.

With these three tools in your workflow, you have complete control over how data moves through your formulas.