Recruitment Tracker for Google Sheets – Free

Posted on

For a simple and effective solution to manage the recruiting process and truly understand its performance via effective Recruiting metrics, use this Google Sheets recruiting template to track status of the applicants (candidates) in an organized way. Get an instant view of how many applicants are in the pipeline and where in the funnel applicants are dropping off. You can also easily view the top reasons why candidates were not selected.

This template is designed for Google Sheets, but if you are looking for an Excel template, please visit Recruitment Tracker in Excel.

Recruitment Tracker Dashboard - Google Sheets- Hiring
Recruitment Tracker Dashboard – Google Sheets- Hiring

HOW TO TRACK RECRUITING IN GOOGLE SHEETS

VIDEO DEMO

Following are the steps:

  1. Copy the template and save in your Google Drive
  2. Enter Settings information
  3. Input candidate or applicant information
  4. View Dashboard
  5. Complete Data Entry after choosing candidate

STEP 1: MAKE A COPY OF THE SHEET

Click on the link above and then make a Copy of the Sheet as shown in the screenshot below.

Google Sheets Template - Make a Copy
Google Sheets Template – Make a Copy

You can save the copy in your own drive for your use. You can then share the copy with your colleagues if needed.

STEP 2: ENTER SETTINGS

In the Settings sheet, start by entering Job Title, Recruiter Name and Job Posted (or Hiring Start) Date.

Settings - Enter Job title, start date and recruiter name
Settings – Enter Job title, start date and recruiter name

The template supports 4 recruitment stages before hiring.

Customize 4 Recruitment Stages
Customize 4 Recruitment Stages

 

You can customize the stages by renaming the default stages.

If you have less than 4 stages, for example 3, please enter only 3 stages as shown below.

Example of 3 Stages in Recruitment pipeline
Example of 3 Stages in Recruitment pipeline

Enter Decline Reasons that are used by your company. These are reasons that you can assign to each candidate that didn’t get selected for the role. Having this information tracked allows understanding the reasons why the candidates are not meeting expectations of the job.

Decline Reasons - 10 values
Decline Reasons – 10 values

STEP 3: ENTER APPLICANTS’ DATA

Enter the list of applications in the DATA sheet.

Enter Application or Candidates Data
Enter Application or Candidates Data
  • When you enter a new Applicant Name, the table will auto-expand by showing gray background color.
  • Enter Applicant (Candidate) name and dates when the applicant reached each of the four stages. If the applicant does not reach a stage, leave it blank.
  • When a decision is made to not proceed with an applicant, enter CLOSED in the STATUS column.
  • Enter ‘Decline reason’ for candidates who were not selected.
  • Enter Comments if needed.
  • STAGE is a calculated field that tells which stage an applicant is. Please do not edit the formulas.

If you had only 3 stages in your recruitment process, the fourth one will show as below (Hide this column). You can hide the column so that you only see columns relevant to you.

Hide extra column
Hide extra column

STEP 4: VIEW RECRUITMENT DASHBOARD

When hiring is on-going, the Dashboard sheet will display useful information about the recruitment.

Recruitment Tracker Dashboard - Hiring
Recruitment Tracker Dashboard – Hiring

You can see the total number of applications, days since Job Posted Date, number of closed and active applications.

The Recruitment Funnel shows how the applicants flow through the recruitment stages. This is helpful to know where applicants are falling off in the process.

The Decline Reasons show the top reasons why candidates are not selected.

Active Pipeline shows the stages where the open applications are currently in.

STEP 5: DATA ENTRY AFTER CHOOSING CANDIDATE

Once a candidate has been selected, enter the HIRED APPLICANT and HIRED DATE in the SETTINGS sheet.

Hired Date and Applicant
Hired Date and Applicant

The Dashboard will now represent the complete information about this recruitment.

Recruitment Tracker Dashboard - Google Sheets - Hired
Recruitment Tracker Dashboard – Google Sheets – Hired

The status will change to HIRED and the funnel represents all applications (open and closed).

