How to Calculate Monthly Expenses in Excel (Formula & Examples)
Managing monthly expenses becomes much easier when you have a clear record of where your money goes. Whether you are tracking household bills, grocery purchases, rent, employee salaries, transportation costs, or business expenses, Microsoft Excel can help you organise everything in one place.
Instead of calculating expenses manually at the end of every month, you can use simple Excel formulas to calculate daily spending, category-wise expenses, and total monthly costs automatically.
For example, imagine you spend money on groceries, electricity, rent, internet, fuel, and other daily needs. By entering each expense into an Excel sheet, you can instantly see how much you have spent during the month and which category is taking the biggest share of your budget.
In this guide, you will learn how to calculate monthly expenses in Excel using simple formulas, practical examples, and an easy-to-follow expense tracking table.
Why Should You Track Monthly Expenses in Excel?
Tracking expenses is not just about knowing how much money you spend. It also helps you understand your spending habits and make better financial decisions.
Here are some benefits of using Excel for monthly expense calculations:
Automatic calculations: Excel adds your expenses without requiring manual calculations.
Better budgeting: You can compare your income with your monthly spending.
Category-wise tracking: Separate expenses into groceries, rent, electricity, travel, and other categories.
Identify unnecessary spending: Find areas where you can reduce costs.
Monthly financial planning: Use previous expense records to prepare a realistic budget.
Excel is particularly useful for individuals, families, freelancers, and small business owners who want a simple way to manage their money.
Create a Monthly Expense Sheet in Excel
Before using formulas, you need to create a basic expense tracking table.
Open Microsoft Excel and create a blank worksheet. Add the following column headings:
Date | Expense Category | Description | Amount |
|---|---|---|---|
01-Nov-2026 | Grocery | Monthly groceries | 3,500 |
03-Nov-2026 | Electricity | Electricity bill | 1,200 |
05-Nov-2026 | Transport | Fuel expenses | 2,000 |
08-Nov-2026 | Internet | Internet bill | 800 |
10-Nov-2026 | Rent | House rent | 8,000 |
15-Nov-2026 | Shopping | Household items | 1,500 |
20-Nov-2026 | Medical | Medicine expenses | 700 |
Your table should look something like this:
In this example:
Column A contains the expense date.
Column B contains the expense category.
Column C describes the purchase or payment.
Column D contains the amount spent.
Enter your daily expenses in separate rows. You can continue adding new entries throughout the month.
Tip: Format the Amount column as Currency or Accounting so that all financial values are displayed consistently.
Calculate Total Monthly Expenses Using SUM Formula
The easiest way to calculate monthly expenses in Excel is by using the SUM function.
The SUM formula adds all the numbers within a selected range.
Excel Formula:
=SUM(D2:D8)Here:
SUMis the Excel function used to add numbers.D2:D8represents the cells containing your expenses.The result displays your total spending.
Using the example above:
Expense Category | Amount |
|---|---|
Grocery | 3,500 |
Electricity | 1,200 |
Transport | 2,000 |
Internet | 800 |
Rent | 8,000 |
Shopping | 1,500 |
Medical | 700 |
Total Expenses | 17,700 |
If you add another expense to the sheet, update the formula range or use a larger range such as:
=SUM(D2:D100)This formula will calculate expenses from rows 2 to 100.
Calculate Monthly Expenses Category-Wise Using SUMIF
Sometimes, you may want to know how much you spend on groceries, transportation, electricity, or rent separately.
For this purpose, Excel provides the SUMIF function.
Excel Formula:
=SUMIF(B2:B100,"Grocery",D2:D100)This formula adds all amounts in column D where the corresponding category in column B is Grocery.
For example:
Expense Category | Monthly Amount |
|---|---|
Grocery | ₹3,500 |
Electricity | ₹1,200 |
Transport | ₹2,000 |
Internet | ₹800 |
Rent | ₹8,000 |
Shopping | ₹1,500 |
Medical | ₹700 |
You can use the following formula for each category:
=SUMIF(B2:B100,"Electricity",D2:D100)=SUMIF(B2:B100,"Transport",D2:D100)=SUMIF(B2:B100,"Rent",D2:D100)This makes it easier to identify which categories consume most of your monthly budget.
Practical tip: Instead of typing category names directly into formulas, you can enter the category name in another cell and reference it.
For example:
=SUMIF($B$2:$B$100,F2,$D$2:$D$100)Here, F2 contains the expense category you want to calculate.
Calculate Expenses for a Specific Month Using SUMIFS
If your Excel sheet contains expenses from multiple months, the SUMIFS function can calculate spending for a particular month.
For example, suppose you have recorded expenses from January to December in the same worksheet. You want to calculate only November's expenses.
Assume:
Column A contains dates.
Column D contains expense amounts.
Cell F2 contains the first date of the month, such as
01-Nov-2026.
Excel Formula:
=SUMIFS(D2:D100,A2:A100,">="&F2,A2:A100,"<"&EDATE(F2,1))This formula calculates expenses from the beginning of November up to, but not including, the beginning of December.
The advantage of this method is that you don't need to change the formula every month. Simply update the date in cell F2.
It also works correctly when your records contain dates from several different months.
Calculate Daily Average Expenses in Excel
Knowing your total monthly expenses is useful, but calculating your average daily spending can provide additional insights.
For example, if your total monthly expenses are ₹17,700 and you want to calculate the average daily expense for a 30-day month, use this formula:
=17700/30You can also calculate the average directly from your expense data:
=SUM(D2:D100)/30If you want to calculate the average of the recorded expense entries rather than the average per calendar day, use:
=AVERAGE(D2:D100)These two formulas serve different purposes:
SUM/30calculates average spending per calendar day in a 30-day period.AVERAGEcalculates the average amount per recorded transaction.
Choose the formula that matches your budgeting requirements.
Calculate Monthly Savings After Expenses
If you also record your monthly income, Excel can calculate how much money remains after paying all your expenses.
For example:
Description | Amount |
|---|---|
Monthly Income | ₹30,000 |
Total Monthly Expenses | ₹17,700 |
Remaining Savings | ₹12,300 |
Suppose your monthly income is entered in cell F2 and your total expenses are calculated in F3.
Use this formula:
=F2-F3The result will show your remaining money after expenses.
If your expenses are higher than your income, the result will be negative. This indicates that you are spending more than you earn.
You can use this information to adjust your monthly budget and reduce unnecessary costs.
Create an Automatic Monthly Expense Summary
Once you have entered your daily expenses, you can create a separate summary section to see your financial position.
Here is an example of a simple monthly expense summary:
Description | Amount |
|---|---|
Monthly Income | ₹30,000 |
Total Expenses | ₹17,700 |
Remaining Balance | ₹12,300 |
Average Daily Expense | ₹590 |
Total Expense Entries | 7 |
Use these formulas:
Calculation | Excel Formula |
|---|---|
Total Expenses |
|
Remaining Balance |
|
Average Daily Expense (30 days) |
|
Number of Expense Entries |
|
This summary updates automatically whenever you add or change expense amounts in your main table.
Make Your Monthly Expense Sheet More Useful
A basic expense tracker is enough to get started, but a few additional Excel features can make it more effective.
Use dropdown lists for expense categories
Create a dropdown list containing categories such as Grocery, Rent, Electricity, Transport, Shopping, Medical, and Internet. This prevents spelling differences and makes category-wise calculations more accurate.
Apply Conditional Formatting
Highlight expenses above a certain amount. For example, you can use conditional formatting to highlight transactions greater than ₹5,000.
Create a monthly expense chart
Select your category-wise summary and insert a pie chart or column chart. This helps you understand how your money is distributed.
Convert your expense data into an Excel Table
Select your expense records and press Ctrl + T on Windows or Command + T on Mac (depending on your Excel version and shortcuts). An Excel Table makes it easier to manage records and extend formulas as new entries are added.
Common Mistakes to Avoid When Calculating Monthly Expenses
Even a simple expense tracker can produce incorrect results if the data is not organised properly.
Watch out for these common mistakes:
Entering dates as text: Excel may not recognise text entries as actual dates, affecting monthly calculations.
Using inconsistent category names: For example, writing Grocery in one row and Groceries in another can create separate category totals.
Forgetting small expenses: Tea, snacks, parking, and other small payments can add up over time.
Including income as an expense: Keep income and expense records in separate columns or tables.
Not updating the sheet: Enter expenses regularly instead of trying to remember everything at the end of the month.
A few minutes of daily record-keeping can save you considerable time when reviewing your monthly finances.
Final Thoughts
Calculating monthly expenses in Excel is a simple way to understand your spending habits and manage your money more effectively. With functions such as SUM, SUMIF, SUMIFS, AVERAGE, and COUNT, you can automate most of your expense calculations without needing advanced Excel knowledge.
Start with a basic expense table, record every payment, and create a monthly summary. As your records grow, you can add category-wise reports, charts, and savings calculations.
The most important thing is consistency. An expense tracker is only useful when you update it regularly and use the information to make better financial decisions.
Frequently Asked Questions (FAQs)
1. How do I calculate total monthly expenses in Excel?
Use =SUM(D2:D100) to add all expense amounts recorded in column D.
2. Which Excel formula is best for tracking monthly expenses?
The SUM function is best for calculating total expenses. SUMIF and SUMIFS are useful for category-wise and month-wise calculations.
3. How can I calculate category-wise expenses in Excel?
Use =SUMIF(B2:B100,"Grocery",D2:D100) to calculate all grocery expenses.
4. Can Excel automatically calculate monthly expenses?
Yes. Once you enter your expenses and set up formulas, Excel updates the totals automatically whenever you add or change records.
5. Can I calculate monthly savings in Excel?
Yes. Subtract total monthly expenses from monthly income using =Income-Expenses or the relevant cell references.



COMMENTS