Track Your Expenses in Mac Numbers

You can use a simple spreadsheet in Numbers to track your home or personal expenses. No formulas or calculations are required. Just record your expenses and then use a simple Pivot Table to see a summary of your expenses by category over months or years. Follow this step-by-step tutorial.

Video Transcript: 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.

Comments: 18 Responses to “Track Your Expenses in Mac Numbers”

John Carter

9 months ago

Excellent overview. Thanks.

Sheldon

Thanks bunches

Daniela

Thanks so much, I ALWAYS learn something new

Rudy

After decades of Excel, I switched to Numbers after taking your course. This tutorial finally unlocks the mystery of Pivot Tables for me. Very useful and a real time saver!

Sherrill

Thank you for explaining how to track expenses. The question I have is there a similar template that will include income and expenses? This template will show how much there are in expenses, and how much they are in income then the balance.

Will

Great stuff! V helpful.

Gary Rosenzweig

Sherrill: You could do that in a separate "Income" sheet with its own pivot table, I suppose. That actually how I do it.

William Robinson

Just watched your instruction on use of pivot tables in Numbers and liked the simplicity. Did a practice spreadsheet and with your guidance found it strait forward and simple but rich with possible applications. I had not used this function prior to your bringing it to my attention, so thanks for posting it.

Graeme CREED

Great introduction to the most powerful tool in Numbers.
My only comment would be to introduce a "Drop-down" box for the category to both limit the categories and to avoid data entry mistakes. It also reminds the user what categories there are to select from.
Keep up the great work Gary and thank you for sharing your knowledge.

James Overstreet

Very helpful. Thank you.

John Weiner

Simple, clear and to the point...example illustrating use of a pivot table very useful.

Susan Moreno

This video was much better for me as I watched the one on Youtube and, I was having trouble finding the equal key (the formula) to get totals. The Pivot table is much easier. Thank you for showing an easier way to add the totals. I am not very savvy when it comes to technical things on the computer.

Jamey

HI. How do I change the autofill for misspelled text. I would like to "clean out" misspelled or text I do not want to come up as an auto fill option. Thanks in advance....you are the best! JP

Jamey: Review the data in that column. It only suggests things that already exist there. So if you spelled "travel" as "travvel" then look for that cell and correct it there.

John MacKenzie

8 months ago

Thanks Gary, you make things look so simple. I'll try this out for my household expenses . Keep up the good work and a Happy New Year to you.

Tim Srhn

Thanks Gary for this trac your expenses tutorial. Works awesome 👍👍

Is there a way on numbers to do a data entry on 1 sheet, and have multiple other sheets using the same data just set on different filters?? For instance a income expense sheet where you enter all expenses, and on the other sheets you and can view It by how you filtered it, a pivot table only gives me the counts or max, not what i am looking for…

As: You could do some really complex things to accomplish this. But it will take a lot of work and then more work to maintain it as you make changes. Instead, just use the filters as they are now to filter your main table. They aren't hard to work with, switch on/off, change, etc.

Track Your Expenses in Mac Numbers

Comments: 18 Responses to “Track Your Expenses in Mac Numbers”

Leave a New Comment Related to "Track Your Expenses in Mac Numbers"

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information