Excel Cell References: Essential Techniques

Have you ever come across some Excel Cell References like this: =A1+A6, =$A$1+$A$6, or =A$1+5-$A$6? Arguments like this are called Cell References in Excel.

Excel Formulas/Functions and Cell Reference have a close relationship. When you use an Excel Formula/Function, you’ll use the Cell Reference as a reference for the formula/function.

Like A$1 in Excel, it is a mixed reference cell of relative and absolute references in Excel.

If you manually enter values into a formula like =5+6. This means you don’t use cell references.

But do you always enter formulas manually? Don’t you want to add or calculate between cells? In fact, in many cases, if you don’t use cell references correctly, Excel can return a #REF! error.

So… This is where Excel Cell References is very useful. How important is it for you to learn it? Please see my answer below.

What is Excel Cell References?

Excel Cell Reference is the cell address in Excel that is used in formulas/functions. The purpose of using cell references in Excel is to tell Excel that a formula/function uses the data in the referenced cell.

Using the correct cell references can minimize reference errors (#REF!) when you copy and paste formulas (SUM, AVERAGE, etc) with cell references.

Because everyone has different goals, Excel provides 2 types of cell references: Relative and Absolute cell reference Excel. The combination of the two is also called a mixed cell reference.

You can combine these types of references (relative, absolute, and mixed) or not to get maximum results.

What is it like? Let’s look at the example below.

As Excel practice material this time, please download the following Excel file:

Relative Cell References in Excel

Relative Cell References in Excel are a type of reference that can adjust cell references to changes in the position of a formula in a cell.

In other words, formulas/functions that use relative references will change if you copy (copy-paste) the formula to another cell or use Excel’s AutoFill feature.

Note: If you use Cut and Paste, Excel will not adjust the formula.

#1 Relative Cell References in Rows

To understand better, please open the Relative Sheet in the previous Excel file. I’ll add up the data with the following criteria:

1. Column E = Data 1 + Data 2
2. Column F = Data 2 + Data 3
3. Row 7 is the sum of all data in each column.

I assume you already know how to enter formulas/functions in Excel. Next… Here are the steps to use relative references in Excel:

1. Type the formula =B2+C2 in Cell E2 to calculate the value in column E. Then press Enter.
2. Perform Excel AutoFill down to calculate the values in Cells E3, E4, E5, and E6. Here are the results:

You can see in the picture or the previous Excel file, the formula in each cell changes to E2: =B2+C2, E3: =B3+C3, E4: =B4+C4, E5: =B5+C5, and E6: =B6+ C6.

Formula changes only occur in row numbers (Row). Look at the formula which I marked in yellow.

Cell Reference changes will be adjusted to how far you AutoFill or Copy-Paste the Formula. For example, try copying Cell E2 to Cell E24 (Remember, Cell E2 contains the formula =B2+C2).

Automatically, the formula in Cell E24 will change to =B24+C24. This change in Relative Cell Reference will make your work easier. So, you don’t need to repeat the formula in other cells.

Important! There are many Excel paste options such as Formulas, Keep Source Formatting, Linked Picture, etc. I hope you have understood the Paste Special feature.

#2 Relative Cell References in Columns

So what if you want to AutoFill or Copy-Paste to the right/left (by column)?

The concept is the same. However, what has changed is the column name such as C2 becomes D2

For example, please do AutoFill/copy-paste range E2:E6 to range F2:F6. Automatically, Excel will change the reference cell as shown in the following image:

It can be seen that the cell reference changes only occur in the column names of Cell F2: =C2+D2, F3: =C3+D3, F4: =C4+D4, F5: =C5+D5, and F6: =C6+D6.

Important! There are many Excel paste options such as Formulas, Keep Source Formatting, Linked Picture, etc. I hope you have understood the Paste Special feature.

Conclusion: With Relative Cell Reference, Excel will adjust the function/formula when you copy or AutoFill Cell.

Mixed Cell References in Excel

Mixed Cell Reference in Excel is used only to lock columns or rows, while other cell references are relative.

Excel will lock the column or row according to where you put the Absolute value (dollar symbols $). Is it before the column name or before the row number?

You can simply lock a column by inserting a dollar symbol ($) before the column name (such as $A1) and inserting a dollar symbol ($) before the row number to lock the row (such as A$1).

Note: The characteristics of Mixed Cell References in Excel are that the Cell Reference only has the 1 dollar ($) symbol before the column name or row number. This is often referred to as Semi Absolute Cell Reference Excel.

The purpose of locking a column or row is that Excel will not change the absolute cell reference if you copy-paste or AutoFill cell to another cell.

So how do you use it? Let’s study them one by one…

1. Mixed Cell Reference for Rows

Let’s go into an example. Please open the Mixed Sheet. I will calculate the sum of Data 1 and 2 with the conditions: Data 1 has a weight of 40% and Data 2 weighs 60%.

See Column B (Data 1). The data is neatly arranged vertically, while the Weight Data Exam 1 is only found in 1 cell, namely C8. Likewise with the data in column C (Data 2) with the weights in cell C9.

Because the Weight Exam for each value is only found in 1 cell, you have to use Mixed Cell References so that the formula can be copied (copy-paste) or AutoFill. Please follow the following steps:

1. Type the following formula in cell D2: =B2*. FYI, the asterisk symbol * is used for the multiplication formula.
2. Click cell C8. Then press the F4 key on the keyboard 2x until the dollar symbol $ is inserted before the row number. So the formula will be like this: =B2*C$8.
   If you can’t use the F4 button, please type the dollar symbol $ manually before the row number.
3. Type the plus sign + and click cell C2. Then type an asterisk * like stage 1.
4. Click cell C9 and put the dollar symbol $ before the row number like in step 2. Then press Enter.
5. Please copy or AutoFill this formula to calculate the values in cells D3 to D6. Here are the results:

Remember this! The formula you enter in Cell D2 looks like this: =B2*C$8+C2*C$9. Next, look at the formulas in Cells D3 to D6.

What hasn’t changed? Yes, you’re right! Excel does not change the Row reference given by the dollar symbol $.

2. Mixed Cell Reference for Columns

Previously, we used Absolute Value to lock row references. By inserting the dollar symbol $ before the row numbers C$9 and C$10, Excel will lock only the row references.

Try copying cell D2 to Cell E2. Automatically the formula will change to =C2*D$8+D2*D$9. Why is that? Because the column names aren’t locked using Absolute values.

For this case, please see the second table on the same sheet (Mixed). The data is the same, only I transposed the data position from Vertical to Horizontal.

Next, please change the position of the Absolute reference before the column name. So the formula changes to =G2*$M5+G3*$N5.

Then please Copy/Autofill cell G3 to cells H3, I3, J3, and K3. You are sure to find the right result.

The column names in Cells M5 and N5 in the formula will not change. like the following picture:

Absolute Cell Reference Excel

Absolute Cell Reference Excel is a type of fixed (locked) cell reference indicated by the dollar symbol ($) before the column name and before the row number (such as $A$1).

Then what’s the point? Let’s learn one by one…

If you want to lock column and row references at once, please insert the dollar symbol $ before the column name and row number (Example $A$1)

This way, in whatever cell you copy and paste the formula, the cell references (columns and rows) will not change.

So…

Please see the Absolute Sheet in the Excel file. I want each value in columns B and C to be multiplied by 2. I put the number 2 in Cell B8.

By using Absolute Cell Reference, you only need to enter the formula in 1 cell. The rest is just copy-paste or AutoFill.

For example? Try entering the formula =B2*$B$8 into cell D2. Then please AutoFill to other cells. The result will be the same as the following image:

Is that true? Absolute Cell Reference locks cell B8. So whichever cell you copy this formula to, Excel will still use Cell B8 as the formula benchmark.

Congratulations! You have passed this guided exercise. So what’s next?

Please use the following Excel Cell Reference Guide Shortcuts to learn all about Cell References in Excel. Or visit the Excel Guide Center at M Jurnal to learn all about Excel.