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.

2 thoughts on “How to filter events to display on calendars?

  1. Is there an easier way to automate filtering of event types, perhaps through a select button and/or macro? For example, I would like to have some buttons that, when selected, changes the formula in the Active field for what that button represents.

    1. Thanks for your feedback.

      I am sorry that there is no button based filtering available. I will be looking into providing event type based filtering in the future version of this template.

      Best wishes.

Leave a Reply

Your email address will not be published. Required fields are marked *