- Introduction
- How to Build an Expense Detail Tracker in Excel (Step-by-Step)
- Step 1: Lay Out the Expense Detail Sheet
- Step 2: Build the Auto-Numbering # Column
- Step 3: Format the Date and Amount Columns, Then Freeze
- Step 4: Add the Department Drop-Down
- Step 5: Add the Color-Coded Approval Drop-Down
- Step 6: Build the Department Summary With SUMIFS
- Step 7: Count Entries With COUNTIFS (and Know the Difference)
- Step 8: Add the Bar Chart and Verify the Log
- Template Features
- When to Upgrade From Excel
- Frequently Asked Questions
- Get Your Approvals in Order
Introduction
Most expense spreadsheets record what was spent, but not who approved it. That works until someone asks who signed off on a $2,400 vendor invoice — and the answer is a shrug and an email search.
An approval-ready log needs three things a simple list doesn’t: a stable reference number for every row; controlled department and approval columns, so the log filters and totals reliably; and a summary that groups spend two ways — by who spent it and by whether it was approved.
The Expense Detail Tracker does that in three sheets — Instructions, Expense Detail, and Summary. The log carries an auto-numbering # column, Date, Expense Item, Description, Amount, a Department drop-down (Operations, Sales, Marketing, Engineering, Finance, Human Resources, Customer Support, Facilities), Paid To, Submitted By, Approved By, and a color-coded Approved drop-down with Yes, No, and Pending.
The Summary sheet totals spend by department with SUMIFS, shows each department’s share of total, counts entries with COUNTIFS, breaks spend down by approval status, and plots a bar chart of spend by department. Download it free above, or build it with the guide below in 30 minutes.
📥 Download the Expense Detail Tracker Template Stop building from scratch. Get the pre-formatted workbook with the auto-numbered log, drop-downs, color-coded status, SUMIFS and COUNTIFS summary, and department bar chart. Save 30+ minutes of setup.
Tested by our data analysts at Healora Studio — we use this structure for departmental spend because it survives handoffs: anyone can filter it, total it, and find a specific expense.
How to Build an Expense Detail Tracker in Excel (Step-by-Step)
Step 1: Lay Out the Expense Detail Sheet
Rename Sheet1 to Expense Detail; add these headers in row 7, data in row 8.
| Col | Header | Col | Header |
|---|---|---|---|
| A | # | F | Department |
| B | Date | G | Paid To |
| C | Expense Item | H | Submitted By |
| D | Description | I | Approved By |
| E | Amount | J | Approved |
Step 2: Build the Auto-Numbering # Column
In A8, enter:
=ROW()-7
ROW() returns the current row, so row 8 yields 1. Fill it down: delete a row and every typed sequence number below it is silently wrong, while a ROW()-based number renumbers itself.
Pro Tip from Healora Studio Analysts: Never sort a log with typed sequence numbers — the numbers travel with the wrong rows. The formula version can’t do that.
Step 3: Format the Date and Amount Columns, Then Freeze
Select B8:B1000, press Ctrl + 1, and apply a Date format. Select E8:E1000 and apply Currency with two decimals. Click A8 and use View > Freeze Panes > Freeze Panes so the headers stay visible through a year of expenses.
Step 4: Add the Department Drop-Down
Select F8:F1000, then Data > Data Validation. Set Allow to List, Source to:
Operations,Sales,Marketing,Engineering,Finance,Human Resources,Customer Support,Facilities
Click OK. Spaces inside entries like Human Resources are fine — Excel splits on commas. This drop-down is what makes the Summary possible: free-typed columns eventually hold Eng, Engineering, and engineering.
Step 5: Add the Color-Coded Approval Drop-Down
Select J8:J1000, open Data > Data Validation, choose List, enter Yes,No,Pending.
Now make it scannable: with J8:J1000 selected, use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains three times — Pending yellow, No red, Yes green.
[Insert Real Screenshot Here: the Expense Detail sheet with the Approved column color-coded green, red, and yellow, and the department drop-down in column F]
Pro Tip from Healora Studio Analysts: Leave Approved By and the status Pending at submission, and fill both only when a decision is made. A log where every row defaults to Yes manufactures a paper trail that never happened.
Step 6: Build the Department Summary With SUMIFS
Add a sheet (Shift + F11) named Summary, with department names in A4:A11 and row-3 headers: Department, Total Spend, % of Total, Entries.
In B4 sum spend by department; in C4 calculate share of total:
=SUMIFS('Expense Detail'!$E$8:$E$1000,'Expense Detail'!$F$8:$F$1000,$A4)
=B4/SUM($B$4:$B$11)
The $B$4:$B$11 reference is locked, so filling down always divides by the same grand total. Fill B and C through row 11, then add a totals row: the percentage column must reach 100%.
Step 7: Count Entries With COUNTIFS (and Know the Difference)
In D4:
=COUNTIFS('Expense Detail'!$F$8:$F$1000,$A4)
SUMIFS adds up values; COUNTIFS counts matching rows.
| Function | Answers | Marketing example |
|---|---|---|
SUMIFS |
How much money? | Spent $18,400 |
COUNTIFS |
How many entries? | 23 expenses |
Below the table, add an approval block with Approved, Rejected, and Pending in A15:A17. The log stores Yes, No, and Pending, so map the criteria directly:
=SUMIFS('Expense Detail'!$E$8:$E$1000,'Expense Detail'!$J$8:$J$1000,"Yes")
=SUMIFS('Expense Detail'!$E$8:$E$1000,'Expense Detail'!$J$8:$J$1000,"No")
=SUMIFS('Expense Detail'!$E$8:$E$1000,'Expense Detail'!$J$8:$J$1000,"Pending")
Add COUNTIFS with the same criteria for counts. The Pending row is your outstanding-approvals queue — anything pending is spend that isn’t confirmed.
Step 8: Add the Bar Chart and Verify the Log
- Select
A3:A11, holdCtrl, then selectB3:B11 - Go to
Insert > Charts > Barand choose Clustered Bar - Right-click the horizontal axis > Format Axis and set the number format to Currency
Sort by Total Spend descending before charting and the bar chart becomes a ranked view of where the money went. Then test with five to ten real expenses across three departments: totals should sum to your spend and changing a status should move the Pending row.
[Insert Real Screenshot Here: the Summary sheet with the department totals, approval block, and ranked bar chart]
Template Features
✅ Three pre-built sheets — Instructions, Expense Detail, and Summary
✅ Auto-numbering # column — references survive insertions and deletions
✅ Department drop-down — all eight departments
✅ Approval drop-down — Yes, No, or Pending, color-coded automatically
✅ Audit trail fields — Paid To, Submitted By, and Approved By
✅ SUMIFS department totals — spend rolled up per department
✅ Percentage-of-total column — each department’s share of spend
✅ Entry counts with COUNTIFS — how many, not just how much
✅ Approval status breakdown — spend and counts for Approved, Rejected, Pending
✅ Bar chart of spend by department — ranked visual, ready to present
✅ Compatible with: Excel 2016+, Microsoft 365, Google Sheets, Windows and macOS
✅ 100% unlocked — add departments or approval states, restyle freely
When to Upgrade From Excel
A spreadsheet approval log works for roughly 100–200 expenses a month with one or two approvers — it breaks down when receipts must attach to rows or finance needs the data in an accounting system.
Expensify — Best for Receipt Capture and Approval
Expensify is built for the workflow this spreadsheet approximates: employees photograph receipts, the app extracts merchant, date, and amount, and approvers approve from their phone with a timestamp. – Pricing: Free for personal use; team plans from roughly $5–9 per user/month – [Affiliate Link: Sign up for Expensify free]
Bill.com — Best for AP Automation
For vendor invoices you need to pay, Bill.com handles approval routing, payment scheduling, and syncing with your accounting software so nothing is entered twice. – Pricing: Starts around $45–79 per user/month – [Affiliate Link: Try Bill.com for accounts payable]
QuickBooks Online — Best for Closing the Loop
When approved expenses need to land in actual books, this is where they belong — bank feeds, categorization, and accountant-ready reports. – Pricing: Starts around $30/month for Simple Start – [Affiliate Link: Start QuickBooks Online with a discount]
Our recommendation: This tracker suits a small team with one approver and no receipt attachments. Move to Expensify when submission becomes a bottleneck, and to Bill.com or QuickBooks when finance needs the numbers in an accounting system.
Frequently Asked Questions
Why use a formula for the # column instead of typing 1, 2, 3?
Typed numbers break the moment you delete or insert a row — everything below shifts silently. =ROW()-7 renumbers itself, so references stay accurate.
What’s the difference between COUNTIFS and SUMIFS?
SUMIFS adds the Amount column for rows matching your criteria — how much was spent. COUNTIFS counts matching rows — how many expenses. Showing both separates volume from value.
Why doesn’t my percentage-of-total column add up to 100%?
A department name in the log doesn’t match the Summary list — a typo, a trailing space, or a blank. Filter the Department column for blanks.
Can I add departments like Legal or IT?
Yes — the workbook is unlocked. Add the name to the Data Validation source and the Summary list, then copy the three formulas into the new row.
Get Your Approvals in Order
The value of an expense log isn’t the total at the bottom — it’s answering “who approved this” without digging through email. Set the structure once and every expense afterward makes that answer faster.
📥 Download the Expense Detail Tracker Template Pre-formatted workbook with the auto-numbered log, drop-downs, color-coded approvals, SUMIFS and COUNTIFS summary, and department bar chart.
This tutorial and template are part of SheetMaster Hub by Healora Studio. For more data efficiency tools, visit healorastudio.com.