2019 Excel Calendar Template – 19 Calendar Designs – Free Download

Posted on
2019 Calendar Design 9 – 12 Pages – Monthly

Download this new 2019 Excel Calendar Template and create your own personalized calendars in Excel. The calendar template can be used to create calendars for any year.  You can choose from 19 different calendar designs, including annual (yearly), monthly, weekly and daily calendars. All in one file, free to download.

Topics in this post (2019 Calendar Template)

  • Features of 2019 Calendar template
  • How to download this template for free?
  • Instructions on how to use the template
  • 19 Calendar designs – their features
  • Tips to further customize the template
2019 Calendar Design 9 – 12 Pages – Monthly
2019 Calendar Design 9 – 12 Pages – Monthly

FEATURES OF 2019 CALENDAR EXCEL TEMPLATE

  • Customize calendars by choosing the Year, Starting month and Starting day of week.
  • Enter Holidays and Personal Events to display on the Calendars
  • Automatically creates 19 Calendar designs (as this is 2019 😊)
    • 12 (of the 19) designs automatically list the Holidays and events
    • 2 (of the 19) designs allow typing events directly on the calendar itself
  • All 19 designs are ready to be printed. Can be Exported to PDF as well.
  • Easy to change formatting (font size, font, text colors, fill colors)

DOWNLOAD 2019 CALENDAR TEMPLATE

2019 Calendar Excel Template
2019 Calendar Excel Template with US Holidays

VIDEO DEMO

Watch On YouTube

 

HOW TO USE THE TEMPLATE 

Overview of steps

  1. Enter Basic settings
  2. Choose Weekends
  3. Enter Holidays
  4. Enter your personal or company Events
  5. Pick a Calendar design
  6. Print or export the calendar to PDF

Now, let’s see each step in detail.

Step 1: Enter Basic settings

Settings - Year Month Weekday - Name
Settings – Year Month Weekday – Name

Enter the year for which you want to build the calendar. I designed it such that you can enter any year and the calendar will still work. 🙂

By default the year begins in January. However, you can modify that to start the calendar with any month.

Settings - Choose Starting Month
Settings – Choose Starting Month

By default, the calendar shows weeks starting in Sunday. However, you can modify it to start with any weekday.

Settings - Choose Starting Day of Week
Settings – Choose Starting Day of Week

We can give a name to our calendar and the name will appear on some of the calendar designs automatically.

Step 2: Choose Weekends

Settings - Choose Weekend Days
Settings – Choose Weekend Days

Calendars show weekend days in red colored font. You can modify the weekends as needed for your purpose. In the image above, I have selected Saturday and Sunday as weekends. So, all Saturdays and Sundays will be shown in red font on the calendars.

Step 3: Enter Holidays

Enter the list of holidays for your company or business in the Holidays table.

Holidays - U.S.
Holidays – U.S.

I have provided a list of holidays in U.S. and India for reference. Please see the Holidays sheet.

I have used Excel Tables for user input. If you are new to Excel tables, please see my article on How to use Excel tables for data entry?

Step 4: Enter your personal or company Events

The template allows the user to enter events, in addition to holidays, to display on the calendars.

Events with dates and names
Events with dates and names

If you are using this template for personal calendar, you can enter your personal events as I have done in the above image.

If you need to create a calendar for your company, enter your company events for 2019 in the Events table.

Step 5: Pick a calendar design from the 19 designs

Based on the input provided in the previous 4 steps, the template builds the calendar designs automatically.

The 2019 calendar template offers 19 different calendar designs to choose from.

In the Pick a Calendar sheet, we can see the 19 designs.

2019 Calendar Template - 19 Designs
2019 Calendar Template – 19 Designs

Just click on any design on and you will be taken to the corresponding calendar sheet.

You can also directly go to any of the sheets named 1 to 19.

Sheet names - 19 Sheets of Calendar Designs
Sheet names – 19 Sheets of Calendar Designs

Note: If you change the sheet names of the calendars, then hyperlinks in the Pick a Calendar sheet will not work.

Step 6: Print the calendar or export to PDF

Excel’s default print menu options and Export options work as usual. If you are not familiar, it is not a problem. I explain them in detail with screenshots towards the end of this post.


19 CALENDAR DESIGNS

Let’s look at each of the 19 calendar designs.

2019 Calendar Designs
2019 Calendar Designs

Calendar Design 1

1 Page 12 Months – 4 X 3

2019 Calendar Design 1 – 1 Page 12 Months – 4 X 3
2019 Calendar Design 1 – 1 Page 12 Months – 4 X 3
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 4 rows X 3 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • There is a place to enter any notes at the bottom of the page.

When both Event and Holiday fall on the same day, Event is prioritized and event color is displayed.

Calendar Design 2

1 Page 12 Months – 3 X 4

2019 Calendar Design 2 – 1 Page 12 Months – 3 X 4
2019 Calendar Design 2 – 1 Page 12 Months – 3 X 4
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 3 rows X 4 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • There is a place to enter any notes at the bottom of the page.

Calendar Design 3

1 Page 12 Months – 2 X 6 with Events

2019 Calendar Design 3 – 1 Page 12 Months – 2 X 6 with Events
2019 Calendar Design 3 – 1 Page 12 Months – 2 X 6 with Events
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 2 rows X 6 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 20 Holiday Names and 20 Event Names are listed in this design.

Calendar Design 4

1 Page 12 Months – 6 X 2 with Events

2019 Calendar Design 4 – 1 Page 12 Months – 6 X 2 with Events
2019 Calendar Design 4 – 1 Page 12 Months – 6 X 2 with Events
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 6 rows X 2 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 20 Holiday Names and 30 Event Names are listed in this design.

Calendar Design 5

1 Page 12 Months – 12 X 42 with Events

2019 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events
2019 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 12 rows X 42 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 20 Event Names are listed in this design.

Calendar Design 6

1 Page 12 Months – 12 X 31 with Events

2019 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events
2019 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 12 rows X 31 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 20 Event Names are listed in this design.

Calendar Design 7

6 Pages – Planner

2019 Calendar Design 7 – 6 Pages – Planner
2019 Calendar Design 7 – 6 Pages – Planner

 

  • This design prints the entire year in 6 pages.
  • The 12 months are shown in 31 rows X 12 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • Type notes directly next to each date on the calendar.

