Absolute References in Microsoft Excel What are they, what is it for and how to define them in my spreadsheets?

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 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 in this microsoft program they will find what they are relative references Y absolute references. In this case references in excel have become one of the most popular resources to be able to enter data in mathematical formulasso they are essential for do not use numeric values ​​but use the contents of a cell range or just a cell.

All this will allow you create formulas quickly without the need to enter data manually. 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 them within your spreadsheet. To do this, follow in detail everything that will be taught below.

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

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

All this has made it a fundamental 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 in your spreadsheet you need to put the $ sign before the number or letter you don’t want to change, that is, that value that is needed to be constant for all formulas. This must stay 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 It is done for the purpose of power. take advantagethat is, that it allows you to carry out some procedure in an easier and faster way, which helps you to minimize workgives you a correct and safe procedureamong many others benefits that can be achieved.

This is how here we are going to show you what 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 safe in the data that is enteredsince depending on it, 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 doubts about when each of these should be used. excel references, the truth of all this is that these are used depending on the desired resultso much relative reference as absolute are the address of a cell within a grid, it must be identified with column letter and row number. Therefore, when you introduce what is the cell reference in a formula it is necessary to enter the name of each cell.

When it is extended to a cell Range, the content of it is adapted and different results will be obtained. If this happens, we 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 putthen it is necessary apply what is the absolute reference. This type of referral will always give you the same resultregardless of where it is in the spreadsheet.

To all this it must be mentioned that Excel by default 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 of this a little better, and that way you can start to use these absolute references in your spreadsheets to save time and workthen here we are going to explain through an example how you can 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 the work officeyou can also see both the amount and the price of each item. In order to get the total of the first item it will be necessary to apply the following formula:

• Clearly when working with a software like Excel The objective is save as much work as possible in order to save time and maximize work. In this case you can 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 you need there fixed.
• But as you can see in this case none of the values ​​in the equation need to be fixedsince what is simply being sought is that each of the articles their price is multiplied 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 tax that must be paid for each of the products that appear on the list. If you want to know the amount of said tax for the first product of the list, a new column must be inserted where said calculation is carried out.

• 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 so you can save time. But, as it happened in the previous case, you should 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 product cells take as reference the tax that was specified 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 you can see in the image, the program has copied the formula using relative references, so to 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 mandatory 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 down 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 that contains the value of the percentage of the tax to pay, so it is necessary that this value appears 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 the 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.