Combinations Generator Excel Template

Posted on
Combinations Generator Excel Template

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.

Employee Leave Manager – Step by Step User Guide

Posted on

This article will walk you through how to use the Employee Leave Manager Excel Template.

If you are new to Excel templates, please visit the Support Page where there is important information that you should know about using Excel templates.

Overview of Steps

  1. ENTER ONE-TIME SETTINGS
    • Enter Leave Types (up to 12)
    • Choose Weekend
    • Enter Holidays
    • Enter Reporting Period
    • Enter Entitlement Settings
  2. ENTER DATA
    • Enter Employees Data
    • Enter Leave Entries
  3. VIEW AUTOMATED REPORTS & DASHBOARD
    • Team Balance Report
    • Daily Report
    • Employee Report
    • Monthly Team Dashboard

Enter One-time Settings

Leave Types

Enter Leave Types in the LEAVE TYPES table.

Enter Leave Types
Enter Leave Types

You can enter up to 12 leave types. You can name the leave types as you need for your organization.

Colors are shown as they will be used in the Reports and Dashboards.

Colors are automatically assigned and cannot be modified.

Choose No for BALANCE TRACKING if you would like the specific leave type to be not tracked in Balance Reports. Examples would be Offsite meetings and training that the employee has to attend but should not be counted as leave against the allowed leave. The employee is unavailable for normal work but is still working for the company in offsite events or training sessions.

Leave the field blank if you would like to track in Balance reports.

Weekends

The template can support customized weekends. You can choose the days of the week that your company considers as weekends. Just select TRUE for those days.

Choose Weekends
Choose Weekends

When calculating the workdays, the template will exclude weekends.

If an employee is taking leave from one Monday to the next Monday (assuming Sat and Sun are weekends), it will count as 6 leave days and not 8. Sat and Sun will not be counted as leave days against the leave allowed for the employee.

Holidays

The template can support customized holidays. You can choose the days of the week that your company considers as company holidays. Just enter the date and name of the holiday.

Enter Holidays
Enter Holidays

You can enter as many holidays as needed. When you continue using template for next year, you can just keep adding holidays for next year.

When calculating the workdays, the template will exclude holidays.

If an employee is taking leave from one Monday to Wednesday (assuming Tue is a holiday) it will count as 2 leave days and not 3. Tue will not be counted as leave day against the leave allowed for the employee.

Reporting Period

The reporting period is the range of dates that are used to calculate the leave balance of employees. Just enter the Start and End Dates for your company’s reporting period.

Enter Reporting Period
Enter Reporting Period

In the above image, we have set up the reporting period to be from Jan 1, 2019 to Dec 31, 2019.

The max reporting period allowed is 12 months. If you enter a date range that is more than 12 months you will see an error message.

Enter Reporting Period - Error - More than 12 months
Enter Reporting Period – Error – More than 12 months

Report End Date cannot be before the Report Start Date. An error message will appear if that is detected.

Enter Reporting Period - Error - End Before Start
Enter Reporting Period – Error – End Before Start

These validations are present to ensure that the date inputs are correct. If there are errors, the Balance report and Employee report will not be populated.

Entitlement Setup

The template provides two options for entitlement (or quota) setup. You can set the entitlement at the total level or per leave type.

Enter Entitlement Option
Enter Entitlement Option

If you choose TOTAL, you can then enter the total entitlement of leave days.

Set Total Leave Entitlement
Set Total Leave Entitlement

In the above example, we have chosen 15 days as total allowed leave for all employees.

If you choose PER LEAVE TYPE, you can then enter the entitled leave days for each leave type.

Set Leave Entitlement per Leave Type
Set Leave Entitlement per Leave Type

The leave types that we set to NO for Balance Tracking earlier, do not need any Entitled leave and can be set to 0. In the above example, each employee is allowed 8 days of Vacation, 5 days of Sick, 2 days of Medical and so on.

If you would like to have some employees have more or less entitled leave, you can enter them in Adjustments sheet.

Enter Data

There are two data inputs you would have to enter. 1) Employees 2) Leave Entries

Enter Employee Data

In the EMPLOYEES sheet, you can enter list of employees in your organization.

Enter Employees Data
Enter Employees Data

