Relative references in Microsoft Excel What are they, what are they for and how do I define them in my spreadsheets?

If you are new to spreadsheet managementsurely you have wondered why the rows and columns are named by numbers and letters. If you think they are for sort cellsyou are not wrong, but also they play a very important role.

It’s about the locate the references. These, uniquely identify one or more cells. That is, they work as addresses or links inside a document Excel.

This allows the formulas locate any box and get its valuein order to be able to carry out the calculations that we indicate. 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 copying and pasting them into another cellthey snap 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 write a formula again. This works relative to cell locationconsidering columns and rows alike.

For example, (observe image) if we write the operation on cell B2 “=A2+B1” when A2 has a value of 2 and B1 has a value 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 relative 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 of a spreadsheet. As we saw in the example.

In the event that we need the result of a function entered in a cell to not be modified when copying it to another, it is recommended to use an absolute reference. To do this, simply 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 on 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 Microsoft 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 with the next three columns, respecting the number of rows used in the first.
• In the first available cell under the first starting columnput the formula “=SUM(A1:A5)” being A1 the beggining from column list A Y TO 5 the end Of the same..
• Select and press “Ctrl+C” on cell that offers the result of the operation.
• Main click on the adjacent box and press “Ctrl+V”.
• Repeat these steps successive times, until completing each column.