Forum Discussion
how to make a chart that separates total data by month/year
I need to total each of the columns by month/year in a separate chart. I don't have any data entered in the spreadsheet yet and will need the totals automatically added to the other chart as they're entered. Can someone help me with how this chart might look and what formulas I would use? The clinical hours will be totaled for about 2 years.
Without an idea of how the raw data is to be collected, it's rather difficult to tell you the definitive way to do it, what formula(s) to use.... In Excel there are often multiple ways to get from A to B.
However, based on the image, I'd suggest you acquaint yourself with the Pivot Table method. No formulas are needed, just an appropriate table with all of the relevant data (including dates). There are also myriad YouTube videos on Pivot Tables.
Finally, when you do get some data collected--or even a reasonably sized sampling of what the data might look like over a period of, say, six months, come back and post a copy of that workbook. Not an image, but the actual workbook. That will give us something to work with.
mathetesThanks so much for your response! The pivot table is actually perfect BUT I need it to separate by months and years. Right now, I have data from October 2023 and October 2024...I need them to be separate. Can you tell me how to add the year into the pivot table as well?
smithl8686 A screen shot is almost useless when compared to the actual workbook, especially when you've collapsed however many rows of dates you have. So I have to ask, do you have lots of dates, and will you have lots more? For example, right now, do you not have data from more 2023 and 2024 months? Add it to your database and see how Excel responds in that set of Pivot Table Fields. My experience is that it will usually offer to "stack" years and months, the latter as a subordinate level. You can force it to be more explicit by adding a column that contains nothing but year (which can be calculated by =YEAR(date) where date is the cell reference to an Excel date, but usually Excel can figure that out on its own.
- I don't have any dates yet because I'm making this for someone. They'll be tracking their work hours for 2-2.5 years. I have 1,000 lines in the table for tracking. I was testing the pivot table and just put a line for October 31, 2023 and October 31, 2024 both with hours data but the pivot table just lumped them together as October instead of separating by year. Any other ideas other than adding the extra columns? If not, I'll definitely try that.
- As I said (or did I just hint?) in my experience -- using a pivot table to summarize personal expenses by month and budget category, once I had a year's worth of data (hundreds of transactions), Excel was smart enough to suggest--just from the date field--that it would make sense to summarize on a monthly basis. When two or more years were involved, it could also recognize different years either as a basis for filtering (showing only one year at a time) or showing the separate months under each year, year being primary, month being secondary. You aren't giving the Pivot Table a fair chance by only having two days worth of data.