Please note that the Active applications will still represent all applications that have not been closed. So, please close all applications.

Enter Decline reasons for all applications except the hired one.

Complete data entry after hiring
Complete data entry after hiring

Final dashboard should look like this.

Final Recruitment Dashboard
Final Recruitment Dashboard

 

LIMITATION:

The template is designed to be used for tracking recruitment for only one job. If you need to track for a second job, make a copy of the file and use.

Please do not make a copy of the sheet in the same file. It will not work that way.

 

I will soon be working on a version that will support multiple jobs and positions in one file. Please post your interest in the comments below, if that template will be useful.

2020 Excel Calendar Template – 20 Calendar Designs – Free Download

Posted on
2020 Calendar Design 13 – 2 Pages - 6 Months each

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

 

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

TOPICS

We will discuss the following topics in this post.

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

FEATURES OF 2020 CALENDAR EXCEL TEMPLATE

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

DOWNLOAD 2020 CALENDAR TEMPLATE

2020 Calendar Excel Template

2020 Calendar Excel Template with Sample Data


VIDEO DEMO

Watch On YouTube

 

HOW TO USE THE TEMPLATE 

Overview of steps

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

Now, let’s see each step in detail.

Step 1: Enter Basic settings

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

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

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

Change Beginning Month of Calendar
Change Beginning Month of Calendar

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

Start Week from any day
Start Week from any day

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

Step 2: Choose Weekends

Choose Weekends
Choose Weekends

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

Step 3: Enter Holidays

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

Enter Holidays for 2020 Calendar
Enter Holidays for 2020 Calendar

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

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

Step 4: Enter your personal or company Events

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

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

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

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

Step 5: Pick a calendar design from the 20 designs

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

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

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

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

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

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

20 Calendar Sheets
20 Calendar Sheets

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

Step 6: Print the calendar or export to PDF

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


20 CALENDAR DESIGNS

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

2020 Calendar Designs
2020 Calendar Designs

Calendar Design 1

1 Page 12 Months – 4 X 3

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

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

Calendar Design 2

1 Page 12 Months – 3 X 4

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

Calendar Design 3

1 Page 12 Months – 2 X 6 with Events

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

Calendar Design 4

1 Page 12 Months – 6 X 2 with Events

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

Calendar Design 5

1 Page 12 Months – 12 X 42 with Events

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

Calendar Design 6

1 Page 12 Months – 12 X 31 with Events

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

Calendar Design 7

6 Pages – Planner

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

Calendar Design 8

1 Page – 54 Weeks – with Events

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

Calendar Design 9

12 Pages – Monthly

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

Calendar Design 10

6 Pages – with Events

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

Calendar Design 11

4 Pages – with Events

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

Calendar Design 12

3 Pages – 4 Months on each page

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

Calendar Design 13

2 Pages – 6 Months on each page

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

Calendar Design 14

12 Pages – Monthly – with Events

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

Calendar Design 15

12 Pages – Monthly – Type Events

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

Calendar Design 16

12 Pages – Picture Calendar

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

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

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

Calendar Design 17

12 Pages – Quotes Calendar

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

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

Calendar Design 18

1 Page – Weekly Calendar

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

Calendar Design 19 (Daily Calendar)

1 Page – Daily Calendar

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

Calendar Design 20 (Two Years Calendar)

1 Page – 2 Years Calendar

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

TIPS TO CUSTOMIZE THE 2020 CALENDAR TEMPLATE

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

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

Select Cells to change formatting

Select Cells to change formatting
Select Cells to change formatting

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

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

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

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

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

A couple of things to note here:

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

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

2. How to change Event and Holiday colors?

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

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

Open Conditional Formatting Menu
Open Conditional Formatting Menu

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

You can apply the same technique to the Holidays table.

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

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

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

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

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

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

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

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

5. How to unprotect (unlock) sheets?

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

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

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

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

6. How to print calendars?

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

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

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

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

7. How to export calendar to PDF?

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

Export calendars as PDF
Export calendars as PDF

In the following dialog box,

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

RECOMMENDED TEMPLATE

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

Additional Features in Event Calendar Maker

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

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

Have a wonderful 2020.

Retention Dashboard – Excel Template – Step by Step User Guide

Posted on

In this article, we will explain step by step how to use the Retention Dashboard Excel Template. To learn more about the features of the template and to download, please visit the Product page.

OVERVIEW OF STEPS

  1. Enter data in Employees Data sheet
  2. Refresh Calculations
  3. View/Interact with 3 Dashboards

VIDEO DEMO

STEP 1: ENTER DATA IN EMPLOYEES DATA SHEET

All the input happens in one sheet. This is a table with default 1000 rows.

Employee Data Entry Table
Employee Data Entry Table

Each row in this table represents one employee’s record.

Start by entering Employee Name, Employee ID and Email information.

Employee ID is a required field.

You can rename EMAIL field and use it to store any other information as needed.

Now, let’s look at 14 other columns in this table. We can categorize them into 3 categories of attributes.

Types of Attributes

4 Employee Attributes – Date of Birth, Hire Date, Gender, Hire Source

Employee Attributes - Date of Birth, Hire Date
Employee Attributes – Date of Birth, Hire Date
  • These attributes are typically those that do not change over time for an employee
  • Hire Date and Date of Birth columns should not be repurposed. They are used for calculating Employee Tenure and Age. Modifying this will break the calculations.
  • Gender and Hire source can be renamed and repurposed. Repurposed means the column can be used to store other information. For example, you can rename Hire Source as ‘Salary Method’ and note values on how the employee gets paid (Check/Cash/Direct Deposit). They will still flow through to the dashboards and be displayed as expected.

7 Job Attributes – Location, Department, Job Type, Job Category, Job Level, Job Title, Salary

Job Attributes - Location, Department
Job Attributes – Location, Department
  • These are attributes that can be updated over time for an employee. For example, an employee can move from one Department to another.
  • 5 are set up to automatically appear on Dashboards.
  • 2 (Job Title and Salary) are available to store data in the table, but do not flow through to the dashboards.
  • All 7 attributes can be customized. They can be renamed and repurposed.

3 Exit Attributes – Exit Date, Exit Type, Exit Reason

Exit Attributes - Exit Date, Exit Type and Reason
Exit Attributes – Exit Date, Exit Type and Reason
  • These attributes are required to be entered only for employees who have left the company. They can be left blank for active employees.
  • Exit Date and Exit Type cannot be repurposed as calculations are based on those two columns.
    • Exit Type requires Voluntary and Involuntary as values.
    • Also called as Termination Type in some companies.
  • Exit Reason can be repurposed. It will appear on Exit Dashboard automatically.
    • Also called as Termination reasons in some companies.

In General values are not limited in any column except Exit Type column.

Exit Type values - Voluntary, Involuntary
Exit Type values – Voluntary, Involuntary

When to enter what data?

  • To begin: When you first start using the template, you will enter all existing employees in your organization.
  • After that, when new employees join, you will enter that new employee’s information in the table.
  • When employees move from one department to another or from one location to another, you can just update the specific employee’s data in the table.
  • When employees leave the company, enter the Exit Attributes for those employees.

How to rename and use customizable fields

In the Employees Data sheet, just click on the column header and rename it.

How to add more attributes

Adding new columns
Adding new columns

You can click on cell R3 and type a new column name. This will add a new column. You can add any number of such columns to track information about employees.

Note: new columns will not appear on dashboards automatically.

Step 2: REFRESH CALCULATIONS

The template uses pivot tables and hence the calculations have to be refreshed every time data is added or edited in the Employees Data sheet.

From the DATA ribbon, click on Refresh All.

Refresh Calculations - Data - Refresh All
Refresh Calculations – Data – Refresh All

STEP 3: DASHBOARDS

Once the data is entered and refreshed, we can view the 3 dashboards.

The template provides three automated dashboards:

Retention Dashboard

