How to Create a Personal Budget in Excel (With Easy Steps)

The quickest way to create a personal budget in Excel is to build a Budget worksheet with planned income, actual income, savings, debt payments, expenses, variance, and money remaining. For a budget you can reuse and audit each month, add a second Transactions worksheet and let SUMIFS calculate actual totals by category and month.

This guide starts with the 10-to-15-minute one-sheet version, then upgrades it into a reusable tracker. It uses ordinary Excel features—tables, formulas, drop-down lists, and conditional formatting—rather than Copilot, macros, Power Query, or PivotTables.

What you will build

Your finished workbook will show:

• Take-home income planned and received
• Planned and actual savings
• Debt payments
• Fixed, variable, irregular, and miscellaneous expenses
• Variance between your plan and what actually happened
• Money available after savings, debt, and expenses

Use the simple version if you want a functioning budget quickly. Use the transaction-tracking version if you want to enter purchases once and have Excel summarize them automatically for any month.

The instructions target Microsoft 365 and Excel 2024. Many of the same features are available in Excel 2021 and earlier editions, but menu names and capabilities can vary between Windows, Mac, and Excel for the web. Office 2016 and Office 2019 reached the end of Microsoft support on October 14, 2025, so they should not be treated as current supported choices. See Microsoft’s Office support information if your version is unclear.

1. Decide what your budget should track

A budget is a written plan for income, spending, and ideally savings over a defined period. For a monthly household budget, start with the amount that reaches your bank account—not the salary shown before taxes and payroll deductions. Gross income can be tracked separately if useful, but take-home income is the practical amount available for the spending plan.

Income

Add each reliable income source as its own row:

• Paychecks
• Freelance, gig, or side-business income
• Benefits
• Child support or alimony, where applicable
• Interest or other recurring income
• Irregular income, using a conservative estimate

If you do not receive income monthly, Consumer.gov suggests adding the income received during the previous year and dividing it by 12 to estimate a monthly amount. That produces a planning estimate, not a promise that the same amount will arrive in every month. With commission, seasonal, or gig income, use a baseline you can reasonably count on and put income above that baseline in a separate category.

Savings

Give savings a planned place in the workbook instead of treating it as whatever happens to remain at month-end. Consumer.gov allows savings to be included as a budget expense, and the FDIC describes a budget as typically including income, expenses, and savings.

Useful savings rows include:

• Emergency fund
• Retirement contribution
• Vacation
• Home or car fund
• Annual bills
• Other financial goals

Calling savings an expense here is a budgeting convention: it means money is deliberately allocated and is not available for ordinary spending. It is not an accounting rule. If money moves from checking to savings, you can either include the transfer as a planned budget outflow or track it separately as a transfer. Choose one method and use it consistently.

Debt payments

List required payments separately from optional extra payments where that helps you make decisions. Examples include:

• Credit cards
• Student loans
• Auto loans
• Personal loans
• Buy-now-pay-later balances

Do not put the same minimum debt payment in both the debt section and the fixed-expense section. Choose one location. Also choose one method for credit-card spending: record purchases when they occur, or record the later card payment when it leaves your bank account. Combining both methods counts the same money twice.

Fixed expenses

Fixed expenses generally stay the same or change little from month to month. Common examples are:

• Rent or mortgage
• Car payment
• Insurance premiums
• Internet and phone
• Minimum debt payments
• Subscriptions

Variable expenses

Variable expenses change with your household’s activity or choices. Examples include groceries, gas, dining out, medical costs, clothing, entertainment, and household supplies. The Consumer Financial Protection Bureau distinguishes generally stable fixed expenses from changing variable expenses.

Irregular and miscellaneous expenses

Monthly bills are not the whole picture. Add rows or a sinking-fund category for car repairs, medical bills, gifts, holidays, annual insurance, tuition, travel, home maintenance, seasonal expenses, charity, and professional fees. The CFPB recommends reviewing several months of spending history and including a miscellaneous category so less frequent costs do not disappear from the plan.

For a predictable annual cost, divide the expected annual amount by 12:

Annual insurance premium: $1,200
Monthly planning allocation: =1200/12
Monthly allocation: $100

