2020 Excel Calendar Template – 20 Calendar Designs – Free Download

Posted on
2020 Calendar Excel Template

Download 2020 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 20 different calendar designs, including annual (yearly), monthly, weekly and daily calendars. All in one file, free to download.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit 2020 Calendar in Google Sheets.

2020 Calendar Template - Monthly - January 2020
2020 Calendar Template – Monthly – January 2020

TOPICS

We will discuss the following topics in this post.

  • Features of 2020 Calendar template
  • How to download this template for free?
  • Instructions on how to use the template
  • 20 Calendar designs – their features
  • Tips to further customize the template

FEATURES OF 2020 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 20 Calendar designs (as this is 2020 😊)
    • 12 (of the 20) designs automatically list the Holidays and events
    • 2 (of the 20) designs allow typing events directly on the calendar itself
  • All 20 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 2020 CALENDAR TEMPLATE

2020 Calendar Excel Template

2020 Calendar Excel Template with Sample Data


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

Basic Settings - 2020 Excel Calendar Template
Basic Settings – 2020 Excel Calendar Template

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.

Change Beginning Month of Calendar
Change Beginning Month of Calendar

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

Start Week from any day
Start Week from any day

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 Weekends
Choose Weekends

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.

Enter Holidays for 2020 Calendar
Enter Holidays for 2020 Calendar

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.

Enter events to display on calendar
Enter events to display on calendar

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 2020 in the Events table.

Step 5: Pick a calendar design from the 20 designs

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

The 2020 calendar template offers 20 different calendar designs to choose from.

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

20 Calendar Designs in 2020 Excel Calendar Template
20 Calendar Designs in 2020 Excel Calendar Template

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

20 Calendar Sheets
20 Calendar Sheets

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.


20 CALENDAR DESIGNS

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

2020 Calendar Designs
2020 Calendar Designs

Calendar Design 1

1 Page 12 Months – 4 X 3

2020 Calendar Design 1 – 1 Page 12 Months – 4 X 3
2020 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

2020 Calendar Design 2 – 1 Page 12 Months – 3 X 4
2020 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

2020 Calendar Design 3 – 1 Page 12 Months – 2 X 6
2020 Calendar Design 3 – 1 Page 12 Months – 2 X 6
  • 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

2020 Calendar Design 4 – 1 Page 12 Months – 6 X 2
2020 Calendar Design 4 – 1 Page 12 Months – 6 X 2
  • 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

2020 Calendar Design 5 – 1 Page 12 Months – 12 X 42 with Events
2020 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

2020 Calendar Design 6 – 1 Page 12 Months – 12 X 31 with Events
2020 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

2020 Calendar Design 7 – 6 Pages - Planner
2020 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

2020 Calendar Design 8 - 1 Page – 54 Weeks – with Events
2020 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

2020 Calendar Template - Monthly - January 2020
2020 Calendar Template – Monthly – January 2020
  • 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

2020 Calendar Design 10 – 6 Pages with Events
2020 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

2020 Calendar Design 11 – 4 Pages with Events
2020 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

2020 Calendar Design 12 – 3 Pages with Events
2020 Calendar Design 12 – 3 Pages with Events
  • 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

2020 Calendar Design 13 – 2 Pages - 6 Months each
2020 Calendar Design 13 – 2 Pages – 6 Months each
  • 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

2020 Calendar Design 14 – 12 Pages with Events
2020 Calendar Design 14 – 12 Pages 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

2020 Calendar Design 15 – 12 Pages - Type your events
2020 Calendar Design 15 – 12 Pages – Type your 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

2020 Calendar Design 16 – Picture Calendar
2020 Calendar Design 16 – 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

2020 Calendar Design 17 – Quotes Calendar
2020 Calendar Design 17 – 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

2020 Calendar Design 18 – Weekly Calendar
2020 Calendar Design 18 – 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

2020 Calendar Design 19 – Daily Agenda Calendar
2020 Calendar Design 19 – Daily Agenda 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.

Calendar Design 20 (Two Years Calendar)

1 Page – 2 Years Calendar

2020 Calendar Design 20 – Two years Calendar
2020 Calendar Design 20 – Two years Calendar
  • This design prints one day in 1 page.
  • Displays 2 years
  • Weekends are highlighted on the calendar.
  • Holidays and Events are highlighted.
  • Holiday Names and Event Names are not listed in this design.

TIPS TO CUSTOMIZE THE 2020 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 - 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 2020.

All Purpose Calendar Excel Template
2013 Tamil Calendar

PTO (Paid Time Off) Balance Calculator for Hourly Employee

Posted on
Employee Report - PTO Calculator - Hourly Employees - Screenshot

If you are looking for a Simple & Effective solution to calculate and track Paid-Time-Off (PTO) for your hourly employee, you have reached the right place. You can download this free Excel template to track and calculate Employee’s PTO accrual balances based on hours worked by the employee.

If you need a PTO Calculator that calculates accrual based on employee tenure, please see PTO Calculator (Salaried Employees)

If you are familiar with PTO calculation, you would be aware that though it appears to be simple from the outside, accounting for various scenarios and PTO policies make such calculation complex and not easy to implement.

For more details on key components of a PTO policy and how to calculate PTO balances, please read PTO Policy Factors and Balance Calculation

Don’t worry. This Excel template makes all that simple to implement, as all the hard work is already done. You enter the inputs of your PTO policy and are ready to track PTO balance automatically within a few minutes.

Employee Report - PTO Calculator - Hourly Employees - Screenshot
Employee Report – PTO Calculator – Hourly Employees – Screenshot

In this article, we will cover the following topics.

  1. Overview of Features of the Excel template
  2. Download link to the free Excel template
  3. Overview of how to use the template
  4. Detailed Step by Step user guide on how to track PTO for your hourly employee

KEY FEATURES OF THE EXCEL TEMPLATE

  • Options available covers most common business PTO policy scenarios
  • Very flexible and easy to customize for your specific business needs
  • Automatically calculates current PTO balance and projected future balance
  • Interactive and printable PTO Report showing all details in a simple calendar view
  • Supports tenure-based accrual rate tiers
  • File is designed for one employee only. Make a copy of workbook to use for second employee.
  • Tracks PTO balance for at least 2 years (and can be reset in new file and extended).

FREE DOWNLOAD

PTO Calculator (Hourly Employee) Excel Template

REQUIREMENTS

Microsoft Excel 2010 or newer

LIMITS

  • Tracking PTO for 1 employee in a file. Please make copy of the entire file to track second employee.
  • 500 Leave Entries

HOW TO USE THE EXCEL TEMPLATE

VIDEO DEMO

OVERVIEW OF STEPS

Before we get into all the details, I want to provide an overview of the 5 simple steps in using the template.

  1. Enter employee details and PTO policy inputs in the PTO POLICY SETTINGS sheet
    • Review first set of accrual days to ensure information is correct. Ensure there are no errors in the data entry validation.
  2. Enter your company’s Weekends and Holidays in WEEKENDS HOLIDAYS Sheet
  3. When employee works different hours (more or less) than the default daily hours, enter them in the HOURS WORKED sheet
  4. When employee takes PTO, enter PTO info in the PTO USED sheet
  5. View PTO balance and balance trends in the PTO REPORT sheet

STEP BY STEP GUIDE