Retention Dashboard presenting 7 KPIs with trend over the last 12 months.

Employee Retention Dashboard
Employee Retention Dashboard

7 KPIs

Following 7 KPIs are automatically calculated by the template

  • Active Employees: Number of employees active with the company
  • Hires: Number of new employees hired
  • Exits: Number of employees who have left the company
  • Turnover Rate: (Number of Exits/Avg. Number of active Employees during the period) * 100
  • Retention Rate: (Number of Exits from Employees active at the Beginning of the period /Number of Employees active at the beginning of the period) * 100
  • Tenure of employees: Average duration of employment of active employees
  • Tenure of Exit Employees: Average duration of employment at the time of exit

Snapshot Dashboard

Snapshot Dashboard provides interactive view of # of Employees and Avg. Tenure by 7 different employee and job attributes.

Page 1 shows the number of employees and breakdown by various attributes.

Company Snapshot Dashboard
Company Snapshot Dashboard

Page 2 shows the Average Tenure of employees and breakdown by various attributes.

Company Snapshot Dashboard - Tenure
Company Snapshot Dashboard – Tenure

There are 10 Slicers available to drill down.

Dashboard Slicers
Dashboard Slicers

To select multiple values in a slicer (for example, Hire Source LinkedIn and Indeed) just press Control while clicking on the specific values.

Retention Dashboard Excel Template - Slicers in Dashboard
Retention Dashboard Excel Template – Slicers in Dashboard

Exit Dashboard

Exit Dashboard provides interactive view of exit data. Exit Type (Voluntary/Involuntary) and Exit Reason can be quickly viewed to understand how, why and when employees are leaving the company.

Employee Exit Dashboard
Employee Exit Dashboard

12 slicers are available to drill down or filter.

This dashboard represents only employees who left the company.

Modifying Dashboards

  • Charts: If you do not need one of the charts in the Dashboard, just remove that one chart by selecting and deleting, without impacting the rest of the dashboard.
  • Slicers: If you do not need one of the slicers in the Dashboard, just remove that one slicer by selecting and deleting, without impacting the rest of the dashboard.
  • Renaming Slicers: You can rename the slicers by changing the settings of the slicer.
    • Right click on a slicer and choose ‘Slicer Settings’
    • Modifying Slicer Settings
      Modifying Slicer Settings

      Rename the Caption

      • Renaming Slicer
        Renaming Slicer
  • Resizing Charts: Depending on the data in your organization, you may need certain charts and slicers to be long or short. Not a problem. Just unprotect the sheet and resize the chart to fit your needs. It will not impact the dashboards in any negative way.
Retention Dashboard - HR Excel Template - Modify Charts
Retention Dashboard – HR Excel Template – Modify Charts

How to Print or Export to PDF

The Dashboards are set up to be ready for print. Press Control+P or use Excel’s file menu to print. You can also use Excel’s default Export to PDF option and export as PDF.

Unprotecting Sheets

You would not need a password to use the template. The template performs the default functionality without having the need to unprotect protected sheets. However, if you would like to modify functionality, you can use indzara as password to unprotect and then make changes.

How to unprotect sheet?

Whenever you unprotect the sheet, please protect it after making the adjustments.

Extending beyond 1000 employee limit

The template has a hidden sheet named HELP which is limited to 1000 rows. That table needs to be extended down to more rows. Then, automatically the dashboards will pick up more than 1000 rows entered in the Employees Data sheet.

 

If there are any questions on this template, please post in the comments section below.

Retention Dashboard – Excel Template – Support Page

Posted on

Thanks for visiting the support page for Retention Dashboard Excel Template which can help simplify employee retention 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 Important Tips before using the template
    • Input Data is always visible & can be edited easily
    • Refresh Calculations after entering or updating input data
    • Backup by saving file regularly
  • 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

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.

PTO (Paid Time Off) Balance Calculator for Hourly Employee

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

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

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

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

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

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

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

In this article, we will cover the following topics.

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

KEY FEATURES OF THE EXCEL TEMPLATE

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

FREE DOWNLOAD

PTO Calculator (Hourly Employee) Excel Template

REQUIREMENTS

Microsoft Excel 2010 or newer

LIMITS

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

HOW TO USE THE EXCEL TEMPLATE

VIDEO DEMO

OVERVIEW OF STEPS

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

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

STEP BY STEP GUIDE

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

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

STARTING DATE

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

Employee Details

EMPLOYEE NAME

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

HIRE DATE

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

STARTING BALANCE

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

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

DEFAULT HOURS WORKED

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

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

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

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

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

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

Here is a sample policy.

PTO Policy - Hourly Employee
PTO Policy – Hourly Employee

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

PTO ACCRUAL PERIOD

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

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

Weekly

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

PTO Policy - Weekly Accrual
PTO Policy – Weekly Accrual

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

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

Every 2 Weeks

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

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

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

Twice a Month

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

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

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

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

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

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

Monthly

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

PTO Policy - Monthly Accrual
PTO Policy – Monthly Accrual

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

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

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

REVIEW FIRST ACCRUAL

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

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

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

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

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

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

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

Now let’s discuss Rollover Policy.

ANNUAL PTO ROLLOVER POLICY

The template provides three options.

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

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

Rollover Timing options
Rollover Timing options

PROBATIONARY PERIOD

Enter the duration of the Probationary period in days.

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

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

MAXIMUM ALLOWED PTO BALANCE

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

ROUNDING IN PTO BALANCE

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

PTO ACCRUAL RATE

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

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

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

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

There should always be a record with 0 tenure completed

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

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

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

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

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

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

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

WEEKENDS & HOLIDAYS

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

Weekends
Weekends

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

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

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

Holidays
Holidays

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

Read how to enter and delete data in Excel tables

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

HOURS WORKED

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

Enter Hours Worked by Employee
Enter Hours Worked by Employee

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

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

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

PTO USED

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

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

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

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

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

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

PTO REPORT

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

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

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

BALANCES

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

POLICY DETAILS

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

The bottom section provides an interactive calendar.

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

DATES

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

DISPLAY

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

Calendar Display Choices
Calendar Display Choices

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

WORKED

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

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

BALANCE CHANGE

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

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

PTO USED

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

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

MONTHLY BALANCES

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

MONTH-END BALANCE TREND

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

PRINT OR EXPORT

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

BALANCE ADJUSTMENTS

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

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

Balance Adjustments
Balance Adjustments

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

 

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

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

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

 


RECOMMENDED TEMPLATES

PTO Policy for Hourly Employees & How to calculate PTO Balance

Posted on
PTO Policy for Hourly Employees

In this article, we cover the common components of a PTO (Paid Time Off) policy for hourly employees. PTO Policy is an important part of a company’s Employee Benefits package. It is one of the factors being considered by candidates when they decide to join a company. An effective employee PTO policy will benefit both employees and employer.

We will go over each of the PTO policy components, explain them in detail and show examples to illustrate how PTO balances are calculated.

COMPONENTS OF A PTO POLICY

Typically, a PTO policy would address the following aspects and they are implemented in the templates from indzara.com.

  1. Probationary Period: Is there a probationary period for an employee where PTO is not accrued?
  2. Accrual Period: How frequently and when does the PTO accrual happen?
  3. Accrual Rate: How much PTO does an employee accrue during each period? Does it change by Tenure?
  4. Rollover Policy: Can balance from one year be rolled over to the next year? If so how much and when?
  5. Max Balance: Is there a maximum balance that an employee can carry at any time?

Now, let’s look at each of them in detail.

1. PROBATIONARY PERIOD

In some companies, employees may not be awarded any PTO for the first X number of days of employment. For example, employee does not earn any PTO during the first 90 days of employment. This period may be called as Probationary period. Work done during this period will not result in any PTO Accrual.

2. PTO Accrual Period

This is to define how often PTO is accrued. Most common options are Weekly, Every 2 Weeks, Twice a Month and Monthly. This is typically aligned with your pay periods.