Calendar Design 8

1 Page – 54 Weeks – with Events

2019 Calendar Design 8 – 1 Page – 54 Weeks – with Events
2019 Calendar Design 8 – 1 Page – 54 Weeks – with Events
  • This design prints the entire year in 1 page.
  • The 12 months are shown in 54 rows X 7 columns.
  • The Holidays and Events are not highlighted on the calendar.
  • 20 Holiday Names and 30 Event Names are listed in this design.

Calendar Design 9

12 Pages – Monthly

2019 Calendar Design 9 – 12 Pages – Monthly
2019 Calendar Design 9 – 12 Pages – Monthly
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as one month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

Calendar Design 10

6 Pages – with Events

2019 Calendar Design 10 – 6 Pages – with Events
2019 Calendar Design 10 – 6 Pages – with Events

 

  •  This design prints the entire year in 6 pages.
  • The 12 months are shown as 2 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 10 Event Names are listed for each month in this design.

Calendar Design 11

4 Pages – with Events

2019 Calendar Design 11 – 4 Pages – with Events
2019 Calendar Design 11 – 4 Pages – with Events
  • This design prints the entire year in 4 pages.
  • The 12 months are shown as 3 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • 5 Holiday Names and 5 Event Names are listed for each month in this design.

Calendar Design 12

3 Pages – 4 Months on each page

2019 Calendar Design 12 – 3 Pages
2019 Calendar Design 12 – 3 Pages
  • This design prints the entire year in 3 pages.
  • The 12 months are shown as 4 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

Calendar Design 13

2 Pages – 6 Months on each page

2019 Calendar Design 13 – 2 Pages – 6 Months on each page
2019 Calendar Design 13 – 2 Pages – 6 Months on each page
  • This design prints the entire year in 2 pages.
  • The 12 months are shown as 6 months in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

Calendar Design 14

12 Pages – Monthly – with Events

2019 Calendar Design 14 – 12 Pages – Monthly – with Events
2019 Calendar Design 14 – 12 Pages – Monthly – with Events
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are listed for each month in this design.

Calendar Design 15

12 Pages – Monthly – Type Events

2019 Calendar Design 15 – 12 Pages – Monthly – Type Events
2019 Calendar Design 15 – 12 Pages – Monthly – Type Events
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.
  • Type any text just below any date directly on the Calendar.

Calendar Design 16

12 Pages – Picture Calendar

2019 Calendar Design 16 – 12 Pages – Picture Calendar
2019 Calendar Design 16 – 12 Pages – Picture Calendar
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.

Right Click on image and change picture for each month. Choose from file on computer or online

Change picture in Picture calendar Choose from file on computer or online
Change picture in Picture calendar Choose from file on computer or online

Calendar Design 17

12 Pages – Quotes Calendar

2019 Calendar Design 17 – 12 Pages – Quotes Calendar
2019 Calendar Design 17 – 12 Pages – Quotes Calendar
  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The Holidays, Events and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.

Click on placeholder text and type any text for each month. Create your own customized calendar of quotes.

Calendar Design 18

1 Page – Weekly Calendar

2019 Calendar Design 18 – 1 Page – Weekly Calendar
2019 Calendar Design 18 – 1 Page – Weekly Calendar
  • This design prints one week in 1 page.
  • Type the Week start date to generate a weekly calendar for any week.
  • Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are listed in this design.
  • Up to 10 Events per day and up to 5 Holidays per day.

Calendar Design 19 (Daily Calendar)

1 Page – Daily Calendar

2019 Calendar Design 19 – 1 Page – Daily Calendar
2019 Calendar Design 19 – 1 Page – Daily Calendar
  • This design prints one day in 1 page.
  • Type the Date to generate a daily calendar for that date.
  • Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are listed in this design.
  • Up to 20 Events per day and up to 5 Holidays per day.

TIPS TO CUSTOMIZE THE 2019 CALENDAR TEMPLATE

1. How to change formatting (colors/fonts/borders)?

One of the common reasons for making changes would be to change the formatting of the calendar. I am sure that you may have your personal favorites when it comes to colors and fonts. The template is designed so that you can make these changes yourself without negatively affecting the functionality of the calendar.

Select Cells to change formatting

Select Cells to change formatting
Select Cells to change formatting

For example, in the image above, I have selected the 2 months (January & February).

In the Home ribbon, you will see the Font section (as shown below).

How to change formatting on calendar (font, font size, font color, border, bold, italics)
How to change formatting on calendar (font, font size, font color, border, bold, italics)

We can change the font, font size, font color, fill color, borders and also apply bold and italics format.

Some of the border options require unprotecting the sheet. We will see how we can unprotect later in this post.

A couple of things to note here:

  • If you change the font color, the dates’ color will change except for the Weekends. Weekends are set up to show with red color.
  • If you change the fill color, you will notice that the days with Holidays and Events will not change. Holidays and Events are set up separately with their own colors.

I will explain now how both the above points can also be addressed.

2. How to change Event and Holiday colors?

Events and Holidays are colored automatically on the calendar using conditional formatting. As with everything with this template, it’s easy to change them too.

First, click on Conditional Formatting menu in the Home ribbon, and select Manage Rules.

Open Conditional Formatting Menu
Open Conditional Formatting Menu

The next dialog box shows the 3 rules we have used in this worksheet. Make sure that you choose ‘This worksheet‘ at the top.

Three conditional formatting rules for Weekends Holidays and Events
Three conditional formatting rules for Weekends Holidays and Events

We can modify each of the rules separately. Just click on one of the rules and then click on ‘Edit Rule’ button. In this example, I have clicked on the ‘Events’ rule.

Conditional formatting - Edit Format for a rule
Conditional formatting – Edit Format for a rule

Click on the Format button. It opens the next dialog box.

Conditional formatting - Change format for the Events dates cells
Conditional formatting – Change format for the Events dates cells

We can modify not just the fill color, but also the Number format, font and border. For this tutorial, I have just highlighted where you can change the background fill color.

You can choose one of the standard colors or click on ‘More Colors‘ button to choose any color from the spectrum.

Click OK to the dialog box and then the following open dialog boxes.

Then, you should see the changes applied to the entire calendar sheet. Save the file to save the changes.

3. How to change the order in which events/holidays appear on calendar?