The $100 is an amount to set aside or plan for each month. It is not necessarily the amount that leaves your bank account every month. If the insurer charges $1,200 in August, your cash-flow record will show the full August payment even though your budget allocated $100 per month.

2. Choose a monthly budget rather than 12 separate sheets

Use a monthly budget as the main view because paychecks, bills, and everyday spending decisions usually happen monthly. A yearly summary can be added later to show annual totals, seasonal costs, month-to-month trends, and progress toward annual savings goals.

Do not create 12 manually maintained worksheets unless you have a specific reason. A single transaction table containing multiple months is easier to maintain. A month selector on the summary sheet can then use SUMIFS to display whichever month you choose.

3. Build the simple one-sheet budget

1. Open Excel and choose File > New > Blank workbook. Microsoft documents this as the standard starting point in its basic Excel workflow.
2. Rename the first worksheet Budget.
3. In cell B2, enter the first day of the month, such as 8/1/2026.
4. Format B2 as mmmm yyyy. It will display as August 2026 while remaining a real date that formulas can use.
5. Create the following layout. Rename, remove, or add categories to match your finances.

Row	Column A	Column B	Column C	Column D	Column E
1	Personal Budget
2	Month	8/1/2026
4	Category	Planned	Actual	Variance	Notes
5	INCOME
6	Paycheck
7	Side work
8	Benefits or other income
9	Total income
11	SAVINGS & DEBT
12	Emergency savings
13	Debt payment
14	Total savings & debt
16	EXPENSES
17	Housing
18	Utilities
19	Groceries
20	Transportation
21	Insurance
22	Healthcare
23	Personal
24	Entertainment
25	Miscellaneous
26	Total expenses
28	Available after plan

Enter planned numbers before the month begins. Enter actual amounts during the month or at month-end. A blank planned amount is treated as zero in the total formulas, so make sure a blank really means zero rather than an item you forgot to plan.

Enter the formulas

In the planned column, enter:

B9 =SUM(B6:B8)
B14 =SUM(B12:B13)
B26 =SUM(B17:B25)
B28 =B9-B14-B26

In the actual column, enter:

C9 =SUM(C6:C8)
C14 =SUM(C12:C13)
C26 =SUM(C17:C25)
C28 =C9-C14-C26

In the variance column, enter:

D9 =C9-B9
D14 =C14-B14
D26 =C26-B26
D28 =C28-B28

For ordinary detail rows, use the same definition:

D6 =C6-B6

Copy D6 down through the detail rows, including the savings, debt, and expense rows. Do not overwrite subtotal formulas in rows 9, 14, 26, or 28. The formulas use SUM over ranges rather than long strings of individually added cells. As Microsoft explains, ranges are easier to maintain when rows or columns change.

Understand the variance sign

This workbook uses one consistent convention:

Variance = Actual − Planned

Row type	Positive variance means	Negative variance means
Income	More income than planned	Less income than planned
Expense	Spent more than planned	Spent less than planned
Savings or debt	Allocated or paid more than planned	Allocated or paid less than planned
Available after plan	More money available than planned	The plan is short by that amount

For example, planned groceries of $500 and actual groceries of $575 produce a variance of $575 - $500 = $75. The $75 is positive, but it is unfavorable for an expense row because spending exceeded the plan. A positive variance is not automatically good; its meaning depends on the row.

4. Format the workbook so it is easy to use

Apply currency formatting

Select the planned, actual, and variance columns, including totals, and apply Currency or Accounting format. Type 1250, not a text entry such as $1,250 in every cell. Excel can display the number as currency while retaining it as a number for calculations.

If amounts are left-aligned, totals are unexpectedly low, or a green warning triangle appears, Excel may be treating them as text. Select the warning icon and choose Convert to Number, use VALUE(cell) in a helper column, or reimport the data with the correct number type. Microsoft’s guide to numbers stored as text covers these fixes.

Highlight problems with conditional formatting

Useful rules include:

• Expense variance greater than 0: red fill or red font.
• Expense variance less than or equal to 0: green or neutral formatting.
• Available actual balance less than 0: red.
• Savings progress meeting or exceeding its target: green.