Weekly: This option means that employee will accrue PTO once a week, provided he/she has worked enough hours (cumulatively) to meet the Accrual rate policy requirements. For example, accruals happen every Friday.

Every 2 Weeks: Every 2 Weeks policy is similar to weekly except it accrues every other week. For example, every other Friday.

Twice a Month: Accruals can happen 1st and 15th of every month. Or 15th and Last day of every month.

Monthly: For example, we can do accruals on 1st of every month or ‘Last Day’ of every month.

3. PTO Accrual Rate

This defines how much PTO hours are earned by the employee on each accrual day.
To explain this, let’s take an example policy: Employee would earn 1 hour of PTO for every 40 hours worked.

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

Another policy example: Employee would earn 4 hours of PTO for every 160 hours worked.

  • For the first 159 hours of work, the employee does not accrue anything. But once the 160th hour of work is completed, the employee accrues 4 hours of PTO.
  • The 160 hours are accumulated over periods.
  • The employee accrues in multiples of 4 hours.

Companies may also have a tiered structure, where loyal employees (with more tenure) will earn PTO at a higher rate.
For example,

  • Employees with <= 2 years of tenure at the company may receive an accrual rate of 1 hour of PTO for every 40 hours worked.
  • Employees with >2 years of tenure at the company may receive an accrual rate of 2 hours of PTO for every 40 hours worked.

You can have more than 2 tiers as well. This may lead to improved employee loyalty.

4. ANNUAL PTO ROLLOVER POLICY

As an employee continues to accrue PTO every period, the balance keeps growing, assuming there are no PTO/vacations taken. Employees are encouraged to take regular time off to maintain a healthy work-life balance.

Also, companies may consider remaining PTO balance as cash that needs to be paid to employee if employee leaves the company. So, very high balance could mean more cash out the door for the company. These are reasons why there is usually a rollover policy.

This policy setting determines how many hours of PTO the employee can carry over from one year to the next year.

Let’s look at three flavors of Rollover policy.

  1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
  2. Rollover Limit: We can set a limit on how many hours are carried over. This is the most common practice among companies.
  3. Unlimited Rollover: Here the employee does not lose any PTO and will carry over everything to next year. This is unusual for a company.

ROLLOVER TIMING

With rollover policies, there is another aspect. Some companies may apply rollover at calendar year change (1st Jan of every year for all employees) or on work anniversary dates (which vary for each employee).

5. MAXIMUM ALLOWED PTO BALANCE

The rollover limit only applies to the end of the year balance. Some companies can set an additional limit on maximum balance at any time during the year, to ensure that there is a limit to PTO accrual. This would mean the employees may have to take regular PTO throughout the year, instead of accumulating towards year end.

This is referred to as the Maximum Allowed PTO Balance.

ILLUSTRATION

Now that we defined the key factors, let’s take an example employee and illustrate how these components work and how to calculate PTO policy for an hourly employee.

EMPLOYEE DETAILS

Let’s assume an employee with the following details for this illustration.

  • Works 8 hrs every weekday
  • Hire date: 3 July 2019
  • Probationary Period: 90 Days
  • Rollover limit: 10 Hours on 1st Jan
  • Max PTO Balance: 30 Hours
  • PTO Taken: 1 hour on 25 Oct 2019, 7 May 2020 and 8 May 2020
  • Accrual Rate:
    o <= 9 Mths Tenure: 1 Hour PTO per 40 Hours Worked
    o >9 Mths Tenure: 2 Hours PTO per 40 Hours Worked

PROBATIONARY PERIOD

As the employee was hired on 3rd July 2019, the employee will not be eligible for PTO accrual until Sep 30th.

First day of eligibility will be Oct 1, 2019

Probationary Period
Probationary Period

The image above shows the number of hours worked by employee, that are used for calculating PTO accrual.

Though the employee may have worked during the probationary period, the work does not lead to PTO accrual. Only work done from Oct 1st will be used for PTO accrual calculations.

