Daily Expense Tracker in Excel: Log Spending by Category and See Monthly Totals Automatically

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: daily-expense-tracker-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

Raja's log (made-up) has 20 rows from 3 September to 16 October 2026. When he types 01-10-2026 in H4, the summary shows only October:

Groceries is the biggest category: 5,588 / 13,321 = 41.9%. By payment mode he spent UPI ₹7,601 + Card ₹3,480 + Cash ₹2,240 = ₹13,321. Change H4 to any September date and the same cells show September's ₹4,086.

2. Build it step by step

Step 1. Set up the log

1. In A4:E4 type Date, Item, Category, Paid by, Amount (₹).
2. Type one spend per row from A5 down. Dates as 03-09-2026 (see date functions if Excel treats them as text).
3. Select C5:C500 > Data > Data Validation > List: Groceries,Transport,Bills,Food out,Health,Fun.
4. Select D5:D500 > the same with UPI,Cash,Card.

Step 2. Pick the month

1. In H4 type any date of the month you want, for example 01-10-2026 (format it as MMM-YYYY).
2. In H5 type =EOMONTH(H4,0): 31-10-2026, the last day of that month.
3. In H6 type =EOMONTH(H4,-1)+1: 01-10-2026, the first day.

Step 3. Monthly total per category

1. Type the six categories in G9:G14.
2. In H9 type =SUMIFS($E$5:$E$500,$C$5:$C$500,G9,$A$5:$A$500,">="&$H$6,$A$5:$A$500,"<="&$H$5).
3. Copy H9 down to H14. Groceries shows ₹5,588.
4. In H15 type =SUM(H9:H14): ₹13,321.

Step 4. Share of each category

1. In I9 type =IF($H$15=0,0,H9/$H$15) and copy down. Format as %.
2. Select I9:I14 > Home > Conditional Formatting > Data Bars (data bars).

Step 5. Count, biggest and payment mode

1. In H16 type =COUNTIFS($A$5:$A$500,">="&H6,$A$5:$A$500,"<="&H5): 15 entries.
2. In H17 type =INDEX(G9:G14,MATCH(MAX(H9:H14),H9:H14,0)): Groceries (INDEX MATCH).
3. Type UPI, Cash, Card in G21:G23 and in H21 type =SUMIFS($E$5:$E$500,$D$5:$D$500,G21,$A$5:$A$500,">="&$H$6,$A$5:$A$500,"<="&$H$5), copy down.

Step 6. Bar chart

1. Select G8:H14 > Insert > Bar Chart (bar charts).
2. Add a new spend at the bottom of the log: the totals and the chart change at once.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Dates typed as text. If a date sits on the left of the cell it is text and SUMIFS skips it. Retype it, or fix the format (see date formats).
2. Writing ">=H6" inside quotes. The cell must be joined outside the quotes: ">="&$H$6. ">=H6" compares with the letters H6.
3. Forgetting the $ signs. Without $E$5:$E$500 the ranges move down as you copy H9 and the totals go wrong.
4. Leaving gaps or totals inside the log. Keep the log as a plain list. Totals belong in the summary area, not between spends.
5. Typing a new category name by hand. "Grocery" will not be counted under "Groceries". Add new names to the drop-down list and to G9:G14.

5. More real examples

Example 1: College student pocket money. Categories: canteen, travel, books, mobile, fun. Monthly limit in a cell and =limit-H15 as money left.

Example 2: Couple sharing one tracker. Add a Who column (Ravindra / Shraddha) and one more SUMIFS condition on it.

Example 3: Cash vs UPI habit check. Compare H21:H23 month by month; small cash spends are usually the ones nobody remembers.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I track daily expenses in Excel?

Keep one row per spend (date, item, category, mode, amount) and let SUMIFS build the totals. Never type totals by hand.

How do I sum expenses for one month only?

Use SUMIFS with two date conditions: ">="&first day and "<="&last day. EOMONTH gives both days from any date in that month.

Can I use an Excel Table instead of fixed ranges?

Yes. Press Ctrl+T on the log and use structured references like Expenses[Amount]; see Excel Tables. The template uses rows 5 to 500 so it works in every version.

Can a PivotTable do this instead?

Yes: Category in Rows, Amount in Values and Date grouped by Months. See group dates by month.

Does it work on mobile?

Yes, the Excel mobile app can add rows and show the totals, so you can log a spend at the shop.

7. Quick quiz

Ten questions on this page. Sign in to save your score.