To add a basic rule, select the relevant variance cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than or Less Than. Enter 0 and select a format. More advanced formula-based rules can be reviewed through Home > Conditional Formatting > Manage Rules. See Microsoft’s conditional-formatting instructions.

Because income and expense variances have different meanings, do not automatically color every positive variance red. Apply expense rules to expense rows and, if desired, a different rule to income rows.

Make input cells obvious

Use one light color for cells where you type planned or actual amounts and another style for formula cells. Bold the section headings and total rows. Widen the Notes column so reminders such as annual bill, payday, or reimbursable remain readable.

Freeze a long transaction header

On a long transaction list, select the cell below the header row and choose View > Freeze Panes > Freeze Panes. Excel keeps rows above and columns to the left of the selected cell visible while you scroll. Microsoft explains this behavior in its Freeze Panes documentation.

5. Add a transaction tracker for automatic actual totals

The one-sheet budget works, but you must manually total actual spending by category. A more durable workbook has one summary sheet and one transaction table covering all months.

Add a worksheet named Transactions with these headers:

Date	Description	Type	Category	Amount	Account	Notes
8/2/2026	Employer paycheck	Income	Paycheck	2,100	Checking
8/3/2026	Rent	Expense	Housing	1,250	Checking
8/4/2026	Grocery store	Expense	Groceries	86.42	Credit card

Use real dates in the Date column and positive amounts for both income and expenses. The Type column tells Excel how to interpret the amount. This avoids relying on negative signs to distinguish a paycheck from a purchase.

Convert the range into an Excel Table

1. Select any cell in the transaction data.
2. Press Ctrl+T, or choose Insert > Table.
3. Confirm that My table has headers is selected.
4. Rename the table Transactions. Depending on the edition, select the table and use the Table Design tab’s table-name box.

Excel Tables expand as rows are added and support structured references such as Transactions[Amount]. That is safer than formulas tied to a fixed range such as E2:E100. See Microsoft’s guides to inserting tables, structured references, and renaming a table.

6. Make actual amounts calculate by category and month

Return to the Budget sheet. Keep the selected month’s first day in B2. For example, 8/1/2026 represents August 2026. The category name is in column A, such as A6 for Paycheck or A17 for Housing.

For an income detail row such as row 6, enter this formula in C6:

=SUMIFS(Transactions[Amount],Transactions[Type],"Income",Transactions[Category],$A6,Transactions[Date],">="&$B$2,Transactions[Date],"<"&EDATE($B$2,1))

For an expense detail row such as row 17, enter this formula in C17:

=SUMIFS(Transactions[Amount],Transactions[Type],"Expense",Transactions[Category],$A17,Transactions[Date],">="&$B$2,Transactions[Date],"<"&EDATE($B$2,1))

Copy the appropriate formula down the income and expense detail rows. If your savings and debt rows are recorded in the transaction table as budget outflows, use the expense version for those rows too. Keep their categories in only one section of the summary.

SUMIFS adds amounts that meet several conditions: the correct type, the correct category, and a date on or after the selected month’s first day but before the first day of the next month. EDATE($B$2,1) supplies that next-month boundary. The less-than boundary includes every date in the selected month without requiring a separate month-end calculation. Microsoft documents SUMIFS and date arithmetic with EDATE.

After entering transactions, the summary’s actual values update when you change B2 to another month, provided the new date is entered as the first day of that month. The transaction log remains intact, so you do not need to copy or clear a monthly block.

Update the subtotal and remaining-money formulas

Keep the summary formulas from the simple version:

B9 =SUM(B6:B8)
C9 =SUM(C6:C8)
D9 =C9-B9

B14 =SUM(B12:B13)
C14 =SUM(C12:C13)
D14 =C14-B14

B26 =SUM(B17:B25)
C26 =SUM(C17:C25)
D26 =C26-B26

B28 =B9-B14-B26
C28 =C9-C14-C26
D28 =C28-B28

If you add categories, expand the subtotal ranges to include them. For example, if the expense detail rows become 17 through 30, use =SUM(B17:B30) rather than leaving the new rows out.

7. Standardize categories with drop-down lists

In a new worksheet named Lists, create lists like these:

