{"id":83,"date":"2018-06-27T14:52:12","date_gmt":"2018-06-27T14:52:12","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=83"},"modified":"2024-03-30T00:38:44","modified_gmt":"2024-03-30T00:38:44","slug":"relative-absolute-and-mixed-cell-references","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/training\/relative-absolute-and-mixed-cell-references\/","title":{"rendered":"Relative, Absolute and Mixed Cell References in Excel"},"content":{"rendered":"\n
Relative-Absolute-and-Mixed-Cell-References-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

When you open an Excel spreadsheet, the first thing you see are cells. You can enter a value into a cell, or use a reference to a different cell by specifying the column letter and row number.<\/p>\n\n\n\n

Excel will automatically refresh every reference in a worksheet allowing formulas to dynamically update their content every time you change something or refresh (F9<\/strong> key) a worksheet.<\/p>\n\n\n\n

There are three types of cell references you can use in Excel: relative<\/strong>, absolute,<\/strong> and mixed<\/strong>.<\/p>\n\n\n\n

Relative cell references<\/h2>\n\n\n\n

The first type of reference is a relative reference. This is the most common type of reference in Excel.<\/p>\n\n\n\n

You can write it in the following way:<\/p>\n\n\n\n

=B3<\/strong><\/p>\n\n\n\n

This means that a cell refers to the value inside cell B3.<\/p>\n\n\n\n

Example 1<\/strong><\/p>\n\n\n\n

Let’s take a look at a few examples to illustrate how this reference works.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In column A, there are integer and text values. In column B<\/strong>, there are references to those cells. It’s not visible in this example, but when you use the keyboard shortcut to display formulas – Ctrl + `<\/strong> (the key that is located below the ESC key), you can see that they are indeed references and not values.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To get back to the standard view, use the same shortcut again.<\/p>\n\n\n\n

If you change values in cell A2<\/strong> or cell A3<\/strong>, Excel will automatically update cells B2<\/strong> and B3<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

What I just showed you are just simple references to single cells. Of course, you can create more complicated formulas.<\/p>\n\n\n\n

Example 2<\/strong><\/p>\n\n\n\n

In this example, cell A1<\/strong> contains the number 2, and cell B1 contains a formula: =A1+2. The value cell B1<\/strong> refers to is 2 (2 + 2 = 4).<\/p>\n\n\n\n

This is the value you can see in cell B1<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you change the value in cell A1<\/strong> to 5<\/strong>, then the value in cell B1<\/strong> automatically changes to 7<\/strong> (5 + 2 = 7).<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Example 3<\/strong><\/p>\n\n\n\n

Cell B1<\/strong> refers to cell A1<\/strong>, then after copying cell B1<\/strong> to cell D2,<\/strong> the cell starts to refer to cell C2.<\/strong> In other words, the cell reference has been moved by the same distance as the copied cell.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Example 4<\/strong><\/p>\n\n\n\n

Look at the following example. Here, you can find the names of employees of a company.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you want to merge the first name with the last name and place them in column D<\/strong>, you don\u2019t have to enter them manually – you can merge them by using the relative references.<\/p>\n\n\n\n

In this case, enter the formula =B2&” “&C2<\/strong> into cell D2<\/strong>. It will merge cell<\/a> B2,<\/strong>  space, and cell C2<\/strong>. Now, you can use AutoFill<\/a><\/strong> to fill the remaining cells.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

You can display formulas<\/a> instead of values by using the Ctrl<\/strong> + `<\/strong>  keyboard shortcut.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

As you can see, only the formula in cell D2<\/strong> refers to cells B2<\/strong> and C2.<\/strong> References in the next cells have been shifted accordingly.<\/p>\n\n\n\n

Absolute cell references<\/h2>\n\n\n\n

Absolute cell reference always points to the same cell, even if you copy the referring cells into a different position.<\/p>\n\n\n\n

Absolute references use two dollar signs<\/a> ($): one before the column letter and one before the row number.<\/p>\n\n\n\n

=$B$4<\/strong><\/p>\n\n\n\n

Example 5<\/strong><\/p>\n\n\n\n

I’m going to explain it using a simple example.<\/p>\n\n\n\n

Cell B1<\/strong> refers to the contents of cell A1<\/strong> (=$A$1<\/strong>). After you copy cell B1<\/strong> to B3<\/strong>, it will still refer to A1<\/strong>, instead of A3<\/strong> as it was with a relative reference.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Example 6<\/strong><\/p>\n\n\n\n

Let’s try another example. The following table shows the earnings of Tom Smith. We need to calculate how much tax he has to pay each month.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Look at the formula bar. It shows how much tax John needs to pay for January (=C3*D7<\/strong>). If you want to automatically fill the remaining months, you will notice that for February the reference doesn\u2019t point to cell D7<\/strong>, instead, it points to cell D8<\/strong>, and for March to D9.<\/strong><\/p>\n\n\n\n

To create an absolute reference,<\/strong> click cell D3,<\/strong> then in the formula bar click text D7.<\/strong> Now press the F4<\/strong> key and confirm the formula entry by pressing Enter.<\/strong> This will change a relative reference<\/strong> to an absolute reference<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Use AutoFill<\/strong> to calculate the taxes for February and March and sum up all the months. Press Ctrl + `<\/strong> to display formulas.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

As you can see in the example above, in all four cells, the first part of the formula is a relative cell reference and the second part is an absolute cell reference.<\/p>\n\n\n\n

With absolute references, instead of using one column with the same value (tax rate), or changing references for each cell manually, you can use a single cell with data and reference to it using the absolute reference.<\/p>\n\n\n\n

Mixed cell references<\/h2>\n\n\n\n

Mixed cell references are locked to a row or a column, but not to both at the same time. There are two different types of mixed references:<\/p>\n\n\n\n

Locked to a row:<\/p>\n\n\n\n

=A$1<\/strong><\/p>\n\n\n\n

Locked to a column:<\/p>\n\n\n\n

=$A1<\/strong><\/p>\n\n\n\n

If you want to create a mixed reference locked to a row – press the F4<\/strong> key on the formula bar<\/a> twice:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

For mixed reference locked on a column, press the F4<\/strong> key three times.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Example 7<\/strong><\/p>\n\n\n\n

The following example shows a practical application of both types of mixed cell references.<\/p>\n\n\n\n

In this table, there is a list of products and their prices. There are three different discounts for each product.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Enter the following formula into cell C3<\/strong>.<\/p>\n\n\n\n

=$B3-($B3*C$2)<\/code><\/pre>\n\n\n\n
The formula is locked on column B ($B3<\/strong>) and row 2 (C$2<\/strong>).<\/p>\n\n\n\n
If you try to autofill<\/a> the cell below (C4), Excel will take the next row for the Price ($B4<\/strong>) but won’t change the row for (C$2), so there is always a 10% discount for this column.<\/strong><\/p>\n\n\n\n
=$B4-($B4*C$2)<\/code><\/pre>\n\n\n\n
Now, let’s take a look at how it works for the cell to the right (D3). Because there is a lock on column B ($B3<\/strong>), it won’t change.<\/p>\n\n\n\n
The cell C$2<\/strong> will change to D$2<\/strong> because there is no lock on the column, only the row. In this case, we are in the same row, so the formula in D3 looks like this:<\/p>\n\n\n\n
=$B3-($B3*D$2)<\/code><\/pre>\n\n\n\n
After you fill the rest of the cell<\/a>, you will get the following result:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The F4 key<\/h2>\n\n\n\n
Pressing the F4 key in the formula bar cycles through all types of cell references.<\/p>\n\n\n\n
Reference Type<\/strong><\/td>Reference Example<\/strong><\/td><\/tr>
Relative<\/td>=A1<\/td><\/tr>
Absolute<\/td>=$A$1<\/td><\/tr>
Mixed, locked on row<\/td>=A$1<\/td><\/tr>
Mixed, locked on column<\/td>=$A1<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
You have two options: you can either insert a dollar sign<\/a> before the row number or column letter by hand, or you can use the F4 key.<\/p>\n\n\n\n
Excel 365 Update<\/h2>\n\n\n\n