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.
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
Then, we enter four event types and assign different colors.
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
The ‘Yearly’ calendar design should look like this now.
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
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
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
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
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
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
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
Calendar appears as shown below.
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
Calendar will appear as below.
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.