In the case of financial and calculation functions, there is a very robust and efficient tool, we refer to Microsoft Excel. In this application, the handling of tables has been one of its most appreciated characteristics for its versatility in offering practical solutions to users.
Today we will talk in this article about one of those characteristics in the handling of tables, which facilitates and takes advantage of the computing potential and we refer to the structured references of Microsoft Excel.
What are structured referrals?
When we make our formulas in Excel we always refer to cells or groups of cells also called Ranges; to which we can give any type of format, even if they belong to a fixed table or a dynamic table; we can refer to that data through a way called structured reference.
Structured references are preferred by users in general, since it is based on an intuitive aspect in the design of the formulas, rather than simply specifying a cell or range of cells to obtain data, which can change if you want to add more fields to the sheet.
If we want to use structured references in Microsoft Excel, we must start by assigning a name to our table from where we are going to take the data. We do that by going to the box “table name” found in the Design menu “within the tab section of Table Tools.
For example, if we wanted to obtain the sum of the column with the name “Column 1” we could use the SUM function in the traditional way and it would be as follows: SUM (B32: B11)
But if we use the Microsoft Excel structured references tool we could do it as can be seen in the following image; Notice the syntax that is generated in the formula:
Note that when we specify the name of the table that we have just created, Microsoft Excel already detects its existence and displays it in the formula option. In this case, we accept Excel’s suggestion and continue
When we want to specify the name of a column in a formula, we add a bracket “[“, which will allow us to visualize the data that make up the table. You can see how immediately we introduce a bracket in the formula, the possible options that appear in the columns of the table are displayed.
In this way, we choose the column that we want to include in our formula using the autocomplete tool and close the bracket to obtain the desired result.
One of the main advantages of using Microsoft Excel’s structured reference tools is that even if we add new records or columns to our table, the formulas will still work.
Work with structured references
When we decide to work on our spreadsheet with the structured references tool, we must take into account some important details for its correct operation.
- Use the AutoComplete tool, it is very useful for writing structured references and guarantees the correct syntax of the formula and minimizes the possible errors when contemplating multiple data in the table. If you have questions about how it is done, you can see it on our blog.
- Turn off the column headings for the table. This is done in the Design tab of the table styles option by deactivating the Header Rows check box, this will not affect the structured references that the headers are used and can continue to be used in the formulas.
- When renaming a table or column, Excel will also rename all structured references and formulas that have referenced those names so that only the result cell will be translated.
- When we copy or move a formula , all the structured references move with the formula without having to worry about any alteration in the results.
There are many applications not only for this tool, but also for many others at the same time that there are many guides and tutorials that you can see and expand your knowledge of these tools.
We hope that this article has clarified your doubts and helped you to use Microsoft Excel structured references correctly. Tell us about your experiences with the use of this tool and leave us your comment.