2019 Excel Calendar Template – 19 Calendar Designs – Free Download

Posted on
2019 Calendar Design 9 – 12 Pages – Monthly

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

Topics in this post (2019 Calendar Template)

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

FEATURES OF 2019 CALENDAR EXCEL TEMPLATE

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

DOWNLOAD 2019 CALENDAR TEMPLATE

2019 Calendar Excel Template
2019 Calendar Excel Template with US Holidays

VIDEO DEMO

Watch On YouTube

 

HOW TO USE THE TEMPLATE 

Overview of steps

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

Now, let’s see each step in detail.

Step 1: Enter Basic settings

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

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

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

Settings - Choose Starting Month
Settings – Choose Starting Month

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

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

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

Step 2: Choose Weekends

Settings - Choose Weekend Days
Settings – Choose Weekend Days

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

Step 3: Enter Holidays

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

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

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

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

Step 4: Enter your personal or company Events

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

Events with dates and names
Events with dates and names

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

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

Step 5: Pick a calendar design from the 19 designs

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

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

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

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

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

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

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

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

Step 6: Print the calendar or export to PDF

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


19 CALENDAR DESIGNS

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

2019 Calendar Designs
2019 Calendar Designs

Calendar Design 1

1 Page 12 Months – 4 X 3

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

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

Calendar Design 2

1 Page 12 Months – 3 X 4

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

Calendar Design 3

1 Page 12 Months – 2 X 6 with Events

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

Calendar Design 4

1 Page 12 Months – 6 X 2 with Events

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

Calendar Design 5

1 Page 12 Months – 12 X 42 with Events

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

Calendar Design 6

1 Page 12 Months – 12 X 31 with Events

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

 

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

Calendar Design 7

6 Pages – Planner

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

 

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

Calendar Design 8

1 Page – 54 Weeks – with Events

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

Calendar Design 9

12 Pages – Monthly

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

Calendar Design 10

6 Pages – with Events

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

 

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

Calendar Design 11

4 Pages – with Events

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

Calendar Design 12

3 Pages – 4 Months on each page

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

Calendar Design 13

2 Pages – 6 Months on each page

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

Calendar Design 14

12 Pages – Monthly – with Events

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

Calendar Design 15

12 Pages – Monthly – Type Events

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

Calendar Design 16

12 Pages – Picture Calendar

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

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

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

Calendar Design 17

12 Pages – Quotes Calendar

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

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

Calendar Design 18

1 Page – Weekly Calendar

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

Calendar Design 19 (Daily Calendar)

1 Page – Daily Calendar

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

TIPS TO CUSTOMIZE THE 2019 CALENDAR TEMPLATE

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

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

Select Cells to change formatting

Select Cells to change formatting
Select Cells to change formatting

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

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

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

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

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

A couple of things to note here:

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

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

2. How to change Event and Holiday colors?

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

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

Open Conditional Formatting Menu
Open Conditional Formatting Menu

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

You can apply the same technique to the Holidays table.

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

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

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

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

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

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

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

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

5. How to unprotect (unlock) sheets?

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

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

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

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

6. How to print calendars?

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

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

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

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

7. How to export calendar to PDF?

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

Export calendars as PDF
Export calendars as PDF

In the following dialog box,

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

RECOMMENDED TEMPLATE

Event Calendar Maker

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

Additional Features in Event Calendar Maker

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

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

Have a wonderful 2019.

Ask Your Excel Question – New Video Series – How to create drop down list in Excel

Posted on

Welcome to a new series ‘Ask Your Excel Question’.

In this series, I provide solutions (with video demos) to questions that I receive from our community. These videos will be short and to the point, addressing a specific feature or concept or a formula. If you would like to be notified of these videos, please subscribe to our YouTube channel.

If you would like to ask your own questions, please post them in the YouTube Channel. I will do my best to answer.

In our first episode of the series, we see how we can create a simple drop down list in Excel.

Video (How to create drop down list in Excel)

More: https://indzara.com/faq/how-to-create-a-drop-down-list-in-excel/

Employee Timesheet Template – Excel Time card & Work Hours Calculator

Posted on
Weekly Timesheet Template -Printable - Excel 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.

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

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

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

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.

Resource Capacity Planner Excel Template – Step by Step User Guide

Posted on

In this blog post, we will see step by step how we can use the Resource Capacity Planner Excel template. We will take a simple example planning scenario and see how the template can help us in smarter planning.

The Resource Capacity Planner template is designed to determine the available resource capacity, compare with demand and identify surplus/deficit in capacity. It also allows us to modify the resource availability or demand (to meet our objectives) and see impact of the changes instantly.

For more on template’s features, please visit the product page.

 

Overview of steps:

  1. Enter Settings
  2. Enter Resource list with standard availability
  3. Enter vacation and overtime
  4. Enter Demand (tasks) data
  5. Refresh Calculations
  6. View Dashboard to evaluate plan
  7. Address any over-utilization and under-utilization
  8. View Calendar to ensure desired utilization at granular time periods

 