Column A: Type	Column B: Category
Income	Paycheck
Expense	Side work
	Benefits or other income
	Housing
	Utilities
	Groceries
	Transportation
	Insurance
	Healthcare
	Debt payment
	Savings
	Personal
	Entertainment
	Miscellaneous

For the transaction table’s Type column:

1. Select the cells in the Type column.
2. Choose Data > Data Validation.
3. Under Allow, choose List.
4. Select the cells containing Income and Expense as the source.
5. Make sure In-cell dropdown is selected.

Repeat the process for the Category column using the category list. If the list is on another worksheet, the most reliable approach is to give the source ranges names—such as TypeList and CategoryList—and enter =TypeList or =CategoryList in the Data Validation Source box. Alternatively, place the list on the same sheet where Excel permits a direct range reference.

For a list that will grow, convert the entries on the Lists sheet to an Excel Table. Microsoft notes that table-based list entries can update the associated drop-down when items are added or removed. Its current drop-down-list instructions use Data > Data Validation > Allow > List.

Data Validation improves consistency but is not an absolute safeguard. Copied or filled values can bypass some validation behavior. It may also be unavailable while a sheet is protected or a workbook is shared. If you cannot edit the lists, temporarily unprotect the sheet and check the workbook’s sharing status.

8. Handle savings, annual bills, and debt correctly

Separate a monthly allocation from a payment

Suppose you expect a $1,200 insurance bill in August. You have two useful views:

• Planning view: budget $100 per month toward the annual bill.
• Cash view: show a $1,200 transaction on the date the bill is paid.

These are not contradictory. The first measures whether you are preparing for the cost; the second measures when cash actually leaves the account. You can add a Notes entry such as annual insurance—due August or create a separate sinking-fund table with columns for Goal, Annual Cost, Monthly Allocation, Amount Saved, and Due Date.

Choose one credit-card convention

The purchase-date method records a grocery purchase as Groceries when the purchase occurs. The later payment from checking to the credit-card company is a transfer or debt payment, not another grocery expense.

The payment-date method records the credit-card payment when it leaves checking and does not separately record the underlying purchases as expenses. This can be useful for a strict cash-flow view, but it provides less useful category information.

The purchase-date method is generally clearer for category analysis. Whichever method you choose, do not record both the purchase and the full card payment as new expenses.

Record refunds and reimbursements consistently

For a refund, either enter a negative amount in the original expense category or record it as income with a clearly labeled category such as Refund. Do not use both methods for the same refund.

For a reimbursement, you can record the original expense and then record the reimbursement as income, or exclude the reimbursable spending from the household budget if it is never truly your cost. The important point is to use the same treatment each time.

9. Understand a budget versus a cash-flow calendar

A monthly budget answers: Will planned income cover planned spending, savings, and debt over the month?

A cash-flow calendar answers: Will money arrive before each bill and expense is due?

You can have a positive monthly balance and still run out of money in the second week if a large bill is due before your next paycheck. The CFPB recommends considering cash-flow timing and bill due dates when monthly totals do not explain shortfalls.

If timing is a problem, create a separate worksheet named Cash Flow with columns such as:

Date	Expected inflow	Bill or outflow	Description	Running balance
8/1/2026	2,100		Paycheck
8/3/2026		1,250	Rent

Put expected paydays and bill due dates in date order, then calculate each running balance from the prior balance plus inflows minus outflows. Review the lowest balance—not just the month-end balance. This sheet is especially helpful for biweekly pay, variable income, and households whose bills cluster around one part of the month.

10. Use a monthly review routine

Before the month starts

1. Enter the first day of the month in Budget!B2.
2. Copy the previous month’s planned amounts.
3. Adjust known changes such as a rent increase, new subscription, or changed paycheck.
4. Add one-time and seasonal expenses.
5. Enter savings and debt targets.
6. Confirm that planned available money is not negative.

During the month

1. Add transactions as they happen or at least once a week.
2. Use the same category names every time.
3. Check the summary for overspending before it becomes difficult to correct.
4. Move money between categories only deliberately, and note the change.
5. Do not delete an unusual transaction just because it makes the budget look worse.

At month-end

