How to Calculate Monthly Expenses in Excel (Formula & Examples)

Learn how to calculate monthly expenses in Excel using SUM, SUMIF, and SUMIFS formulas. Track spending, manage budgets, and calculate monthly savings
How to Calculate Monthly Expenses in Excel (Formula & Examples)

   Learn how to calculate monthly expenses in Excel with simple formulas and practical examples. Discover how to track daily spending, calculate category-wise expenses, create monthly summaries, and manage your budget more effectively.

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:

How to Calculate Monthly Expenses in Excel (Formula & Examples)

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:

  • SUM is the Excel function used to add numbers.

  • D2:D8 represents 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

Total Monthly 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/30
Average Daily Expense: ₹590

You can also calculate the average directly from your expense data:

=SUM(D2:D100)/30

If 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/30 calculates average spending per calendar day in a 30-day period.

  • AVERAGE calculates 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-F3

The 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

=SUM(D2:D100)

Remaining Balance

=F2-F3

Average Daily Expense (30 days)

=F3/30

Number of Expense Entries

=COUNT(D2:D100)

This summary updates automatically whenever you add or change expense amounts in your main table. 

How to Calculate Monthly Expenses in Excel (Formula & Examples)

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

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content