Absolute references in microsoft excel what are they, what are they for and how to define them in my spreadsheets?

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

Thus in this program Microsoft are going to find what are the references relative and absolute references . In this case, the references in Excel have become one of the most popular resources to be able to enter data in mathematical formulas, so they are essential not to use numerical values ​​but to use the content of a cell range or just one cell.

All of this will allow you to quickly create formulas without the need to enter data manually. Thus here we’ll explain a little more about what are the absolute references, what it does and how you can start them to use within 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 are a reference in the formula that does not change when it is copied to another cell, that is, it becomes a constant value that will be kept in the mathematical equation that is being applied in the worksheet . Therefore, when this type of reference is used , the cell address will be maintained despite copying from one cell to another .

All this has made it a fundamental tool especially when working with a worksheet that contains a large amount of data, so entering numbers manually can become very tedious or make the process difficult .

In order to apply this tool in your spreadsheet, you need to place the $ sign before the number or letter that you do not want to change, that is, the value that needs to be constant for all formulas. This should be 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 in order to be able to take advantage of it, that is, that it allows you to perform a procedure in an easier and faster way, which helps you minimize work, provides you with a correct and safe procedure, among many other beneficial aspects 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 to save time when working with formulas.
  • It helps you avoid having to apply the same formula multiple times manually.
  • It helps you minimize the risk of making a mistake when entering the data, since you only have to do it the first time. Therefore, it is recommended to be completely sure in the data that is entered, since depending on it, it will be the result of the calculations you want to obtain.

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

Surely you are having the doubt of when each of the Excel references should be used , the truth of all this is that they are used depending on the result that you want to obtain, both the relative and absolute reference are the address that a cell has inside of a grid, it must be identified by the letter of the column and the number of the row . Therefore, when entering what the cell reference is in a formula it is necessary to enter the name of each cell .

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

To all this, it should be mentioned that Excel creates all references as relative by default, so the real question here is when should an absolute reference be created? This should be considered only when you plan to 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 copying and pasting 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 using these absolute references in your spreadsheets to save time and work, so 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 items that must be purchased to take to the work office, you can also see both the quantity and the price of each of the items . In order to get the total for the first article, it will be necessary to apply the following formula:

  • Clearly when working with software like Excel the goal is to 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 by copying the formula downwards . However, before copying it you must be completely sure that none of those elements need it fixed there .
  • But as you can see in this case , none of the values ​​of the equation need to be fixed, since what you are looking for is simply that each of the items is multiplied its price by the quantity to know the total that is will spend for them . When the formula is copied downwards, the necessary relative references will be created offering the result of each of the products .

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

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

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

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

  • The cell reference in column D is automatically adjusted to the cell reference B8, so you can obtain the tax for each of the products in the list without having to perform the procedure manually for each of them. .

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button