ACCRUAL PERIOD

Since our employee accrues PTO every week, you can see the accrual calendar below where accrual happens on Oct 4, 11, 18, 25…..

First accrual day is Oct 4th. 

Weekly Accrual Period in Calendar
Weekly Accrual Period in Calendar

The numbers above refer to how much PTO balance changed each period. We will get to how we calculated that shortly.

ACCRUAL RATE

We will begin with simpler calculations and layer in more complexity later.

WEEK 1

Let’s take the first accrual period ending Oct 4th.

There are 2 key inputs needed. Hours Worked and PTO Hours used.

  • Employee worked for 32 hours from Oct 1st (first eligible date) to Oct 4th (Friday).
  • Employee didn’t take any PTO.

For every period, we calculate 3 output.

  1. PTO Accrued in Period: How much PTO has employee earned in this period alone?
  2. Unaccrued Work Hours: How much hours did the employee work that have not converted to PTO yet?
  3. PTO Balance at end of period: How much is the PTO Balance at end of period, available for employee to use?

 

PTO Accrual Rate Calculation - Week 1
PTO Accrual Rate Calculation – Week 1

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (32+0)* Accrual Rate = 32 Hours *Accrual Rate

= 0 hours of PTO accrued + 32 work hours unaccrued.

PTO Accrued in Period is 0 since employee has not reached 40 work hours yet. However, the 32 hours should count towards the following week’s balance calculation. So, we store that in the ‘Unaccrued Work Hours’ column.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 0 + 0 – 0

= 0 hours

PTO Balance for the employee at the end of Oct 4th is 0 as the previous balance is 0 and no PTO was used during the period.

WEEK 2

Let’s move to the next week – ending on Oct 11th.

Inputs:

  • Employee worked for full 40 hours.
  • No PTO taken.
PTO Accrual Rate Calculation - Week 2
PTO Accrual Rate Calculation – Week 2

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (40+32)* Accrual Rate = 72 Hours *Accrual Rate

= 1 hour of PTO accrued + 32 work hours unaccrued.

PTO Accrued in Period is 1 since employee has reached 40 work hours cumulatively. The 32 unaccrued work hours from previous week get added to total hours worked.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 0 + 1 – 0

= 1 hour

WEEK 3

Now, third week comes along.

Inputs:

  • Employee only worked for 8 hours in that week.
  • No PTO Taken
PTO Accrual Rate Calculation - Week 3
PTO Accrual Rate Calculation – Week 3

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (8+32)* Accrual Rate = = 40 Hours *Accrual Rate

= 1 hour of PTO accrued + 0 work hours unaccrued.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 1 + 1 – 0

2 hours

WEEK 4

Now, on to the fourth week .

Inputs:

  • Employee takes 1 hour of PTO and works only 39 hours.

Though the employee does get paid for 1 hour of PTO, for our PTO accrual calculation that 1 hour of PTO will not be used. Essentially, an employee’s PTO time does not earn him more PTO accrual.

Accrual Rate Calculation - Week 4
Accrual Rate Calculation – Week 4

 

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (39+0)* Accrual Rate = 39 Hours *Accrual Rate

= 0 hours of PTO accrued + 39 work hours unaccrued.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 2 + 0 – 1

1 hour

WEEK 5

Now for the 5th week.

Inputs: Employee does overtime and works for 41 hours. No PTO Taken.

PTO Accrual Rate Calculation - Summary
PTO Accrual Rate Calculation – Summary

PTO Accrued in Period

= (Hours worked in this period + Any unaccrued Work Hours from Previous period) * Accrual Rate

= (41+39)* Accrual Rate

= 80 Hours *Accrual Rate

= 2 hours of PTO accrued + 0 work hours unaccrued.

PTO Balance

= Previous Period Balance + PTO Accrued in Period – PTO Used

= 1 + 2 – 0

3 hours

SUMMARY

In summary,

Employee worked 160 hours in 5 weeks, earned 4 hours of PTO (1 hr per 40 hours worked) and used 1 hour of PTO. The Balance as of Nov 1st is 3 hours of PTO.