Let’s start from the beginning – in the PTO POLICY SETTINGS sheet.

Settings - Employee Details and Start Date
Settings – Employee Details and Start Date

STARTING DATE

Enter the date from which you would like to use this template to track PTO. There are two key purposes for this date. 1) The template will only calculate PTO from this date. 2) The Starting Balance (which we will discuss soon) will be the balance at the end of the day before this Starting Date.

Employee Details

EMPLOYEE NAME

Enter name of employee for whom we will be tracking and calculating PTO balance. This will automatically then appear in the PTO Report.

HIRE DATE

Enter the date when employee joined the company. Even if you have been tracking PTO using some other tool and now want to switch to use this template, enter the actual hire date of the employee. I will soon explain how you can carry over balance from your previous tool.

STARTING BALANCE

Here, we can enter balance we carry over for employees whom you have been tracking PTO from other tool before you switch to using this template.

For example, if the employee started working in 2017, but you started to switch to this template on July 3, 2019, then you can enter the starting balance as of end of July 2, 2019. Any PTO used from July 3, 2019 will have to be entered in the template.

DEFAULT HOURS WORKED

This is to record how many hours this employee works every working day. This option is given to reduce data entry. Let’s say the employee works usually 8 hours a day but occasionally he/she may work less or more than 8 hours. You can enter 8 as default working hours. On all days except holidays and weekends, the template will assign the default hours worked by employee.

You can then enter adjustments for specific dates when the employee did not work 8 hours.

Instead of entering the actual hours every day, the template allows setting the default hours once and then only entering work hours when it deviates from the default. Less data entry. Simple and Effective.

If your employee has varying hours daily, then you can enter 0 hours as default and choose to enter actual hours for every day (I will show where to enter that information in the upcoming steps below).

That’s how flexible the template is. You can make it work to suit your business requirements.

Now, let’s get into the PTO Policy. Fun Stuff!

Here is a sample policy.

PTO Policy - Hourly Employee
PTO Policy – Hourly Employee

There are several components of a PTO policy. We will go through one by one.

PTO ACCRUAL PERIOD

This is to inform how we often accrue the PTO. We have 4 options here: Weekly, Every 2 Weeks, Twice a Month and Monthly.

PTO Accrual Period - Options - Weekly, Every 2 Weeks, Twice a Month, Monthly
PTO Accrual Period – Options – Weekly, Every 2 Weeks, Twice a Month, Monthly

Weekly

This option means that employee can accrue PTO balance once a week, provided he/she has worked enough hours (cumulatively) to meet the Accrual rate policy.

PTO Policy - Weekly Accrual
PTO Policy – Weekly Accrual

In the above screenshot, you can see that a new input (First Accrual Date) becomes necessary for weekly policy. Since a weekly accrual can happen on Fridays in some companies and Mondays in some companies, we provide the option to enter a specific date when the first accrual should happen. Here, we entered 5th July 2019 – which is a Friday.

So, the template will set accruals every Friday beginning on 5th July 2019. 5th July, 12th July, 19th July…..

Every 2 Weeks

Entering an Every 2 Weeks policy is similar to weekly. The template, in this case, will only set accruals every 2 weeks.

PTO Policy - Every 2 Weeks Accrual
PTO Policy – Every 2 Weeks Accrual

If the first accrual date is 5th July 2019, the template will automatically set up accruals every other Friday – 5th July, 19th July, 2nd Aug, 16th Aug…..

Twice a Month

Now, let’s move to Twice a Month option. Here, we can enter the two dates of the month the accrual will happen.

PTO Policy - Twice a month Accrual
PTO Policy – Twice a month Accrual

For example, in the screenshot above, we have chosen 1st and 15th of every month.

You have control over the specific 2 days of the month. The template even supports ‘Last Day’ option.

PTO Policy - Twice a month Accrual - Last Day
PTO Policy – Twice a month Accrual – Last Day

For example, the above scenario will set up accrual on 15th of every month and Last Day of every month. If the month has 28 days or 29 days or 30 days or 31 days, the template will automatically figure out the last day and set up accrual for that day.

Monthly

I am sure that by now, you see the pattern here. For monthly, we can set the day of month that we want accrual to happen every month.

PTO Policy - Monthly Accrual
PTO Policy – Monthly Accrual

For example, as shown above, we can do accruals on 1st of every month.

Instead of 1st, you can type in any day of the month. You can also choose ‘Last Day’ of month.

PTO Policy - Monthly Accrual - Last Day
PTO Policy – Monthly Accrual – Last Day

REVIEW FIRST ACCRUAL

We need to check and ensure that the data provided in the inputs are correct and result in a valid accrual policy. The template shows the first set of accrual dates.

Review First Set of Accrual Days
Review First Set of Accrual Days

In the above screenshot, there are no errors. The accrual dates are weekly from 5th July. (Probationary period setting is not considered in this list of dates and the dates shown will include dates in any probationary period you may have set).

The template has in-built data validations to identify if the inputs do not align.

For Weekly and Every 2 Weeks options, First Accrual Date should be within 1 or 2 weeks from the Starting Date. Otherwise, you will see an error message like this one.

Data Validation - First Accrual Date should be within 1 week of Starting Date
Data Validation – First Accrual Date should be within 1 week of Starting Date

In these cases, update the First Accrual Date input to ensure that it is correct.

Now let’s discuss Rollover Policy.

ANNUAL PTO ROLLOVER POLICY

The template provides three options.

Rollover policy options
Rollover policy options
  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over.
  3. Unlimited Rollover: Here the employee does not lose any PTO and will carry over everything to next year.

With rollover policies, there is another variation. Some companies may apply rollover at calendar year change 1st Jan of every year or on work anniversary dates (which vary for each employee). You can easily change that setting.

Rollover Timing options
Rollover Timing options

PROBATIONARY PERIOD

Enter the duration of the Probationary period in days.

Settings - Probationary Period, Maximum Allowed Balance
Settings – Probationary Period, Maximum Allowed Balance

For example, in the screenshot above, employee does not earn any PTO during the first 90 days of employment.

MAXIMUM ALLOWED PTO BALANCE

You can set a limit on maximum balance at any time, to ensure that there is a limit to PTO accrual. You can set the amount (hours)in the Maximum Allowed PTO Balance field.

ROUNDING IN PTO BALANCE

When the template calculates balances and display in the report, you can control the precision by just entering the number of decimals.

PTO ACCRUAL RATE

This defines how much PTO hours are earned by the employee for hours worked.

Accrual Rate Based on Tenure - One Policy
Accrual Rate Based on Tenure – One Policy

For example, in the above screenshot, the employee would earn 1 hour of PTO for every 40 hours worked.

Since the tenure completed is 0 and we have only tier, the policy will apply to all employees regardless of tenure.

There should always be a record with 0 tenure completed

  • If the employee worked 39 hours, he/she would not earn any PTO. But once the 40th hour is completed, he/she will earn 1 hour of PTO.
  • The 40 hours do not have to be in the same week or month.

If in your company, the employee would earn 4 hours of PTO for every 160 hours worked, then you would enter 4 and 160 in the corresponding cells.

Accrual Rate Based on Tenure - One Policy - Multiples
Accrual Rate Based on Tenure – One Policy – Multiples

