When Do You Need Absolute References in Excel?

There are many different functions in Excel that you can use. And due to the feature of insert, you can save a lot of time on changing the area in formulas. However, in some formula, you don’t need to change certain area. Thus, we have today’s article of absolute references in Excel formulas.

Excel contains many different functions, and some of which can always be used in your work. For example, the sum function, average function, count function and many others. Some of the functions can realize more advanced functionalities. But in the formula, you will sometimes input the area where you require doing some calculations with. When it comes to inserting, the area will automatically change according to different cells. This is very convenient for dealing with a bunch of information. However, in some conditions, you need to calculate the values in a certain area. Thus, this automatic mechanism will be a little annoying.

An Example of Automatic Mechanism

In this example, you need to calculate the complexion rate of the numbers.Calculating Complexion Rate

Thus, you will certain input the formula into the cell “C2”: =A2/B2.

And then you use the feature of inserting. But now, you will find that will the other cells show error.Error in Cells

And in this image, you can see that the cell automatically changes into B3 instead of remaining the B2. Therefore, the error will certain appear. And to solve this problem, you can use the absolute reference.

The Form of Absolute References

The form of absolute reference is very simple. You only need to use the mark of “$” before the reference cell. And in this example, you only need to add the “$” before the cell number. And the formula will be: =A2/B$2. Thus, when you insert formulas into the other cells in the column, the value of the number will be 100.

This is only a simple example. If you quote an area, and you can use the mark like this: $A$1:$C$5. Thus, the area will remain unchanged. In addition, if you want to use the values in one column with different rows, you can put the mark in front of the column: $A1. All those different forms can be used according to your need.

Take Care of Excel Files

Absolutely, in your Excel files, you will input some important information into those files. Thus the security of Excel files becomes rather important. Except for installing the antivirus program in your system, you can also prepare an Excel recovery tool at hand. It can be a very good assistant in case you meet with Excel file corruption.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.