Hi, this is Gary with MacMost.com. Let me show you how to create a simple spreadsheet in Mac Numbers
to help you track your personal or home expenses. MacMost is brought to you thanks to a great group
of more than 1000 supporters. Go to MacMost.com/patreon. There you
can read more about it. Join us and get exclusive content and course discounts.
So it is fairly simple to track your expenses in a spreadsheet in Numbers. We're not going
to get too complex here. We're going to keep it as simple as possible. As
a matter of fact I'm not even going to use any formulas at all in this tutorial.
First let's launch Numbers and I'm going to choose a Template here. Now I'm going to just choose the
blank template. We're going to start from scratch. So here's the blank template. Now Number's
documents are made up of different sheets. You start off with just one sheet here. Think of
them as different pages. Inside of each sheet you can have multiple tables. So here I've got
this one sample table that's automatically there in this template. I'm going to use this sample
table to create a journal for my expenses. So everytime I have a new expense I record it here.
Now a mistake that a lot of people make when trying to record expenses is to separate months or
years. Have them in different tables or different sheets. This makes it very hard to do calculations
that go over that time period. In other words if you want to sum up the expenses for several months
or several years, but you've got them in different tables. It's important to keep everything in one
table. So all of your data is just basically going to be in this database. That's the single table
on a single sheet here. Don't worry about the fact that it is going to cross multiple months
and years because we're going to be able to look at individual months and years later on.
So I want to get rid of the Header column here because it doesn't make sense in this context.
Usually this would be for a unique identifier for each row, like each row represented a day or each
row represented an inventory item or something like that. We don't have that here because we
may have multiple expenses on a given day and not every expense is going to have some sort of
ID number or something like that. So let's go over to Format & Table on the right sidebar. I'm going
to change the number of Header columns to zero. Now I still have a Header Row here. This is where
we identify what each column stands for. So I'm going to make the first one Date. The second one
I'm going to have a Description for the expense. The third one I'm going to make this the Amount.
The fourth one I'm going to make a Category. This is our most useful column here. It is really the
whole reason for doing this. So we can look at our expenses and see how much we've spend,
say on housing or clothing or travel. I'm going to have another column here for Miscellaneous
things. So Notes. If I want to include a Note with any one item I can do it here.
I don't need these two columns so I'm going to select both of them, hold down the Shift
Key to select a range here, and I'm going to click right here, this little down arrow there,
and Delete Selected Columns. So now I've just got these. I can resize the columns as well.
I probably need a little bit more space for description. So I can grab the line between
B and C here at the top. The same thing for Notes. I can grab the line to the right of
E and make that bigger as well. Let's give this a name. We'll call this Expenses' Journal. We'll do
the same thing with the sheet here since the only thing in this sheet is this one table.
Now I've got this nice table here and I can start recording things. So, for instance, I
can record that on January 2, 2023 I went grocery shopping and spent $120. We'll call this Category,
Food and there are no notes here. Now let's work on these individual columns here. If we select the
entire column by clicking on the column letter on the top we can now go to Format and then Cell and
set the format for all the cells in this column. So we'll go and have this be Date & Time format
and now we can pick what we want here. So we can, maybe, have it be something like this if we like,
or maybe just something like this. Whatever it is you're used to. For Time we'll select None.
We don't want a specific time here. Just dates. For Column B we'll just leave it as regular
text there. But for Column C we want this to be a dollar amount. So I'm going to go and set that to
Currency, 2 decimal places, I like a thousand separator in case the numbers get that big,
and we've set our currency type. So now if we enter a new number in here,
like say 35 notice how it automatically formats it for that. Then for Category and Notes we'll
leave that just normal automatic so we can type whatever we want in here.
So when we enter a row we want to enter the date, a description and this could be any thing that we
want. So we can get a little more detailed here. For instance I could have specified by the name of
the store, the reason I was going grocery shopping if I wanted to. I would then put the amount and
then for category this is where I want to get really selective. The more categories you have
the less you'll be able to use this spreadsheet to look at your expenses. For instance having
dozens of different categories is going to make it hard to figure out what you spend money on.
So you want to limit it to a small number. Things like maybe food, housing, clothing, travel, and
so on. Maybe keep it to 7 or 10 at most. You'll see why when we start to process the data later.
For now I'm going to go and enter in a whole bunch of sample data here and fill this table
up. So you can see now I've got a ton of data here stretching over two different years. It's
not necessarily in order. It really doesn't have to be. You can put this in any order
that you want. But if you want to sort it, say by date, you can click on that little
down arrow next to the column letter and you can do a Sort. So I'll sort ascending here.
So now it is sorted with the earliest date first. Now this table is sample data so it
simulates what would happen if I had almost 300 rows stretching two years worth of data in here.
So if you're keeping track of things this now works. You can go and look at any specific
item here and figure out how much money you spent on that. But chances are you want this
spreadsheet because you want to be able to form calculations. Like be able to see how
much you spend on food from month to month. There are several ways to do that. One is to
create another table and use formulas in that table to calculate things like sum up all of
the food expenses from a given date range. Another thing you can do is use Filters in
categories. So you can go and filter by date and then use categories to
filter everything by your column actually called Category, to see the totals there.
But by far the easiest way to do it is to just use Pivot Tables. So you've got a Pivot Table
button up here. If you select this table, just any cell in it select it, and you click the Pivot
Table button it will create a new sheet and in that new sheet place a Pivot Table that links to
this table. So let's do that. I'm going to click the Pivot Table button. So we have a second sheet
now. You can see I've got this table here. But I haven't chosen any options yet. If I look over on
the right it takes me to Organize Pivot Options and I can see my columns here. Date, Description,
Amount, and Category. I can also scroll up here and see that I've got nothing selected
for Columns, Rows, and or Values.So let's do that. Let's go and say that we want the Rows
here to be the Date. So I'll drag this down. Then let's say we want the Columns to be the Category.
So I'll drag this down here. We have no date in the middle because we haven't specified anything
to be the Values. So let's drag the Amounts to Values. So now we've got a Pivot Table that shows
each category at the top and each year here on the left. So we could see, for instance, that in
2022 this is how much we spent on food. In 2023 this is how much we spent on food.
Now you may not want everything by year here. Maybe you want it by month. If you look at
the rows here it says Date and it automatically chose year because there are multiple years worth
of values. If you click the i button there you can change this so that it is grouped,
instead of by year, you could say year, month and now you've got every month of each year in
a separate row. So I can see, for each month here, exactly how much I spent on food. So
without creating any formulas we have this handy Pivot Table here. As we update and add new items
in this journal table here, we can go back to this Pivot Sheet and let's call it Categories By Month,
like that. I'll use the same name here for the name of the Pivot Table. Note that when
you do update the expenses you have to select the table here and click this Refresh Button
up here at the top right. That will bring in the latest data when you've added new rows.
Notice you've got some columns on the right. You've got the grand total. So this is how
much you've spent each month. You also have a grand total here at the bottom. So I can see,
for instance, that on Clothing this is how much money I spent over those two years.
I can also Filter this. So I can go here with this Table selected. Go
to Filter and add a filter. Say I want to filter by the date here and I don't
want to select individual dates. Instead I want to go to Date here, select a range,
and let's say I only want to look at 2023. So I can do 1/1/2023 to 12/31/2023 and you can see
now I only have those months showing. So now I've got a great summary of my expenses for the year.
Now you can also create multiple Pivot Tables. So I want to go back to this Journal Table here.
Make sure it is selected and I'm going to create another Pivot Table and for this one I'm going to,
in the columns, put the date and in the rows I'm going to put the category. Then the amounts in
Values here. So now you can see I've got 2022, 2023 as the two columns. Then it looks at all
of the different categories here. So this gives me another way to visualize my data.
One last thing I want to show you if we go back to this Categories By Month table I
can go to Organize, Filter and I've got the Date range set already. So I can get rid of
that with the little Trash button there. I can also add another filter here and let's say I
want to filter by category. Notice the default is Quick Filter and it gives check boxes next
to each of these. So if I simple wanted to go and remove some of these and just have Housing,
Health, Food, and Clothing I can really, easily make this Quick Filter here and it
just shows me those columns. The totals will match the columns I've selected.
So there is a simple way to keep track of your expenses in a spreadsheet in
Numbers. Hope you found this useful. Thanks for watching.