This Excel accounts tutorial will answer the question: "How much money did my business make last month in Excel?". We will look at:
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.
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:
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)
You can now see the filter triangles applied to your column headings.
If you want to 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.
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:
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:
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.