8/8/17
8:03 am

Monthly Expenses On a Single Spreadsheet

Many people make the mistake of splitting Numbers data across multiple spreadsheets when it should be kept in one long table. You can use formulas and filters to keep your data in a single table and still easily show only a subset of the data, such as a monthly expense report.

You can also watch this video at YouTube (but with ads).

Video Transcript: I see this a lot in Numbers. People will have separate tables, in fact entire separate sheets, for each month of the year when keeping track of things like expenses. This is handy when you need to produce a monthly report, maybe PDF or printout for each month. But it's not so great when you need to relate these items across months like totals for the entire year or, you know, tracking expenses over a few months by item type. That kind of thing. Also if you need to update these tables in someway you have to go and do it for each month. If you have three or four years worth it takes a long time to do that. 
A better way to do it is to have a spreadsheet like this where everything is in one long table. You can keep adding to this. This can go and be thousands and thousands of rows long after several years. But the problem is what happens when you need to produce a monthly report. How can you do that? 
So you've got, here at the top, month and year. I want to just do February of 2017 but I see everything from when I started keeping records all the way to the present day. How do I just get this to show February. Well you could do filters. So you could go in here, click on the Action button for column A, and then Filter Table, and select every item that is in that specific month. That takes a long time to select them all and then deselect them and it's not the proper way to do it. 
A better way is to create a new column. So I'm going to create another column here. So Add column before B and here I'm going to put a formula. This formula is going to compare two things. So I'm going to do And, to compare more than one thing, and the first thing I'm going to compare is the year from this cell, whether it's equal to this number. I'm going to set it so that it preserves own column. So that it always goes from this specific cell even when I get further down in here. 
Then the second thing I want to compare is whether the month here is equal to this. But the month will actually return a number, 1 to 12. In fact when I type February in here it interprets that as a date. So this is actually interpreted as February 1st of the current year. So I want to just basically extract the month from that as well. The month from this cell and I'm going to also preserve row, preserve column and I'll close out the And statement there and I will get False because that's in January, not February. If I copy this and then paste it all the way down here you can see I only get True for the month of February and False for everything else. 
So now I can filter. I can say Filter Table only on Trues. You can see I only get February. The great thing about this is if I  change this to January then the Trues only are for January and this changes there automatically. In addition, I can get rid of this column here by hiding it. Now I don't see it anymore.  So now it's like magic. I just type March in here and it only shows me March 2017 dates that match over here. 
Now to get the sum to work here at the bottom, this sum here is just a formula. It's the sum of everything in this column whether it's filtered or not. I want to use sum if. So I will do sumif and I will say if column B, even though it's hidden I can still type B, is True then include column E in the Cost. Now I only get the visible ones because this formula is basically looking for true and this filter that I had on column B is also looking for true. They're two different things but they are both looking for true values in that B column that is now hidden. So when I change this to February it's now only showing me February and the sumif is now only acting on those same ones. 
So now it works. I can change it for any month and any year that I want and it only shows me those items that are there. If I want to enter new data I have to turn filtering off. So I go over to the right here to Sort and Filter to Filter. I've got to select the table  and then I just uncheck Filters. That means the filter in column B is not deleted. It's still there. If I hit the Trashcan it will delete it and I have to recreate it every time. But it just turns it off and turning it off means now that I can enter in new data, so I can enter in something else for April, and now I can turn Filters back on again and change this to April to see that.
So now I have a fully functioning spreadsheet that's one sheet with just this simple little table here at the top to tell me the month and year. Then this big table here which I can just keep growing and I can keep doing all sorts of things. I can add another Footer column here. So I can extend this out. Let me turn Filters off and let me extend this to show another Footer column there at the bottom. I want to move that up there. This I'm going to do an actual sum of E. So now I've got that's the monthly total and this is the complete total and I can do more work to actually make the total for the year. That kind of thing. 
But you get the basic idea for how to actually get a Table like this to fit your needs if you need to keep track of something over the period of many months and many years but you only need to show in a report what goes on during a given month.

