Recruitment Funnel & Recruiter Performance Dashboard

1. What is a Recruitment Dashboard?

Before we start building, it's useful to understand the purpose of a recruitment dashboard. It's a command center for hiring, providing a real-time, bird's-eye view of your most important hiring metrics.

As the article mentions, a good dashboard visualizes key metrics. Today, we'll focus on two core components:

1. Recruiter Performance: How many active candidates is each recruiter handling?
2. Recruitment Funnel: How efficiently are we moving candidates from application to hire?

2. Setting Up the Dashboard Canvas

Best practice in Excel is to keep your raw data, your calculations (like PivotTables), and your final dashboard on separate worksheets. This keeps your workbook organized and easy to maintain.

1. Open your MasterTracker Excel file from the previous lesson.
2. Create a new worksheet and name it Dashboard. This will be your presentation layer.
3. To give it a clean, professional look, go to the View tab and uncheck Gridlines. This removes the cell borders and makes the sheet look like a blank canvas.
4. Optionally, you can add a title at the top, for instance in cell A1: "Recruitment Pipeline Dashboard".

3. Visualizing Recruiter Performance with a PivotChart

Let's start by visualizing the PivotTable you created in the last lesson. A PivotChart is a chart that is linked directly to a PivotTable, meaning it will update automatically when the PivotTable's data changes or is filtered.

The most efficient way to learn this is by watching a demonstration.

Your Turn: Create the Recruiter Performance Chart

Now, apply those steps to your own data.

1. Go to the worksheet containing the PivotTable you built in the last lesson (the one with Pipeline Stage in Rows and Recruiter in Columns).
2. Click anywhere inside your PivotTable.
3. Go to the PivotTable Analyze tab on the ribbon and click PivotChart.
4. In the Insert Chart dialog, choose a Stacked Column chart and click OK. A stacked column chart is great here because it shows each recruiter's total candidate load while also showing the breakdown by stage.
5. Clean up the chart:
   • Click on the chart title and change it to something meaningful, like "Active Candidates by Recruiter".
   • Right-click on one of the grey "field buttons" on the chart (e.g., the one that says "Pipeline Stage") and select Hide All Field Buttons on Chart. This makes it look much cleaner.
6. Move the chart:
   • Select the chart.
   • Press Ctrl+X to cut it.
   • Go to your Dashboard sheet.
   • Press Ctrl+V to paste it.
     .
7. Position and resize the chart to fit nicely on the left side of your dashboard.

You now have your first dashboard component!

4. Building the Recruitment Funnel Chart

A funnel chart is a classic visualization for tracking recruitment. It shows how many candidates progress through each stage, making it easy to spot bottlenecks. While Excel has a built-in Funnel chart type, it's not easily connected to a PivotTable. We will use a more powerful and flexible technique: a customized stacked bar chart.

This involves creating a small helper table to calculate an "offset" that centers the bars, creating the funnel illusion.

Your Turn: Create the Funnel Chart

Let's break this down into manageable steps.

Step 1: Create a New PivotTable for the Funnel

1. Go back to your MasterTracker data sheet and click anywhere in the table.
2. Insert a new PivotTable on a New Worksheet. Let's rename this new sheet to Calcs. This sheet will hold all our background calculations.
3. Drag Current Stage into the Rows area.
4. Drag Candidate ID into the Values area and set it to Count.
5. Sort the stages in the correct funnel order (e.g., Screening, Interview, Offer). You can do this by manually dragging the row labels in the PivotTable.

Step 2: Build the Helper Table

On the same Calcs sheet, to the right of your new PivotTable, create a small table with four columns: Stage, Cumulative Count, Offsetter, and Actual Count.

1. Stage: Manually type or copy the stage names from your PivotTable, in the correct funnel order.
2. Actual Count: Use simple cell references to pull the counts from your PivotTable. For example, if your first stage count is in cell B4, the formula is just =B4.
3. Cumulative Count: This is the count of candidates who reached the current stage or any subsequent stage.
   • In the cell for your last stage (e.g., Offer), the formula will be the same as its actual count.
   • For the stage above it (Interview), the formula will be: (Interview's Actual Count) + (Offer's Cumulative Count).
   • Continue this pattern all the way to the top. The top stage (Screening) should have a cumulative count equal to the total number of candidates.
4. Offsetter: This calculates the size of the invisible bar needed to center the real bar. The formula for each row is: (Cumulative Count of the TOP stage - This stage's Cumulative Count) / 2. Make sure you use an absolute reference ($) for the top stage's cumulative count.

Step 3: Create and Format the Chart

1. Select the Stage, Cumulative Count, and Offsetter columns in your helper table.
2. Go to Insert > Chart and choose a 2D Stacked Bar chart.
3. Right-click the vertical axis (the stage names) and select Format Axis. In the Axis Options, check the box for Categories in reverse order. This puts the 'Screening' stage at the top.
4. Click on the bars representing the Offsetter series. In the Format tab, set the Shape Fill to No Fill. They are now invisible spacers.
5. Add Data Labels to the visible bars (the Cumulative Count series). Format them to be centered.
6. Clean up the chart: Give it a title ("Recruitment Funnel"), remove the legend, and remove the horizontal axis at the bottom.
7. Cut (Ctrl+X) the chart and paste (Ctrl+V) it onto your Dashboard sheet. Position it next to your first chart.

5. Making the Dashboard Interactive with Slicers

Now for the final and most powerful step: making the dashboard interactive. Slicers are user-friendly buttons that filter your PivotTables (and therefore your PivotCharts).

Your Turn: Add and Connect Slicers

1. Click on your Recruiter Performance chart on the dashboard.
2. Go to the PivotChart Analyze tab and click Insert Slicer.
3. In the dialog box, choose the fields you want to filter by. Let's pick Recruiter and Job Title. Click OK.
4. Two slicer boxes will appear. Position them neatly on your dashboard, perhaps on the right side or along the top.
5. This is the most important step. By default, these slicers only control the PivotTable they were created from. You must connect them to your funnel chart's PivotTable as well.
   • Right-click on the Recruiter slicer.
   • Select Report Connections....
   • In the dialog box, you'll see a list of all PivotTables in your workbook. Check the box next to the PivotTable you created for the funnel chart (it might be named PivotTable2). Click OK.
   • Repeat this process for the Job Title slicer.

Now, test it! Click on a recruiter's name in the slicer. Both the Recruiter Performance chart and the Funnel chart should update to show data for only that recruiter. You have just built a fully interactive dashboard.

Conclusion

Fantastic work today! You have successfully moved from a static summary table to a dynamic, visual, and interactive dashboard. This is a huge step in your journey to becoming an MIS Executive, as it demonstrates your ability to not just process data, but to present it in a way that is insightful and easy for stakeholders to understand.

Here are your key takeaways:

• A dashboard is a single-sheet view that visualizes key metrics using charts and interactive filters.
• PivotCharts are a quick way to create dynamic charts directly from your PivotTable summaries.
• Advanced visualizations like a recruitment funnel can be created in Excel using clever techniques like a stacked bar chart with an invisible "offsetter" series.
• Slicers are user-friendly buttons that filter dashboard data.
• The Report Connections feature is crucial for linking a single slicer to multiple PivotTables, ensuring your entire dashboard updates in sync.

In our final lesson, you will learn how to put all these pieces together to assemble a professional weekly MIS report with charts, summary tables, and analytical commentary. The dashboard you built today will be the central component of that final deliverable.