【Absolute References in Excel】What are they? + Advantages ▷ 2022

When working with a calculation software it is very important to be able to know why they serve and how each of the elements that are available there work, since each one of them will be in charge of offering you important and essential functions to carry out any type of work in them.

It’s like this microsoft program they will find what they are the Y absolute references. In this case references in excel have become one of the most popular resources to be able to enter data into mathematical formulasso they are essential for not use numeric values ​​but use the contents of a cell range or just a cell.

All this will allow you create quickly without the need to manually enter data. This is how here we are going to explain a little more about what they are absolute references, what it is for and how you can start using it in your spreadsheet. To do this, follow in detail everything that will be taught below.

What is an absolute reference and what is it for in an Excel spreadsheet?

absolute references They’re a reference in the formula that doesn’t change when copied to another cell, that is, it becomes a constant value that will be held in the mathematical equation that is being applied in spreadsheet. Therefore, when using this type of reference the cell address it will remain despite copy in one cell and another.

All this has made it an essential tool, especially when working with a worksheet containing a large amount of data, for what to be entering numbers manually can reach be very tedious or make the process difficult.

In order to apply this tool to your spreadsheet you need to put the $ sign before the number or letter you don’t want to change, that is, that value that needs to be constant for all formulas. This must remain as follows: $C$4, this will indicate that it is an absolute reference.

What are the main advantages of using absolute references in my Excel documents?

When a tool is used in a calculation program such as Excel is done for the purpose of power take advantagethat is, that it allows you to carry out a procedure in an easier and faster way, which helps you to minimize workprovides you with a correct and safe procedureamong many others benefits that can be achieved.

This is how here we are going to show you which are the main advantages that you can get when using absolute references in your worksheets:

• It allows you save time when working with formulas.
• Helps you avoid having to apply the same formula many times manually.
• It helps you to minimize the risk of making a mistake at the time of get into the data, since you only have to do it the first time. So it is recommended to be completely secure in the data enteredsince depending on it will be the result of the calculations that you want to obtain.

When is it more convenient to use an absolute or a relative reference in a spreadsheet?

Surely you are having the doubt of when each one of them should be used. the excel references, The truth of all this is that these are used depending on the result you want to getso much relative reference as absolute are the address of a cell within a grid, it must be identified with the letter of the column and the number of the row. Therefore, when you introduce what is the cell reference in a formula it is necessary to introduce the name of each cell.

When extended to a cell range, its content is adapted and different results will be obtained. If this happens, you would be witnessing what is a relative reference. Now if what you want is that the formula does not adapt and the value of the cell is always the same, that is, that stay fixedthen it is necessary apply what is the absolute reference. This type of reference will always offer you the same result, regardless of the place it is occupying within the spreadsheet.

To all this it should be mentioned that By default Excel creates all references as relative, so the real question here is When should an absolute reference be created? This should only be considered when considering copy a formula from one place to another and it is necessary that one of its references does not vary but that it remains fixed at all times, even after copy and paste the formula.

Learn how to define absolute references in your Excel documents

So that you can understand all this a little better, and that way you can start to use these absolute references in your spreadsheets to save time and workso here we are going to explain it through an example How can you define all this in your Excel documents.

To do this, follow each of the steps that we are going to teach you below:

• In this example we are going to show you a List of articles what to buy to take away the work officeyou can also see both the amount and the price of each item. In order to get the total of the first article it will be necessary to apply the following formula:

• Clearly when working with a software like Excel The objective is save as much labor as possible in order to save time and maximize labor. In this case, it is possible to avoid creating the same formula for each of the products on the list, this is achieved copying the formula down. However, before copying it you must be absolutely sure that none of those elements need it there fixed.
• But as you can see in this case none of the equation values ​​need to be fixedsince simply what is being sought is that each of the articles multiply their price by the quantity to know the total that will be spent for them. When the formula is copied down it will create the necessary relative references offering the result of each of the products.

• Now in this case in cell B8 you have the percentage of the tax that must be paid for each of the products that appears on the list. If you want to know the amount of said tax for the first product in the list, a new column must be inserted where said calculation is performed.

• As you can see, it has already been obtained the first resultjust as it happened in the previous column can be scrolled down for that formula to be applied for the other items on the list to save time. But, as in the previous case, you must ask yourself if you want one of the values ​​of the equation remain fixed, in this case the answer is “YES”.
• In this case it is necessary that all the cells of the products take as reference the tax that was entered in cell B8. In the following image the displacement of the formula without maintaining the value of the fixed tax, In this case, the following results will be returned.

• As can be seen in the image, the program has copied the formula using what are the relative references, so for cell E3 applied =D3 *B9, for cell E4 =D4*B10 was applied and so on. This is generated because both cell B9 and B10 have no valueso the program takes a zero value causing a zero as result.
• This means that in this case it is necessary to use what is the absolute reference for the cell that contains the value of the tax, in this case cell B8. When cell E2 is copied downwards it will be necessary that cell B8 remains fixed for each of the products. To achieve this it is necessary create the formula as follows:

• For this you must set cell $B$8 since this is the cell containing the value of the percentage of the tax to be paid, so it is necessary that said value appear in all the formulas that they will be copied downin this way the expected result will be obtained:

• The reference of the cell in column D automatically adjust to cell reference B8so you can get the tax of each of the products on the list without having to carry out the procedure manually for each of them.