Converting excel data into a printable calendar template

Occasional Visitor

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.




1 Reply

@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.