1. Reconcile the transaction log against bank and credit-card statements.
2. Compare planned and actual amounts.
3. Investigate large variances rather than simply changing the plan to make them disappear.
4. Record annual, seasonal, and miscellaneous expenses you missed.
5. Use actual spending to set the next month’s plan.

This follows the basic cycle recommended by Consumer.gov: plan at the beginning of the month, record spending during the month, compare planned and actual spending at the end, and use the result for the next plan.

Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Support on Ko-Fi

11. Troubleshoot common Excel budget problems

The automatic actual total is zero

Check these items in order:

• The transaction table is named exactly Transactions.
• The category spelling matches the summary row exactly.
• The Type value is consistently Income or Expense.
• The dates are real Excel dates, not text that only looks like a date.
• The amounts are numbers, not text.
• B2 is the first day of the intended month.
• The SUMIFS ranges cover the same number of rows.

In a SUMIFS formula, mismatched range sizes can produce errors or incorrect results. Microsoft identifies this as a common issue in its guide to correcting SUMIF and SUMIFS errors.

Dates are stored as text

If changing the date format does not change the value’s behavior, the date may be text. Try re-entering one date manually, sort the column, or convert imported dates before relying on them in SUMIFS. Excel stores genuine dates as serial numbers so they can be sorted and calculated; text that merely resembles a date may not meet the date criteria. Microsoft documents related functions such as DATEVALUE and date calculations.

Amounts are stored as text

Symptoms include left-aligned amounts, totals lower than expected, a green warning triangle, or a formula that returns zero. Select the warning icon and choose Convert to Number; use VALUE(cell) in a helper column; format the column as Number or Currency; or reimport the file with the amount column defined as numeric.

Excel rejects a copied formula

The examples use commas as formula separators, as in =SUM(B6:B8). Some regional settings use semicolons instead. If Excel rejects a formula, replace the commas with the separator your installation expects. Microsoft explains that list separators can vary by operating-system locale and Excel settings in its guide to avoiding broken formulas.

Categories do not match

Groceries, Grocery, and Groceries with a trailing space can be treated as different text. Use the drop-down lists, standardize existing entries, and avoid creating near-duplicate categories unless the distinction is genuinely useful.

Negative amounts produce confusing results

The transaction design in this guide uses positive amounts for both income and expenses and relies on the Type column. If you import a bank file in which spending is negative, do not blindly paste it into this design. Either convert expense amounts to positive values before loading them or redesign the formulas to accommodate signed amounts. Mixing positive and negative conventions can make expenses appear to reduce the total or make income disappear.

The sheet is protected and you cannot edit a list

Worksheet protection can stop users from changing formulas, but it can also prevent edits to Data Validation settings. Unprotect the sheet, update the lists, and protect it again. If you protect formulas, first unlock the cells intended for input, then choose Review > Protect Sheet. Protection helps prevent accidental edits; it is not a complete security feature. Do not store passwords, Social Security numbers, full account numbers, or other confidential information in an unencrypted workbook. See Microsoft’s guidance on worksheet protection and Excel protection and security.

Do not use IFERROR to conceal every problem

If you add an optional savings-progress percentage, use a deliberate zero-denominator check:

=IF(B17=0,"",C17/B17)

Automatically changing every error to zero with IFERROR can make a broken reference or invalid input look like a legitimate result. IFERROR is useful when a replacement is genuinely intended, but it should not replace troubleshooting.

12. Add a chart only after the workbook works

Charts are optional. A clear summary is more valuable than a decorative chart, and charts do not prevent overspending. Once the formulas and categories are correct, useful choices include:

• Column chart: planned versus actual spending by category.
• Doughnut chart: the mix of actual expenses by category.
• Line chart: monthly income, expenses, and remaining money over time.

To create one, select the relevant category and amount columns, choose Insert > Recommended Charts, preview the suggestions, and insert the chart. Add a descriptive title and data labels if they improve readability. Microsoft notes that Recommended Charts are available across Excel for Windows, Mac, and the web, although recommendations and interfaces can vary by product and subscription. See Recommended Charts.

13. Save and reuse the workbook

Save the file as an .xlsx workbook. If you use the one-sheet version, duplicate the file for the next month or change the month and clear or replace the actual values after saving a copy of the completed month. If you use the transaction version, leave the historical transactions in place and change only Budget!B2 to the new month.

