If you are new to spreadsheet managementsurely you have wondered Why are they named by numbers and letters?. If you think they are for order the you are not wrong, but also play a very important role.
It is about locate the references. These, uniquely identify one or more cells. That is to say, they work addresses or links inside a document Excel.
This allows the formulas locate any box and get its valueto be able, in this way, to carry out the calculations that we tell you. If you want to know more about relative referencesWe recommend this post.
What is a relative reference and what is it used for in my Excel documents?
The relative references are those commands that bind to a given column and row location. But when you copy and paste them into another cellthey are adjusted to a different location.
These are always made up of combination of a letter and a number. For example “A1” refers to the cell located in the column “A” and the row “1”.
What are the main advantages of using relative references in my spreadsheets?
We can say that the main benefit of this type of reference occurs at the time of copy paste. Making it unnecessary to rewrite a formula. This works relative to cell locationtaking into account columns and rows alike.
For example, (see image) if we write the operation on cell B2 “=A2+B1” when A2 has a value of 2 and B1 of 3, the result, it is of course 5.
Nevertheless, when we copy this formula and paste it in cell F8, the result is different. This is because it takes the relative reference of the position of the other cells respect to where the formula is placed. The same, in the example is “=E8+F7”.
When is it more convenient to use a relative or an absolute reference in a document?
It is important to mention that relative references are the default way to copy and paste formulas between cells. This type of use is recommended in numerical tables.
It is also widely used if we need to do the same operation in different cells from a spreadsheet. As we saw in the example.
In the event that we need the result of a function entered in a cell not to be modified when copying it to another, it is recommended to use an absolute reference. To do this, it is enough to add a sign “$” before the column reference and one before the row reference. For example, the operation will be “=$A$1*2” which means that we will multiply by 2 the contents of cell A1.
Unlike the relativewhen let’s copy and paste the box with the operation in another location of the spreadsheet, the value will remain the same.
Learn how to define relative references in your Excel spreadsheets
To understand more efficiently how relative references work in a document of MS Excel, it is best to do it in a practical example.
Continue with these steps:
- Start the program.
- create a book in white.
- Place in the first columnrandom numbers.
- Do the same for the next three columns, respecting the number of rows used in the first.
- In the first available cell under the first initial columnput the formula “=SUM(A1:A5)” being A1 the beggining column list A Y TO 5 the end Of the same..
- Select and press “Ctrl+C” on cell that returns the result of the operation.
- Main click on the adjacent square and press “Ctrl+V”.
- repeat these steps successive times, until completing each column.
Facebook Comments
Disqus