Employee Name and Start Date are Required Fields. End Date should be used if the employee has left the company.

Employee Name should be unique. Do not repeat the same employee name twice in this table.

Other columns in blue fill can be customized as needed. You can rename the columns and use to store any information you want to store about the employees.

Enter Leave Entries

In the LEAVE sheet, you can enter list of leave entries for all employees in your organization.

Enter Employee Leave Data in Table
Enter Employee Leave Data in Table

For each leave entry, enter the Employee Name, Start of leave, End of leave, Leave Type and Daily Leave Value.

If an employee is taking a single day leave, enter the END DATE same as START DATE. Do not leave the END DATE blank or empty.

DAILY LEAVE VALUE can be partial days as well. 0.5 would represent half a day.

If an employee is taking multiple day leave, for example, from Aug 5, 2019 to Aug 8, 2019 and Daily Leave Value is 0.5, that would mean 4 days of 0.5 each, resulting in 2 whole days of leave taken. Do not enter the total leave value in the DAILY LEAVE VALUE field.

DAILY LEAVE VALUE can be entered as negative value if an employee is working on a holiday or a weekend. Let’s call it a ‘Make up Day’. The template can support such scenarios.

Validations

There are some validations put in place to highlight data entry errors in different colors.

Leave Entries - Validations
Leave Entries – Validations
  • If you are entering negative leave value (make up day) it needs to be for a holiday or a weekend.
  • Leave Start Date cannot be after the Employee has left the company.
  • Leave Start Date cannot be before the Employee has joined the company.
  • Leave End Date cannot be before Leave Start Date.

Any leave Entries with these errors will be excluded from reporting.

Custom Columns

Just like in Employees table , we can add custom columns to the Leave table.

Enter Employee Leave Data - Custom columns
Enter Employee Leave Data – Custom columns

You can enter new columns to the end of the table to record information about leave. However, do not insert any columns in between.

That completes our data input for the template. You are now ready to view the automated output from the template.

View Automated Reports & Dashboards

Balance Report

Balance report lists the employees along with their leave balance information at total as well as each leave type.

Team Balance Report
Team Balance Report

There are 3 controls available in the Balance report to customize it.

  1. Display
    • You can choose to display one of three measures on the report.
    • Entitled: Number of days of leave entitled; Used: Number of days of leave used ; Balance: Number of days of leave available as balance
  2. Filter
    • You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
    • You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
    • Please note that any employee who is not employed during the reporting period will automatically not be displayed in this report.
  3. Page #
    • The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.

Negative balances will be shown in red fill.

Daily Report

Daily report lists all the leave entries for a specific date.

Daily Report
Daily Report

 

Here, you can type in any date and the report will pull the leave entries for that specific date.

The report also summarize the total leave by leave type on the left.

The last column on display is a custom column where you can replace it with any column from Leave table.

Employee Report

Employee report is a printable report that will display the summary and details of leave and balance for a specific employee.

This report has two pages. Page 1 displays the summary by leave type for the entire reporting period at the top and a calendar view in the bottom showing leave information.

Employee Report Page 1 with Calendar
Employee Report Page 1 with Calendar

 

Page 2 displays the monthly totals by leave type and the summary for the entire reporting period. It also displays the Worked Days calculated as (Total Workable Days in the Month – Leave taken by each employee).

Employee Report Page 2 - Monthly Totals by Leave Type
Employee Report Page 2 – Monthly Totals by Leave Type

Monthly Team Dashboard

Team Dashboard displays the leave information of all employees in one specific month.

Page 1 displays the calendar view showing leave of each employee, where leave types are shown in different colors.

Monthly Team Dashboard Page 1 with Calendar view
Monthly Team Dashboard Page 1 with Calendar view

Page 2 shows the totals for each employee by leave type. It also displays the overall total leave and worked days.

Monthly Team Dashboard Page 2 with totals by Leave Types
Monthly Team Dashboard Page 2 with totals by Leave Types

There are 3 controls available in the Team Dashboard to customize it.

  1. Custom Column
    • As the second column next to Employee Name, you can choose any column from Employees table. In the above example, we have chosen Department.
  2. Filter
    • You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
    • You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
      • Please note that any employee who is not employed during the Month chosen will automatically not be displayed in this report.
  3. Page #
    • The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.

Employee Leave Manager Excel Template – Step by Step User Guide & FAQs

Posted on

Thanks for visiting the support page for Employee Leave Manager Excel Template which can help simplify leave tracking in your organization.

The following are the support articles to help you get started with using the template. If there are any questions not addressed here, please drop a comment and I will add article/video demo about that topic.

Support Articles

  • Please see this article on 3 Important Tips before using the template
    1. Do not edit calculated cells
    2. Input Data is always visible & can be edited easily
    3. Backup by saving file regularly
    4. Bonus Tip: Do not insert columns in between in Leave sheet. Add to the end of the table.
  • This template uses Excel tables for data entry. If you are not very familiar with Excel tables, please see this article on How to enter data using Excel Tables?
      • What is a Table?
      • Why do we use Tables?
      • Components of a Table
      • 3 Basic Data Entry Operations
      • 5 Essential Tips to avoid mistakes
  • Step by Step User Guide 
    • Step by Step instructions on entering data
  • Upcoming articles
    • How to change or add colors for leave type?
    • How to handle if some employees have different holidays than others?
    • How to ensure dates are entered in correct format?
    • What are the differences between the free vacation tracker and the Employee Leave Manager templates?
    • Which template is suitable for your organization? PTO Manager or Leave Manager?

If you have any questions on the template, please post them in the comments section below. I will respond as soon as I can. If it will benefit others, I will also publish a blog post and video answering your question.

2014 Indian Lok Sabha Elections Results in Excel

Posted on

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

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.

Related templates

  1. 2015 Cricket World Cup Template
  2. India Geographical Heat Map
  3. 2016 US Presidential Heat Map

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.

Template Version

This user guide is for the latest version v3 of the template. If you are using a previous version and need the new file, please email support@indzara.com with your Order Number. If you need support with the previous version, please email your questions to support@indzara.com

Overview of steps:

  1. Enter Settings
  2. Enter Resource list with standard availability and allocate Capacity to Skills and Projects
  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

 

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.

Step 1 Enter Planning Period Dates
Step 1 Enter Planning Period Dates

We will use a 3 month period from Apr 1, 2019 to June 30, 2019.

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.

Step 3 - Enter List of Holidays
Step 3 – Enter List of 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

Resources
Enter list of Resources available for planning.

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.

Step 6 - Enter list of Resources
Step 6 – Enter list of Resources

Step 2: Enter Resources’ Capacity

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

We must enter the resources, provide start and end dates of availability, available hours every weekday and other information related to capacity.

Tip: To help with data entry, you can copy the values you entered in the Settings sheet, for Resources, and then paste in the Capacity sheet. Please make sure that you paste as values.
After copying the values, right click on cell A4 in Capacity Sheet. In the menu that pops up, choose Values under Paste Options. This ensures that only the values are carried over, without impacting the formatting and drop-down validations.

Pasting Resource Names as Values
Pasting Resource Names as Values

After entering resource names, we enter the Start 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.

Enter Resources Capacity - Available Work hours by Weekday
Enter Resources Capacity – Available Work hours by Weekday

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

We can provide the End Date of each resource if there is an End Date for the availability. If no end date is provided, the template will assume that the resource is available through the end of the planning period.

Resources Capacity Table - Entered
Resources Capacity Table – Entered

Finally, we enter the Cost per Hour for each resource.

Important: The last column is a calculated field that is critical to the functioning of the template. Please do not edit/delete it. If you don’t want to see it, you can hide the column

How to allocate same resource to multiple skills?

You can allocate each resource to one or more skills.

To allocate one resource’s capacity to multiple skills, add a new row to the table and assign a different skill. Ensure the available work hours are distributed between the two skills.
For example, if Developer 4 was available 6 hours in total and can perform Development role for 4 hours and Project Mgmt role for 2 hours, we would enter as shown below.

Entering same resource with multiple skills
Entering same resource with multiple skills

Resource name will be same Developer 4, but work hours may vary, and Skill Group will be different. In this case, we assigned to same Project B.

How to allocate same resource to multiple Projects?

You can allocate each resource to one or more Projects.

To allocate one resource’s capacity to multiple projects, add a new row to the table and assign a different Project. Ensure the available work hours are distributed between the two Projects.

For example, if Developer 4 was available 6 hours in total (Development skill), and will work 4 hours for Project B and 2 hours for Project A, we would enter as shown below.

Entering same resource with multiple Projects
Entering same resource with multiple Projects

Resource name will be same Developer 4, but work hours may vary, and Project will be different.

How to allocate same resource to multiple Projects and Skills?

You can allocate same resource to one skill in one project and a different skill in another project.

For example, if Developer 4 was available 6 hours in total, and will work 4 hours in a Development role for Project B and Project Mgmt role for 2 hours for Project A, we would enter as shown below.

Entering same resource with multiple Projects and Skills
Entering same resource with multiple Projects and Skills

How to shift same resource to a different Project after a certain date?

If we want a resource to work in one project for a certain date range and then change to a different Project, we can use the same approach. We can use the END DATE column to enter the end date of a certain Project assignment and then enter a new row with a different project.

Assigning a resource to different projects over time
Assigning a resource to different projects over time

In the above illustration, Developer 4 works on Project B for 6 hours a day until 30th Apr. From May 1st, the resource works 6 hours a day on Project A.
You can apply the same method if you want a resource to change skill from a certain date.

Let’s go with the simple setup now where each resource is assigned to only one skill and one project.

Limitation:

Now, the above examples would have made it clear that this Capacity table can have more than 1 record per resource. Though the default Resource limit is 100, this table can accept 300 entries.

For example, if you have 50 resources, each resource assigned to 2 skills on average and 3 projects on average, that would require 50 X 2 X 3 = 300 entries.

300 unique Resource-Skill-Project combinations can be entered.

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 Overtime taken by Resources
Enter Vacation Overtime taken by Resources

Project Planner 1 is taking 3 days off (8 hrs each day) from 16th Apr to 18th Apr.

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 must 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 21st May. We enter 21st May as Start and End Dates. It is important that we fill both dates.

Finally Project Planner 2 also does overtime on 25th May. We enter 8 (not -8) in Hrs, since this is overtime.

This 25th May 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.

Important:

If you have assigned a resource to multiple skills and/or projects, you will have to enter vacation or overtime separately for each combination. This allows more flexibility in capacity planning as you can enter overtime only for a certain project instead of both projects (assuming resource assigned to 2 projects).

Step 4: Enter Demand data

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

Enter Demand as Tasks assigned to Resource Project and Skill
Enter Demand as Tasks assigned to Resource Project and Skill.

Required fields: Date of the task, Resource assigned to do that task, Hours of work required, Project assigned and Skill Group.

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

The three 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.

Do Not Edit Calculated Fields
Do Not Edit Calculated Fields

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.

Capacity Planning Dashboard
Capacity Planning Dashboard

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

Dashboard Slicers
Dashboard Slicers

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

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

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

On the left side, you can see the Capacity (2472 hrs), Demand (2287 hrs), Surplus of 185 hrs at 93% Utilization Rate.

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

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. Though the overall utilization rate may be 100%, individual resources have surplus and deficit and that must be addressed.

Hence it is important to view utilization at resource level.

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.

Project level Capacity vs Demand
Project level Capacity vs Demand

The above visual presents the capacity vs demand for each of the Projects.

On the left, you will see projects where there is deficit – that is, capacity is less than demand. In this case, we don’t have any.

A project will either be in deficit or surplus – not both.

On the right, you will see projects where there is surplus – capacity is greater than demand. Project A has capacity of 1236 hours while demand is only 1097 hours. The surplus is 139 hours.

Project B has capacity of 1236 hours while demand is only 1190 hours. The surplus is 46 hours.

Skill level Capacity vs Demand
Skill level Capacity vs Demand

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

Development has a total capacity of 1488 hours but demand of 1495 hours. 7 hours overutilized (deficit).

Project Mgmt has a total capacity of 984 hours but demand of 792 hours. 192 hours under-utilized (surplus).

A Skill Group will be in Surplus or Deficit or neither; never in both.

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

Resource level Capacity vs Demand
Resource level Capacity vs Demand