Comments: 16 Responses to “Monthly Expenses On a Single Spreadsheet”

Jan E. Wille

7 years ago

Hi. Useful tip, but what if I need to show more than one month at a time - two months, a quarter etc.?

Gary Rosenzweig

7 years ago

Jan: The formula to set the value in column B to TRUE or FALSE just has to get more complex. For instance, instead of comparing the month and year, it could look at two dates and see if the date falls within that range. If you are not good with formulas you could always simplify it and make a column for "Quarter" and put values in there like "Q1 2017" and then filter by that value.

Jean-Claude

7 years ago

Ingenious!

Eric

7 years ago

Could there be a chart that dynamically changed depending on the filter criteria? For example, a pie chart showing the relative amounts for the different types of expense.

Gary Rosenzweig

7 years ago

Eric: Yes, depending on what you want. Pie charts will react to Filters. Try it and you'll see. Experiment and play around.

Eric

7 years ago

Very good! I see how to achieve the dynamic chart. So, using your example, if, say, a pie chart of Cost was wanted: with the filter switched off select all the entries in the Cost column as the data for the chart; create the chart; switch the filter back on; et voila!

Thanks Gary. Perhaps you could extend your tutorial to demonstrate this to other followers.

Pete Schirling

7 years ago

I do not want to rely on my typing skills in order to enter the type of expense in a new entry. I have a list of categories in a separate table. How can I pick from that list and enter into the expense table. This will serve to allow for filtering over months or years by category such as "electricity" or "Dining out" but avoids missing items because of typing errors.

Gary Rosenzweig

7 years ago

Pete: You can't have the entry pull from a column in another table. But you can use another feature in Numbers, if you start typing it will pick from entries already available. So as soon as you start typing "D" it should show you options that include other entries that start with D, such as "Dining Out." Select that one and avoid making a mistake.

Pete Schirling

7 years ago

Gary, Thank you.

George Rubin

7 years ago

Just exactly what I needed to know, Thanks Gary

nick

7 years ago

hi Gary
hope this is "on topic" enough :)
is there a way to input data into Numbers by voice dictation? It would be neat if we could dictate a bunch of expenses into the sheet as we sort through receipts etc.

Gary Rosenzweig

7 years ago

nick: Sure, you could do it. Did you try it? I just did and the normal dictation function worked fine in Numbers. I had to hit return to go to the next row, but I was able to dictate some numbers into a table. It is a bit precarious as a mistake in an entry is probably hard to spot, unlike a wrong word in a sentence. So I don't think it will save you much time.

Michelle

7 years ago

Hi Gary,

I love the look of this and wondered if you have done the same in Excel? I've tried googling using the AND formula, as well as the MONTH and YEAR formulas but without success (meaning I've found explanations of the formulas but none seem to be working for me). Thanks.

Gary Rosenzweig

7 years ago

Michelle: It seems to work fine in Excel. Try what I did: get it working in Numbers, then export to Excel. Then look at the exported version in Excel. Looks like AND, MONTH and YEAR all work the same.

Michelle

7 years ago

I cannot work out why it won't work for me. I have replicated this in Numbers but the only result returned is 'False' although I have triple-checked the date and year in both places. My formula looks exactly the same as yours but I just can't get it to return 'True'. I had the same issue with Excel - everything looked right - formulas, formatting, cell numbers but always 'false'. It's driving me nuts!

Gary Rosenzweig

7 years ago

FYI -- for others interested in the solution to Michelle's problem. The issue was that the cell with the year (2017) was formatted as a date, so it looked like 2017, but it was really 1/1/2017 12:00:00 AM. So this didn't match in the formula. The solutions are to either force the cell to be numeric format, not date, or to enclose the reference to that cell in another YEAR() function.

Comments Closed.

Monthly Expenses On a Single Spreadsheet

Comments: 16 Responses to “Monthly Expenses On a Single Spreadsheet”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information