Forum Discussion
Converting excel data into a printable calendar template
My work runs about 200 events per year.
These are listed in an excel spreadsheet.
However they also have a word template which has the layout of an annual calendar.
This means there are months across the top, and dates down the left hand side (like those big annual calendars you see on a noticeboard).
Everytime they add an event or edit an event, it has to be done in the excel spreadsheet manually, and then go into the word spreadsheet manually.
I am wondering if there is a way to update it in excel and it magically links to a template (or app) that has the look of a wall calendar.
It seems there is a lot double handling and opportunity to make mistakes.
Thanks
Tammy
tammyangel Perhaps you are willing and able to move away from Word, because you could do all within Excel, with Power Query / Power Pivot (provided you are working on a PC, not a Mac).
The attached workbook contains an example that demonstrates what's possible. Sheet1 contains the Events listing (Date, Event) and Sheet2 has a (power) pivot table with the Annual Calendar view you described. All don in a quick-and-dirty manner and most likely it needs to be adapted to your real situation.
Change the events listing (change a date, add, delete, rename), and refresh the pivot table. That's it!
Edit: Added another file ("tammyangel-2.xlsx) . Power Query only. Personally I prefer that one.
Hi Riny,
Do you have a solution for multi-day events? I am trying to implement the same concept for a year-long calendar of conferences and many are multi-day.
Thanks in advance!
sophiaperigee This is an old and long thread and many different calendar options were discussed. Perhaps better to start a new thread where you focus on your specific situation.
So far you have been very helpful. I still can't adapt the table for my needs. Some of my needs are for a class name
Class name | Date| Date| Date| etc. (up to 12 possible dates)
Is there a way to see the table on a normal calendar view or is the list the only way to use that raw data?
Thanks for your insight
RickJ80 Share a file please, because it's difficult to visualise how Class, Date, Date, etc will affect the calendar.
Hi Riny_van_Eekelen. Can you possibly please explain how you would turn the attached data spreadsheet into a monthly or annual calendar of events - like a monthly planner view not a list as it is now using power query/power pivot? Thank you 🙂
tahnee_h That's a very broad question and not so easy to answer, but the basis for any scheduling would be a list of columns in the correct structure. In your case a table with columns for Date, Time, Location, Function Name, Function Type and Pax.
But that's going to be a lot of information to be fitted inside a calendar template like the one being discussed in this thread. I dare to say that it would be best to use an out of the box calendar system like the ones that are included in your e-mail system. I would never consider developing such a system in Excel myself.