Some of the calendar designs list the names of events and holidays on the calendar. They are designed to be listed in the same order in which they were entered in the Holidays and Events tables in Settings sheet.

If you had entered them in a random order in the tables, then you can easily sort them. For example, in the Events table, click on the Event Date header label. A menu pops up where you can choose ‘Sort Oldest to Newest‘.

Sort Events by Dates in Events table
Sort Events by Dates in Events table

This would sort the Events table with dates in ascending order.

You can apply the same technique to the Holidays table.

4. How to change the date format for events and holidays?

I have used the date format MMM-DD (example: Jan-05) to show the dates. I usually try to show the month in text instead of numbers as I understand that there are differences in formats used by different countries. 1/10 could be interpreted as Jan 10th or Oct 1st, depending on which country you live in.

However, you can easily change it to any format to suit your needs.

Just select the cells where you want to apply the change.

Then, press Ctrl+1 to open the Format cells dialog box.

Change date formats using Format cells option
Change date formats using Format cells option

You can type your own custom format or click on ‘Date’ in the list on the left. Then, you can choose from the standard date formats.

Choose one from the standard date formats
Choose one from the standard date formats

5. How to unprotect (unlock) sheets?

All the customization mentioned above can be made without unprotecting any sheets.

If you plan to make more modifications and you see that you are not able to, you can unprotect using indzara as password.

For more details, please see my article on how to unprotect sheets.

Please remember to protect the sheets again in order to prevent accidental editing of formulas.

6. How to print calendars?

To print, press Ctrl+P or find the Print option in the File menu.

Open Print settings with Ctrl P
Open Print settings with Ctrl P

Since the template is already pre-set for printing calendars, you can quickly review the calendar in preview and then click on Print to print.

If you need to print only a subset of pages, please enter page number range.

7. How to export calendar to PDF?

To export to PDF, choose Export in the File menu and then click on Create PDF/XPS.

Export calendars as PDF
Export calendars as PDF

In the following dialog box,

  • Choose the place in your computer to store the PDF.
  • Give a meaningful name to the PDF
  • If you need to control which pages are being exported to PDF, click on Options. Choose specific page numbers you want to export and click OK.
  • Click Publish to publish PDF.

RECOMMENDED TEMPLATE

Event Calendar Maker

Event Calendar Maker - Product Logo
Event Calendar Maker – Product Logo

Additional Features in Event Calendar Maker

  • 12 Frequency Types (automatically generate recurring events)
  • Categorize events into 12 Event Types
  • Choose from 15 Colors to highlight
  • 7 Calendar Designs (2 Yearly, 3 Monthly, Weekly and Daily calendars)
  • Works for any year
  • 300 events and 1200 instances
  • Control which events you would like to display by deactivating events
  • Control order of priority of events
  • Switch Off Non-business days easily

If you find the template useful, please share with your friends. Please leave a comment below to let me know your feedback. Thank you.

Have a wonderful 2019.

Excel Calendar Template – 17 Calendar Designs in Excel – Free Download

Posted on
2017 Calendar Design 2 - 1 Page 12 Months - 3 X 4

I am very pleased to present the new Excel Calendar template. Download this new Calendar Excel template and create your own personalized calendars in Excel.

In this post, we will be discussing the following.

  1. Features of the Calendar template
  2. Link to download template for free
  3. Instructions to use the template
  4. 17 Calendar designs
  5. Tips to further customize the template

 

Monthly Calendar Sample From 2017 Excel Calendar Template
Monthly Calendar Sample From 2017 Excel Calendar Template

 

FEATURES OF CALENDAR TEMPLATE

  • Customize calendars by choosing the Year, Starting month and Starting day of week.
  • Enter Holidays and Personal Events to display on the Calendars
  • Automatically creates 17 Calendar designs (No points for guessing why 17 🙂 )
    • 10 (of the 17) designs automatically list the Holidays and events
    • 2 (of the 17) designs allow typing events directly on the calendar itself
  • All 17 designs are ready to be printed. Can be Exported to PDF as well.
  • Easy to change formatting (font size, font, text colors, fill colors)

 

DOWNLOAD CALENDAR TEMPLATE

Calendar Excel Template

2017 Calendar with US Holidays

 

VIDEO DEMO

 

HOW TO CREATE CALENDAR

Overview of steps

  1. Enter Basic settings
  2. Choose Weekends
  3. Enter Holidays
  4. Enter your personal or company Events
  5. Pick a Calendar design
  6. Print or export the calendar to PDF

 

Now, let’s see each step in detail.

Step 1: Enter Basic settings

 

Enter Basic Settings for 2017 Calendar Year Month Week Begin Day and Name
Enter Basic Settings for 2017 Calendar Year Month Week Begin Day and Name

 

Enter the year for which you want to build the calendar. Even though I created this from scratch just for 2017, I designed it such that you can enter any year and the calendar will still work. 🙂

By default the year begins in January. However, you can modify that to start the calendar with any month.

Choose Starting Month for the Calendar in Excel Template
Choose Starting Month for the Calendar in Excel Template

 

By default, the calendar shows weeks starting in Sunday. However, you can modify it to start with any weekday.

Choose Starting Day of Week for the Calendar in Excel Template
Choose Starting Day of Week for the Calendar in Excel Template

 

We can give a name to our calendar and the name will appear on some of the calendar designs automatically.

 

Step 2. Choose Weekends

Choose Weekend days to highlight on Calendar
Choose Weekend days to highlight on Calendar

 

Calendars show weekend days in red colored font. You can modify the weekends as needed for your purpose. In the image above, I have selected Saturday and Sunday as weekends. So, all Saturdays and Sundays will be shown in red font on the calendars.

 

Step 3: Enter Holidays

Enter the list of holidays for your company or business in the Holidays table.

I have provided a list of holidays in U.S. and India for reference. Please see the Holidays sheet.

Enter Holidays with dates and names for Calendar
Enter Holidays with dates and names for Calendar

 

I have used Excel Tables for user input. If you are new to Excel tables, please see my article on How to use Excel tables for data entry?

 

Step 4: Enter your personal or company Events

The template allows the user to enter events, in addition to holidays, to display on the calendars.

Enter calendar Events with dates and names
Enter calendar Events with dates and names

 

If you are using this template for personal calendar, you can enter your personal events as I have done in the above image.

If you need to create a calendar for your company, enter your company events for 2017 in the Events table.

 

Step 5: Pick a calendar design from the 17 designs

Based on the input provided in the previous 4 steps, the template builds the calendar designs. The 2017 calendar template offers 17 different calendar designs to choose from. In the Pick a Calendar sheet, we can see the 17 designs.

17 Calendar designs for 2017 Excel Calendar
17 Calendar designs for 2017 Excel Calendar

 

Just click on any design on and you will be taken to the corresponding calendar sheet.

You can also directly go to any of the sheets named 1 to 17.

Sheets in the Excel 2017 Calendar Template
Sheets in the Excel 2017 Calendar Template

 

Note: If you change the sheet names of the calendars, then hyperlinks in the Pick a Calendar sheet will not work.

 

Step 6: Print the calendar or export to PDF

Excel’s default print menu options and Export options work as usual. If you are not familiar, it is not a problem. I explain them in detail with screenshots towards the end of this post.

 

 


17 CALENDAR DESIGNS

Let’s look at each of the 17 calendar designs.

2017 Calendar Designs
2017 Calendar Designs

 

Calendar Design 1

2017 Calendar Design 1 - 1 Page 12 Months - 4 X 3
2017 Calendar Design 1 – 1 Page 12 Months – 4 X 3

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 4 rows X 3 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • There is a place to enter any notes at the bottom of the page.

When both Event and Holiday fall on the same day, Event is prioritized and event color is displayed.

Calendar Design 2

2017 Calendar Design 2 - 1 Page 12 Months - 3 X 4
2017 Calendar Design 2 – 1 Page 12 Months – 3 X 4

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 3 rows X 4 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • There is a place to enter any notes at the bottom of the page.

 

Calendar Design 3

2017 Calendar Design 3 - 1 Page 12 Months - 2 X 6 with Events
2017 Calendar Design 3 – 1 Page 12 Months – 2 X 6 with Events

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 2 rows X 6 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • 20 Holiday Names and 20 Event Names are listed in this design.

 

Calendar Design 4

2017 Calendar Design 4 - 1 Page 12 Months - 6 X 2 with Events
2017 Calendar Design 4 – 1 Page 12 Months – 6 X 2 with Events

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 6 rows X 2 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • 20 Holiday Names and 30 Event Names are listed in this design.

 

Calendar Design 5

2017 Calendar Design 5 - 1 Page 12 Months - 12 X 42 with Events
2017 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 12 rows X 42 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 20 Event Names are listed in this design.

 

Calendar Design 6

2017 Calendar Design 6 - 1 Page 12 Months - 12 X 31 with Events
2017 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 12 rows X 31 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 20 Event Names are listed in this design.

 

Calendar Design 7

2017 Calendar Design 7 - 6 Pages - Planner
2017 Calendar Design 7 – 6 Pages – Planner

 

  • This design prints the entire year in 6 pages.
  • The 12 months are shown in 31 rows X 12 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.
  • Type notes directly next to each date on the calendar.

 

Calendar Design 8

2017 Calendar Design 8 - 1 Page - 54 Weeks - with Events
2017 Calendar Design 8 – 1 Page – 54 Weeks – with Events

 

  • This design prints the entire year in 1 page.
  • The 12 months are shown in 54 rows X 7 columns.
  • The Holidays and Events are not highlighted on the calendar.
  • 20 Holiday Names and 30 Event Names are listed in this design.

 

Calendar Design 9

2017 Calendar Design 9 - 12 Pages - Monthly
2017 Calendar Design 9 – 12 Pages – Monthly

 

  • This design prints the entire year in 12 pages.
  • The 12 months are shown as one month in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

 

Calendar Design 10

2017 Calendar Design 10 - 6 Pages - with Events
2017 Calendar Design 10 – 6 Pages – with Events

 

  • This design prints the entire year in 6 pages.
  • The 12 months are shown as 2 months in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • 10 Holiday Names and 10 Event Names are listed for each month in this design.

 

Calendar Design 11

2017 Calendar Design 11 - 4 Pages - with Events
2017 Calendar Design 11 – 4 Pages – with Events

 

  • This design prints the entire year in 4 pages.
  • The 12 months are shown as 3 months in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • 5 Holiday Names and 5 Event Names are listed for each month in this design.

 

Calendar Design 12

2017 Calendar Design 12 - 3 Pages
2017 Calendar Design 12 – 3 Pages

 

  • This design prints the entire year in 3 pages.
  • The 12 months are shown as 4 months in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

 

Calendar Design 13

2017 Calendar Design 13 - 2 Pages - 6 Months on each page
2017 Calendar Design 13 – 2 Pages – 6 Months on each page

 

  • This design prints the entire year in 2 pages.
  • The 12 months are shown as 6 months in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed in this design.

 

Calendar Design 14

2017 Calendar Design 14 - 12 Pages - Monthly - with Events
2017 Calendar Design 14 – 12 Pages – Monthly – with Events

 

  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are listed for each month in this design.

 

Calendar Design 15

2017 Calendar Design 15 - 12 Pages - Monthly - Type Events
2017 Calendar Design 15 – 12 Pages – Monthly – Type Events

 

  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.
  • Type any text just below any date directly on the Calendar.

 

Calendar Design 16 (Picture Calendar)

2017 Calendar Design 16 - 12 Pages - Picture Calendar
2017 Calendar Design 16 – 12 Pages – Picture Calendar

 

  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.
  • Right Click on image and change picture for each month.
Change picture in Picture calendar Choose from file on computer or online
Change picture in Picture calendar Choose from file on computer or online

 

Calendar Design 17 (Quotes Calendar)

2017 Calendar Design 17 - 12 Pages - Quotes Calendar
2017 Calendar Design 17 – 12 Pages – Quotes Calendar

 

  • This design prints the entire year in 12 pages.
  • The 12 months are shown as 1 month in each page and each month as 6 rows X 7 columns.
  • The HolidaysEvents and Weekends are highlighted on the calendar.
  • Holiday Names and Event Names are not listed for each month in this design.
  • Click on placeholder text and type any text for each month. Create your own customized calendar of quotes.

 


TIPS TO CUSTOMIZE THE CALENDAR TEMPLATE

1. How to change formatting (colors/fonts/borders)?

