Bank Statement Lesson 8: Project – Monthly Expense Dashboard

Bank Statement Lesson 8: Project – Monthly Expense Dashboard featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 2 min read

πŸ“˜ Bank Statement Automation Course Β· Lesson 8 of 8

In this article
  1. Layout
  2. Key formulas
  3. Make it monthly
  4. Practice
  5. Continue learning

Put it all together: one page that answers “where did the money go?” every month.

Advertisement

Layout

  • KPI cards: income, spending, net saving, savings rate, lowest balance.
  • Bar chart: spending by category (sorted largest first).
  • Column chart: spending by month.
  • Table: top 10 payees (lesson 4’s Payee column + SUMIFS + SORTBY).

Key formulas

Category column:  lesson 3 formula on the combined table
Top category:     =LET(c, UNIQUE(Cat), s, SUMIFS(Withdrawal, Cat, c), INDEX(c, MATCH(MAX(s), s, 0)))
Food share:       =SUMIFS(Withdrawal, Cat, "Food") / SUM(Withdrawal)
Top 10 payees:    =TAKE(SORTBY(UNIQUE(Payee), SUMIFS(Withdrawal, Payee, UNIQUE(Payee)), -1), 10)
πŸ’‘ In this statement EMI is the biggest category, which is normal with a home loan. Exclude fixed commitments (EMI, insurance) in a second chart to see the spending you can actually change.

Make it monthly

Build on the Power Query table from lesson 7. Each month: drop the CSV, Refresh All, glance at “Other” and add any new keywords. Five minutes instead of an evening.

Turn the formulas you used into named functions with the Excel LAMBDA Library course.

Advertisement

Practice

Download the workbook below. The statement is made up but follows real Indian bank formats (NEFT, NACH, UPI narrations). The Check column turns green when your formula gives the right answer.

πŸ“Ž Practice files for this article

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong