You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers spreadsheet for tracking expense categories

How would I set up a Numbers spreadsheet so that I can run end of the year reports for tracking personal expense categories (donations, travel, etc) for taxes? I want to use it to replace Quickbooks for personal use. Don't really need monthly budgets but will need to pull out individual categories.

Very unfamiliar with Numbers.

iPhone 4, Need advice on 16g or 32g.

Posted on Sep 30, 2018 11:29 AM

Reply
Question marked as Top-ranking reply

Posted on Sep 30, 2018 3:35 PM

Hi Laura,


If you aren't that familiar with Numbers and don't want to reinvent the wheel then I highly recommend that you have a look at the 'Personal Budget' template at File > New in your menu. It does exactly what you describe. You enter the transactions in the Transactions table and the expenses are summarized by category on the first sheet.


It will take you just a few minutes to customize it for categories that match what you need for your year-end report. You won't need to change any formulas.


In the Category column of the Summary by Category table on the first sheet simply replace the existing categories with the categories that you need to track.


User uploaded file


Then in column C of the Transactions table on the second sheet change the items in the Pop-Up Menu to match the categories you have entered in column A of the Summary by Category table on the first sheet. The spelling has to be the same in both places.


User uploaded file


To do this first change the Pop-Up Menu choices in cell C2 only. You do that in the panel at the right, as shown in the screenshot. Then with cell C2 selected choose the blank at the bottom of the list.


User uploaded file


That sets the Pop-Up Menu to a blank for C2.


Now, with C2 still selected, type command-c to copy it. Then select all the cells below C2 in column C and type command-v to paste. Now the Pop-Up Menu with your new categories will be in each cell in that column.


Whenever you enter a new transaction just choose the category you want from the Pop-Up Menu in column C on that line. The Summary by Category table on the first sheet will then update automatically. At the end of the year it will contain the summary that you want.


SG

Similar questions

5 replies
Question marked as Top-ranking reply

Sep 30, 2018 3:35 PM in response to laurafromleesburg

Hi Laura,


If you aren't that familiar with Numbers and don't want to reinvent the wheel then I highly recommend that you have a look at the 'Personal Budget' template at File > New in your menu. It does exactly what you describe. You enter the transactions in the Transactions table and the expenses are summarized by category on the first sheet.


It will take you just a few minutes to customize it for categories that match what you need for your year-end report. You won't need to change any formulas.


In the Category column of the Summary by Category table on the first sheet simply replace the existing categories with the categories that you need to track.


User uploaded file


Then in column C of the Transactions table on the second sheet change the items in the Pop-Up Menu to match the categories you have entered in column A of the Summary by Category table on the first sheet. The spelling has to be the same in both places.


User uploaded file


To do this first change the Pop-Up Menu choices in cell C2 only. You do that in the panel at the right, as shown in the screenshot. Then with cell C2 selected choose the blank at the bottom of the list.


User uploaded file


That sets the Pop-Up Menu to a blank for C2.


Now, with C2 still selected, type command-c to copy it. Then select all the cells below C2 in column C and type command-v to paste. Now the Pop-Up Menu with your new categories will be in each cell in that column.


Whenever you enter a new transaction just choose the category you want from the Pop-Up Menu in column C on that line. The Summary by Category table on the first sheet will then update automatically. At the end of the year it will contain the summary that you want.


SG

Sep 30, 2018 1:30 PM in response to laurafromleesburg

Hi Laura,


If your document lists transactions during a single year, you'll be able to get the totals you want using SUMIF.


If the document contains transactions during a period of more than a year, you'll need to turn to SUMIFS.


Here's are two small examples illustrating each case.

User uploaded file

In the simpler case above, where all transactions are in the same year, the formula is concerned only with totals for each category.


The Expenses-1 table has no formulas. All data shown is entered directly.


Summary-1 has two formulas:


Summary-1::C2: IF(LEN(A2)<1,"",SUMIF('Expenses-1'::B,A2,'Expenses-1'::C))

Filled down to C10.


The part shown in bold checks the categories in column B of the Expenses-1 table for a match with the category name in cell A2 ('this row') of 'this table', and, where it finds a match, includes the amount in that row of column C of Expenses-1.


The part in normal type is a 'switch' that prevents the SUMIF part from being called when the category cell in column A is empty.


C11: SUM(C)


Calculates the total expenses for all listed categories. Used here as a checksum to show the number of expenses included in the total above. As all expenses listed are 1.00, the number should equal the number of rows included in the total.


The second case contains expenses from two years, 2017 and 2018. The Summarised totals are to consider only the expenses recorded with dates in 2018:

User uploaded file

Data here is the same set as above, except that the dates cover a different period—15 are in December, 2017, and are not to be included in the totals, 14 are in January and February of 2018, and are to be included.


As above, Expenses has no formulas. All data is idrectly entered.


Summary has an added Header row to contain the year to which the Summary applies.


As in the first example, Summary has two formulas:


C12: SUM(C)

This is the same formula as in the summary table in the first example, pushed down one row by the added header row in this example.


C3: IF(LEN(A3)<1,"",SUMIFS(Expenses::C,Expenses::A,">="&DATE(A$1,1,1),Expenses::A,"<"&DATE(A$1+1,1,1 ),Expenses::B,A3))


Fill down to C11.


The normal type part is the same as in the first example.


The bold part uses SUMIFS in place of SUMIF as it now has to consider the date in column A as well as the category in column B.

SUMIFS also reverses the order of arguments, placing the sum-values first, followed by as many pairs of test-values,condition as needed (in this case, 3). ALL test-value, condition pairs must return 'true' for the sum-value on that row to be included in the sum.


test-value,condition:


Expenses::A,">="&DATE(A$1,1,1), the date in column A of Expenses must be on or after

January 1 of the year in A1 of 'this table' (Summary)

Expenses::A,"<"&DATE(A$1+1,1,1), the date in column A of Expenses must be before

January 1 of the year after the year in A1 of 'this table' (Summary)

Expenses::B,A3 the category in column B must match the category in

'this row' of column A of 'this table'.


If all three conditions are met, the value is included in the sum in the cell containing the formula.


Regards,

Barry

Oct 8, 2018 7:25 AM in response to Barry

Hi Barry - thanks for the reply, yesterday was the first chance I had to try the two suggestions I got. I didn't get far with your method. I don't know much about Numbers and got a little lost. I'm trying to keep a register much like a check register but with the ability to track categories for the end of the year. I did start the budget template and see where it tracks amounts in the budget sheet. Not sure if your way would be more efficient if I could figure it out. Thank you for answering.

Oct 8, 2018 7:35 AM in response to laurafromleesburg

Hi - sorry, I see I typed a reply to the first person who answered my question to you. I did try your method yesterday and get my first month's entries in the transaction list with categories. I do see that totals are kept in the budget page so that is helpful. Is there a way to do this monthly? I'm trying to reconcile my statements plus track the categories. Not sure either how to divide my my credit card payments so it goes into the correct. categories.

Thank you for your reply and help.

Numbers spreadsheet for tracking expense categories

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.