Computer Tutoring Logo
Est. 2002
Menu Button

How to Work Out How Much Money my Business made Last Month in Excel?

Share

Work out how much money did my business make last month?

This Excel accounts tutorial will answer the question: "How much money did my business make last month in Excel?". We will look at:

  • Work out how much money my business made last month
  • Work out how much I spent on something in Excel
  • Answer the "How do I work out how much money I made in a quarter?" question.

Download the starter exercise file should you want to follow along.

This tutorial is one of a series of Accounts Tutorials that you are encouraged to follow. As a result you will learn everything from creating a basic accounts worksheet to a Pivot Table. Also, you will learn many everday Excel hints and tips.

Before we answer the How much money my business made last month in Excel? You will need to apply filters to your data in Excel then use the SUBTOTAL function.

Applying Filters to Excel Data

How to apply filters in Excel Sheet? - 1:50

Before you apply filters or apply the triangles at the top of you data you will first have to ensure that your data is correctly structured.

The top row of your data should be the column headings. Each heading relates to the data below. For example, should you have an order date as a column header all of the rows beneath the column should be dates written as:

01/04/2021

Correct way to enter dates in Excel. Using forward slashes.

The above date is the 4th of April 2021. If you want Excel to be able to separate the date into different parts such as year, quarter or month you need to enter dates as above using the forward slashes to separate the day/month/year or month/day/year depending on where you're from in the world. This way Excel is able extract the month from the date allowing you to select one month. This is an important step in working out how much money your business made last month.

Once you're sure all your data has been entered accurately, then you can apply the filters. (Those little arrows at the top in Excel)

  1. Click once anywhere in your data.
  2. Now from the ribbon click the data tab.
  3. Now click on Filter.

Applying filters in Excel

You can now see the filter triangles applied to your column headings.

Little triangles at the top in Excel

Select only certain customers in Excel

Select only certain customers in Excel - 2:20

If you want to select only certain customers in Excel:

  1. Click on the filter drop down arrow for description.
  2. Now either choose the customer you want to see by checking the checkbox next to the customer name you want to see.
  3. Or, use the search box to search for the customer you want to see.

Select only certain customers in Excel

Now you can see selected customers in Excel. In the above example you are looking at all the jobs you did for Mr Bush.

Working out how much money my business made last month

How to work out how much money my business made last month in Excel? - 4:58

We're nearly there, so hangon in there. Before we can see how much we made last month we will also need to use the subtotal function.

Try the following:

  1. Click where you would like your result displayed.
    (In the video tutorial I placed the total at the top of the spreadsheet. This makes it easier to access as I don't need to scroll down to the bottom of the data to see my result.)
  2. Now type in the subtotal function.
    =SUBTOTAL(109,F:F)

Using SUBTOTAL function in Excel

The 109 in the first argument of the SUBTOTAL function represents the function you want to use. The 109 in this example represents SUM. However, as you type the formula, the helper text shows other functions that are available including Average, Max and Min. The F:F part of the formula in the second argument is the column that you want to sum. In the above screenshot, you can see that the subtotal function is set to work with the range D7:D5000.

For more details on the SUBTOTAL function see the Microsoft Excel More Functions site.

Now you can filter the totals and find out how much money your business made last month. Do the following:

  1. Click on the drop down arrow for any date column.
  2. Uncheck the Select All check box.
  3. Expand the Year filter by clicking on the plus to the left of the year.
  4. Check the month previous to the current month. So if the current month is March, click February.

Work out how much money your business made last month.

Now when you look at the SUBTOTAL function, you added from the previous step, at the top of the page you will see that the total reflects the fact that you have filtered the data to show only lasts months figures. That's how you can work out how much money your business made last month in Excel.

Other Excel Accounts Tutorials






Join mailing list!

Sign up to receive our monthly newsletter including special promotions, hints & tips, and the latest Computer Tutoring news!

Subscribe