One of the common reasons for making changes would be to change the formatting of the calendar. I am sure that you may have your personal favorites when it comes to colors and fonts. The template is designed so that you can make these changes yourself without negatively affecting the functionality of the calendar.

Select the cells you want to change formatting.

Select Cells to change formatting
Select Cells to change formatting

 

For example, in the image above, I have selected the 2 months (January & February).

In the Home ribbon, you will see the Font section (as shown below).

How to change formatting on calendar (font, font size, font color, border, bold, italics)
How to change formatting on calendar (font, font size, font color, border, bold, italics)

We can change the font, font size, font color, fill color, borders and also apply bold and italics format.

For example, when I changed the font, applied bold text and reduced the font size, it appears like this.

Changes to font and font size and bold
Changes to font and font size and bold

 

Changed font and font size and bold
Changed font and font size and bold

 

Some of the border options require unprotecting the sheet. We will see how we can unprotect later in this post.

 

A couple of things to note here:

  • If you change the font color, the dates’ color will change except for the Weekends. Weekends are set up to show with red color.
  • If you change the fill color, you will notice that the days with Holidays and Events will not change. Holidays and Events are set up separately with their own colors.

I will explain now how both the above points can also be addressed.

 

2. How to change Event and Holiday colors?

Events and Holidays are colored automatically on the calendar using conditional formatting.  As with everything with this template, it’s easy to change them too.

First, click on Conditional Formatting menu in the Home ribbon, and select Manage Rules.

Open Conditional Formatting Menu
Open Conditional Formatting Menu

 

The next dialog box shows the 3 rules we have used in this worksheet. Make sure that you choose ‘This worksheet‘ at the top.

Three conditional formatting rules for Weekends Holidays and Events
Three conditional formatting rules for Weekends Holidays and Events

 

We can modify each of the rules separately. Just click on one of the rules and then click on ‘Edit Rule’ button. In this example, I have clicked on the ‘Events’ rule.

Conditional formatting - Edit Format for a rule
Conditional formatting – Edit Format for a rule

 

Click on the Format button. It opens the next dialog box.

Conditional formatting - Change format for the Events dates cells
Conditional formatting – Change format for the Events dates cells

 

We can modify not just the fill color, but also the Number format, font and border. For this tutorial, I have just highlighted where you can change the background fill color.

You can choose one of the standard colors or click on ‘More Colors‘ button to choose any color from the spectrum.

Click OK to the dialog box and then the following open dialog boxes.

Then, you should see the changes applied to the entire calendar sheet. Save the file to save the changes.

 

3. How to change the order in which events/holidays appear on calendar?

Some of the calendar designs list the names of events and holidays on the calendar. They are designed to be listed in the same order in which they were entered in the Holidays and Events tables in Settings sheet.

If  you had entered them in a random order in the tables, then you can easily sort them. For example, in the Events table, click on the Event Date header label.

A menu pops up where you can choose ‘Sort Oldest to Newest‘.

Sort Events by Dates in Events table
Sort Events by Dates in Events table

 

This would sort the Events table with dates in ascending order.

You can apply the same technique to the Holidays table.

 

4. How to change the date format for events and holidays?

I have used the date format MMM-DD (example: Jan-05) to show the dates. I usually try to show the month in text instead of numbers as I understand that there are differences in formats used by different countries. 1/5 could be interpreted as Jan 5th or May 1st, depending on which country you live in.

However, you can easily change it to any format to suit your needs.

Just select the cells where you want to apply the change.

Then, press Ctrl+1 to open the Format cells dialog box.

Change date formats using Format cells option
Change date formats using Format cells option

 

You can type your own custom format or click on ‘Date’ in the list on the left. Then, you can choose from the standard date formats.

Choose one from the standard date formats
Choose one from the standard date formats

 

5. How to unprotect (unlock) sheets?

All the customization mentioned above can be made without unprotecting any sheets. If you plan to make more modifications and you see that you are not able to, you can unprotect using indzara as password.

For more details, please see my article on how to unprotect sheets.

Please remember to protect the sheets again in order to prevent accidental editing of formulas.

6. How to print calendars?

To print, press Ctrl+P or find the Print option in the File menu.

Open Print settings with ctrl P
Open Print settings with ctrl P

Since the template is already pre-set for printing calendars, you can quickly review the calendar in preview and then click on Print to print.

If you need to print only a subset of pages, please enter page number range.

 

7. How to export calendar to PDF?

To export to PDF, choose Export in the File menu and then click on Create PDF/XPS.

Export calendars as PDF
Export calendars as PDF

In the following dialog box,

Publish Calendars as PDF options
Publish Calendars as PDF options
  1. Choose the place in your computer to store the PDF.
  2. Give a meaningful name to the PDF
  3. If you need to control which pages are being exported to PDF, click on Options. That will open the following window.

Exporting only some pages as PDF

Choose specific page numbers you want to export and click OK.

4. Click Publish to publish PDF.


RECOMMENDED TEMPLATE

Event Calendar Maker 

Additional Features in Event Calendar Maker
Additional Features in Event Calendar Maker

 

  • Works for any year
  • 300 events and 1200 instances
  • Categorize events into 12 Event Types
  • Choose from 15 Colors to highlight
  • 7 Calendar Designs (2 Yearly, 3 Monthly, Weekly and Daily calendars)
  • 12 Frequency Types (automatically generate recurring events)
  • Control which events you would like to display by deactivating events
  • Control order of priority of events
  • Switch Off Non-business days easily

I hope you find this sheet useful. Please share with your friends if you do.

Please leave a comment below to let me know your feedback. Thank you.

Design your own Calendar Layout – Event Calendar Maker

Posted on

In this article, we will learn how we can create our own calendar layout design. The Event calendar Maker Excel template comes with 7 different layouts (Yearly, Yearly with Events, Monthly, Monthly with Events, One Month, Weekly and Daily). However, if we need to have any other layout, we can do that easily using Excel’s ‘Paste as Linked Picture‘ feature (also called as ‘Camera tool’).

For example, we have the Yearly design which displays four months in a row with 3 rows in total. Our Yearly w Events design displays two months in a row with 6 rows in total. Let’s say we want to have 3 months in a row and we want to be able to add other elements as well. It’s easy and can be done in just a few minutes.  In this exercise, we will create a new layout where three months are shown in one row. Once you understand the fundamentals of the approach, you can create any other layout you would prefer. Let’s get started.

 

VIDEO DEMO

If you prefer videos, you can watch this exercise demo in this video.

 

CREATING THE BASIC LAYOUT

Let us create a new sheet first where we will design the layout.

Create New Sheet
Create New Sheet

 

  1. Go to the Yearly sheet and select the first month’s cells.
Select a Month's Cells - Yearly Calendar Design
Select a Month’s Cells – Yearly Calendar Design

 

Remember to select 6 weeks even if the month you select doesn’t have 6 weeks. This will ensure that our approach will work even if the month is changed in the future.

2. Copy cells by pressing Ctrl+C (Ctrl key and C key together).

3. Go to the new sheet we created. Click on Paste in the top left menu (in the Home ribbon) and then choose ‘Linked Picture‘ in the options available.

Paste as Linked Picture
Paste as Linked Picture

 

We have now created a linked image which will display whatever will be in the Yearly design’s first month’s cells.  This is a picture now and hence if you click on it, you will realize that you can move it around like a picture object.

Pasted Month as Image
Pasted Month as Image

 

This in turn means that you will now have options to format it as a picture. Look at the top right in the menu bar and you will see a new ribbon ‘Picture Tools‘. This will appear only when you select a picture object.

Picture Tools Ribbon
Picture Tools Ribbon

 

For example, we can apply border to the image by selecting the color, border weight and line type (dashes).

Choose Picture Border as needed
Choose Picture Border as needed

 

We can repeat the above steps with second and third month. At the end, we will have an image like this one below.

3 Months in the new Layout
3 Months in the new Layout

 

ALIGN AND DISTRIBUTE

If we are not very careful in lining up the three images in one line, it might appear like above. Not to worry. Excel has all the tools needed. If you would prefer the three months to appear aligned in one row, then we can use Excel’s picture tools to align. Select all three images. To select all three,

  1. Click on the first month image.
  2. Press the Ctrl key and click on the second month image while pressing the Ctrl key.
  3. Click on the third while still pressing the Ctrl key.

Then, click Align in the Picture Tools ribbon’s Format section. Select ‘Align Top‘.

Align Top option in Picture Tools
Align Top option in Picture Tools

 

You would also have noticed that the three months are not spaced equally horizontally (horizontal gaps between them are not equal). Excel to the rescue again. With all the three images selected, go back to the Align menu and choose ‘Distribute Horizontally‘ this time.

Distribute Horizontally in Align Menu
Distribute Horizontally in Align Menu

 

Now, the calendar will look aligned and evenly distributed.

Three Months Aligned Calendar Layout
Three Months Aligned Calendar Layout

 

This is how easily we can design our own calendar layout using Excel’s ‘Paste as Linked Picture’ option. This new calendar will update as you enter new events or change event types (colors). If you change the beginning month in the Home sheet’s settings, that will also get reflected in this new layout. Everything in this new layout will work exactly as it does with the default calendar design layouts in the template.

Usually the pasted image appears slightly less clear compared to the original cells. This is how Excel does and is usually not an issue. However, depending on your need, please test the clarity of the output in print or PDF.

 

ADD LEGEND AND MORE

It is important to note that you can apply this method to any type of cell. For example, I recommend adding the legend to your new calendar layout so that it is clear what the different colors mean. We can copy the legend from the Yearly design and ‘paste as linked picture’. Similarly, we can add event details as needed.

We can also insert other images from our computer such as our logo. For this, we would go to the Insert ribbon and choose ‘Pictures‘.

Insert Logo or other pictures to Calendar
Insert Logo or other pictures to Calendar

 

A sample of such a calendar layout will look like the one below.

Add Legend, Logo and Event Details to Calendar Layout
Add Legend, Logo and Event Details to Calendar Layout

 

A couple of useful tools while inserting your own pictures will be Cropping (if your image has other elements you don’t need) and re-sizing (ex. reducing the size of the logo)

Cropping and Resizing Image in Picture Tools in Excel
Cropping and Resizing Image in Picture Tools in Excel

 

Tips

We used the Yearly design as the source for our demo. Please note that you can copy calendar months from any of the existing calendar designs.

You can copy as many months as needed.

You can also apply colors/borders on the cells (in addition to the images) in the new calendar layout

You can add text or text boxes as needed.

 

PRINTING

If we would like to export to PDF or to print this new design, we need to set it up for printing.

Select cells that cover the printable area. We shouldn’t select the images directly. Whatever area of cells we select, any image that floats over that area will get printed.

Click on Page Layout ribbon and select Print Area –> Set Print Area.

Set Print Area for Calendar
Set Print Area for Calendar

 

Then, we can print or export as usual from the File menu. We would choose the appropriate print settings based on the layout we design (for example: landscape or portrait).

 

Please visit the Event Calendar Maker template to learn more about its features

 

If there are any questions about how to design the calendar layout or if you have suggestions to improve this article, please comment.

How to filter events to display on calendars?

Posted on
Event Calendar Maker - Excel Template - Filter Events

In this article, I will explain how we can filter events in calendars using our Event Calendar Maker Excel Template. The template allows creating customized event calendars easily with 7 different designs automatically created based on your input event data.

Sometimes, when you print or export those calendars, you may want to display only certain event types or remove certain event types. For example, you are storing events assigned to different projects and you want to display only events of one project at a time. I explain below how we can easily do that.

 

VIDEO DEMO

STEPS

First, let’s enter some information in the SETTINGS sheet. Let’s leave the default basic settings as it is, to create a 2016 calendar.

Event Calendar Maker - Excel Template - Basic Settings
Event Calendar Maker – Excel Template – Basic Settings

 

Then, we enter four event types and assign different colors.

Event Calendar Maker - Excel Template - Event Types
Event Calendar Maker – Excel Template – Event Types

 

In the EVENTS sheet, we enter four events, one for each event type. They all are weekly events happening for the entire year, on Mon, Tue, Wed and Thu respectively.  We are choosing this so that showing them on the calendar designs will be easier. The concept we are going to learn is applicable for any type of one-time or recurring events.

Event Calendar Maker - Excel Template - Events
Event Calendar Maker – Excel Template – Events

 

The ‘Yearly’ calendar design should look like this now.

Event Calendar Maker - Excel Template - Yearly Calendar
Event Calendar Maker – Excel Template – Yearly Calendar

 