Clearly two resources Developer 3 and Developer 2 do not have enough capacity to complete the tasks assigned to them. Meanwhile, 3 other resources have a lot of additional hours available but not utilized.

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 Planning Dashboard - Hours - Capacity vs Demand - Charts
Capacity Planning Dashboard – Hours – Capacity vs Demand – Charts

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 Planning Dashboard - Cost - Capacity vs Demand - Charts
Capacity Planning Dashboard – Cost – Capacity vs Demand – Charts

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 must do one or more of the following:

  1. Change Capacity
    • Adjust standard availability of resources in the Resources sheet.
      1. Increase (to increase capacity) or Decrease (to reduce capacity) available hours
    • Add overtime (to increase capacity) or vacation time (to reduce capacity)
  2. Change Demand
    • Change the demand hours if possible. This would reduce the demand and allow the capacity to fulfill the demand.
  3. Change Allocation/Assignment
    • Change assignment of tasks (demand) to a different resource.
    • Change capacity allocation of resource to Projects.

The first option Change Capacity is straight forward and don’t need much explanation. The impact is that it will cost more to increase capacity.

The second option Change Demand 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 may neither increase the cost nor reduce the demand. It is the re-assignment of capacity and demand (tasks) to existing resources to remove over-utilization.

Changing Assignment of Tasks to a Different Resource

Let’s filter the dashboard to Project A by using the slicers on the left of the Dashboard.

The Resources table shows the following.

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

Developer 2 is overutilized  by 5 hours while Developer 1 has 37 unutilized hours.

In the Demand sheet, Filter to Resource Developer 2. Find a task for 5 Hours and re-assign to Developer 1.

Reassigning a task to Developer 1
Reassigning a task to Developer 1

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

That will update the dashboard calculations.

Project A - Resource - Capacity vs Demand - After assignment
Project A – Resource – Capacity vs Demand – After assignment

Now, there are no overutilized resources in Project A.

Let’s clear the Project filter in Dashboard.

Now, this is what the Resources table shows.

All projects - Resource - Capacity vs Demand
All projects – Resource – Capacity vs Demand

Developer 3 has 39 overutilized hours, but we don’t have a Developer with that many unutilized hours.

Project Planner 2 who also works on Project B has 85 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.

Assign Project Planner 2 Development capacity
Assign Project Planner 2 Development capacity

Important things to note here:

  1. A new record must be created in the Capacity Sheet as shown above (last row). Resource Name will be same, but Skill will be Development. Cost can vary and can be entered any value.
  2. When we enter the standard work hours of the new record (we have entered 1 hour a day), we must review the work hours already assigned for the resource (Project Planner 2). Previously Project Planner 2 had 8 hours a day availability. We have updated that to 7 hours for Project Mgmt and assigned 1 hour to the Development. This assumes that the resource is still working same total number of hours. It’s just that we are changing the skill being used.
  3. When you split a resource to multiple skills or projects, check the Vacation/overtime sheet and update if that resource has Vacation/Overtime entries.
    1. In this case, Project Planner 2 has vacation and overtime entries. We create additional entries for the new skill and update the previously entered entries.
Review Vacation and Overtime entries when splitting resources
Review Vacation and Overtime entries when splitting resources

Now that we have created the capacity allocation for Development skill, we need to assign tasks from Developer 3 to Project Planner 2 in Demand Sheet. Select enough tasks to switch to overcome the overutilization.

Assign tasks from Developer 3 to Project Planner 2
Assign tasks from Developer 3 to Project Planner 2

Let’s refresh the Dashboard again.

All projects - Resource - Capacity vs Demand - Final
All projects – Resource – Capacity vs Demand – Final

We have no resources over utilized. 🙂

Overall metrics look like below.

Capacity Planning Dashboard - Summary Metrics - After Optimization
Capacity Planning Dashboard – Summary Metrics – After Optimization

All the resources will have enough capacity to meet the demand. We have 185 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.

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.

Calendar - Capacity by Resource - Hours - Weekly
Calendar – Capacity by Resource – 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.

Calendar - Surplus Deficit by Resource - Hours - Weekly
Calendar – Surplus Deficit by Resource – 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