Keep a backup before making structural changes. If you add a new category, add it to the Lists sheet, add a corresponding row to the Budget sheet, expand the subtotal range, and check any chart or conditional-formatting ranges.

Which Excel budgeting approach is right for you?

Approach	Best for	Advantages	Trade-offs
One-sheet summary	Beginners or simple finances	Fast, clear, and uses few formulas	Actual totals must be entered or totaled manually
Summary plus Transactions table	Ongoing monthly use	Automatic category totals, reusable months, and an audit trail	Requires consistent dates, categories, and transaction entry
12-month planning grid	Annual projections	Convenient for recurring monthly estimates and annual totals	Awkward for individual purchases and cash-flow timing
Microsoft budget template	Users who want a ready-made starting point	Faster setup with built-in formulas and sometimes charts	The layout may not fit your categories or conventions
Dedicated budgeting app	Users who want bank synchronization	Less manual entry	May require an account, subscription, or decision about sharing financial data

Microsoft provides Excel budget templates and a personal budget planner. A template is a legitimate shortcut, but understanding the categories, formulas, and transaction rules makes it easier to modify a template safely. Excel for the web may be available through Microsoft’s web experience, while desktop Excel and some features can require a license or Microsoft 365 subscription depending on the product and region.

Special situations to plan for

Biweekly pay

Biweekly pay usually means 26 paychecks per year. Many months have two paychecks, while two months have three. A practical approach is to cover regular bills from the two-paycheck baseline and treat the two extra checks as additional money. Another approach is to divide annual take-home pay by 12, but that can obscure the timing of cash. Neither method is universal; choose the one that matches how you handle the timing difference.

Irregular income

Separate guaranteed or dependable income from uncertain income. Build fixed bills around the conservative baseline, not your best freelance month. When extra income arrives, allocate it deliberately to savings, debt, irregular bills, or another goal rather than allowing the budget to assume it will recur.

Shared household finances

For a couple or household, add a Person column to the Transactions table or use the existing Account column to distinguish people and accounts. Use one shared category list, decide whether all income is take-home income, and record reimbursements consistently. Do not mix personal and shared transactions without a person, account, or category field that explains the difference.

Importing bank or card transactions

Manual entry is easier to control and explain. Importing CSV data can save time, but inspect it before adding it to the table. Common problems include dates interpreted in the wrong order, amounts imported as text, debits represented as negative numbers, inconsistent merchant descriptions, duplicate rows, and credit-card purchases being counted again when the card payment is imported.

Excel can import text and CSV files, but imported columns may need conversion and cleanup. Microsoft’s CSV import guidance is a useful starting point. Always reconcile the imported transactions to the statement before relying on the totals.

Frequently Asked Questions

Should I record a credit-card purchase or the credit-card payment in my Excel budget?

Use one convention, not both. For category analysis, record the purchase when it occurs and treat the later card payment as a transfer or debt payment rather than another expense. For a strict cash-flow budget, record the payment and omit the underlying purchases. Recording both counts the same money twice.

Can one Excel budget track several months?

Yes. Keep all dated transactions in one Excel Table named Transactions and change the first day in Budget!B2. The SUMIFS formulas can filter by category, type, and the selected month without separate worksheets for January through December.

Why does my monthly budget show money left when I run out before payday?

A monthly budget measures the total month, not the timing of paychecks and bills. Add a Cash Flow worksheet with paydays, bill due dates, inflows, outflows, and a running balance. Review the lowest balance during the month, not just the final monthly balance.

Do I need a Microsoft Excel budget template?

No. A template can save setup time, and Microsoft provides budget templates, but a custom one-sheet or two-sheet workbook is easier to understand and adapt when you know how its categories and formulas work.

The Bottom Line

Start with the one-sheet Budget worksheet if you need a working plan today. Enter take-home income, savings, debt, fixed expenses, variable expenses, and irregular costs; use Actual − Planned consistently for variance; and calculate the remaining amount with formulas. When manual actual totals become tedious, add a Transactions table, standardize categories with drop-downs, and use SUMIFS with the month in B2. The result is a reusable budget that shows both whether the month balances and where your money actually went.

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.