This is great. We can print or export to PDF and share. Sometimes, we may not want the calendar to have all the event types. We may want to print each event type (‘Meetings’ vs ‘Holidays’) separately. We can do so very easily. We are going to use the ‘ACTIVE?’ column available in the ‘Events’ table. This column is designed for you to enter No for any single event that you do not want to display on the calendar. But in this case, we want to be able to filter on a group of events and instead of going to each row and enter No, we will see how we can write formulas to do this more efficiently.

Each row in the Events table is considered as an ‘Event’. Events are grouped into ‘Event Types’. An ‘Event’ can be recurring and each such occurrence is called ‘instance’.

First, let’s change the values allowed in this column. Choose DATA ribbon –> ‘Data Validation’ –> ‘Data Validation’, as shown below.

Event Calendar Maker - Excel Template - Data Validation
Event Calendar Maker – Excel Template – Data Validation

 

Then, in the following window, please add Yes to the list of allowed values and check the box at the bottom.  Click OK.

Event Calendar Maker - Excel Template - Change Data Validation
Event Calendar Maker – Excel Template – Change Data Validation

 

Now, we are ready to start filtering. We will cover 4 different scenarios. Depending on which scenario is applicable to you, please choose the corresponding formula.

1. Display only events of one specific ‘Event Type’

Let’s write a simple formula.

=IF([@[EVENT TYPE]]=”Meetings”,”Yes”,”No”).

This formula will enter Yes in the ‘ACTIVE?’ column whenever the ‘Event Type’ is Meetings. Otherwise, it will be set to No.

Event Calendar Maker - Excel Template - Keep only one event type
Event Calendar Maker – Excel Template – Keep only one event type

 

Let’s see how the Calendar looks now. Only events of ‘Event Type’ Meetings will appear.

Event Calendar Maker - Excel Template - Calendar - Keep Only One Event Type
Event Calendar Maker – Excel Template – Calendar – Keep Only One Event Type

 

A mouse can also be used while writing the formula. Please see the video demo above.

2. Filter (and not display) events of one specific ‘Event Type’

The formula will be

=IF([@[EVENT TYPE]]=”Meetings”,”No”,”Yes”).

This formula will enter No in the ‘ACTIVE?’ column whenever the ‘Event Type’ is Meetings. Otherwise, it will be set to Yes.

Event Calendar Maker - Excel Template - Filter One Event Type
Event Calendar Maker – Excel Template – Filter One Event Type

 

The Calendar will appear as below. All the three event types other than the Meetings event type will appear on the calendar.

Event Calendar Maker - Excel Template - Filter One Event Type - Calendar
Event Calendar Maker – Excel Template – Filter One Event Type – Calendar

 

3. Display only events of more than one specific ‘Event Type’

The formula will be

=IF(OR([@[EVENT TYPE]]=”Meetings”,[@[EVENT TYPE]]=”Holidays”),”Yes”,”No”).

This formula will enter Yes in the ‘ACTIVE?’ column whenever the ‘Event Type’ is Meetings or Holidays. Otherwise, it will be set to No.

Event Calendar Maker - Excel Template - Display more than one event type
Event Calendar Maker – Excel Template – Display more than one event type

 

Calendar appears as shown below.

Event Calendar Maker - Excel Template - Display More than One Event Type - Calendar
Event Calendar Maker – Excel Template – Display More than One Event Type – Calendar

 

4. Filter (and not display) events of more than one specific ‘Event Type’

The formula will be

=IF(OR([@[EVENT TYPE]]=”Meetings”,[@[EVENT TYPE]]=”Holidays”),”No”,”Yes”).

This formula will enter No in the ‘ACTIVE?’ column whenever the ‘Event Type’ is Meetings or Holidays. Otherwise, it will be set to Yes.

Event Calendar Maker - Excel Template - Filter more than one Event Type
Event Calendar Maker – Excel Template – Filter more than one Event Type

 

Calendar will appear as below.

Event Calendar Maker - Excel Template - Filter more than one Event Type
Event Calendar Maker – Excel Template – Filter more than one Event Type

 

This is how we can easily control which event types’ events are displayed on the calendars. We just saw the ‘Yearly’ calendar design but there are totally 7 calendar designs in the template which will be automatically created for you instantly.

For more about this template, please visit Event Calendar Maker Excel Template.

5 reasons why events are not displayed on Calendar

Posted on

In this article, we will cover the reasons why your events may not be displayed on the calendars in our Event Calendar Maker Excel Template. You may enter some events data in the ‘Events’ sheet, then go over to the Calendar design sheets and be disappointed not to see those events displayed on the calendars. Let’s see why that might happen and how to avoid them.

REASONS

Please note that the reasons are also available inside the template in the Events sheet at the top. Just select YES to the ‘Do you need Help?’ question.

Event Calendar Maker - Excel Template - Help Section
Event Calendar Maker – Excel Template – Help Section

 

The following is the list of potential reasons. We will see them in detail further down this post.

  1. Validation Field shows ‘Error’
    1. Event Start Date is blank (or empty)
    2. Event End Date is before the Event Start Date
    3. Frequency Type is blank
  2. Validation Field shows ‘Not displayed’
    1. Event End Date is before the Calendar Start Date
    2. Event Start Date is after the Calendar End Date
  3. Data has more than 300 active events and 1200 active instances
  4. Event Type is blank or not one of the Event Types listed in the Home sheet.
  5. Event is not entered inside the ‘Events Table’

 

VIDEO DEMO

 

1. Validation Field shows ‘Error’

The last column (column U) in the table is ‘Validation’. If it displays ‘Error’ for any row, then that event will not be displayed on the calendars.

Event Calendar Maker - Excel Template - Error
Event Calendar Maker – Excel Template – Error

 

The potential reasons for the error are

  • Event Start Date is blank (or empty)
  • Event End Date is before the Event Start Date
  • Frequency Type is blank

Event Start Date, Event End Date (except for One-time events) and Frequency Type are required fields and without them the event cannot be displayed.

 

2. Validation Field shows ‘Not Displayed’

The same validation field may show ‘Not Displayed’.

Event Calendar Maker Excel Template - Not Displayed
Event Calendar Maker Excel Template – Not Displayed

