Using Excel PivotTables to create a calendar
HTML-код
- Опубликовано: 11 сен 2024
- Update: I haven't had time to update this video, but I do have a template now. It's much faster to use and is pre-made for different years. Find it here: goo.gl/P8qFDm
Sometimes you want to see a calendar visualization of data. A PivotTable can quickly get data into that format without having to labor over manually copying the data into a calendar format.
This is one of the greatest things I've ever seen, thank you for posting this video!
Great stuff Sarah. Your video inspired a scheduling project I'm currently working on
Well done Sarah!
This is simple, elegant & useful all at the same time.
Brilliant, BRILLIANT! 🧠👏🏾 I feel I need to give something in exchange, so, humbly and just in case you havn't figured it out just yet, if you "paste reference" you would make it dynamic 🙂 Thanks for the great idea I was just starting to theorize
Saved me tons of time! Cheers!
this is brilliant :), i was also using pivots for the viualization of production data (trash collection all year round), only today did it cross my mind to put everything in a calendar.
a further development of this model would have been to connect the calendar to a pivot, have the calendar display the dates (as a calendar should) and the conditional formatting should reflect the quantities behind the dates. i'm gonna do more research on that.
thanks for a great idea! keep up the good work.
I've been playing with this idea since. I couldn't quite get the PivotTable to drive a separate calendar but I had to put it down before completing. I hope you take it further!
This is absolutely brilliant
this helped me sooooo much
I love this. Wondering how I get text, rather than numerals, in the fields. We have key assets that have due dates so looking to create a calendar that has what is due on a particular date. Are you able to show me how I can get this field to show as text? My text would be the equivalent or your incident number.
If I'm not mistaken, I believe you can simply use the IN_DATE column and create "Groups" within the Field list of the Pivot Table. Excel usually does this automatically by Year, Quarter, Month. But I think you can also create groups for days.
Also, what's the point of the Pivot Table if you simply copy and paste the data into a unconnected data set? If you wanted to set it up that way, the best approach is to forego the Pivot Table and instead set up your calendar manually and use =INDEX(MATCH formulas to link your data.
Thank you for your feedback! This is the path I took, based on the data I work with and the current skills I had. The Groups function of PivotTables works for months, but not the Weeknum. I use the calendars in static publications. I started playing with this visualization specifically for this report: firemarshal.ks.gov/docs/default-source/default-document-library/2015-fire-in-kansas-report.pdf?sfvrsn=0
I chose PivotTables because I'm so comfortable with them. I can filter, slice, and hunt for the answer so quickly that PivotTables are my go-to tool. I am not so comfortable with formulas. I'll have to stretch my skills and check out the solution you brought up using formulas. I did finally spend some time making a template PivotTable for this that gets rid of needing anything but the IN_Date field. I'll be posting a follow-up video soon about that option. It also fixes the dynamic quality of the calendar.
This is dope! Love it.
❤
Awesome
👍👍👍👍👍👍
Legit
Your voice is very much identical to Bollywood actress, katrina kaif
I am unfamiliar with her work and hope that's a good thing!