In this scenario, for the first 159 hours, the employee does not earn anything. But once the 160th hour of work is completed, then it accrues 4 hours of PTO. Essentially, the employee accrues in multiples of 4 hours.

You can also have a tiered structure where you reward loyal employees with higher PTO accrual rate.

Accrual Rate Based on Tenure - Tiered Policy
Accrual Rate Based on Tenure – Tiered Policy

In the above screenshot, employees with less than 12 months tenure will earn 1 PTO hour per 40 hours worked. Employees between 12 to 24 months tenure will earn 2 PTO hours for same 40 hours worked, while employees above 24 months tenure will earn 3 PTO hours per 40 hours worked.

WEEKENDS & HOLIDAYS

Now, we are ready to enter the company weekends and holidays.

Weekends
Weekends

Choose TRUE for days that are considered weekends in your company.

For example, in the above screenshot, we have set Saturday and Sunday to be weekends. Hence the employee will not by default work any hours during those days. Also, if an employee takes PTO that spans across weekends, the weekend days will not be counted as PTO taken and thus not reduce the PTO balance.

Holidays work similarly, except here you must enter each holiday individually. This is done in the HOLIDAYS sheet.

Holidays
Holidays

For example, in the below screenshot, we have set Saturday and Sunday to be weekends. Hence the employee will not by default work any hours during those days. Also, if an employee takes PTO that spans across weekends, the weekend days will not be counted as PTO taken and thus not reduce the PTO balance.

Read how to enter and delete data in Excel tables

Now we have gone through the various PTO policy input options in the PTO calculator. These settings have to be entered only once for an employee.

HOURS WORKED

In this template entering data on hours worked is simple, as we have already set default hours worked initially. On days when employee worked hours different than the default, then we enter them in the HOURS WORKED sheet.

Enter Hours Worked by Employee
Enter Hours Worked by Employee

It is important to note that you should only enter the adjustment.

For example, if the default hours worked is 8 and if you enter an adjustment of -8, that means the employee worked 0 hours on that day.

If the adjustment is 1, then the employee worked 9 hours on that day.

PTO USED

When an employee takes PTO or plans to take PTO, you can enter that in the PTO USED sheet.

Enter PTO Days taken by employee
Enter PTO Days taken by employee

We can enter date ranges to enter multi-day vacation. Even if it is a single day vacation, please enter both start and date as the same date. All 3 fields (PTO START DATE, PTO END DATE, PTO HOURS) are required entries here.

Multi-day Vacations
It is important to note here that the PTO Hours you enter is the daily value not total for multiple days.

In the above example, 1 hour of PTO for each of the 2 days (May 7th, May 8th) – in total 2 hours – will be subtracted from the PTO balance, assuming those dates are not Weekends and Holidays in your company. However, you should enter 1 and not 2 in PTO Hours column.

Now that we have entered the necessary data inputs, we are ready to view the PTO report.

PTO REPORT

Employee Report - PTO Calculator - Hourly Employees - Screenshot
Employee Report – PTO Calculator – Hourly Employees – Screenshot

Let’s take the report in two sections. The top section shows the summary.

Employee Report - PTO Calculator - Hourly Employees - Summary
Employee Report – PTO Calculator – Hourly Employees – Summary

BALANCES

By default, the report shows today’s PTO balance for the employee. The Projected Balance on a future date can also be shown. You can modify the date and can view PTO balance at any date. That will consider future data (work hours, PTO used, holidays and weekends).

POLICY DETAILS

Policy Details along with key dates are shown so that when you print the report, the policy details are also clearly visible.

The bottom section provides an interactive calendar.

Employee Report - PTO Calculator - Hourly Employees - Calendar
Employee Report – PTO Calculator – Hourly Employees – Calendar

DATES

The Calendar (12 month) view is to show specific details on each date for a period you choose. You can enter the FROM and TO dates. The calendar will then display information for that specific period.

DISPLAY

The display inside the calendar shows the values for each date and you can control what is displayed.

Calendar Display Choices
Calendar Display Choices

You can display Worked (Number of Hours worked by employee on the day) or Balance Change (PTO Balance Accrued on the day) or PTO Used (PTO used by employee on the day).

WORKED

Employee PTO Report - Calendar Hours Worked
Employee PTO Report – Calendar Hours Worked

You can view the number of hours worked by employee on each day. This will include any work hours adjustments you entered.

BALANCE CHANGE

This shows the accrual days and the amount of PTO Balance change (hours) on those accrual days.

Employee PTO Report - Calendar - Balance Change
Employee PTO Report – Calendar – Balance Change

PTO USED

Employee PTO Report - Calendar - PTO used
Employee PTO Report – Calendar – PTO used

This will reflect the dates when the employee takes PTO and the number represents the number of hours of PTO used.

MONTHLY BALANCES

On the right side you can view the Monthly starting PTO Balance and Monthly Ending PTO Balance. Please note that all balances reflect at end of day.

MONTH-END BALANCE TREND

At the bottom of the report, the month-end balance trend chart shows the balances month over month.

PRINT OR EXPORT

You can Print the report or export to PDF and share.

BALANCE ADJUSTMENTS

If you would like to add or remove PTO to the balance outside the PTO policy settings you have entered, then you can use the Adjustment table. This allows you to add to PTO balance (enter positive value) or reduce from PTO balance (enter negative value).

For example, if you would like to reward employee with extra PTO, you can enter here as a positive adjustment.

Balance Adjustments
Balance Adjustments

This sheet can be ignored if you don’t need to make such adjustments.

 

I hope this free Excel template is useful to track Paid Time Off (PTO) for hourly employee in your organization.

Does this template address policy settings in your organization? Does this save time in calculating PTO balances? Do you have any suggestions on how this template can be improved further?

Please provide your feedback in the Comments below. Thanks for your support.

You may also be interested in...

Employee PTO Tracker - Salaried
Employee Timesheet Exclel Template

Combinations Generator Excel Template

Posted on

A few months ago, I published a video (on YouTube) on how to create combinations of values in 2 lists. There have been requests to expand that to more than just 2 lists. So, here we go. I updated the formulas to accommodate 6 lists and made it into an easy-to-use free template.

Combinations Generator Excel Template
Combinations Generator Excel Template

FREE DOWNLOAD

Combinations Generator Excel Template

HOW TO USE THE TEMPLATE

It is one of the simplest templates published on indzara.com.

Just enter your values in each list (max 6) and see the combinations automatically calculated and displayed.

Combinations Generator Excel Template
Combinations Generator Excel Template

You can also change the separator (character that separates the values in the concatenated string of values)

When entering data, ensure the following are avoided

  • No blank values in the middle of any list
  • Enter lists sequentially (eg. please do not enter list 6 when list 5 is empty)

VIDEO DEMO

LIMITS

  • You can enter up to 6 lists
  • 500 combinations are supported by default. The formulas are hidden in the HELP sheet. You are welcome to edit and expand to more combinations if needed.

RECOMMENDED TEMPLATE

2014 Indian Lok Sabha Elections Results in Excel

Posted on
2014 Lok Sabha Election Results

It is election season now, with results of 2019 Lok Sabha Elections in India to be announced in a few days on 23rd May 2019.

I wanted to develop a simple template in Excel that can allow us to view 2009 and 2014 Election results, and gain insights through simple data visualization techniques.

Download this free Excel template to view the results of 2009 and 2014 Lok Sabha Elections. You may also find data visualization techniques that you can apply to other topics or datasets. If you have any suggestions to make it better, please post them in Comments section below.

 

2014 Lok Sabha Election Results in Excel
2014 Lok Sabha Election Results in Excel

 

Features of Template

 

  • Easily view 2009 and 2014 Lok Sabha results and compare 2014 vs 2009
  • Visualize the data in 3 different aspects – Parliament View, State View and Constituency View
    • Parliament View – 543 seats visualization by State, Party, Alliance & Win Margin %
    • State View – Filter by state to see top parties and their vote share
    • Constituency view – choose one constituency and see the top 5 candidates

 

Free Download

 

 

Video Demo

 

https://youtu.be/9hQtZ44gvKE
Video Demo – 2014 Lok Sabha Election results in Excelhttps://youtu.be/9hQtZ44gvKE

 

Parliament View

In the Parliament view, each cell represents a constituency and there are 543 constituencies. You can use 4 options available to you, to create a visual that specifically addresses a question.

 

Lok Sabha Elections - 543 Seats
Lok Sabha Elections – 543 Seats

First, let me explain the four options to control to visual.

1. DISPLAY: This determines what value is displayed on each cell.

 

Display Filter options
Display Filter options

The four options within DISPLAY are

 

  1. STATE – State to which the constituency belongs
  2. PARTY – (Abbreviated) Name of Party that won that constituency in the election.
  3. ALLIANCE – Alliance (UPA or NDA or Others) that won that constituency in the election
  4. WIN MARGIN % – The Winning Margin % (difference between winner and runner up as a % of all votes polled in that constituency) in that constituency

2. COLOR: This determines what color is applied to each cell.

 

Color Filter options
Color Filter options

The four options are

 

  • STATE – State to which the constituency belongs. Each state and union territory is given a different color

 

States and Union Territories - By Color
States and Union Territories – By Color

 

  • PARTY – (Abbreviated) Name of Party that won that constituency in the election. To simplify the visual, INC (Indian National Congress) and BJP (Bharatiya Janatha Party) are shown separate colors and all others are combined to one color.

 

Party Colors INC, BJP and Others
Party Colors INC, BJP and Others

 

  • ALLIANCE – Alliance (UPA or NDA or Others) that won that constituency in the election. Each alliance is given a different color.

 

Alliance Colors NDA, UPA and Others

 

  • WIN MARGIN % – The Winning Margin % (difference between winner and runner up as a % of all votes polled in that constituency) in that constituency. This is grouped into 5 categories. 1) 0 to 1% 2) >=1% and <5% 3) >=5% and <10% 4) >=10% and <20% 5) >=20%

 

Win Margin % in 5 categories
Win Margin % in 5 categories

3. SORT: This determines how the 543 constituencies are sorted and thus which constituency represents which cell. This helps in organizing or grouping constituencies together by State or Party or Alliance.

 

Sort Filter options
Sort Filter options

The three options are

 

  1. STATE – State to which the constituency belongs
  2. PARTY – (Abbreviated) Name of Party that won that constituency in the election. To simplify the visual, all parties except INC (Indian National Congress) and BJP (Bharatiya Janatha Party) are combined as one unit.
  3. ALLIANCE – Alliance (UPA or NDA or Others) that won that constituency in the election

4. YEAR: As the template has data for 2009 and 2014 elections, you can choose Year to view results of that specific year’s elections.

 

Year Filter options
Year Filter options

Using the above 4 choices, you can come up with many combinations to create different visuals. I will share a few common ones that I find interesting below. When you download the template, please feel free to change the filters to create your own visuals.

 

Lok Sabha Representation by Alliance

 

 2009 Lok Sabha by Alliance
2009 Lok Sabha by Alliance

UPA had only 262 seats in 2009, not enough to win majority (272) needed to form government. But it had outside support from some of the ‘Others’ to get it cross over the majority mark.

 

2014 Lok Sabha by Alliance
2014 Lok Sabha by Alliance

NDA had 336 seats in 2014 to clearly win majority (272) needed to form government.

 

Lok Sabha Representation by Party

 

2009 Lok Sabha by Party
2009 Lok Sabha by Party

INC by itself did not have enough seats (206) in 2009 to win majority (272) needed to form government. But it had outside support from some of the ‘Others’ to get it cross over the majority mark.

 

2014 Lok Sabha by Party
2014 Lok Sabha by Party

BJP by itself had enough seats (282) in 2014 to win majority (272) needed to form government.

 

Lok Sabha Representation by State/Union Territory

This view shows how many seats each State/Union Territory contributes to 543. UP is clearly the state that gets the most seats, while MH (48), AP (42), WB (42), BR (40), TN (39) are the next 5 states with a lot of seats.

Note: AP is split to AP and TG (Telangana) from 2019 Lok Sabha elections.

 

Lok Sabha by State/Union Territory
Lok Sabha by State/Union Territory

 

Colors of State/Union Territory
Colors of State/Union Territory

 

Top Parties in State/Union Territory

 

2009 Lok Sabha - Display State - Color by Party
2009 Lok Sabha – Display State – Color by Party

In 2009, INC was strong in AP, RJ, HR, DL and KL. It also had good representation in MP, MH, GJ and UT.

 

2014 Lok Sabha - Display State - Color by Party
2014 Lok Sabha – Display State – Color by Party

BJP almost swept UP in 2014. It also had good representation in MP, RJ, GJ, DL, JH, HR and UT.

 

Top Parties in State/Union Territory

This is related to the previous set of views. But here, we are displaying Party name and hence can see other parties that won.

 

2009 Lok Sabha - Display Party Name - Color by State
2009 Lok Sabha – Display Party Name – Color by State

 

2014 Lok Sabha Elections - Display Party - Color by State
2014 Lok Sabha Elections – Display Party – Color by State

 

BJP almost swept Uttar Pradesh, Madhya Pradesh, Gujarat and Rajasthan. Among other parties, AITC in West Bengal and ADMK in Tamil Nadu had big wins as well.

 

Win Margin % by Alliance

The next two views show the Win Margin % by each alliance in 2009 (first image) and 2014 (second image).

 

2009 Win Margin % by Alliance
2009 Win Margin % by Alliance

 

2014 Win Margin % by Alliance
2014 Win Margin % by Alliance

 

You can see that the 2014 NDA has a lot more green (Dark Green is >=20% win margin) than the UPA in 2009. This indicates that NDA in 2014 had a clearer mandate from the people with a much bigger win margin in each constituency it won.

 

State View

State view is much simpler to explain. You can view the top parties and their performance either for all states or for chosen states. You can use the filter on the left to choose states.

 

State View of 2009 and 2014 Lok Sabha Elections
State View of 2009 and 2014 Lok Sabha Elections

The view shows 2009 results, 2014 results and compares 2014 vs 2009. Let’s consider 2009 results first.

 

 State View of 2009 Lok Sabha Elections
State View of 2009 Lok Sabha Elections

You can see Parties sorted by Seats Won and when there is a tie, they are sorted by Votes Polled.

For example, in 2009, INC had 206 seats won, that is 47% of 440 seats it contested. It received 11,91,11,019 votes that is 29% of all votes polled in 2009.

Calculations

 

  • Seats Win % = Seats Won by Party / Seats Contested
  • Vote Share % = Votes Received by Party / Total Votes Polled in all Seats

 

State View of 2014 Lok Sabha Elections
State View of 2014 Lok Sabha Elections

You can see the same metrics for 2014. In addition, you can see the change in 2014 compared to 2009.

For example, BJP won 282 seats out of 428 it contested (at 66%). It had a vote share of 31% of all votes in entire country. It had 166 more seats in 2014 compared to 2009. It increased its votes by 9,32,22,168 and 12.2% increase in Vote share% (31% in 2014 compared to 19% in 2009).

As mentioned earlier, you can view this information for one or multiple states. Let me show one example of that. If I filter on Tamil Nadu as State, this is what I will get.

 

Tamil Nadu - 2009 Lok Sabha Elections
Tamil Nadu – 2009 Lok Sabha Elections

DMK had 18 seats, ADMK 9 and INC 8 in 2009.

 

Tamil Nadu - 2014 Lok Sabha Elections
Tamil Nadu – 2014 Lok Sabha Elections

In 2014, It was almost a sweep where ADMK had 37 seats. BJP and PMK won 1 each. ADMK increased vote share by 21.4% gaining mostly from INC and DMDK.

NOTA (None of the Above) option was introduced in 2014 and made it to top 10 parties’ list.

 

Constituency View

In this view, you can choose a specific constituency and view the top 5 candidates in 2009 and in 2014.

First, choose the State which will narrow down the Constituency drop down.

 

Select State to narrow the values in Constituency drop down
Select State to narrow the values in Constituency drop down

Then, you can view the details of that specific constituency. An example is shown below with call-outs of information presented.

 

Example of Constituency view result
Example of Constituency view result

 

  • You can see the votes polled, number of contestants (including NOTA in 2014) and winning margin as votes and %.
  • If the win is a close win (<1%), it will be highlighted.
  • In 2014 section, you can see the same metrics but also the increase (%) in votes polled.
  • The winning parties’ symbol is shown if available.
  • Message at top right indicating if the seat was retained by same party in 2014 or it switched from one party in 2009 to another in 2014.

Let me share screenshots of some constituencies.

The most famous constituency from 2014 – Varanasi in UP.

 

2014 Varanasi - Lok Sabha Election Result
2014 Varanasi – Lok Sabha Election Result

An example seat that switched from one party to another in 2014.

 

2014 Chennai North - Lok Sabha Election Result
2014 Chennai North – Lok Sabha Election Result

One of the closest wins with 36 votes making the difference – in Ladakh in 2014.

 

2014 Ladakh - Lok Sabha Election Result
2014 Ladakh – Lok Sabha Election Result

One of the most comfortable wins – Surat in 2014 – Winning Margin was 5.3 lakh votes (56.2%).

 

 

About Elections Data

Elections data could be considered as an ideal dataset for visualizations and analysis in Excel, as they are usually smaller in size. However, what makes it challenging, especially Indian elections data, is the quality of data available.

New states are created, constituencies are re-organized and renamed, new parties are formed, new alliances are created every time, and so on. They make it hard to expand the scope of analysis to more than 1 or 2 election years. Candidate names and Party Names are not entered in a consistent way. That makes historical analysis by candidate and party almost impossible, unless you spend significant time in improving data quality.

 

Source

I have tried my best to source data from official Indian election commission website and where needed, I leveraged Wikipedia. If you find any inaccuracies in the template, please post in comments below and and I will be glad to correct. My goal is to make such public data available in usable formats to all.

 

Elections and Me

Since my school days (a long time ago 😊), I have always been fascinated with elections. I never followed any political news for 5 years (which is the term of a government in India), but still sat in front of TV on the counting day to watch the results live. I used to view it as a race or a live sporting event where results come in every minute and the winner can change. The excitement lasted only for a few hours as the winner will be usually clear after that. I never bothered to follow politics after that day until the next counting day came up 5 years later.

As I grew up, my views on politics and elections have changed for the better (at least that’s what I think). I do follow everyday politics day and understand the nuances slightly better now. Though I get disgusted at the current state of political activities, it’s hard for me to stay away from it.

If you find this template useful, please share with your friends. Please provide your feedback in the comments section below. I look forward to hearing from you.

 

 

You may also be interested in...

2016 US Presidential Election Tracker

2019 Excel Calendar Template – 19 Calendar Designs – Free Download

Posted on
2019 Calendar Excel Template

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.

You may also be interested in...

All Purpose Calendar Excel Template
2013 Tamil Calendar

Employee Timesheet 2020 – Excel Time card & Work Hours Calculator

Posted on
Employee Timesheet Exclel Template
If you are looking for a simple and effective Timesheet template or Time Card template, you have reached the right place. In this blog post, I will present a free time sheet template in Excel and explain step by step how we can use it to calculate hours worked.

You can enter time card entries like time in, time out and break time and let the template create printable weekly timesheet, biweekly timesheet and monthly timesheets.

If you are an employee, no more wondering ‘How many hours did I work?’ or searching for ‘How to calculate hours worked?’. This timesheet template makes it easy by automating all the calculations.

If you are an HR (Human Resources) professional who needs a work hours calculator for the employees in the company or team, you can use this too. The template accounts for various commonly used rules in the industry and makes your task simple and easy.

Essentially, this template can be used by anyone who needs a timesheet in Excel or work hours tracker or payroll hours calculator.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Employee Timesheet in Google Sheets.

Continue reading Employee Timesheet 2020 – Excel Time card & Work Hours Calculator

Kerala Heat Map by District – Free Excel Template for Data Visualisation

Posted on
Kerala Heat Map

In this blog post, we will discuss the Kerala State Heat Map by District Excel template. This is a free Excel template that you can use to create heat maps instantly. You will learn how you can enter your own data in the template to create heat maps. You will also learn the features and options available within the template to customize and extend.

 

What is a Heat Map?

A heat map (Wikipedia – What is a Heat Map?) is a form of data visualization, where the data is presented in the form of varying levels of colors. We use colors to understand the data easily and quickly.

Here we will be creating a geographical heat map where the area we will be coloring is a geographic division (by district) of the state of Kerela. Each district can have a different color based on the data value for that specific district. For example, we can visualize the population distribution by district, literacy rate by district, or any numeric data we have at the district level. There are other types of use cases too, which we will discuss later.
Our Excel Template allows you to enter your own data and visualize that instantly.

Kerala Map
Kerala Population by District Heat Map Excel Template – Gradient Color Scheme

FEATURES

  • Enter your own data by district and visualize in a heat map instantly
  • Turn district names on or off
  • Turn data values on or off
  • Choose default gradient color scale
  • Option to choose custom colors by data ranges (7 data ranges and 10 colors)
  • Supports various types of data
  • Enter title and notes to go on the map
  • Readily printable or export to PDF
  • Copy image and paste in any application such as PowerPoint
  • Data can be entered in any order
  • Open for editing and customizing for your needs

 

ABOUT THE DATA

There are currently 14 districts in Kerela (as of March 2018). The names in English are sometimes different depending on which source you choose. We have used Wikipedia – List of Districts as the source for the names of districts in English.

Kasaragod, Kannur, Wayanad, Kozhikode,Malappuram, Palakkad, Thrissur, Ernakulam, Idukki  Kottayam, Alappuzha, Pathanamthitta, Kollam, Thiruvananthapuram.

Disclaimer: We have done our best to create the map outlines as accurately as we can. However, doing it in Excel has its limitations. The map is just for illustrations and is not expected to be 100% accurate with respect to the exact size and outlines of district and state.

 

FREE TEMPLATE DOWNLOAD

Kerala Heat Map by District

 

How to use the template?

The template is very simple to use with just three steps.

1. Enter district level data in the data table

Enter Kerala District level Data in Table
Enter Kerala District level Data in Table
  • You can type in just the data column and replace the data that is in the template by default.
  • If you have data already in a digital (from some website or spreadsheet) form, then you can copy and paste the district names and data values. Please make sure that you are pasting as values.
Right click and Paste Values
Right click and Paste Values

2. Edit the Settings to customize the heat map

  • Choose to display the district name (YES or NO)
  • Choose whether to display the data value (YES or NO)
Settings - Choose District Name and Data Value to display on map
Settings – Choose District Name and Data Value to display on map
  • Choose which color scheme to use.
    • There are two options here.
Choose color scheme for heat map - Gradient color or by Data ranges
Choose color scheme for heat map – Gradient color or by Data ranges

Gradient Colour scheme will apply the 3-color scale with lowest value being Red and mid-point being Gold and highest value being Green.

Gradient color scale in Excel for Heat Map
Gradient color scale in Excel for Heat Map

Colour by Range scheme will allow you to enter data ranges and choose color for each range.

Tamil Nadu Heat Map - Color by range in Excel
Kerela Heat Map – Color by range in Excel

In the table above, we are setting the districts with data values from 1 to less than 10,00,000 to be Red. From 10,00,000 to less than 15,00,000 to be Brown and so on. Any district with value from 40,00,000 will be colored in Gold.

You can customize the ranges as well as the colors. You can create up to 7 ranges and choose from 10 color options.

In your case, let us assume that you want to create a data range from 1 to 1000 as Red and 1001 to 2000 as Blue and anything from 2001 as Orange. The following is the data we should enter in the table.

Customizing data ranges
Customizing data ranges

To enter the data ranges, just type over the default numbers. Also, if you don’t need all the seven ranges, then just click on the cell and press Delete key to clear contents. Do not delete the row.

You can change colors by selecting from the drop down. The 10 color choices available are Red, Green, Blue, Yellow, Pink, Violet, Brown, Gold, Orange & Gray. (In a separate post, I will explain how to change or add colors).

Change colors from the drop down from 10 options
Change colors from the drop down from 10 options

Any district with value less than the first value will use the gradient color scheme by default. In our examples here, we started with 1. So, any negative value will use the gradient color scheme. But you are not limited to only positive values.

Here is how you can also use negative values in the data ranges.

Customizing data ranges - Negative values
Customizing data ranges – Negative values

Values from -100 to 1000 will be Green and 1001 to 2000 will be Blue.

You can also enter Title and Comments that will be displayed on the Map. You can choose to leave them as blank if you want to not have Title and comments.

 

Enter Title and Comments
Enter Title and Comments

3. View/Print/Export Screenshots of Heat Maps

After changing the settings, you should be able to view the updated map instantly. If the calculations do not refresh, please press ‘Calculate Sheet’ in Formulas Ribbon or press Shift+F9.

Let’s look at the screenshots of heat maps with different options in the settings we discussed in Step 2.

Screenshots of Heat Maps with different settings

In the first set of examples, we will be using the Gradient color scheme option.

1. If we choose only the district name to be displayed

Kerala Population by District Heat Map – Excel Template – Gradient
Kerala Population by District Heat Map – Excel Template – Gradient

2. If we choose only the data value to be displayed

Kerala Heat Map -Excel Template -Gradient with data values
Kerala Heat Map -Excel Template -Gradient with data values

3. If we choose both district name and data value to be displayed

Kerala District Heat Map – Excel Template – Gradient – Names and Data Values
Kerala District Heat Map – Excel Template – Gradient – Names and Data Values

The map will look busy with this option, especially since we have large numbers. However, you can convert the numbers to be shorter in length and that would make it more readable. You can also change the font size, which we will cover later in a separate post.

Now with the ‘Colour by Data Ranges’ option,

4. If we choose only the district name to be displayed

Kerala Population by District -Heat Map – Excel Template – Color Range
Kerala Population by District -Heat Map – Excel Template – Color Range

5. If we choose only the data values to be displayed

Kerala Heat Map – Excel Template – Color by Data Ranges with data values
Kerala Heat Map – Excel Template – Color by Data Ranges with data values

6. Now when we choose both names and data values

Kerala District Heat Map – Excel Template – Color by Data Ranges – Names and Data Values
Kerala District Heat Map – Excel Template – Color by Data Ranges – Names and Data Values

Now that we know how the heat map will look, let’s talk about printing..

PRINTING

In Excel, when you try to print, the print area in the file is already pre-set to print the map and the settings section.

Print Preview
Print Preview

If you would like to print only the map image, then please select the area around the map image and then click on Set Print Area from the Page Layout ribbon.

Heat Map Image as Print Area
Heat Map Image as Print Area

Now, when you print, you will print only the map image.

Export the heat map image as PDF

Similarly, you can save as PDF.

Export Heat Map as PDF
Export Heat Map as PDF

What is in the print area will now be saved as a PDF.

Copy-Paste Image

Instead of printing or exporting, you can also copy the image and paste in other applications directly as needed. You can copy the image by right clicking on the map and choosing ‘Copy’.

Kerala Map - Copy & Paste
Kerala Map – Copy & Paste

 

You can then paste in other applications such as PowerPoint and Word.

Paste options in PowerPoint
Paste options in PowerPoint

In addition to creating heat maps, this template can be used for other purposes too. In the next article, we will discuss some of those special use cases of this template.

If you find the template useful, please share this post with your friends. If you have any feedback or suggestions, please post in comments section below.

You may also be interested in...

Tamilnadu Heat Map
World Heat Map
Activity Pattern Heat Maps

World Heat Map by Continent – Free Excel Template for Data Visualisation

Posted on
World Heat Map

World Heat Map by Continents Excel Template is a free Excel template that you can use to create heat maps of the world instantly. You will learn how you can enter your own data in the template to create heat maps. You will also learn the features and options available within the template to customize and extend.

World Heat Map by Continent - Gradient Color Scheme - Names
World Heat Map by Continent – Gradient Color Scheme – Names

 

What is a Heat Map?

A heat map (Wikipedia – What is a Heat Map?) is a form of data visualization, where the data is presented in the form of varying levels of colors. We use colors to understand the data easily and quickly.

Here we will be creating a geographical heat map where the area we will be coloring is a geographic division (by Continent) of the World. Each continent can have a different color based on the data value for that specific district. For example, we can visualize the population distribution by Continent, or any numeric data we have at the Continent level.

There are other types of use cases too, which we will discuss later.

Our Excel Template allows you to enter your own data and visualize that instantly.

 

FEATURES

  • Enter your own data by Continent and visualize in a heat map instantly
  • Turn Continent names on or off
  • Turn data values on or off
  • Choose default gradient color scale
  • Option to choose custom colors by data ranges (7 data ranges and 10 colors)
  • Supports various types of data
  • Enter title and notes to go on the map
  • Readily printable or export to PDF
  • Copy image and paste in any application such as PowerPoint
  • Data can be entered in any order
  • Open for editing and customizing for your needs

 

ABOUT THE DATA

7 Continents. We have used https://en.wikipedia.org/wiki/List_of_continents_by_population  as source.

Asia, Africa, Europe, North America, South America, Australia and Antarctica

Disclaimer: We have done our best to create the map outlines as accurately as we can. However, doing it in Excel has its limitations. The map is just for illustrations and is not expected to be 100% accurate with respect to the exact size and outlines of Continents and World.

 

VIDEO DEMO

 

FREE DOWNLOAD

Download World Heat Map by Continents – Excel Template

 

How to use the template?

The template is very simple to use with just three steps.

  1. Enter Continent level data in the data table
Excel Template - Enter Continent level data in Data Table
Excel Template – Enter Continent level data in Data Table

 

  • You can type in just the data column and replace the data that is in the template by default.
  • If you have data already in a digital (from some website or spreadsheet) form, then you can copy and paste the district names and data values. Please make sure that you are pasting as values.

 

Right click and Paste Values
Right click and Paste Values

 

2. Edit the Settings to customize the heat map

Settings - Choose Name and Data Value display
Settings – Choose Name and Data Value display

 

  1. Choose to display the Continent name (YES or NO)
  2. Choose whether to display the data value (YES or NO)
  3. Choose which color scheme to use

There are two options here.

Choose color scheme for heat map - Gradient color or by Data ranges
Choose color scheme for heat map – Gradient color or by Data ranges

 

  • Gradient Colour scheme will apply the 3-color scale with lowest value being Red and mid-point being Gold and highest value being Green.
Gradient color scale in Excel for Heat Map
Gradient color scale in Excel for Heat Map

 

  • Colour by Range scheme will allow you to enter data ranges and choose color for each range.
Enter Colour by Data Ranges
Enter Colour by Data Ranges

 

In the table above, we are setting the Continents with data values from 1 to 29,999,999 to be Green. From 30,000,000 to 399,99,999 to be Brown and so on. Any Continent with value from 4,000,000,000 will be colored in Pink.

You can customize the ranges as well as the colors. You can create up to 7 ranges and choose from 10 color options.

In your case, let us assume that you want to create a data range from 1 to 1000 as Red and 1001 to 2000 as Blue and anything from 2001 as Orange. The following is the data we should enter in the table.

Customizing data ranges
Customizing data ranges

 

To enter the data ranges, just type over the default numbers. Also, if you don’t need all the seven ranges, then just click on the cell and press Delete key to clear contents. Do not delete the row.

You can change colors by selecting from the drop down. The 10 color choices available are Red, Green, Blue, Yellow, Pink, Violet, Brown, Gold, Orange & Gray.

Change colors from the drop down from 10 options
Change colors from the drop down from 10 options

 

Any Continent with value less than the first value will use the gradient color scheme by default. In our examples here, we started with 1. So, any negative value will use the gradient color scheme. But you are not limited to only positive values.  Here is how you can also use negative values in the data ranges.

Customizing data ranges - Negative values
Customizing data ranges – Negative values

 

Values from -100 to 1000 will be Green and 1001 to 2000 will be Blue.

You can also enter Title and Comments that will be displayed on the Map. You can choose to leave them as blank if you want to not have Title and comments.

Enter Title and Comments for Map display
Enter Title and Comments for Map display

 

3. View/Print/Export Screenshots of Heat Maps

Let’s look at the screenshots of heat maps with different options in the settings we discussed in Step 2.

In the first set of examples, we will be using the Gradient color scheme option.

 

1. If we choose only the Continent names to be displayed

World Heat Map by Continent - Gradient Color Scheme - Names
World Heat Map by Continent – Gradient Color Scheme – Names

 

2. If we choose both Continent name and data value to be displayed

World Heat Map by Continent - Gradient Color Names and Data Values
World Heat Map by Continent – Gradient Color Names and Data Values

 

Now with the Colour by Data Ranges option,

3. If we choose only the Continent name to be displayed

World Heat Map by Continent - Color by Data Ranges - Only Names
World Heat Map by Continent – Color by Data Ranges – Only Names

 

4. Now when we choose both names and data values

World Heat Map by Continent - Color by Data Ranges - Names and Data Values
World Heat Map by Continent – Color by Data Ranges – Names and Data Values

 

Now that we know how the heat map will look, let’s talk about printing.

 

Printing

In Excel, when you try to print, the print area in the file is already pre-set to print the map and the settings section.

Print Preview of World Heat Map
Print Preview of World Heat Map

 

If you would like to print only the map image, then please select the area around the map image and then click on Set Print Area from the Page Layout ribbon.

Heat Map Image as Print Area
Heat Map Image as Print Area

 

Now, when you print, you will print only the map image.

 

Export the heat map image as PDF

Similarly, you can save as PDF.

Export Heat Map as PDF
Export Heat Map as PDF

 

What is in the print area will now be saved as a PDF.

 

Copy-Paste Image

Instead of printing or exporting, you can also copy the image and paste in other applications directly as needed. You can copy the image by right clicking on the map and choosing ‘Copy’.

Copy Map as Image
Copy Map as Image

 

You can then paste in other applications such as PowerPoint and Word.

Paste options in PowerPoint
Paste options in PowerPoint

 


Recommended

 


If you find the template useful, please share with your friends. If you have any feedback or suggestions, please post in comments section below.

You may also be interested in...

Geographic Heat Map - India
Activity Pattern Heat Maps

Tamil Nadu Heat Map by District – Free Excel Template

Posted on
Tamilnadu Heat Map

In this blog post, we will discuss the Tamil Nadu State Heat Map by District Excel template. This is a free Excel template that you can use to create heat maps instantly. You will learn how you can enter your own data in the template to create heat maps. You will also learn the features and options available within the template to customize and extend.

 

What is a Heat Map?

A heat map (Wikipedia – What is a Heat Map?) is a form of data visualization, where the data is presented in the form of varying levels of colors. We use colors to understand the data easily and quickly.

Here we will be creating a geographical heat map where the area we will be coloring is a geographic division (by district) of the state of Tamil Nadu. Each district can have a different color based on the data value for that specific district. For example, we can visualize the population distribution by district, literacy rate by district, or any numeric data we have at the district level. There are other types of use cases too, which we will discuss later.
Our Excel Template allows you to enter your own data and visualize that instantly.

Tamil Nadu Population by District Heat Map Excel Template - Gradient Color Scheme
Tamil Nadu Population by District Heat Map Excel Template – Gradient Color Scheme

 

FEATURES

  • Enter your own data by district and visualize in a heat map instantly
  • Turn district names on or off
  • Turn data values on or off
  • Choose default gradient color scale
  • Option to choose custom colors by data ranges (7 data ranges and 10 colors)
  • Supports various types of data
  • Enter title and notes to go on the map
  • Readily printable or export to PDF
  • Copy image and paste in any application such as PowerPoint
  • Data can be entered in any order
  • Open for editing and customizing for your needs

 

ABOUT THE DATA