This can happen if a) Event End Date is before the Calendar Start Date or b) Event Start Date is after the Calendar End Date.

Calendar Start Date is what you enter in the Home sheet.

Event Calendar Maker Excel Template - Calendar Start Date
Event Calendar Maker Excel Template – Calendar Start Date

 

In the below image, Event Event Date is Dec 1, 2015. Calendar Start Date is Jan 1, 2016. Hence this event will not be displayed on the calendars.

Event Calendar Maker Excel Template - Event End is before the Calendar Start
Event Calendar Maker Excel Template – Event End is before the Calendar Start

 

In the below image, Event Start Date is Feb 1, 2017. Assuming the same Calendar Start Date of Jan 1, 2016 and it will end on Jan 31, 2016 as the template is a 12-month calendar template. Hence this event will not be displayed on the calendars.

Event Calendar Maker Excel Template - Event Start Date is after Calendar End Date
Event Calendar Maker Excel Template – Event Start Date is after Calendar End Date

 

3. Data has more than 300 active events and 1200 active instances

The template is designed to handle 300 active events and 1200 active instances. You can deactivate an event by entering No in the ACTIVE? column.

The number of events and instances are displayed on the Events sheet at the top. If the number of events is greater than 300 or if the number of instances is greater than 1200, those additional event instances will not displayed. Please limit the number of instances if possible.

Event Calendar Maker - Excel Template - More than 1200 instances
Event Calendar Maker – Excel Template – More than 1200 instances

 

4. Event Type is blank or not one of the Event Types listed in the Home sheet

The template uses the Event Types to be able to color the events on the calendar. As we map each Event Type to a color in the Home sheet, this drives all the colors on the calendars.

Event Calendar Maker – Excel Template – Event Types
Event Calendar Maker – Excel Template – Event Types

 

So, if you do not enter an Event Type in the event row in the Events sheet, then the template does not know how to color the event on the calendars. Please note that the event will still be listed but not colored.

There are 7 calendar designs in the template and 5 of them list events along with coloring. 2 of the designs do not list the events, but show the colors on the calendars.

Similarly, if you mistype an Event Type and that is not one of the Event Types you have set in the Home sheet, it will have the same result. So, please make sure that the correct Event type is selected for each event.

 

5. Event is not entered inside the ‘Events Table’

Data entry in the Events sheet should be done inside the table.

For more on Excel Tables – please see these videos from Useful Excel for Beginners course

The data in the table should begin from row 11. So, to begin entering your events, please enter your first event in row 11. Then, enter in row 12 and so on. If any of the events is not displayed on the calendars, to check whether the data is inside the table, follow this simple step. Let’s say your event entered in row 12 is not getting displayed on calendar.

Event Calendar Maker - Excel Template - Events Data
Event Calendar Maker – Excel Template – Events Data

 

Click on cell A12. Check at the top menu section to see if a new ‘Table Tools’ ribbon appears. If it appears, your data is inside the table, if not, it is not. 🙂 In the image below, when I had clicked on cell A11, the Table Tools ribbon appears, but it does not when I clicked on cell A12.

 

Event Calendar Maker - Excel Template - Table Tools Ribbon
Event Calendar Maker – Excel Template – Table Tools Ribbon

 

This issue will not happen if you enter first in row 11 and then in row 12 and move on one row at a time.

 

 

More support articles about the Event Calendar Maker Excel Template

 

If you have any further questions about this template, please let me know. Thank you.

How to prioritize events on multi-event days?

Posted on

In this article, we will see how we can control the order in which events appear on days that have multiple events. We will be using the Event Calendar Maker Excel template.

You can watch this video or follow the instructions in text further below.

 

First, in the Home sheet, let’s create three event types and assign them to colors.

Event Calendar Maker Excel Template - Event types
Event Calendar Maker Excel Template – Event types

 

Then, let’s create actual events.

Event Calendar Maker Excel Template - Events
Event Calendar Maker Excel Template – Events

 

Once the data is entered, the 7 calendar designs are automatically created.  We will use the ‘Monthly with Events’ design for this demo, since it can display up to 5 events per day. The month of February 2016 will appear as shown below.

Event Calendar Maker Excel Template - Calendar
Event Calendar Maker Excel Template – Calendar

 

As we zoom into Feb 8th (where all 3 events overlap), we will see that the 3 events appear in the order in which they are entered in the Events table.

Event Calendar Maker Excel Template - Feb 8th - Initial
Event Calendar Maker Excel Template – Feb 8th – Default

 

I would like to change the order and have Conference appear at the top, followed by Stakeholder Update and finally Team Meeting. This will create a nice teal band (Conference) at the top all the way from Feb 4th to Feb 15th. 🙂 It is very easy to do that with this template. We will use the PRIORITY column in the Events table to assign the priority among events. (as shown below).

Event Calendar Maker Excel Template - Priority
Event Calendar Maker Excel Template – Priority

 

Now, when we see the calendar, the order has changed. 🙂

Event Calendar Maker Excel Template - Calendar - Prioritized
Event Calendar Maker Excel Template – Calendar – Prioritized

 

And Feb 8th specifically looks like this.

Event Calendar Maker Excel Template - Feb 8th - Prioritized
Event Calendar Maker Excel Template – Feb 8th – Prioritized

 

This is how you can easily control the order of events on the calendar. If there are any questions, please leave them in the comments. Thank you.

How to create U.S. holidays on Calendar?

Posted on

In this video, I create official U.S. holidays on calendar using our Event Calendar Maker Excel template. It is to illustrate the functionality of the template where creating events such as 3rd Monday of a month and last Monday of a month is simple and easy. Watch the video to learn how you can also create similar events for such holidays or special days in your organization.

 

How to create annual events on Calendar?

Posted on

It is very easy to create annual events such as Birthdays or Anniversaries, using our Event Calendar Maker Template. In the video below, I explain how to create such events and then re-use the same template year after year. There are multiple ways to create the desired effect. I create events that occur every 12 months in the video.

 

Another approach would be to create one-time events and then every year come and change just the year alone. Changing the year for all the events can be done easily by Excel’s built-in ‘Find and Replace’ feature by pressing Ctrl+F.

Also, please remember that you can create quarterly events by simply choosing N = 3 instead of 12. The template is designed to be very flexible to accommodate various scenarios and it does so with minimal data entry as well. 🙂