VIDEO DEMO

 

Step 1: Enter Settings

In the Settings sheet, we will enter the basic settings.

Planning Period
We will set the planning period by entering a Start Date and End Date. The template allows a maximum of 2-year planning period.

Enter Settings - Planning Period
Enter Settings – Planning Period

We will use a 3 month period from July 1, 2018 to Sep 30, 2018.

 

Weekends
In most work places, we have days during a week where we don’t work (also called weekends). We can choose which weekdays are weekends from the list of options available. The template will set the availability of resources to 0 by default.

If you don’t have any weekends, then leave this input blank. Just click the cell and press the Delete key. It will remove the contents of the cell. Please do not delete the column or row.

Enter Weekends
Enter Weekends

We will choose SAT & SUN as weekends for this tutorial.

Enter Settings - Weekends
Enter Settings – Weekends

If a resource works during a weekend day, we can enter that as overtime in the VACATION_OVERTIME sheet.

 

Excel Tables

In the next set of inputs, we use Excel Tables. Entering the data correctly inside the Excel tables is critical to the functioning of the template. If you are new to Excel Tables, please see our article on How to use Excel Tables for data entry?

Tip: Start entering data right below the Header of each Table

 

Holidays
Since most companies have holidays (other than weekends), it is important that we factor that in when we calculate capacity. We can enter our company holidays and the template will set the availability of resources to 0 by default on those days. Each holiday must be entered individually.

We will enter a couple of days in the Holidays table.

Enter Settings - Holidays
Enter Settings – Holidays

If a resource works during a holiday, we can enter that as overtime in the VACATION_OVERTIME sheet.

 

Projects
The template allows managing capacity for multiple projects.
We will enter 2 projects, to keep it simple.

Enter Settings - Project List
Enter Settings – Project List

 

Skill Groups
Skill Group is how we can group resources for planning.

Skill Group and Skill are used interchangeably in the template.

In our example, we have resources from 2 skill groups: Project Mgmt and Development.

Enter Settings - Skill Group List
Enter Settings – Skill Group List

 

Step 2: Enter Resources

Now, we move to the Resources sheet in the template.

The template uses the term ‘Resource’. The most commonly used resource in most scenarios is the human resource (can be called as Employee). But a machine could be a resource too. Hence instead of using ‘Employee’, the term ‘resource’ is generally used.

Let’s assume we have 6 resources in total.

Enter list of resources and their standard availability Hours
Enter list of resources and their standard availability Hours

We enter the Start Date and End Date of each resource.

Resources can be hired anytime and similarly resources may leave a company anytime. The template will assign 0 availability before start date and after end date.

We enter the number of hours each resource will be available each weekday. For example, Project Planner 1 is available 8 hours a day from Mon to Fri. The template can handle varying hours by weekday as well.

We assign the 6 resources each to a Skill Group and a Project.

If a resource needs to be shared across 2 projects or 2 skill groups, they would have to created twice with a different resource name. We will explain this later in this blog post below.

Finally, we enter the Cost per Hour for each resource. The cost is fixed for each resource and cannot vary over time.

 

Step 3: Enter Vacation and Overtime

Though we have entered standard availability above, we know that employees may take vacation or sometimes work overtime.
We can enter any vacation or overtime taken by resources in the VACATION_OVERTIME sheet.

Enter Vacation and Overtime hours for employees
Enter Vacation and Overtime hours for employees

Project Planner 1 is taking 3 days off (8 hrs each day) from 16th July to 18th July. Two important things to note here:

  1. It is 8 hrs of vacation each and hence totally 24 hours in total for all 3 days. However, we only enter 8 hrs as the column is for daily hours. The template will calculate automatically the total.
    • If the employee’s vacation hours vary over the three days, then we have to create separate rows.
  2. We enter -8 since it is vacation. If it is overtime, we will enter 8.
    • If vacation hours for a day is greater than the standard availability of that resource, then the result will be set to 0 to avoid negative availability.

 

Project Planner 2 is taking one day off (8 hrs) on 16th July. We enter 16th July as Start and End Dates. It is important that we fill both dates.

Finally Project Planner 2 also does overtime on 21st July. We enter 8 (not -8) in Hrs, since this is overtime.

This 21st July is a Saturday and is a weekend day. However, overtime can be done on weekends and holidays as well. The template supports that.

Overtime is positive to capacity and Vacation is negative to capacity.

This completes our capacity inputs. Now, we move to the Demand data input.

 

Step 4: Enter Demand data

In this template, demand is entered as Tasks in the Demand sheet.

Enter Demand (Tasks) - Date, Resource and Hours
Enter Demand (Tasks) – Date, Resource and Hours

Required fields are Date of the task, Resource assigned to do that task and Hours of work required.

Detail column is optional to enter any details about the task or any notes.

The four calculated cells on the right should not be edited or modified. That would break the functionality of the template. Please do not delete or edit these columns.

We can enter just the first 3 required fields and move to the next row and continue entering tasks.

Calculated Fields - Do not delete or edit
Calculated Fields – Do not delete or edit

 

