Spreadsheets

Excel data model for expenses: link transactions and categories

Add a transactions table, category table and date table to the Excel data model, draw the relationships, and build one pivot that uses fields from all three.

Editorial illustration for Excel data model for expenses: link transactions and categories

To link transactions, categories and dates in Excel, turn each list into a table, add all three to the data model, create a one-to-many relationship from each lookup table to the transactions, and then build a pivot with fields from all three. The lookup tables hold each category or date once, and the transactions table holds many rows pointing at them.

What do I need before I start?

Three clean ranges, each with one header row and no merged cells or blank rows.

  • Transactions: Date, Narration, Category, Amount. Say it has 2,400 rows.
  • Categories: Category and Group. Say 18 rows, each category listed once, for example Groceries under Food and Metro under Travel.
  • Calendar: Date, Month, Year, one row per day from 1 April 2025 to 31 March 2026, so 365 rows.

Select inside each range and press Ctrl+T to make it a table, then name them Transactions, Categories and Calendar in Table Design, which makes the formulas and field lists readable.

How do I add the tables to the data model?

  1. Click inside Transactions, choose Insert, PivotTable, and tick Add this data to the Data Model in the dialog. Press OK.
  2. Repeat for Categories and Calendar: go to Insert, PivotTable on each and tick the same box. You can cancel out of the empty pivots afterwards. The tables stay in the model.
  3. If your version has the Power Pivot tab, you can also use Add to Data Model on the tab.

How do I create the relationships?

Go to the Data tab and choose Relationships. Press New. Set the first table to Transactions and its column to Category, then the second table to Categories and its column to Category. Excel labels one side as the lookup column, which must hold unique values. Repeat with Transactions Date linked to Calendar Date.

Many side (Transactions)One side (lookup table)What it lets you do
CategoryCategories, CategoryGroup spending by Group
DateCalendar, DateGroup by Month and Year

In the Power Pivot window you can instead open Diagram View and drag a line from one column to the other. The result is the same.

Why will Excel not create the relationship?

  • The lookup side has duplicates. If Groceries appears twice in Categories, Excel refuses because one column must hold unique values.
  • Trailing spaces or different spellings. Groceries and Groceries with an extra space are different values, so some rows will not match. Clean them with TRIM.
  • Dates with times. A Date column that includes a time of day will not match a Calendar of whole dates. Strip the time first.
  • Mixed types. A category column that holds numbers in some rows and text in others can fail the link.

How do I build one pivot using all three tables?

Insert a PivotTable from the data model and open the All tab of the field list. Drag Calendar Month to Rows, Categories Group to Columns and Transactions Amount to Values. You now have a month-by-group grid fed by three tables, with no lookup formulas in the transactions.

Take a hypothetical row: a 640 rupee grocery bill dated 12 June 2025 with category Groceries. The model finds Groceries in Categories, reads its group as Food, finds 12 June in Calendar, reads its month as June, and adds 640 to the Food column in the June row.

What should I check each time I add data?

  • Paste or load new transactions into the Transactions table, then use Data, Refresh All.
  • Add any new category to the Categories table first, otherwise its rows land under Blank.
  • Extend the Calendar when a new April to March year begins.

Maintaining three tables by hand is the cost of this approach. If you would rather keep a single list, SpendixAI keeps categories, sub-categories and each transaction's account together after you import a statement, and its reports group the same way without relationships to maintain.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports