**Bottom Line: **Learn to create an interactive dashboard for employee attendance using Excel 2010 or beyond.

**Skill Level:** Intermediate

## Download the Excel File

The Excel file I used in this post can be downloaded here:

**Update:** I opened the file in Excel 2010 and did not like how the transparent shapes look when pasted as a picture. The shape looks like a gray box instead of just adding white transparency.

So I created another version that uses two different icons on the Shapes sheet. I used the Color drop-down on the Picture Format tab to change the color saturation of the “bad” shape to give it a grayed-out look.

Here is the file that contains that solution.

## Office 365 Alternative

As part of an **Excel Hash competition**, I submitted a winning entry featuring an attendance dashboard. This dashboard report was a parody of something Dwight Schrute would use in the TV show *The Office*. You can see the entry explained here: Excel Hash: Attendance Report with Storm Clouds & Fireworks.

That solution uses new features that only available in the latest versions of Excel:

- XOR – Excel 2013
- Icons – Excel 2016
- Dynamic Arrays Functions (FILTER, UNIQUE, SORTBY) – Office 365
- XLOOKUP – Office 365

However, a lot of readers aren't running that newest version of Excel, so I've created this post as **an alternative that can be used with older versions of Excel**.

I've created **six video tutorials** that walk you through the creation of that dashboard. Even if you don't have Office 365, I recommend viewing them as they will** help to give you context** and detail. Then come back here and see how I adapt the process. We can use other formulas and workarounds to create the same report with **Excel 2010 or beyond**. It should work in 2007 as well but I have not tested.

The first video in that series can be found here: How to Use the XOR Function

## Setting Up the Source Data

In the original series of posts, I use the **XOR function** to determine if the attendance data entries were “In” or “Out” entries. We could figure that out depending on whether there was an even or odd amount of entries.

We can accomplish the same thing using the **ISEVEN **and** COUNTIF** functions.

The first thing we need to do is sort the data **from newest to oldest** instead of the other way around. With XLOOKUP, we had the ability to search in reverse order. But since we are using an older version of Excel, we will use **VLOOKUP** instead. VLOOKUP can't search in reverse order. Here is a post about VLOOKUP if you want to know more about it: VLOOKUP Explained.

Using VLOOKUP, we can **find the previous date** in order to calculate the duration.

To calculate the duration, we subtract the prior “In” timestamp from each “Out” timestamp and multiply by 24 to get the number of hours the employee was at work.

## COUNTIF for Distinct Count

We also need to calculate the average duration for all employees in the selected department. We are going to use a pivot table for this, which is explained below.

The pivot table needs to calculate the distinct count of number of employees in the selected department, so the average can be calculated.

Average Duration = Total Hours / Count of Employees

Excel 2010 and earlier does not have the ability to calculate distinct count directly in the pivot table. So we can use a formula in the source data instead.

**=1/COUNTIF([Employee],[@Employee])**

This returns a decimal number for each row. When the row is summed it will return a 1 to the pivot table for each employee in the rows area, based on the filter criteria for the selected department.

I've got another blog post that takes a closer look at **the COUNTIF function**: How to Use the COUNTIF Function.

## Summarizing with Pivot Tables

To create the same interaction on the dashboard that the original worksheet features, we can use **pivot tables** and a **slicer **instead of Dynamic Array Functions. If you're not familiar with pivot tables, you'll want to be. They are really useful. You can learn more about **how to use pivot tables** in this post: Introduction to Pivot Tables and Dashboards.

Starting on the Data sheet, you can go to the** Insert** tab and choose **Pivot Table**, inserting it on a new sheet. On the **Pivot Tables pane**, just move **Employee** to the **Rows** area. Then move **Duration** to the **Values** area and **Department** to the **Filters** area.

This creates a table that can be **filtered down by department**. As you select the department you want, it automatically changes to list the** employees in that department **and the total **duration of their work hours**. This is how the Accounting Department would look, for example:

Another metric on the dashboard that we need to calculate is **average number of hours per department**. To accomplish this, we can duplicate the pivot table we've already created and swap out **Duration** for **Distinct Employee Count**.

This will give us a pivot table that looks like this:

## Adding a Slicer

Now that we have these two pivot tables, we want to tie them together so that if we filter by department on one of them, the filter **automatically applies** on the other. We can do this by **adding a slicer** to the worksheet.

To add a slicer, start from anywhere in either of the pivot tables and go to the **Pivot Table Analyze tab**. Choose **Insert Slicer**, and then check the box for **Department**. Click **OK**.

That will create your slicer. If you right-click on your slicer, you can then choose **Report Connections** on the menu that appears. This will allow you to **connect the pivot tables** through the slicer. Just **make sure both check boxes are selected for the pivot tables** and hit **OK**.

To calculate the average number of hours per department, we just divide the Grand Total for the **hours table** by the Grand Total for the **employee count table**.

When we do this, Excel automatically populates the formula with the **GETPIVOTDATA function** because it is pulling from the pivot tables. The formula looks like this:

This returns a dynamic average calculation that changes as the pivot table is filtered.

## Icons with Conditional Formatting

To create icons that appear to be semi-transparent or have a grayed-out look, I've simply set up a **Shapes worksheet**. This sheet has a couple of cells formatted with **white fill** and a **15% transparency**. (These settings can be found on the **Format Shapes Pane**, accessed through the right-click menu or by typing `Ctrl` + `1`).

These formatted cells will be overlaid on icons of your choosing. I found my icons at iconfinder.com.

Essentially, I've set up a formula that **indexes** a different **named range **depending on whether the department's hours are more than or less than the goal. (Click here to learn more about the INDEX function.) Those named ranges will display a shape that either has **no formatting**, or the **slightly transparent formatting **that I mentioned above.

This overlaid transparent shape makes the icon obscure but still visible, so it gives a grayed-out appearance.

See my note and additional file in the Downloads section at the top of the page about using two different colored icons instead of the transparent shape for Excel 2010.

There are also **text boxes** under the icons that are used to display the numbers. These are linked to the corresponding cells with a formula that **returns the average** depending on whether or not it is **less than** or **more than** the goal amount.

## Conclusion

While I love the newest features and functions that the latest version of Excel bring, it's great to see how we can solve this problem with some of the older tools in our Excel toolbox. There are always **so many ways **to solve the same problem in Excel, so if you have any other ideas or suggestions, I'd love to hear them. Just leave a comment below.

Jon, I have seen the first 3 parts, are the next 3 available?

Hi Tom,

The next 3 parts will be available in the coming weeks. Stay tuned… Thanks! 🙂

Hi Jon, Could you pls. help me to solve one problem I’m facing in Pivot. When any changes in main data sheet then, how changes will be done automatically in pivot sheet without any refresh.

Hi Mr. Jon,

I would like to know the VBA code for setting the width of every 4th column up to some extent say “DD:DD”. Could you please help me?

Thank You.

Saji Cherian

Seems the Accounting employees didn’t use your advice on Excel, they’re working more then 35 hours a week!

Impressive solution, many features involved.

Haha! They are very punctual. 🙂

Thank you, Jean-Sebastien! I appreciate the nice feedback.

hi jon,

congratulations on winning the excel hash competition!

i have been eagerly following your videos, and downloaded the attendence report file for older versions (work with a stand-alone version 2016).

on the “Data” sheet of this file, i noticed that there are from row 516 through 528, #N/A values, and row 529 (dwight schulte) is shown correctly – although looking at the formula i see that the row has a relative value, not absolute like all the previous rows, and i am not sure of the reason for this, but would like to know 🙂

I’ve the same comment as Colin Huntley. Unfortunately, in this box, I’m not able to display a screenshot of my Spreadsheet. Your data span from the 4th to the 8th. However, the earlier date of 4th has an IN but not OUT time resulting in #N/A errors which threw the Pivot table into a corresponding #N/A error. I suspect that something is wrong with your Data. Plse advise so that all of us can continue with your step-by-step narrative. Thank you.

Sir How to calculate the attendance in the excel sheet.

Emp Code EMP Name 01-Aug 02-Aug 03-Aug 04-Aug 05-Aug 06-Aug 07-Aug 08-Aug 09-Aug 10-Aug 11-Aug 12-Aug 13-Aug 14-Aug Total Days

2197 AADARSH KUMAR A A A A A A A A A A A A A A

3012 ANWAR QAZI A A A A A A A A A A A A A A

3027 BABLU MAHATO A A A A A A A A A A A A A A

3030 CHANDAN KUMAR A A A A A A A A A A A A A A

2435 Kartikmohanti A “08:25:32

00:00” “08:06:30

00:00” A “08:27:40

00:00” A A A A “08:00:52

00:00” A A A “08:23:53

00:00”