How to Create Structured References in Excel?
Structured references start with Excel tables. When the tables are created in Excel, it automatically makes structured references for you.
Table of contents
Now take a look at the below image.
- Step 1: We had given a link to cell B3. Instead of showing the link as B2, it is showing as Table1[@Sales]. Here Table1 is the table’s name, and @Sales is the column we refer to. All the cells in this column are referred to by a Table name and followed by the column heading name.
- Step 2: Now, we will change the table name to Data_Table and the column heading to Amount.
- Step 3: Place a cursor inside the table > go to Table Design >Table Name to change the table name.
- Step 4: Mention the table name as Data_Table.
- Step 5: Now, give a reference to the B3 cell.
So, we have understood that structured reference has two parts: Table Name and Column Name.
Using structured references, you can make your formula dynamic. Unlike normal cell references, it allows the formula to be live in case of addition and deletion in the data range.
Let me apply the SUM formula for the normal range and Excel table.
SUM Formula for Normal Range.
SUM Formula for Excel Table.
Let me add a few lines to the data of both normal and Excel tables. We have added 2 line items to the data and now see the difference.
The structured reference in the Excel table shows the updated value. Still, the normal data range does not show the updated values unless you make some changes to the formula manually.
Now, look at one more example. We have a product name, quantity, and price information. Using this information, we need to arrive at the sales value.
The formula is Qty * Price to get the sales value. Let us apply this formula to the table.
Formula says [@QTY] * [@PRICE]. It is more understandable than the normal reference of B2 * C2. We do not get the table name if we are putting the formula inside the table.
Problems with Excel Structured References
While using structured references, we face some problems, which are enlisted below.
Structured references have their problems as well. We are all familiar with applying the excel formulaExcel FormulaThe term "basic excel formula" refers to the general functions used in Microsoft Excel to do simple calculations such as addition, average, and comparison. SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN Excel, MAX Excel, LEN Excel, TRIM Excel, IF Excel are the top ten excel formulas and functions. and copying or dragging it to the other remaining cells. It is not the same process as structured references. It works a bit differently.
Now, look at the below example. We have applied the SUM formula in excelSUM Formula In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges. for the normal range.
If we want to sum price and sales value, we will copy and paste or drag the current formula to the other two cells, giving us the SUM value of price and sale value.
Now, apply the same formula for the Excel table for the “Qty” column.
Now, we got the sum of the “Qty” column. Like the normal range, copy the current formula and paste it into the “Price” column to get the total price.
It is not showing the total of the “Price” column. Rather, it only shows the total of the “Qty” column. So, we cannot copy and paste this formula to the adjacent cell or any other cell to refer to the relative column or row.
Drag the Formula to Change the Reference
Now, we know its limitations. For example, we can no longer do the copy-paste job with structured references. So, then how do we overcome this limitation?
The solution is very simple. First, we need to drag the formula instead of copying it. Select the formula cell, use the fill handleFill HandleThe fill handle in Excel allows you to avoid copying and pasting each value into cells and instead use patterns to fill out the information. This tiny cross is a versatile tool in the Excel suite that can be used for data entry, data transformation, and many other applications., and drag it to the remaining two cells to change the column reference to “Price” and “Sale Value.”
Now, we have updated formulas to get the respective totals.
We have seen one problem with the structure references. We also found the solution, but we have one more issue: we cannot make the call as an absolute reference if we drag the formula to other cells.
Let us take a look at the below example now. We have a sales table with multiple entries, and we want to consolidate the data using the SUMIF function in excelSUMIF Function In ExcelThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. .
We will apply the SUMIF function to get the consolidated sales values for each product.
We have applied the formula for Jan month. Unfortunately, since it is a structured reference, we cannot copy and paste the formula to the remaining two columns. However, it will not change the reference to Feb and Mar, so that we will drag the formula.
We did not get any values in the “Feb” and “Mar” columns. What would be the problem? Closely look at the formula.
We have dragged the formula from Jan month. Since we dragged the formula, the SUMIF function’s first argument is the criteria range, Sales_Table [Product]. Therefore, it has changed to Sales_Table [Jan].
So, how do we deal with it? We need to make the first argument, i.e., the “Product” column as absolute and other columns as a relative reference. Unlike normal references, we do not have the luxury of using the F4 key to alter the referencing type.
The solution is to duplicate the referencing column, as shown in the image below.
Now, we can drag the formula to the other remaining two columns. Again, the criteria range will be constant, and other column references will change accordingly.
Pro Tip: To make the ROW an absolute referenceAbsolute ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing., we need to make a double ROW entry, but we need to put @ symbol before the ROW name.
How to Turn Off Structured Reference in Excel?
If you are not a fan of structured references, you can turn them off by following the below steps.
- Step 1: Go to FILE > Options.
- Step 2: Formulas > Uncheck Use table names in formulas.
Things to Remember
- We must double the column name to make the absolute reference in structured reference.
- We cannot copy the structured reference formula. Instead, we need to drag the formula.
- We cannot see which cell we are referring to in structured references.
- We can turn them off if you are not interested in structured references.
This article has been a guide to what Structured References are in Excel. Here we learn how to create structured references in Excel TablesExcel TablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range. with the examples and problems and how to switch them off. You can learn more from Excel from the following functions: –
- Mixed References in ExcelMixed References In ExcelA mixed reference is a type of cell reference that differs from absolute and relative cell reference. In the mixed cell reference, we only refer to the column of the cell or the row of the cell.
- Circular Reference in ExcelCircular Reference In ExcelA circular reference in Excel refers to the same cell we are working on. A circular reference is a type of pop-up or warning displayed by Excel that we are using a circular reference in our formula and the calculation might be incorrect.
- Relative References in ExcelRelative References In ExcelIn Excel, relative references are a type of cell reference that changes when the same formula is copied to different cells or worksheets. Let's say we have =B1+C1 in cell A1, and we copy this formula to cell B2 and it becomes C2+D2.
- Absolute Reference in ExcelAbsolute Reference In ExcelAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.