Planning granularity

An important concept to understand is planning granularity. We have entered daily level of tasks in the above image. What level of tasks to be entered depends on the planning granularity you need.

When we say ‘weekly planning’ it means that our goal is to ensure all the tasks for the week are completed by end of week. If we are behind on Monday, Tuesday or Wednesday, that is okay. As long as we complete by end of week, we will be accomplishing our goals. In this scenario, you don’t have to enter daily level tasks. You could enter just one record for a resource for each week.

If you want to ensure you will be meeting your demand every single day, then, you should enter daily tasks individually.

Thus, the template can accommodate more granular demand entry (daily – even multiple tasks per day) or aggregated demand entry (weekly or monthly or quarterly).

Once we complete entering the demand, we are done with data entry.

 

Step 5: Refresh Calculations

The template uses pivot tables and hence every time we change input data, please refresh calculations.

Data ribbon –> Refresh All.

Refresh All calculations
Refresh All calculations

 

Step 6: Review Dashboard

Dashboard

Dashboard sheet shows the summary of metrics across all projects, skills and resources.

Resource Capacity Planner Dashboard with slicers
Resource Capacity Planner Dashboard with slicers

On the left side, you will have the slicers (filters) that you can use to drill down to specific Project(s), Skill group(s) and Resource(s).

 

At the top, you will see the overall summary of metrics.

Dashboard - Resource Capacity Planning - Summary Metrics
Dashboard – Resource Capacity Planning – Summary Metrics

 

You can see the number of Projects (2), Skills (2) and Resources (6) in the Plan.

We also see that there are 2 over utilized resources and 3 under-utilized resources.

Why is this important?

The overall aggregate utilization rate will not provide the complete picture. If there is a Developer resource who is over utilized (utilization rate > 100% or capacity < demand) has 50 hrs over utilized and another Project Mgmt resource who is under-utilized (utilization rate < 100% or capacity > demand) with 50 unutilized hrs, they cancel each other when we aggregate.

Hence it is important to view utilization at resource level.

 

On the left side, you can see the Capacity (2478 hrs), Demand (2303 hrs), Surplus of 175 hrs at 93% Utilization Rate.

On the right side, you can see the same metrics – based on Cost. The resources available cost totally $25,614 while the demand tasks will cost $23,453 resulting in a surplus of $2,161 at 92% utilization.

 

Changing Currency

To change the currency from US$ to another currency, please follow the steps below:

Press Ctrl+G and select CURR named range.

Changing Currency - Press Ctrl G and select CURR
Changing Currency – Press Ctrl+G and select CURR

Press Ctrl+1 to open the Formatting dialog box. Choose the currency desired and click OK.

Press Ctrl+1 and Change Currency
Press Ctrl+1 and Change Currency

Now, the currency on the dashboard display will change accordingly.

 

Let’s continue with the rest of the dashboard.

Resource Capacity Planning Dashboard - Over utilized and under utilized Projects
Resource Capacity Planning Dashboard – Over utilized and under utilized Projects

The above visual presents the number of projects where resources are over utilized (on the left side) and under-utilized (on the right side).

For example, Project B has 1 resource who is over utilized. That resource has a capacity of 360 hours but they have been assigned to tasks for an additional 39 hours (referred to as Over Utilized hours). They have been assigned to (360 + 39= 399 hours).

A project can have over utilized resources as well as under-utilized hours.

Project B also has 1 resource who is under-utilized. That resource has a capacity of 504 hours but 80 hours of that are unutilized. They have been assigned to only (504 – 80 = 424 hours).

We can also understand if there is over utilization or under-utilization at skill group level.

Resource Capacity Planning Dashboard - Over utilized and under utilized Skill Groups
Resource Capacity Planning Dashboard – Over utilized and under utilized Skill Groups

The above visual presents the Skills where resources are over utilized (on the left side) and under-utilized (on the right side).

For example, Development has 2 resources who are over utilized. Those 2 resources have a capacity of 738 hours but they have been assigned to tasks for an additional 44 hours (referred to as Over Utilized hours). They have been assigned to (738 + 44 = 782) hours.

A Skill Group can have over utilized resources as well as under-utilized hours.

Development also has 1 resource who is under-utilized. That resource has a capacity of 378 hours but 37 hours of that are unutilized. They have been assigned to only (378 – 37 = 341) hours.

 

Resource table shows which specific resources are over utilized and under-utilized.

Resource Capacity Planning Dashboard - Over utilized and under utilized Resources
Resource Capacity Planning Dashboard – Over utilized and under utilized Resources

Clearly two resources Developer 3 and Developer 2 do not have enough capacity to complete the tasks assigned to them.

 

In the next page of the dashboard, you can see the charts of Capacity vs Demand in Hours.

The charts show the overall capacity vs demand for each Project, Skill group and Resource.

Capacity vs Demand Hours Chart
Capacity vs Demand Hours Chart

 

In the final page of the dashboard, you can see the charts of Capacity vs Demand in Cost.