Key Takeaways:

  •  PTO Accrual is based on cumulative work hours and not just hours worked in that period.
  •  Unaccrued work hours are tracked to be used for next week’s calculation
  • PTO used will be deducted from balance
  • PTO hours do not yield PTO accrual
  • Formulas:
    • PTO Accrued = (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate
    • PTO Balance = Previous Period Balance + PTO Accrued in Period – PTO Used

ROLLOVER POLICY

Rollover limit was 10 hours and it was applied on calendar year end. So, the first accrual date in Jan 2020 (Jan 3rd, 2020) will see the rollover policy implemented.

The formula we used for calculating balance in the previous section gets an upgrade only for rollover periods.

Assuming balance as of Dec 27th was 11 and 30 unaccrued work hours.

Rollover Policy - Calculation
Rollover Policy – Calculation

Calculating the balance for Jan 3.

PTO Accrued

= (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate

= 40 * Accrual Rate

= 1 Hour of PTO Accrued + 0 unaccrued work hours

PTO Balance

= min(Rollover limit, Previous Day Balance) + PTO Accrued in Period – PTO Used
= Min (10,11) + 1 – 0
= 10 + 1 – 0

= 11 Hours

Only 10 hours get carried over from Dec 27th Balance due to the rollover limit of 10 hours. If the rollover policy was 0 rollover, then all 11 hours will be lost. In case of unlimited rollover policy, all 11 hours will be carried over.

So, the new balance as of Jan 3rd is 11 hours.

From Jan 10th again, the employee will carry over the balance over to the next week as usual without any rollover limit. The limit is applied only on rollover windows once a year.

TENURE BASED ACCRUAL RATE

As the employee continues to work at the company, he/she reaches the 9 month tenure period where the accrual rate increases to 2 hours of PTO for every 40 hours worked.

Employee completes 9 months on Apr 2nd and on Oct 3rd will be eligible for the new rate.

Tenure Based Accrual Rate calculation
Tenure Based Accrual Rate calculation

As shown above, the employee earned 1 hour per 40 hours worked on Mar 27.

From Apr 3rd, the employee earns 2 hours of PTO for 40 hours worked.

Note: New rate applies to all hours worked during transition week (Mar 28th to Apr 3) regardless of the exact date when the rate transition happened.

MAX PTO BALANCE

Now, for the last setting: MAX PTO BALANCE of 30 hours.

The employee continues to earn more balance over time and on Apr 24th the balance reaches 30 hours.

MAX PTO Balance calculation
MAX PTO Balance calculation

The employee worked 40 hours as usual during the next week.

PTO Accrued = (Hours worked in this period + Any unaccrued Work Hours from previous) * Accrual Rate

= 40 * Accrual Rate = 1 Hour of PTO Accrued + 0 unaccrued work hours

PTO Balance = min (Max PTO Balance , (Previous Day Balance + PTO Accrued in Period – PTO Used))

= Min (30,30+1-0) = Min (30,31) = 30 Hours

Note: Though the PTO accrued should be 1 hour, it is represented as 0 in the image above, in order to avoid confusion. Employee does not actually get the benefit of that 1 hour as it will exceed the Max PTO Balance Policy limit.

When the employee uses 2 hours of PTO during the week ending May 8th, the PTO balance becomes 28 hours.

In the following week, the employee will restart accruing new PTO as 28 is less than 30 (max limit).

CONCLUSION

I hope the above example illustrates how PTO balance can be calculated for hourly employees. The 5 key policy factors we discussed can become complex to implement due to various flavors possible and the calculations are not very easy to build. I spent quite a bit of time writing the formulas to accommodate the various scenarios.

I am happy to inform that you don’t have to spend your time building these formulas. I will be publishing a free Excel Template that comes pre-built with the formulas. You can implement your PTO policy and start calculating balance for an hourly employee within 5 minutes.

Please post your feedback in the comments below. Thank you.

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