I have daily data that I roll up to weekly totals and present a 6-week rolling chart of the data. Currently the daily data is going across rows with the dates as column headings. Since I recently learned the best way to format the data is with a table, what is the best way to get the daily data into a table to feed the chart?
Hi Torie,
Great to hear you're embracing the tabular data layout 🙂
You can use PivotTables to group your dates into 7 day periods: https://www.myonlinetraininghub.com/excel-pivottable-group-data-video
Let me know if you get stuck.
Mynda
The video is very helpful. Thanks!
I have a related question. I see now how to group days into 7-day weeks. However, I would like to also group by Months and Year, too. Ideally, I would like to be able to filter by Month and Year, but then show weekly data. But it seems to be either/or. When I select Days, Months, and Year, the weekly grouping goes away. Is that right? Is there a workaround?
Update: Actually, I figured it out. I can extract year and month in the source table and use those to filter. So, I'm good now. Thanks!
Or you can just add the weekly grouped field to the row/column area and use Slicers for the Month/Year filters. You don't have to have the Month/Year fields present in the PivotTable to filter by them.
This will be more efficient than adding the fields to the source data table.
Mynda