The charts show the overall capacity vs demand for each Project, Skill group and Resource.

Capacity vs Demand Cost Chart
Capacity vs Demand Cost Chart

 

The Dashboard has so far given us a good overview of our plan and identified 2 over-utilized resources which need to be addressed.

3 resources have unutilized hours that will be costing the team. In this tutorial, I am assuming the under-utilization is not a major concern. In some project scenarios, underutilization can also be an issue. You can follow similar steps as explained below to address that too.

Now, let’s see how the template will help us address this.

 

Step 7: Addressing Over-utilization and Under-Utilization

In order to address the over utilization and under utilization, we have to do one or more of the following:

  1. Increase (to increase capacity) or Decrease (to reduce capacity) standard availability of resources in the Resources sheet.
  2. Add overtime (to increase capacity) or vacation time (to reduce capacity)
  3. Change assignment of tasks to a different resource.
  4. Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.

The first 2 options are straight forward and don’t need much explanation. The 4th option is also straight forward to implement in the template (update the demand sheet), though it may be hard to get project stakeholders to reduce the demand (or scope).

We will focus on the 3rd option which will neither increase the cost nor reduce the demand. It is the re-assignment of tasks to existing resources to remove over-utilization.

If we filter the dashboard to Project A.

Assigning Resources in Project A - Capacity Planning
Assigning Resources in Project A – Capacity Planning

Developer 2 needs just 5 more hours of capacity. However, Developer 1 has 37 unutilized hours.

Since they are in same project, we can just go to the Demand sheet and change the assignment of a task that will take 5 hrs, from Developer 2 to Developer 1.

Change assignment of task
Change assignment of task

 

Let’s refresh the calculations. Data ribbon  –> Refresh All.

That will update the dashboard calculations.

Let’s remove the project filter so that we can see all the resources.

Resources under utilized and over-utilized
Resources under utilized and over-utilized

Now, we can see that Developer 2 is no longer over utilized. Developer 3 in Project B is the only one we need to address.

There are 39 over utilized hours. However, Project B does not have any unutilized resources with Development Skill. We have Project Planner 2 with 80 unutilized hours. To demonstrate how this template can handle a resource having multiple skills, we will assume that Project Planner 2 can handle Development tasks.

Now, we need to assign some of the capacity of the Project Planner 2 to Development skill.

Splitting a resource to two skills
Splitting a resource to two skills

Important things to note here:

  1.  A new resource must be created and the resource name has to be different. Resource Name column should only have unique names for the template to function.
    1.  We have given the name Project Planner 2 D an assigned to Development skill.
  2. When we enter the standard work hours of the new resource (we have entered 1 hour a day), we must review the work hours for the existing resource (Project Planner 2). Previously Project Planner 2 had 8 hours a day availability. We have updated that to 7 hours and assigned the 1 hour to the new resource Project Planner 2 D. This assumes that the resource is still working same total number of hours. It’s just that we are changing the skill being used.

 

Now that we have created a new resource with Development skill, we need to assign tasks from Developer 3.

Re-assign tasks to new resource
Re-assign tasks to new resource

 

Let’s refresh the dashboard again.

Resources under utilized and over-utilized updated
Resources under utilized and over-utilized updated

We have no resources over utilized. 🙂

Overall metrics look like below.

Dashboard Summary updated
Dashboard Summary updated

 

All the resources will have enough capacity to meet the demand. We have 176 hours surplus capacity and are at 93% utilization.

This was just an illustration of how to use the template to modify capacity and demand. The target utilization rate can vary in each organization.

 

How to assign a resource to multiple projects

The Dashboard provides the metrics for the entire planning period duration in aggregate. If, in your organization, it is important to ensure a certain utilization every day, then you can update the planning period to a day or use the Calendar sheet to view capacity/demand at a daily level.

 

Step 8: View Calendar for granular time periods

Calendar is very flexible as it has many options to choose from. It also has the slicers (filters) just like the Dashboard.
The below shows the capacity in hours for each resource every week.

Capacity Planning Calendar - Capacity Hours Weekly
Capacity Planning Calendar – Capacity Hours Weekly

 

There are four selections (Dimension, Measure, Unit, Time Dimension) to make and each selection has several options.

Resource Capacity Planner Calendar Settings
Resource Capacity Planner Calendar Settings

 

Dimension
The calendar can be created for the list of resources or list of skill groups or list of projects.

Calendar Settings - Dimension Resource Skill or Project
Calendar Settings – Dimension Resource Skill or Project

 

Measure
You can select to display Capacity data or Demand data or Surplus/Deficit data.

Calendar Settings - Measure Capacity Demand or Surplus Deficit
Calendar Settings – Measure Capacity Demand or Surplus Deficit

 

Unit
The options for Unit are Hours and Cost.

Calendar Settings - Unit Hours or Cost
Calendar Settings – Unit Hours or Cost

 

Time Dimension
Finally, you can choose to show daily, weekly, monthly, quarterly or annual data.

Calendar Settings - Time Dimension Daily Weekly Monthly Quarterly or Annual
Calendar Settings – Time Dimension Daily Weekly Monthly Quarterly or Annual

 

The template will display up to 53 periods (53 days, 53 weeks, 24 months, 8 quarters, 2 years).

When you make the selections, the calendar display will update automatically to reflect the selections.

For example, let’s see Surplus/Deficit Hours by week.

Capacity Planning Calendar - Surplus Deficit Hours Weekly
Capacity Planning Calendar – Surplus Deficit Hours Weekly

We can see that though overall there may be no deficit for a resource based on total hours, some weeks have deficit which are made up by surplus capacity in some other weeks.

If we want to ensure that the team completes the tasks every week, then we need to follow the steps outlined earlier on modifying the capacity or demand.

 


If there are any questions about this template, please see the Product Support page for more information.

Recruitment Manager Excel Template – Most Frequently Asked Question

Posted on

In this blog post, we will cover the most frequently asked question about Recruitment Manager Excel template.

To learn more about the template’s features, please visit the product page.

To learn how to use the template, please visit the support page.

Before we begin, we are assuming that the template’s formulas have not been edited/modified.

 

Why does the Dashboard not reflect data entered?

This is by far the most common question.

The answer is somewhat long, as we need to cover various scenarios which could lead to this. Let’s explain the different reasons and how to address each of them.

7 Reasons for Why Dashboard does not reflect data
7 Reasons for Why Dashboard does not reflect data

 

VIDEO DEMO

 

1. Data Not Refreshed

The template uses pivot tables and hence we should refresh the calculations whenever we make any changes to existing data or add new data.

If you have entered data for the first time in Jobs and Applications sheets, but see that the Dashboard is completely blank, this could be one of the reasons.

Solution: Refresh the calculations. In the DATA ribbon press Refresh All button.

Data Ribbon -- Refresh All
Data Ribbon — Refresh All

2. Errors in applications Data

Applications which have data entry errors will not be included in Dashboard and hence if you have any errors, that could be the reason why you don’t see what you expect in the Dashboard. We put these data validations in place so that the metrics calculated in the Dashboard are always correct.

To identify if this is true in your case, please see ERROR column (column Q in v2 of the template) in Applications sheet.

Error column in Applications sheet
Error column in Applications sheet

 

This is a calculated column. So, please don’t edit it. But you can filter using this column to see the applications having Errors.

There are two main reasons why there would be an Error.

  1. Required Dates are missing.
  2. Dates are not chronological

Let’s use a sample data set to illustrate this clearly.

For Hired Applications, all the dates until the hiring stage should have a date. In other words, applications should go through and complete all the stages to be hired.

Errors in Hired Applications
Errors in Hired Applications

 

In the above screenshot, first application is missing OFFER DT. The application status is ‘HIRED’. Hence we need to have all the stage dates until the Hire stage.

The second application has all the dates, however, the MGR INTERVIEW DT is Feb 22nd whereas the previous stage PHONE SCREEN DT is Feb 24th. That is an error, as it is assumed that the stages are done in sequence.

To summarize,

Job Posted Date <= Stage 1 DT <= Stage 2 DT < = Stage 3 DT <= Stage 4 DT <= Stage 5 DT <= Stage 6 DT

Note: Job Posted Date is coming from the Jobs sheet for the specific Job ID. You can also see the Job Posted Date in column Y in Applications sheet.

The third application in the screenshot does not have an error, as the dates are present and they are chronological.

 

It is a little different for not hired applications. This includes applications with status ‘NOT HIRED’ and applications with no status (where we have not taken a decision yet).

The dates must be in chronological order. However, we don’t need all the dates to be populated. If an application goes to Stage 3 and then is put in NOT HIRED status, then we don’t need dates for Stages 4 to 6.

Errors in Not Hired Applications
Errors in Not Hired Applications

 

In the screenshot above, the first application is missing PHONE SCREEN DT. As it has an APPLICATION DT and MGR INTERVIEW DT, it should also have the PHONE SCREEN DT in-between.

Second application has a MGR INTERVIEW DT (25th Apr) that is prior to the PHONE SCREEN DT (4th May). That is an error.

 

What if a candidate skips a stage?

Sometimes a candidate may skip a stage and go to the next stage directly. In such cases, it is recommended that we enter the same date and not leave it blank.

For example, if a candidate skips Phone Screen and directly goes to the Mgr Interview because it is an internal candidate and a Phone Screen may be considered unnecessary. In that case, I would enter the Application Date again as the Phone Screen Date.
This will result in the calculations of time taken for Phone screening as 0 days.

 

Solution: Enter missing dates and ensure they are chronological

 

3. Required fields are missing

In the Jobs sheet, required fields are Job ID, Job Posted Date, Positions and Status.

Job ID required in Jobs sheet
Job ID required in Jobs sheet

 

Required Fields in Jobs sheet
Required Fields in Jobs sheet

Each Job has to be in one of the three status values (OPEN, COMPLETED or CANCELLED).

In the Applications sheet, JOB ID and APPLICATION DT are required fields.

Required Fields in Applications sheet
Required Fields in Applications sheet

Required fields are marked as shown above in the template for your identification.

 

Solution: Enter any missing values in required fields.

 

4. Dates in invalid format

Please check that the dates are in correct date format. In certain language and region versions of Excel, the date formats are different from the U.S. version. This could cause Excel to not recognize the entries as dates and thus result in errors in ERROR column.

To check if the date is a valid date format, please click on the cell with the specific value. Then, click on the Number format drop down list.

If Excel shows all the formats with the same value, it means that Excel is treating it as text and not date.

Correct Date format check - Example Invalid Date
Correct Date format check – Example Invalid Date

 

If Excel treats as date, it would appear as shown below.

Correct Date format check
Correct Date format check

 

Solution: Enter dates in correct format.

 

5. Filters applied in Dashboard

If you have chosen any filters (slicers) in the Dashboard sheet, that will narrow down the data set used to calculate metrics on Dashboard. If you believe that the Dashboard is not reflecting all the jobs and applications, please check if there are any filters applied. If so, clear the filters.

Clear Filters in Dashboard
Clear Filters in Dashboard

 

Solution: Clear filters applied.

 

6. Open positions with 0 applications

Template uses data in Applications sheet to build dashboard. Jobs data is also pulled over to Applications sheet.

If we have 0 applications for a job, then they will not included in the Open Positions metric. It will be shown separately next to it as ‘Open Positions w/o Applications in Market’

Open Positions with 0 Applications
Open Positions with 0 Applications

 

Solution: Check if there are applications in Applications sheet for the job. Check the Open Positions w/o Applications metric.

 

7. No Hired/Relevant Applications

Some parts of dashboard may not be populated if there are no relevant data yet. Each section of the Dashboard calculates certain metric that needs relevant data to make that metric meaningful. If that relevant data is not present, the section will appear blank.The following shows the data that is relevant to each section on the dashboard.

 

  • 4 KPIs related to Hired Positions – only if there are HIRED applications.
HR Dashboard Summary Metrics
HR Dashboard Summary Metrics

 

  • 3 KPIs related to Open Positions – even if there are no HIRED applications.
Open Positions with 0 Applications
Open Positions with 0 Applications

 

  • Recruitment Funnel – if there are applications with HIRED status or NOT HIRED status – basically applications where decisions have been taken.
Recruitment Funnel (up to 6 stages)
Recruitment Funnel (up to 6 stages)

 

  • Monthly Metrics – only if there are HIRED applications.
Monthly Metrics (Hired and Days to Hire)
Monthly Metrics (Hired and Days to Hire)

 

  • Pipeline Efficiency of Hiring – only if there are HIRED applications.
Pipeline Efficiency - Time Spent in each stage
Pipeline Efficiency – Time Spent in each stage

 

  • Application Sources – only if there are HIRED applications.
Application Sources - Hired & Conversion Rate
Application Sources – Hired & Conversion Rate

 

  • Decline Reasons – only if there are NOT HIRED applications.
Decline Reasons for Candidates/Applications
Decline Reasons for Candidates/Applications

 

  • Cost – if cost is entered in Cost column in Jobs sheet.
  • Active Pipeline – if there are applications with no status – basically applications where decisions have not been taken.
Active Recruiting Pipeline - Stage breakdown
Active Recruiting Pipeline – Stage breakdown

 

Solution: Ensure that there is relevant data in the Jobs and Applications sheet.

 

If you followed the above suggestions and still have any questions, please post them below in the comments section. Thanks for your support.

 


Recommended

How to extend calendar view in PTO Manager Excel Template

Posted on

Our Small Business PTO Manager Excel template can be used to calculate and report PTO balances for employees in a company. One of the features of the template is the Calendar which shows the monthly view of PTO taken/scheduled by the employees. By default, this sheet was limited to show 40 employees at a time so that it can fit within 1 page to print. If your company has more than 40 employees to track PTO, then this could be a limiting factor. In this blog post, we will learn how to extend the Calendar view to show more than 40 employees.

The default calendar view looks like this.

PTO Manager - Calendar View - Default
PTO Manager – Calendar View – Default

 

It would stop with 40th Employee.

Calendar shows Employees 1 to 40
Calendar shows Employees 1 to 40

 

For this exercise, let’s assume we have 80 employees in our company. We will use the following simple steps to view more than 40 employees.

 

VIDEO DEMO

 

 

Option #1 (40 employees at a time)

If you don’t need to see data for all 80 employees in the calendar at the same time, then we have a very easy solution already built-in.

Just type 41 in cell A7.

 

PTO Manager Calendar - Enter different starting number
PTO Manager Calendar – Enter different starting number

 

Now, you will be able to see data for employees 41 to 80.

Calendar shows Employees 41 to 80
Calendar shows Employees 41 to 80

 

Option #2 (More than 40 employees at a time):

This involves a few more steps.

 

Before we begin, please save a copy of the file for backup. We will be making formula changes and it is always recommended to create a backup.

Tip: It is also recommended to create backups regularly even if you don’t make formula changes.

 

Step 1: Unprotect sheet

Please unprotect sheet with password indzara

Unprotect Sheet
Unprotect Sheet

 

Step 2: Select Row 45

Click on 45 to select row 45.

Click on 45 to select row 45
Click on 45 to select row 45

 

Step 3: Right click and choose ‘Copy’

Right Click and Choose Copy
Right Click and Choose Copy

 

Step 4: Select rows 46 to 86

Select rows 46 to 86 (we need to select 1 row more than our desired expansion. Since we want to expand by 40 rows, we select 41 rows)

Step 5: Right click and choose ‘Insert Copied Cells’

Right click and choose Insert Copied Cells
Right click and choose Insert Copied Cells

 

Step 6: Select row 87

Click on 87 to select row 87

Click on 87 to select Row 87
Click on 87 to select Row 87

 

Step 7: Clear Row 87

Right click and choose ‘Clear Contents’

Right click and choose Clear Contents
Right click and choose Clear Contents

 

Step 8: Change Print settings

Since the calendar will be more than 1 page long, we need to make some modifications to the Print Settings.

First, Click on ‘Print Titles’ in the Page Layout ribbon.

Select Print Titles
Select Print Titles

 

Set rows 1 to 6 to repeat. This allows the 6 rows to print on second page (employees 41 to 80) as well – allowing the reader to align the dates to the calendar.

Select rows 1 to 6 to repeat in the Page Setup
Select rows 1 to 6 to repeat in the Page Setup

 

Step 9: Protect sheet

Now we are done with making the changes to extend the calendar. We can protect the sheet again to prevent unintentional editing of formulas.

Protect sheet
Protect sheet

 


Recommended

  • Support Page: For all the help articles on Small Business PTO Manager Excel template
  • Product Page: For product features and highlights of the PTO Manager Excel Template

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

Posted on
World Heat Map by Continent - Color by Data Ranges - Only Names

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.

How to increase tracking period in PTO Manager Excel Template

Posted on

The Small Business PTO Manager allows tracking PTO accruals and balances for employees.

For more about the template – Product Page

It is designed such that we can continue to use the same file for many years. By default, the tracking period is set to 2 years. This was set so that the file size is smaller, and the calculations are faster. When we extend the tracking period, Excel needs to do a lot more calculations and store a lot more data. So, we limit the default period to 2 years, but provide an easy option for the user to extend the period in few simple steps. In this blog post, we will see how we can easily extend that to more than 2 years.

 

If you prefer video demos, please watch the video.

 

VIDEO DEMO

 

Before we start the process, it is important that we understand the Start Date concept used in the template first.

You can choose to start tracking PTO in any year from any date. By default, the template has 1-Jan-2018 as starting date.

Start Date - Date of tracking PTO
Start Date – Date of tracking PTO

 

You can change this Start Date to any date.

The template will then assume 2 things:

1) PTO balances of employees as of (31-Dec-2017) the date prior to Start Date will be entered in the Employees sheet.

Starting Balances for Employees
Starting Balances for Employees

 

2) All PTO for employees from 1-Jan-2018 will be entered in the Time Off sheet

Enter PTO for employees from Start Date
Enter PTO for employees from Start Date

 

This is how we ensure that you can use the template to track PTO in companies where you have employees who have been working already. They have already accrued balances and you can just enter them as starting balances and then use just this template to track new PTOs from now onwards.

 

Tracking Period Limit

In some cases, you may have detailed PTO dates for each employee tracked for the past few years and you may want to migrate that detailed data to this template. Or if you have purchased the template in 2017, you would reach the end of default tracking period of 2 years in 2019.

For example, we want to track PTOs from 2010. The Start Date would be 1-Jan-2010.

The PTO Report though will not show current balances as of today (March 2018 when this tutorial was written).

PTO Report not showing Balances
PTO Report not showing Balances

 

And the Balance Trend Chart will only track until 2 years from Start Date.

(To illustrate, I have entered 4000 as the number of days to display on the chart.)

Report Chart shows only 2 years
Report Chart shows only 2 years

 

Now that we understand the limitation, let’s start our process to extend the tracking period.

 

6-Step Method to increase tracking period

 

Overview of steps

  1. Backup file
  2. Unhide hidden CAL sheet
  3. Find the end of table and extend calculations down
  4. Check PTO Report sheet for verification
  5. Hide the CAL sheet
  6. Save changes in File

 

Step 1: Backup

We recommend saving copies (archiving) of your files regularly as there is no other backup system.

Similarly, before making any extensions like we are going to do now, please save a copy of your current file separately with a different name (for example PTO_Manager_Archived_2018_03_18.xlsx) and then use your main file for the following steps.

 

Step 2: Unhide the hidden CAL sheet

Right-click on a sheet name and then choose Unhide.

Right Click on a sheet name and then choose Unhide
Right Click on a sheet name and then choose Unhide

 

Choose CAL and then click OK

Unhide CAL sheet
Unhide CAL sheet

 

Step 3: Extend calculations

Find the end of the calculations table. Click anywhere outside the table. The last cell of the table will appear as shown below.

End of CAL table
End of CAL table

 

Hover over the right bottom corner of that cell. When the cursor becomes double edged arrow, click and drag down.

Hover, Click and Drag Down
Hover, Click and Drag Down

 

We dragged down until row 3653. This extends the calculations until 12/31/2019 (10 years from 1/1/2010).

Extended calculations for 10 years
Extended calculations for 10 years

 

Step 4: Verify

If you now go to the PTO Report sheet, you will see current balances.

PTO Report showing Balances
PTO Report showing Balances

 

The Chart will now track until 12/31/2019.

Balance Trend Chart for 10 years
Balance Trend Chart for 10 years

Step 5: Hide the CAL sheet

Now that we have completed extending the period, we need to hide the sheet.

Right-click on CAL sheet name and choose Hide.

Hide the CAL sheet
Hide the CAL sheet

Step 6: Save the changes

Until we save the file, all the changes made so far will not be retained.

Press Ctrl+S or use the File menu to save the file.

 

Now, we have extended the tracking period to 10 years from the default 2 years.


Recommended 

More uses of Geographic Heat Map Excel Templates

Posted on

We have already published some Geographic Excel heat map templates on indzara.com. The primary purpose of the templates is to visualize data by geographical region. For example, we have country templates (India, United States, Canada, Australia) which allow visualizing data by state in that country. We have state templates (Tamil Nadu) which allow visualizing data by district in that state. There are also several options and settings that the user can customize to create the heat maps.

In this blog post, we will see some of the uses of our heat map templates for purposes other than visualizing state/district level data.

Specifically, we will learn the following

  1. How to make the entire map single color and change color
  2. How to group some regions together and color them
  3. How to play ‘Find the District’ or ‘Find the State’ game

We will use the Tamil Nadu heat map to illustrate. The concept is applicable to all heat map templates available on indzara.com

 

VIDEO DEMO

 

1. Make entire map single color

This is quite straight forward. We want to show the entire map in a single color and to be able to change the color.

Enter 1 as data value for all the districts.

Single Color Map - Enter data - Enter 1 for all districts
Single Color Map – Enter data – Enter 1 for all districts

 

Choose Colour by Range for Colour Scheme

Single Color Map - Settings - Color by Range
Single Color Map – Settings – Color by Range

 

In the data ranges table, enter only one range as shown. This means that all the districts with values from 1 will be shown in red color. Now, the entire map will be Red.

To change the color, change the red to another color from the drop down.

Single Color Map - Settings - Choose Color
Single Color Map – Settings – Choose Color

If I choose BLUE from the drop down, the map image changes to blue as shown below.

Single Color Map - Blue color across all districts
Single Color Map – Blue color across all districts

 

2. How to group districts together into regions?

Sometimes, we would want to group certain districts together in same color.

Enter data such that the districts in a group will have same value. For example, Northern districts are given 1, Central districts as 2, Eastern districts as 3, Western districts as 4 and Southern districts as 5.

Regions by Color - Enter data
Regions by Color – Enter data

 

In the Settings, choose Colour by Range scheme and then enter data ranges as shown below.

Regions by Color - Settings - Color by Range
Regions by Color – Settings – Color by Range

This will apply colors such that districts with 1 will be Blue color, 2 will be Green, 3 will be Yellow, 4 will be Pink and 5 will be Brown.
And the map will appear as below.

Regions by Color - Tamil Nadu Regions
Regions by Color – Tamil Nadu Regions

If we choose not to display District Names, then the map will appear as below.

Regions by Color - Tamil Nadu Regions - No names
Regions by Color – Tamil Nadu Regions – No names

You can, of course, change colors as needed.

 

3. Find a district

First, we should make the entire map in one color. Enter 0 as data for all districts.

Find a District - Enter data for all districts as Zero
Find a District – Enter data for all districts as Zero

 

Let’s remove the district names from display. Then, enter the data ranges as shown below.

Find a District - Settings - Colors
Find a District – Settings – Colors

 

Give the name of a district to your friend and ask to guess where that district is. For example, let’s say Tiruchirappalli district.

Find a District - Where is Tiruchirappalli in Tamil Nadu
Find a District – Where is Tiruchirappalli in Tamil Nadu

 

Then, you can reveal the correct location by typing 1 as data for that one district.

Find a District - Enter data for one district as One
Find a District – Enter data for one district as One

 

The map will display the district of Tiruchirappalli in Gold color as that is the color we chose for data values from 1.

Find a District - Tamil Nadu
Find a District – Tamil Nadu

You can even play this by yourself. It will be a good learning tool.

You can also play the game in reverse if you are playing with a friend. You can first show one district location on map and then ask friend to guess the name.

It is a good tool to use in schools teaching the location of a district in the state.

If you have used the heat map templates in other interesting ways, please share in the comments section below.

 

Recommended