The Ultimate Guide: Master These 7 Microsoft Excel Features for Superior Financial Planning

Quick Links

• Add and Subtract Income and Expenses: SUM
• Add Certain Expenses: SUMIF
• Find the Highest or Lowest Expense: MIN or MAX
• Count Expenses or Payments: COUNT
• See How Many Days to Pay: DAYS
• See How Many Business Days to Pay: NETWORKDAYS
• View the Current Date: TODAY

With all of the tasks you can use Microsoft Excel for, one of the most popular is tracking finances. If you’d like to create a sheet to manage your budget, we’ll show you several basic functions you need .

What makes Excel such a terrific tool for managing finances are its functions. You can use formulas with built-in functions to add your bills, subtract them from your income, see your highest expenses, and more.

1. Add and Subtract Income and Expenses: SUM

The SUM function is one that you’ll use the most when it comes to finances in Excel. It allows you to add numbers, cells that contain numbers, or a combination of both. You can use the SUM formula in your budget for totaling your income and adding your expenses.

Related: How to Add Numbers in Microsoft Excel

The syntax is

    `SUM(value1, value2,...)`

where value1 is required and

    `value2`

is optional. You can add the formula manually or use a handy button to insert it.

To total up your income for the year, select the cell where you want the result. Click the AutoSum button in the Editing section on the Home tab.

Confirm or edit the cell range that displays and press Enter or Return.

Alternatively, you can type the following formula replacing the cell references with your own:

=SUM(C5:N5)

You can also use the SUM function to subtract values in Excel . You’ll likely want to subtract your total expenses from your total income to see how much you have left.

Go to the cell where you want the result and enter the following replacing the cell references with your own:

=SUM(C7-C17)

Alternatively, you can enter the following using the minus sign operator, without the SUM function involved:

= C7-C17

2. Add Certain Expenses: SUMIF

Similar to SUM, the SUMIF function allows you to add numbers that meet specific criteria. You can use this to add the amounts due for your loans or maybe those bills your roommate pays.

The syntax is SUMIF(cell_range, criteria, sum_range) and the first two arguments are required. The third argument, sum_range, is useful if you want to add numbers in one range of cells that correspond to criteria in another range.

Here, we want to add expenses listed in cells C10 through C17 only if those amounts are labeled Loan in cells B10 through B17.

=SUMIF(B10:B17,”Loan”,C10:C17)

You can use the SUMIF formula to match criteria for text as we did here, but also for numbers.

In this example, we’ll add the expenses in cells D10 through D17 but only those that are over $400.

=SUMIF(C10:C17,”>400”)

3. Find the Highest or Lowest Expense: MIN or MAX

When you’re keeping track of bills in your budget, you might want to see the highest values. This allows you to adjust for upcoming months or years. MAX shows you the highest value while MIN shows you the lowest.

The syntax for each is MAX(value1, value2...) and MIN(value1, value2...) where the values can be numbers or cell ranges. Plus, you can enter the formula manually or use the SUM drop-down arrow and select it.

To see the highest amount for an expense during the year, select the cell where you want the result. Click the arrow next to AutoSum in the Editing section on the Home tab and pick “MAX.”

Confirm or edit the range of cells in the formula and press Enter or Return.

To enter the formula manually, use the following replacing the cell references with your own:

=MAX(C12:N12)

You can use MIN the same way to find the lowest value. Select MIN from the SUM drop-down list or enter the formula manually.

=MIN(C12:N12)

4. Count Expenses or Payments: COUNT

Want to know how many bills you pay each month or the number of paychecks you receive throughout the year? Using the COUNT function , you can count how many cells contain numbers.

Related: How to Count Cells in Microsoft Excel

The syntax is COUNT(value1, value2,...) where value1 is required. Like SUM, you can use a button or manually enter the formula.

Select the cell where you want the result. Click the arrow next to AutoSum in the Editing section on the Home tab and pick “Count Numbers.”

Confirm or edit the cell range that displays and press Enter or Return.

Alternatively, you can type the following formula replacing the cell references with your own:

=COUNT(C10:C17)

5. See How Many Days to Pay: DAYS

If part of your budget is seeing how many days you have between when you get paid and when a bill or loan payment is due, the DAYS function does exactly that.

The syntax is DAYS(end_date, start_date) with both arguments required. You can use dates or cell references.

To find the number of days between our end date (due date) in cell B3 and start date (pay day) in cell A3, we would use this formula:

=DAYS(B3,A3)

To find the number of days between specific dates rather than cell references, you would use the following formula. Enclose the dates in quotes and remember that the end date comes first:

=DAYS(“1-DEC-2022”,”1-MAR-2022”)

6. See How Many Business Days to Pay: NETWORKDAYS

Similar to the DAYS function, NETWORKDAYS counts the number of work (or business) days between two dates. This resulting number excludes weekends and recognized holidays.

The syntax is NETWORKDAYS(start_date, end_date, holidays) where the dates are required and holidays is optional to include a cell range with exclusions.

To find the number of business days between our start date (pay day) in cell A3 and our end date (due date) in cell B3, we would use this formula:

=NETWORKDAYS(A3,B3)

7. View the Current Date: TODAY

As you work on your budget, the current date is important. Without looking at the calendar, you can display the current date in your sheet and see it updated each time you open the workbook.

The syntax is TODAY() with no arguments. Simply enter this formula into the cell and hit Enter or Return.

=TODAY()

If you want to create your own budget in Excel rather than use a template, these functions have you off to a great start. You might also take a look at how to use Money in Excel to track your bank accounts and loan balances. (Unfortunately, Microsoft has announced it will shut down “Money in Excel” after June 2023. The company now recommends Tiller instead.)

Related: How to Use Microsoft’s “Money in Excel” to Manage Your Finances

Also read: