HomeCasa Personal finance spreadsheet with formulas and a dashboard
Design an income and expense tracker in Excel or Google Sheets step by step: tabs, columns, SUMIFS formulas, budget vs actual, conditional formatting and dashboard charts. Designed for ChatGPT, Claude, Gemini and Microsoft Copilot. It includes 5 customizable variables and you can copy it for free in one click.
Intermediate · Text · Pending editorial review
Prompt
Act as a spreadsheet expert who designs personal finance templates that are easy to maintain.
My setup:
- Tool: {{tool}}
- Income (type and frequency): {{income}}
- Spending categories I want to use: {{categories}}
- Goals I want to track: {{goals}}
- My spreadsheet level: {{level}}
If something important is missing (for example, whether I share the sheet with a partner), ask me first.
Deliverables:
1. Tab structure: Transactions, Categories, Budget, Monthly summary, Goals and Dashboard, with the purpose of each.
2. The exact columns for Transactions (date, description, category, subcategory, amount, type, account, month) with data validation (dropdown lists) and formatting.
3. Formulas ready to paste, in the syntax of {{tool}}: month from date, totals by category and month (SUMIFS or equivalent), budget vs actual, variance in dollars and %, savings rate and goal progress.
4. Conditional formatting that warns when a category passes 90% and 100% of its budget.
5. Two or three recommended dashboard charts and how to build them.
6. How to import my bank's CSV export: basic cleanup, assigning categories and avoiding duplicates.
7. A 15-minute weekly routine and a month-end close routine.
8. Common mistakes (dates stored as text, decimal separators, ranges that do not expand) and how to avoid them.
Constraints:
- Use the correct argument separator (comma or semicolon) for the locale.
- Do not invent amounts: use sample data clearly labeled "sample".
- Explain each formula in one line.
Format: numbered sections, tables for the columns and code blocks for the formulas.How to use it
Create a new file and follow the sections in order. Test the formulas with a week of sample transactions before importing your real data.
Usage example
Tool: Google Sheets · Income: biweekly paycheck plus occasional freelance work · Categories: housing, groceries, transport, fun, health, subscriptions · Goals: emergency fund and vacation.
Sample output
2. "Transactions" tab
| Column | Content | Validation |
|---|---|---|
| A Date | 2/3/2026 | Valid date |
| B Description | "Grocery store (sample)" | Text |
| C Category | housing, groceries… | Dropdown from "Categories" |
| E Amount | 54.20 | Number > 0 |
| F Type | Expense / Income | Dropdown |
| H Month | formula | — |
3. Formulas (Google Sheets, US locale → commas)
H2 =DATE(YEAR(A2), MONTH(A2), 1)First day of the month, so grouping never breaks on text.
C2 =SUMIFS(Transactions!$E:$E, Transactions!$C:$C, $A2, Transactions!$H:$H, C$1, Transactions!$F:$F, "Expense")Actual spending per category (row) and month (column).
D2 =C2-B2
E2 =IFERROR(C2/B2-1, 0)Variance in dollars and in % against budget.
Savings rate =IFERROR((Income-Expenses)/Income, 0)
Goal progress =MIN(1, Saved/10000)Biweekly paychecks: budget monthly, but note the two months a year with three paydays.
4. Conditional formatting
- ≥ 90% of budget: amber fill.
- ≥ 100%: red fill and bold text.
All amounts are sample data. …
Key facts
- Category
- Finance · Spreadsheets
- Output type
- Text
- Level
- Intermediate
- Designed for
- ChatGPT, Claude, Gemini and Microsoft Copilot
- Verified in
- Pending editorial verification
- Variables
- Tool, Income, Spending categories, Goals and Spreadsheet level
- Language
- English
- Price
- Free, no sign-up
- Updated
- Nov 7, 2026
Frequently asked questions
Do the same formulas work in Excel and Google Sheets?
Most of them do, but a few function names and the argument separator change with language and locale. The prompt asks for the exact syntax of your tool.
Can I use it with my bank statement?
Yes. It includes steps to import the CSV, clean dates and amounts, and assign categories without duplicating transactions.
What is this prompt for?
Design an income and expense tracker in Excel or Google Sheets step by step: tabs, columns, SUMIFS formulas, budget vs actual, conditional formatting and dashboard charts.
Which AI tools does it work with?
It is designed for ChatGPT, Claude, Gemini and Microsoft Copilot. We have not verified it in these tools yet, so results may vary between them.
How do I customize the prompt?
Click “Customize” and fill in the variables (Tool, Income, Spending categories, Goals and Spreadsheet level). The text updates instantly and you can then copy it in one click. Values are stored only in your browser.
Do I need experience to use it?
It is intermediate: it helps to know the topic so you can provide good context and review the result critically.
Is this prompt free?
Yes. Every HomeCasa Prompts prompt is free, with no sign-up or subscription. You can also use them in professional projects.
Can I trust the AI output?
Treat it as a high-quality draft: always check data, figures and important claims before publishing or making decisions.