How to work out how much money my business made last month in Excel? How can I work out how much
I spent on something in Excel? and how do I work out how much money I made in a quarter?
I'm Simon from Computer Tutoring a real life Excel trainer. So let's answer these questions.
Great so this is the fifth in a series of accounts tutorials in Excel
during which we have created this spreadsheet, so if you want to follow along and you haven't
done the previous tutorials then you're more than welcome to download it. I'll
give you the link above and give you the link down below. So before I can answer the
How can I work out how much money my business made last month in Excel? question
first I will need to know how to apply filters in an excel spreadsheet. Filters are adding the little
triangles at the top in excel. First I will need to add a row in the spreadsheet, what I'm going to do
is I'm going to right click on row 5 here because I want to add the row above, I'm going to go down
to and click on insert and you can see that I have a new row added into my spreadsheet here,
now I'll change the background color of the row in my spreadsheet so let me select from cells A5
across to I5 I'm then going to go up to the home tab on the top, and then choose the drop down menu
here for the fill color, shows us the background color of the cell and I'm going to choose a black,
and then I'm just going to adjust the height of the row in excel by dragging that up. So
let's add the little arrows at the top in excel. So click once anywhere in your data ,
so you can see here I've just clicked on this VAT rate here, and then we're going to go up
to the data tab here at the top, and then we're going to click on this filter button just here,
and as you can see now we've added these little arrows at the top so we've applied filters to
our excel spreadsheet. So now that we've applied filters in excel what do we get? Well now we can
select only certain customers in excel. So say for example I want to just look at Mr Bush roses, so
I'm going to click on the drop down list that's now appeared under description and then if I go
down to I get a series of check boxes here now I'm only interested in Mr Bush roses, so I'm going to
untick the select all and click on Mr Bush roses and then click on OK at the bottom and now you can
see I'm only seeing a certain customer in excel. Now I want to only view customers that have spent
over a certain amount in excel, so let's go down here and I'm going to clear the filter
and I'm going to click on this drop down list in the received column
let me show you clearly, I'm going to go down to number filters here, and go and choose
greater than or equal to, in the box that appears I'm going to type in
100. So in this way it's only going to show me the customers that have spent 100 or more per
job, click on OK and now I can see that's that I think there's only one customer here I've
got which is Mrs. Smith. Now say you want to know how to add multiple filters in excel.
Well let's do that, so we're gonna just remove the filter from received, just clear so I click on
received, and then clicking on clear filter from received here, now I'm interested in all of the
expenses, so I'm just going to click on the drop down list for expense and remove the blanks,
and click on ok but I want to know all of the expenses that were VAT exempt,
so I click on the drop down list for VAT and then I'm going to untick 5 and 20 and then click on OK
and now I have multiple filters in excel. I can see all of the expenses that were VAT exempt, so
I can see it's my lunch! Great so now I want to clear them, so I could go down to this drop-down
list here, and then I could click on clear from VAT rate and do the same with expense, but say you
want to know how to clear more than one filter at the same time in excel? This is how you do it.
Make sure that you're on the data tab here at the top, d then there is a 'clear' button just to the
right of the filter button, when you click on the clear button it clears all the filters from excel.
Now we need to answer How to work out how much money my business made last month in excel?
As you can see when I filter the data the totals at the top are not changing, so if I just
say for instance I go to gloves here, and click on that drop down list, I can see that these filters
are not changing, so we need to know 'How to add totals so that they change when the data
is filtered?' To do that we will be using subtotal function in excel. Okay so let's swap the sum
function for the sub total function, so I can work out how much money my business made last month.
So let's go into E2 and I'm getting close, so you can see. So there's the sum function that's not
taking into account the filters, so I'm going to double click on that and type in the word Subtotal
so subtotal and the brackets, after the bracket I'm going to type in a 9. Now this 9 here if I
go back to it represents the function that you want to run, so I'm interested in sum which is
number nine so I'm typing in 9 here, typing a comma and then the rest of the function is fine ,
press enter, and we can see 540 there. Okay what I'm just going to do is I'm just going to remove
or clear the filter from description here, and you can see that my expenses has updated.
So this function here, takes into account the description. So let's
apply the subtotal function to the other totals. So let's first do VAT to reclaim,
let's double click on SUM, type in Subtotal, click after the brackets, 9
press enter, do the same with Gross, double click on Gross, double click on Sum type in Subtotal,
click after the bracket, type in a 9 and a comma. That's great, and we'll do the VAT to pay as well,
go down to Subtotal press tab there we go that's good, and then type in 9 representing the sum,
that's great, and the last one is this Running total I need to update.
So I'm going to go in here, type in Subtotal again add the 9 in,
and the same with this sum here, subtotal and then add in the 9 after the bracket.
Okay let me just zoom back here, okay that's great. What I'm just going to do is auto
fill this one, go down to the bottom right hand corner and make this running total
formula apply to the entire column by double clicking. That's great, so now I can
have running totals for this. So now if I want to know 'How much money my business made last month
in excel?' This is what I do! I go into dates just here, click on the drop down list for dates.
If I scroll down here, today's the 3rd March, so what I'm going to do is untick all the dates,
expand 2021, and then I'm going to going to choose February which is last month. Click on OK.
So now you know 'how to add totals so that they change
when the data is filtered?' Now let's answer the question 'How can I work out how much I
spent on something in excel?' This is how you do it. I can go down, let's just clear the filter
from date just here, and then if I want to go down to and choose, let's see how much we spent on
gloves, you can see I've got gloves selected and click on ok, I can look at the top here and I've
in entirety spent £540 on gloves. And what about the question 'how do I work out how much
money I made in a quarter?' Well for that we're gonna just go back , let me just clear the
description from the description filter. I'm gonna go back to Date here, let's say it was the
last quarter of last year 2020, so let's clear all the dates and I can scroll down to the
bottom and I can choose October November December click on ok, and now at the top here I've got here
the gross amount that I've taken last year. If I want to know how much profit, I can just
simply do a formula here that takes the expenses and adds together the gross to see how much i've
got just here happy to see that the running total amounts and this balance there is exactly the same!
There you go. Now you know how to add totals so that they change when data
is filtered. You know also how to work out how much money I made last month in excel '.
Now the next tutorial will introduce pivot tables which will show you how to work out 'how much money
my business makes each month?' in a report. You can see a list of January February March April so
make sure that you subscribe, you click on that notifications button so you don't miss a thing!
If you've got anything out of this excel accounts tutorial then please
give us a thumbs up it means so much ! Reach out to us as well on Instagram Facebook and Twitter we'd
love to hear from you! And if there's any suggestions that you have in the way that
you deal with accounts or you think that there's something I've missed out then please mention them
in the comments below! So I just want to say take take care guys and thank you so much for watching.