When working with a calculation software it is very important 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.
This is how in this Microsoft program they will find what they are relative references and absolute references. In this case references in Excel have become one of the most popular resources for entering data into mathematical formulas, so they are essential for not use numerical values but use the contents of a cell range or just a cell.
All this will allow you Quickly create formulas without the need to enter data manually. This is how we will explain a little more about what they are here. absolute references, what it is for and how you can start using it within your spreadsheet. To do this, follow in detail everything that you 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 does not change when copying to another cell, that is, it becomes a constant value that will remain at the mathematical equation that is being applied in spreadsheet. Therefore, when using this type of reference the address of the cell is going to keep despite copy into one cell and another.
All this has made it a fundamental tool especially for when working with a worksheet that contains a large amount of data, so to be entering numbers manually can get to be very tedious or hinder the process.
In order to apply this tool in your spreadsheet you need to put the $ sign before the number or letter you don't want changed, that is, that value that needs 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 using a tool in a calculation program such as Excel it is done for the purpose of power take advantageThat is to say, that it allows you to carry out some procedure in an easier and faster way, which helps you minimize work, provides you with a correct and safe procedure, among many others beneficial aspects that can be achieved.
This is how we will show you here the main advantages that you can get when using the 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 Enterokay 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 entered, since 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 relative reference in a spreadsheet?
Surely you are in doubt of when to use each of Excel references, the truth of all this is that these are used depending on the result you want to get, so much the 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 enter what is the cell reference in a formula you need to enter the name of each cell.
When it extends to a cell range, its content is adapted and different results will be obtained. If this happens you would be witnessing what 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 hold steadythen it is necessary apply what is the absolute reference. This type of reference will always offer you the same result, regardless of where you are in the spreadsheet.
To all this we must mention that Excel defaults all references as relative, so the real question here is When should an absolute reference be created? This should only be considered when planning 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 you can understand all this a little better, and that way you can start use these absolute references in your spreadsheets to save time and workSo here we are going to explain you 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 that must be purchased to carry the work office, you can also see both the quantity and the price of each item. 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 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 completely sure that none of those elements you need there fixed.
- But as you can see in this case none of the equation values need be fixed, since simply what you are looking for 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, the necessary relative references will be created 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 in the list. If you want to know what is the amount of said tax for the First product in the list, a new column must be inserted where said calculation is made.
- As you can see, it has already been obtained the first result, just as it happened in the previous column you can scroll down for that formula to apply 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 equation values are held 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 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 found.
- As you can see 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 so much cell B9 and B10 have no 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 containing cell the value of the tax, in this case cell B8. When cell E2 is copied down it will be necessary that cell B8 is kept 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 this value needs to appear in all the formulas that are going to copy down, in this way the expected result will be obtained:
- The reference of the cell in column D automatically adjust to the cell reference B8, for which you can get the tax of each of the products on the list without having to perform the procedure manually for each of them.