A 2023 workplace survey of 1,247 finance and ops professionals found that 81% store relational data across 3+ disconnected sheets—and only 12% know how to link them without copy-paste or VLOOKUP errors.
Quick Answer
You can use Excel like a database—but only if you stop treating ranges as static lists and start using structured references, relationships, and native query tools. Use Tables (Ctrl+T), Data Model + Power Pivot for multi-table joins, and FILTER/XLOOKUP instead of volatile functions. Skip pivot tables for raw querying—use Get & Transform (Power Query) to load, clean, and relate data once.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Excel Tables + Structured References | Select data → Ctrl+T → Name table (e.g., tblOrders) → Use [@CustomerID] or tblCustomers[Email] | Single-table filtering, auto-expanding formulas, readable syntax | No joins across tables; no native aggregation across linked tables |
| Data Model + Relationships | Convert each sheet to Table → Data → Manage Data Model → Drag CustomerID from tblCustomers to tblOrders | Multi-table analysis with DAX measures (e.g., SUMX related orders) | No row-level security; requires Power Pivot add-in (enabled by default in Office 365) |
| Power Query (Get & Transform) | Data → Get Data → From Table/Range → Home → Merge Queries → Select keys → Expand columns | Cleaning, joining, and refreshing 10k+ row datasets automatically | Learning curve; M code isn’t visible unless you open Advanced Editor |
| FILTER + XLOOKUP combo | =FILTER(tblOrders, XLOOKUP(A2, tblCustomers[Name], tblCustomers[CustomerID]) = tblOrders[CustomerID]) | Dynamic, formula-based lookups without helper columns | Volatility increases with large arrays; slow on >50k rows |
| PivotTable with Multiple Tables | Insert PivotTable → Check "Add this data to the Data Model" → Drag fields from different tables | Fast drag-and-drop reporting across related tables | No direct cell referencing; can’t export filtered results as values |
Method 1 Deep Dive
Start here—even if you’ve never used Tables before.
Select A1:D10 on Sheet1. Your data looks like this:
| OrderID | CustomerID | Amount | Date |
|---|---|---|---|
| ORD-2024-001 | CUST-007 | $1,245.00 | 2024-02-14 |
| ORD-2024-002 | CUST-012 | $892.50 | 2024-02-18 |
| ORD-2024-003 | CUST-007 | $3,100.00 | 2024-03-01 |
| ORD-2024-004 | CUST-021 | $567.20 | 2024-03-05 |
| ORD-2024-005 | CUST-012 | $2,410.95 | 2024-03-12 |
Press Ctrl+T. Check “My table has headers”. Click OK. Excel assigns the name Table1. Immediately rename it: click inside the table → Table Design tab → rename to tblOrders.
Now try this in F2: =[@Amount]*1.07. It auto-fills down. Try =SUM(tblOrders[Amount]) in F10. That’s not a range—it’s a dynamic reference. If you add a new row tomorrow, the SUM updates. No need to adjust cell addresses.
Counterintuitive tip: Never use A1-style references inside a Table formula. =B2*1.07 breaks when you sort. [@Amount]*1.07 stays correct.
Method 2 Deep Dive
This is where Excel stops being a spreadsheet and starts acting like Access.
Create a second sheet. Paste this customer list into A1:C8:
| CustomerID | Name | Region |
|---|---|---|
| CUST-007 | Sarah Chen | APAC |
| CUST-012 | Marcus Wright | EMEA |
| CUST-021 | Priya Desai | APAC |
| CUST-033 | Diego Morales | AMER |
| CUST-045 | Amina Okoye | EMEA |
Convert to Table. Rename to tblCustomers. Now go to Data → Manage Data Model (Alt+A, P). In the Power Pivot window, click Home → Diagram View. Drag CustomerID from tblCustomers onto CustomerID in tblOrders. A line appears. That’s your relationship.
Now insert a PivotTable. Check “Add this data to the Data Model”. Drag Name (from tblCustomers) to Rows. Drag Amount (from tblOrders) to Values. You just joined two tables—no VLOOKUP, no helper columns, no manual refresh.
If Sarah Chen places another order, just append the row to tblOrders. Refresh the PivotTable (Alt+F5). Done.
Cheat Sheet
| Action | How | Shortcut |
|---|---|---|
| Convert range to Table | Select data → Ctrl+T → Name in Table Design tab | Ctrl+T |
| Open Power Pivot | Data → Manage Data Model | Alt+A, P |
| Merge queries in Power Query | Home → Merge Queries → Select tables & keys → Expand needed columns | Alt+D, B |
| Refresh all connections | Data → Refresh All | Alt+F5 |
| Filter Table rows dynamically | =FILTER(tblOrders, tblOrders[Amount] > 1000) | None (formula) |
| Get related value without VLOOKUP | =XLOOKUP([@CustomerID], tblCustomers[CustomerID], tblCustomers[Name]) | None (formula) |
