Forum Discussion
I need to subtotal similar items in a checkbook spreadsheet monthly by category
You write: I need to build a new spreadsheet within the workbook to display these subtotals for each category(the number of categories doesn't change) for each month.
And I first want to challenge that "need," by suggesting that what you really need is to display the subtotals for each category for each month. That is to say, whether or not a "new spreadsheet within the workbook" is what you need is questionable. Are you aware of the Pivot Table? Since your database already identifies months (dates) and categories, the Pivot Table would readily grant you your subtotals, with the ability to identify all of the backup details as needed. And a Pivot Table would work without new formulas, could be refreshed at will.....
Here's a good resource to learn about PivotTables.
If you want more guidance, might I suggest that you post a copy of your current workbook, either on OneDrive or GoogleDrive, posting here a link that grants access to it.
You write: I need to build a new spreadsheet within the workbook to display these subtotals for each category(the number of categories doesn't change) for each month.
And I first want to challenge that "need," by suggesting that what you really need is to display the subtotals for each category for each month. That is to say, whether or not a "new spreadsheet within the workbook" is what you need is questionable. Are you aware of the Pivot Table? Since your database already identifies months (dates) and categories, the Pivot Table would readily grant you your subtotals, with the ability to identify all of the backup details as needed. And a Pivot Table would work without new formulas, could be refreshed at will.....
Here's a good resource to learn about PivotTables.
If you want more guidance, might I suggest that you post a copy of your current workbook, either on OneDrive or GoogleDrive, posting here a link that grants access to it.
Thanks for your idea of the pivot table. I'm having difficulty in some of the formatting around a pivot table. The file that I have attached is just something created quickly so that I could learn about the pivot tables. I have typed in the word September since I can't figure out how to get the "Sep" formatted for the complete month name. If this can't be done, can I how can I get rid of the "Sep" label? Also, same question with the words "Sum of Amount". How do I not display these words?
I have come to the conclusion that the pivot table makes a nice intermediate tool for summing the amounts by month. The reports that I need cannot rely solely on the pivot table however, as some possible fields may not be used in a given month. Example: We only pay insurance once per quarter. The report needs to display amounts spent on insurance in a given month whether we did or not. Since the pivot table may not display the insurance field in the early months, I can't rely on it to display in the reports. I can however use the data in the pivot table in the reports.
Unless someone can help with the formatting, it appears that the pivot table isn't always a good tool for reports which need to formatted in a certain way. Again, I think it is a good intermediate step.
I have a spreadsheet for a checkbook used by a condo association. We display subtotals every month of different expenditure types such as lawn care, maintenance, insurance, legal, utilities, etc. The spreadsheet has fields for date and category. I need to build a new spreadsheet within the workbook to display these subtotals for each category(the number of categories doesn't change) for each month. What is the easiest way to do this?
I put that quote from your first post here, so I (and other potential advice-givers) don't lose sight of the fact that this is a condo association check book that you're maintaining, not your own; that, therefore, there are expectations in terms of reporting that might not apply in the same way if it were your own personal expenses.
I have come to the conclusion that the pivot table makes a nice intermediate tool for summing the amounts by month. The reports that I need cannot rely solely on the pivot table however, as some possible fields may not be used in a given month. Example: We only pay insurance once per quarter. The report needs to display amounts spent on insurance in a given month whether we did or not. Since the pivot table may not display the insurance field in the early months, I can't rely on it to display in the reports. I can however use the data in the pivot table in the reports.
Let me invite you to hold off on final conclusions until you've worked with the concept a bit more. For one thing, you should extend the set of raw data to include a lot more months of expenses, to see how it looks and works. There are some aspects of the Pivot Table that you probably haven't discovered yet.
I've attached an example of a similar spreadsheet with raw data summarized using the Pivot Table. It still is pretty simple, but I want to draw your attention to a couple of features.
- This spreadsheet records both income and expenses. There's a column in the RawData table (which could be hidden if desired) called AdjAmt, for "adjusted amount." The "adjustment" referred to is displaying all expenses as a negative amount; income as positive. That way the Pivot Table's grand totals show the net of Income minus Expenses. That may or may not be of use to you. (I incorporate that idea in my own personal budget tracking spreadsheet, which incorporates data from multiple check accounts and multiple credit cards.)
- In a Pivot Table, if you click on any one of the summary totals, you'll be presented with a sheet that shows all of the back up data. In this sample sheet (as in yours) there's not much to show here, but in my own, where (for example) there are months when we spend a lot on restaurants, it's helpful to see all of the individual charges with a single click.
- You did discover the "Filter" capability, but in your case I don't think you need it, since it makes more sense (I would submit) to see September expenses next to August and October, (etc). Filter might come in handy if you want to look, say, at income or expenses for different buildings within the Condo (the Coop that my wife and I live in has five high-rise buildings; whether or not they look at things that way, I don't know!)
Now, with regard to your insurance example--paid quarterly but reported monthly--I'm sure there are ways to handle that. One way (which might not be acceptable) is to do all your reporting on a quarterly basis--which Excel can do for you based on the dates. Another would be to have an expense type called "budgeted expense" wherein you record that budget amount on a monthly basis, and then still record the actual quarterly expense, but mark it in such a way that it doesn't appear in the Pivot Table.
[I do this on my personal budget tracking in this way: I pay our credit card bills in total each month, but the check that goes out to pay is marked "Ignore" and the payment as recorded by the Credit Card company is similarly marked "Ignore"--because what I'm interested in tracking is the individual transactions that are recorded as expenses in each credit card statement. The cashflow out from checking and in to the credit card is "neutral" so long as I'm accounting for all of the transactions that make up that credit card bill. Yet I've retained the data should I need to go back and reconstruct it.]
I'm also attaching a slightly revised version of your spreadsheet, with changes to the display of the Pivot Table.
