Excel is one of themost comprehensive Office suite software outthere. If you learn to use it correctly, you will discover that it offers endless benefits and that you can perform all kinds of tasks in spreadsheets .
However, learning to use the program is not as simple as it seems. In this particular post we want to talk in depth about what a cell reference is in Excel, what it is for and how to create one without major inconveniences.
With the help of cell references, the user can know exactly how to access the values stored in each cell of the spreadsheet . In this way, if you want to learn everything about the subject, keep reading.
What is a cell reference in Microsoft Excel and what is it for?
First of all, if you are new to using Excel, you should know that spreadsheets are divided into columns and rows . In the case of columns, the headings are represented by letters, while in the rows it is by numbers. In this sense, the references of a cell consist of two parts : the first that indicates the column in which a value is found and the second part that is its row number.
This means that they are one of the fundamental parts of spreadsheets, because by means of a reference it is possible to identify each of the cells, in order to access the value they contain. So that you can identify the reference of which is the active cell much easier, it is shown highlighted in a different color and apart from that, in the Name Box you can see the reference of the active cell.
Regarding its uses, you should know that you can use a cell reference in one or more formulas to refer to different aspects such as:
- Data from a cell within a spreadsheet.
- Data that is located in different areas of the spreadsheet.
- Data that is in cells, but from other worksheets within the same workbook.
Learn step by step how to create a cell reference in Excel
When it comes to how to create a cell reference in Excel, you should know that there are different mechanisms to achieve it. For example, you can create one on the same spreadsheet, on a different sheet or even through the Link Cells command .
Therefore, we will explain how to do it in each case:
On the same spreadsheet
- Click on the cell where you want to write the formula.
- In the formula bar write the equal sign “=” .
Follow any of the procedures below:
- To create a reference to one or more cells: select a cell or range of cells on the same sheet. You can drag the border of the selection to move or drag the corner of the border to enlarge it.
- To create a reference to a defined name: type the name. You can also do this by pressing F3, selecting the name in the Paste name box, and clicking OK .
Finally, follow any of the following steps:
- To create a reference in a single cell, tap Enter .
- To create a reference in an array formula, tap Ctrl + Shift + Enter .
Keep in mind that the reference can be a single cell or range of cells, while the array formula can be a formula that calculates a certain result or several of them .
Cell reference to another worksheet
You can create references to cells that are located in other worksheets, as long as the worksheet name followed by an exclamation point (!) Is appended to the beginning of the reference.
Once this is clarified, you can proceed to create a cell reference to another sheet as follows:
- Click in the cell where you want to write the formula.
- In the formula bar, type “=” along with the formula to use.
- Click on the label of the spreadsheet in which you are going to make the reference.
- Select the range of cells or the cell where you are going to make the reference and that’s it.
One thing to consider is that if the name of the other sheet has non-alphabetic characters, it is necessary that you write the name (or path) between single quotes, that is, ” .
Using the Link Cells command
Finally, there is an option that you can use and that is to create a cell reference using the Link Cells command . To do this, you must copy and paste a cell reference, to later use the Link Cells command to create the reference.
The use of this command is recommended to:
- Easily display important information in a more prominent position.
- Facilitate the creation of references between worksheets and workbooks. This command automatically pastes the correct syntax.
Once this is clarified, the procedure to follow to create references with the Link Cells command is:
- Tap on the cell that contains the data to link.
- Press Ctrl + C or go to Home / Clipboard / Copy .
- Then press Ctrl + V or Start / Clipboard / Paste .
- Click the Paste Options button and click Paste Link .