Office hacks: Excel reference relative or absolute

Usually in the work, we often in Excel functions in some elements such as cells, rows, columns and other elements of the relative or absolute reference. Today we will explore the difference between the two, and when we should be relative or absolute references.

Relative OR absolute, recognize references

In Excel functions inevitably need to refer to the ranks, relative reference is the reference, the reference to the ranks will change with the location of different. For example, in the table below, suppose we enter the formula “= A1” at C1, when we pull down the formula, the formula will change to “= A2”, “= A3” in turn, the horizontal pull will will become “=B1”, “=C1” in turn. That is, whether the formula is pulled down or pulled across, the reference to the value of the ranks will be synchronized changes, which is the relative reference (Figure 1).


Figure 1 Relative reference

If you enter the formula “=$A1” in D1, then when you pull down the formula, the formula will change to “=$A2” and “=$A3” in order, and the horizontal pull will remain unchanged as “= $A1”, which means that the line marker in front of the letter with the “$” symbol, that is, the line is locked, so that the formula in the horizontal pull to maintain the reference to the original line number, which is the absolute reference (the example is for the line). If the formula is changed to “=A$1”, it means that the absolute reference to the column, if the formula becomes =$A$1 will be locked ranks, which is the so-called “absolute reference to the ranks” (Figure 2).


Figure 2 Absolute citation

Local adaptation, relative/absolute references have to pay attention to

Above we understand what is a relative reference and absolute references, then in practice, relative references and absolute references should be applied to what occasions?

1. Relative reference

As the relative reference is to synchronize the changes in the ranks, so there are no special requirements for the formula, the regular situation should use relative references. So that when the formula is pulled down or pulled across, the reference value of the formula will not be wrong. For example, we commonly used SUM summation function, in a similar “= SUM (A1: A13)” formula, where the (A1: A13) region for the relative reference, if the regional value changes, such as deletion or increase the number of lines, then the formula will be automatically referenced, so as to ensure that the reference is always correct (Figure 3). .


Figure 3 relative reference area

2. Absolute reference

In addition to relative references, there are many times in the actual work is required to absolute references to the ranks. So that the value of the reference will not appear in error. For example, we often use the Vlookup function to create a query statement, because the scope of the query is fixed, so you need to use the absolute reference to fix the query area.

For example, the following table is the use of Vlookup query A1:E5 data, if you use the relative reference statement, enter the formula “= VLOOKUP (A11,A1:E5,2,0)” in B11, the function will be pulled down when B13 will appear “# N / A ” prompt (Figure 4).


Figure 4 relative reference caused by the reference error

This error is caused by relative references, positioning to B13, you can see that the formula here is “= VLOOKUP (A13, A3:E7, 2, 0)”, that is, in the A3:E7 range to find the data, because the product A is actually in cell A2 (not in the A3:E7 region), so resulting in a citation error. Therefore, to achieve the correctness of the reference, we need to make an absolute reference to the A1:E5 region. positioned as above to B11, the formula changed to “= VLOOKUP (A11, $A$1: $E$5, 2, 0)” (in Excel, enter the formula, as long as you press the F4 key, you can simply switch between the relative and absolute references to the cell), where the use of $ fixed reference area, so that formula drop-down will no longer appear after the reference error (Figure 5).


Figure 5 absolute reference can avoid errors

The above example is a fixed area for absolute references, in practice we can also be used according to the actual needs of rows, columns, or cells for absolute references. For example, in the case of a certain profit margin of the product line, when we calculate the sum of product profits in the table below (sales * profit margin). As the profit margin is a fixed reference to B6 data, at this time it is necessary to enter the formula in E2 “= D2 * $ B $ 6” (that is, fixed B6 for absolute reference, if you use relative references, it will lead to inaccurate reference to the value of the profit margin), so that the drop-down formula will not appear data errors (Figure 6).


Figure 6 absolute reference to the cell

3. mixed references

The above relative reference and absolute reference, in practice will also encounter mixed references, that is, in a formula that requires the above two references. For example, the data in the table below, a product for different levels of membership have more than one price, if you want to quickly calculate the price of each member, then you need to use a mixture of references.

Because here the discount parameters are in row 2 (E2 and F2), the data in columns C and D are obtained from the data in column B * discount rate. So in this case, we need to fix the B data and fix the second row of data, so that the formula in the pull-down and pull-right, the base price is always quoted in the B data, while the right pull is always quoted in the second row of discount data. This is a typical mixed reference (Figure 7).


Figure 7 mixed reference example

Positioned to C2 enter the formula “= $ B2 * E $ 2” (fixed column B, fixed line 2), so that the formula will be pulled down and right to see the reference is a fixed row of data, so that you can quickly find the actual discount price of different members (Figure 8).


Figure 8 Mixed references

Leave a Comment