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.

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

    Jan E. Wille
    2 years ago

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

    2 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
    2 years ago

    Ingenious!

    Eric
    2 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.

    2 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
    2 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
    2 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.

    2 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
    2 years ago

    Gary, Thank you.

    George Rubin
    2 years ago

    Just exactly what I needed to know, Thanks Gary

    nick
    2 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.

    2 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
    2 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.

    2 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
    2 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!

    2 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.