- Structured references in Excel only work on tables formatted as such in the program, not on data ranges.
- Using structured references makes formulas more human-readable and dynamic.
- When formatting a table in Excel, change its name to something meaningful. Otherwise, Excel will name it Table[number], which can become confusing.
- Structured references work both inside and outside tables, can be used inside other functions, and will automatically update if headers receive new names.
Working in Excel typically revolves around finding connections between various data points. However, when inserting complicated formulas, repeatedly using relative and absolute explicit cell references (like “B7” or its variations) can only get you so far before the formula bar becomes an unreadable mess.
Structured references in Excel allow you to streamline that work by assigning names to tables and their headers. Those names can then be used as implicit cell references so Excel can automatically fetch the structured data and calculate it.
Here are some of the most common ways to use structured references in Excel.
1. Calculating Inside Tables
Since structured references only work on tables, the best way to utilize them is inside those same tables.
For example, we’ll create a simple table from B2 to F8 with sales data for a store. Note that we named the table “Sales” (see the “Table Name” in the top-left).
Let’s calculate the total for each sale:
Step 1: Click on F2 (but not on the drop-down icon). Go to “Home” then to “Insert,” and select “Insert Table Columns to the Right.” This will automatically add a new column to the table.
Step 2: Name the column G header “Total.”
Step 3: In G3, insert =[@PricePerUnit]*[@Quantity] and hit Enter. Format the cell output as needed.
The “[@PricePerUnit]” and “[@Quantity]” are references to the corresponding fields in those columns. The “@” argument before the column names means each result cell will use the references from the same table row.
To translate, the formula =[@PricePerUnit][@Quantity] in G3 is essentially the same as writing =$C3$D3 .
2. Fetching a Range Outside of the Table
When you want to use a structured reference in a cell outside the table, you need to preface the reference with TableName. In our previous example, using “Sales[Total]” will fetch the entire range under the header “Total” of the table “Sales.” This means you’ll get multiple values in an array that you can manipulate.
Here’s how this looks inside Excel in cell I3, provided you leave enough room for the range to spill down.
3. Summing and Partially Summing a Column
To quickly sum an entire column, you can use the “Total Row” checkmark in the “Table Design” options (under “Table Style Options”). Here’s an example of getting the totals for the “Quantity” and “Total” columns.
While the “Total” row by itself can’t be moved and will be placed at the end of the table (allowing for insertions), you can duplicate its result elsewhere:
- To get the sum of all the rows in the “Total” column, use =SUM(Sales[Total]) .
- If you want to get only the sum of the visible columns, such as after filtering the table, use =SUBTOTAL(109,Sales[Total]) . This formula is what the “Total Row” option in Table Format actually does in its row.
You can also get a partial sum based on a specific variable found inside the table without formatting it. For example:
- To get the sum of all the sales by Mike, you can use =SUMIF(Sales[Seller],”Mike”,Sales[Total]) . In the formula, “Mike” is a string that was manually input.
- To get the sum of all products with the ID “41230”, use the following =SUMIF(Sales[ProductID],41230,Sales[Total]) . Note that since the ProductID column has a “General” format, you can input the number directly.
4. Data Validation from a Table via INDIRECT
Suppose you have the previously used Sales table. You can create custom Data Validation options to make searching through the table easier. Let’s make a smaller table that will allow you to select between ProductIDs, Dates, or Sellers, and then choose any single item from those subsets to display the subtotal.
Step 1: In cell B13, create Data Validation (Data tab > Data tools > Data Validation).
Step 2: In the popup, choose “List” from the “Allow” options, then insert the values of the columns manually in the “Source” box, separated by commas. In this case, we inserted “ProductID, Seller, Date.”
Step 3: In cell C13, create another Data Validation. Again, choose “List.” For “Source,” insert the following formula: =INDIRECT(“Sales["&B13&”]") .
Step 4: In cell D13, use the following formula: =SUMIF(INDIRECT(“Sales["&B13&”]"),B14,Sales[Total]) .
You can now choose the options from the two data validation lists, and the subtotal will be displayed in D13.
Was this helpful?
- Corrupted Excel files, faulty add-ins, and outdated printer drivers are some of the most common causes of this issue.
- Try printing another Excel file to ensure the issue is not limited to a specific spreadsheet.
- You can try running the Microsoft Office repair tool if nothing else works.
Fix 1: Save Your Excel File in XPS Format and Try Again
If Excel can’t respond to print requests, save your file in the XPS format and try again. Several users on Microsoft Community post reported fixing the issue with this simple workaround. So, if you’re in a rush, try this method.
Step 1: Open the Excel file you wish to print and click the File menu at the top left corner.
Step 2: Navigate to the Print tab and use the drop-down menu under Printer to select Microsoft XPS Document Writer . Then, click the Print button.
Step 3: When the Save Print Output As dialog appears, save your Excel file in the XPS format. It should print without issues.
Fix 2: Open Excel in Safe Mode
You can try printing an Excel file in safe mode to see if one of the third-party add-ins is causing the problem. For that, press the Windows key + R to open the Run dialog. Type excel -safe in the box and press Enter .
Check to see if Excel prints your file in safe mode. If it does, one of the third-party add-ins is to blame. You can disable all add-ins and re-enable them individually to isolate the culprit.
Step 1: Open Microsoft Excel and go to File > Options .
Step 2: In the Excel Options window, switch to the Add-ins tab from the left column. Select COM Add-ins in the Manage drop-down menu and click the Go button.
Step 3: Uncheck all the Add-ins and click OK .
After this, restart Excel and enable your add-ins one at a time. Print a test page after enabling each add-in to identify the one causing the issue.
Fix 3: Update Printer Driver
Issues with your printer driver can affect Excel’s ability to print spreadsheets and lead to problems. To avoid this, you should ensure that your printer driver is up to date and functioning properly.
Step 1: Right-click on the Start icon and select Device Manager from the menu that appears.
Step 2: Double-click on Print queues to expand it. Right-click on your printer and select Update driver .
Follow the on-screen prompts to finish updating the printer drivers. After that, try printing your file again.
Fix 4: Remove and Reinstall Your Printer
If Microsoft Excel still can’t print, try removing your printer and re-adding it on Windows. Here’s how to do it.
Step 1: Press the Windows key + I to open the Settings app.
Step 2: Select Bluetooth & devices from the left sidebar and go to Printers & scanners .
Step 3: Select your printer from the list.
Step 4: Click the Remove button at the top to delete your printer.
Step 5: After that, return to the Printers & scanners menu and click on Add device . Then, follow the on-screen prompts to add your printer again.
Fix 5: Repair Microsoft Office
Microsoft Office offers a handy repair tool for any issues with Office apps. If nothing else works, consider repairing Microsoft Office by following the steps below.
Step 1: Right-click on the Start icon and select Installed apps from the list.
Step 2: Scroll down to locate the Microsoft Office product on the list. Click the three-dot menu icon next to it and select Modify .
Step 3: Select Online Repair and hit Repair .
