3 Common Types of References in Excel

Learn about 3 Common Types of References in Excel, Most Frequently Used

Excel is one of the most useful tools in Microsoft. They are widely used in many fields, including education and work. References in Excel are a common function that helps you optimize the calculation process with quite lengthy formulas. So what types of references are there? Let’s find out the definition and how to use these references in detail in the following article on Buffcom.net.

Relative References in Excel

Definition

It can be said that all cell references are relative references. Simply put, when you copy a formula and paste it into another cell, the relative reference will change.

Depending on the position of the row and column, the value of the reference will change accordingly. For example, if you copy the formula A1B1 from column 3 to column 4, the formula will change to A3B4.

This is an extremely useful tool when you need to repeat formulas with multiple rows and columns, without having to calculate separately in each cell.

How to Use Relative References

References in Excel in relative form are quite easy to use. You can refer to the following example:

We have an Excel table about the sale price of furniture at Company A. The task is to calculate the exact price of each item.

Step 1: Enter the calculation formula into cell E2. Press Enter to display the result.

Step 2: Reference the formula to all the rows below. Click on cell E2, drag the mouse pointer to the bottom right corner and a square box will appear. You drag the mouse down to all the rows that need to copy the formula. Then release the mouse.

To check the details of each formula, you can double-click on the cell. The relative reference will vary depending on the value of the row.

Absolute Reference in Excel

Definition

If the reference changes when copied to different cells and rows, the absolute reference will still retain the value no matter which cell you copy to.

Absolute reference in Excel helps to fix rows and columns in the formula. It is usually fixed by the $ sign, which can be placed in front of the column reference, row reference, or both.

How to Use Absolute Reference

Using absolute reference in Excel is a bit more complicated because you need to add the $ format before the cell or row.

For example, calculating the order cost with a fixed tax rate of 9%. You need to enter the formula into cell D2 and turn $F$1 into an absolute reference.

Step 1: Enter the formula you want to calculate in cell D2. You enter (D2*C2) *$F$1.

Step 2: Press Enter to display the calculation result. You can also copy the formula and Fill handle to the rows below.

When you release the mouse, you will receive the result as shown in the table above. To check accurately, you can double-click on each cell to view the formula. Surely the other values will change with each row, but the absolute reference remains the same.

Mixed Reference in Excel

There are three types of references commonly used in Excel: relative reference, absolute reference, and mixed reference.

Definition

A mixed reference means that a portion of the reference will be fixed, while the rest will be relative. This is a combination of the two reference types in the same value.

This reference only has one $ sign to fix it, not two $ signs like the absolute reference.

Using Mixed Reference

To create a mixed reference in Excel, you can refer to the following example:

Step 1: Enter the information you need to calculate into the Excel cell. Enter the formula into the cell when you need to calculate the value of the order. For example, =B16*C15.

Step 2: To select a fixed row, add the $ character before B, then copy the formula to other cells. Press Enter to get the result as shown below:

Step 3: To select a fixed column, add the $ character before the row to be replaced. For example, =(C$17*D16)

Therefore, when using a mixed reference, the returned value will have a significant difference.

Conclusion on Excel References

The above article has shared with you three types of references in Excel that we often encounter. These are extremely useful tools that help you save time when calculating multiple information and formulas on an Excel spreadsheet. Stay tuned for more useful information on Buffcom.net!

In addition, Buffcom.net also provides cheap office software licenses. If you are interested, please contact Buffcom.net!