Forum Discussion
how to make a chart that separates total data by month/year
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.
mathetes well, I don't have a choice. I'm not entering the data, the customer is and they'll be slowly entering over 2 years. So it is what it is, I guess. Thanks for the help.