There are currently 38 districts in Tamil Nadu (as of March 2020). The names in English are sometimes different depending on which source you choose. We have used Wikipedia – List of Districts as source for the names of districts in English.

Ariyalur, Chengalpattu, Chennai, Coimbatore, Cuddalore, Dharmapuri, Dindigul, Erode, Kallakurichi, Kancheepuram, Kanniyakumari, Karur, Krishnagiri, Madurai, Mayiladuthurai, Nagapattinam, Namakkal, Perambalur, Pudukkottai, Ramanathapuram, Ranipet, Salem, Sivagangai, Tenkasi, Thanjavur, The Nilgiris, Theni, Tiruvallur, Tiruvarur, Thoothukudi, Tiruchirappalli, Tirunelveli, Tiruppur, Tirupattur, Tiruvannamalai, Vellore, Viluppuram, Virudhunagar

 

Disclaimer: We have done our best to create the map outlines as accurately as we can. However, doing it in Excel has its limitations. The map is just for illustrations and is not expected to be 100% accurate with respect to the exact size and outlines of district and state.

VIDEO DEMO

FREE TEMPLATE DOWNLOAD

Tamil Nadu Heat Map by District

 

How to use the template?

The template is very simple to use with just three steps.

1. Enter district level data in the data table

Enter Tamil Nadu District level Data in Table
Enter Tamil Nadu District level Data in Table

 

  • You can type in just the data column and replace the data that is in the template by default.
  • If you have data already in a digital (from some website or spreadsheet) form, then you can copy and paste the district names and data values. Please make sure that you are pasting as values.
Right click and Paste Values
Right click and Paste Values

 

2. Edit the Settings to customize the heat map

  • Choose to display the district name (YES or NO)
  • Choose whether to display the data value (YES or NO)
Settings - Choose District Name and Data Value to display on map
Settings – Choose District Name and Data Value to display on map

 

  • Choose which color scheme to use.
    • There are two options here.
Choose color scheme for heat map - Gradient color or by Data ranges
Choose color scheme for heat map – Gradient color or by Data ranges

 

Gradient Colour scheme will apply the 3-color scale with lowest value being Red and mid-point being Gold and highest value being Green.

Gradient color scale in Excel for Heat Map
Gradient color scale in Excel for Heat Map

Colour by Range scheme will allow you to enter data ranges and choose color for each range.

Tamil Nadu Heat Map - Color by range in Excel
Tamil Nadu Heat Map – Color by range in Excel

 

In the table above, we are setting the districts with data values from 1 to less than 5,00,000 to be Red. From 5,00,000 to less than 15,00,000 to be Brown and so on. Any district with value from 40,00,000 will be colored in Gold.

You can customize the ranges as well as the colors. You can create up to 7 ranges and choose from 10 color options.

In your case, let us assume that you want to create a data range from 1 to 1000 as Red and 1001 to 2000 as Brown and anything from 2001 as Orange. The following is the data we should enter in the table.

Customizing data ranges
Customizing data ranges

 

To enter the data ranges, just type over the default numbers. Also, if you don’t need all the seven ranges, then just click on the cell and press Delete key to clear contents. Do not delete the row.

You can change colors by selecting from the drop down. The 10 color choices available are Red, Green, Blue, Yellow, Pink, Violet, Brown, Gold, Orange & Gray. (In a separate post, I will explain how to change or add colors).

Change colors from the drop down from 10 options
Change colors from the drop down from 10 options

 

Any district with value less than the first value will use the gradient color scheme by default. In our examples here, we started with 1. So, any negative value will use the gradient color scheme. But you are not limited to only positive values.

Here is how you can also use negative values in the data ranges.

Customizing data ranges - Negative values
Customizing data ranges – Negative values

 

Values from -100 to 1000 will be Green and 1001 to 2000 will be Blue.

You can also enter Title and Comments that will be displayed on the Map. You can choose to leave them as blank if you want to not have Title and comments.

Enter Title and Comments
Enter Title and Comments

 

3. View/Print/Export Screenshots of Heat Maps

Let’s look at the screenshots of heat maps with different options in the settings we discussed in Step 2.

Screenshots of Heat Maps with different settings

In the first set of examples, we will be using the Gradient color scheme option.

1. If we choose only the district name to be displayed

Tamil Nadu Population by District Heat Map - Excel Template - Gradient
Tamil Nadu Population by District Heat Map – Excel Template – Gradient

 

2. If we choose only the data value to be displayed

Tamil Nadu Heat Map -Excel Template -Gradient with data values
Tamil Nadu Heat Map -Excel Template -Gradient with data values

 

3. If we choose both district name and data value to be displayed

Tamil Nadu District Heat Map - Excel Template - Gradient - Names and Data Values
Tamil Nadu District Heat Map – Excel Template – Gradient – Names and Data Values

 

The map will look busy with this option, especially since we have large numbers. However, you can convert the numbers to be shorter in length and that would make it more readable. You can also change the font size, which we will cover later in a separate post.

Now with the ‘Colour by Data Ranges’ option,

 

4. If we choose only the district name to be displayed

Tamil Nadu Population by District -Heat Map - Excel Template - Color Range
Tamil Nadu Population by District -Heat Map – Excel Template – Color Range

 

5. If we choose only the data values to be displayed

Tamil Nadu Heat Map - Excel Template - Color by Data Ranges with data values
Tamil Nadu Heat Map – Excel Template – Color by Data Ranges with data values

 

6. Now when we choose both names and data values

Tamil Nadu District Heat Map - Excel Template - Color by Data Ranges - Names and Data Values
Tamil Nadu District Heat Map – Excel Template – Color by Data Ranges – Names and Data Values

 

Now that we know how the heat map will look, let’s talk about printing..

 

PRINTING

In Excel, when you try to print, the print area in the file is already pre-set to print the map and the settings section.

Print Preview
Print Preview

 

If you would like to print only the map image, then please select the area around the map image and then click on Set Print Area from the Page Layout ribbon.

Heat Map Image as Print Area
Heat Map Image as Print Area

 

Now, when you print, you will print only the map image.

 

Export the heat map image as PDF

Similarly, you can save as PDF.

Export Heat Map as PDF
Export Heat Map as PDF

What is in the print area will now be saved as a PDF.

 

Copy-Paste Image

Instead of printing or exporting, you can also copy the image and paste in other applications directly as needed. You can copy the image by right clicking on the map and choosing ‘Copy’.

Copy Heat Map image
Copy Heat Map image

You can then paste in other applications such as PowerPoint and Word.

Paste options in PowerPoint
Paste options in PowerPoint

 

In addition to creating heat maps, this template can be used for other purposes too. In the next article, we will discuss some of those special use cases of this template.

 

If you find the template useful, please share this post with your friends. If you have any feedback or suggestions, please post in comments section below.

You may also be interested in...

Kerala Heat Map
World Heat Map
Activity Pattern Heat Maps

Retail Inventory Tracker 2020 – Free Inventory (Stock) Management template

Posted on
Retail Inventory Tracker
I am glad to present a simple and effective way to manage orders and inventory for your retail business. If you are getting started with a retail business where you plan to buy products from your suppliers and then sell them to customers with a margin, then you would need a tool to track your business in an effective way.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Retail Inventory Tracker in Google Sheets.

Continue reading Retail Inventory Tracker 2020 – Free Inventory (Stock) Management template