How to Create a Recruitment Tracker in Excel

Tired of tracking job applicants through a messy inbox, scattered sticky notes, and a dozen different folders? Building a dedicated recruitment tracker in Excel can bring order to the chaos, giving you a central source of truth for your entire hiring process. This guide will walk you through creating a simple yet powerful applicant tracking system in Excel, from laying the foundation to creating a dynamic, visual dashboard.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

First, Why Even Use Excel for Recruitment?

While there are plenty of dedicated Applicant Tracking Systems (ATS) out there, a well-built Excel tracker has some serious advantages, especially for small to medium-sized businesses or teams just starting to formalize their hiring.

• It’s Cost-Effective: You (and your team) already have it. There's no need to purchase new software or pay for monthly subscriptions.
• It's Fully Customizable: You can create a tracker that perfectly matches your company's unique hiring workflow. You decide what information to track and how to display it, without being locked into the rigid structure of a third-party app.
• It's Simple to Share: With tools like OneDrive or Google Sheets, your Excel tracker can be a collaborative document that your whole hiring team can access and update in real-time.
• It Centralizes Everything: No more digging through emails to find a candidate's status or last contact date. Everything you need is in one organized place.

Step 1: Setting Up the Basic Structure of Your Tracker

The foundation of any good tracker is a well-organized table. Open a new Excel workbook, name the first sheet "Candidate Tracker," and create the following columns in the first row. These headers represent the core pieces of information you'll want to track for every applicant.

Essential Columns for Your Tracker:

• Candidate ID: A unique identifier (e.g., 001, 002) for each applicant. This prevents confusion if you have multiple candidates with the same name.
• Full Name: The applicant's full name.
• Email Address: Their primary email.
• Phone Number: Their primary phone number.
• Position Applied For: The specific role they are applying for. This is crucial if you're hiring for multiple positions at once.
• Source: How did they find you? (e.g., LinkedIn, Referral, Indeed, Company Website). This is incredibly valuable data for optimizing your recruiting efforts.
• Application Date: The date their application was received.
• Current Stage: Their current place in your hiring pipeline (e.g., Application Received, Phone Screen, Technical Interview, Final Interview). This is the most frequently updated column.
• Next Step: The next action item for this candidate (e.g., "Schedule technical interview," "Send rejection email").
• Next Step Date: The deadline for that next action.
• Hiring Manager: The person leading the hiring process for that role.
• Status: The overall status of the application (e.g., Active, Hired, Offer Rejected, Withdrew, Rejected).
• Notes: A catch-all column for miscellaneous notes, feedback links, or important context.

Once you’ve created these headers, select them, make them bold, and consider applying a fill color to make them stand out. Then, select the entire data range (including the headers) and format it as a table by going to Home > Format as Table. This not only looks better but also makes filtering and sorting much easier.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Making the Tracker Dynamic and User-Friendly

A simple list of candidates is good, but a great tracker uses Excel’s features to enforce consistency and provide at-a-glance insights.

Use Drop-Down Menus for Consistency

Mistyping "Phone screen" instead of "Phone Screen" can throw off your reporting. Data validation is the solution. By creating drop-down menus for columns like "Current Stage" and "Status," you ensure everyone on your team uses the exact same terminology.

Here’s how to set it up for your "Current Stage" column:

1. On a new sheet (let’s call it "Lists"), type out your hiring stages, with each stage in a separate cell. For example:
2. Go back to your "Candidate Tracker" sheet and select the entire "Current Stage" column (excluding the header).
3. Navigate to the Data tab in the Excel ribbon and click on Data Validation.
4. In the settings tab, under "Allow," select "List."
5. In the "Source" box, click the small icon with the red arrow, navigate to your "Lists" sheet, and select the range of cells containing your hiring stages. Press Enter.
6. Click "OK."

Now, when you click on any cell in the "Current Stage" column, a drop-down arrow will appear, allowing you to select from your pre-defined list. Repeat this process for the "Status" and "Source" columns to keep your data clean and standardized.

Add Conditional Formatting for Visual Cues

Instead of manually reading every line, you can make important statuses pop with color. For instance, you could make hired candidates green, rejected candidates red, and active candidates yellow.

Here's how to format your "Status" column:

1. Select the entire "Status" column (excluding the header).
2. Go to the Home tab and click Conditional Formatting > Highlight Cells Rules > Text that Contains...
3. In the dialog box, type "Hired" and choose "Green Fill with Dark Green Text" from the drop-down menu. Click "OK."
4. Repeat the process for "Rejected" (with light red fill) and "Active" (with yellow fill).

Now, your tracker will automatically color-code candidates as you update their status, giving you a quick visual overview of your pipeline's health.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3: Creating a Recruitment Dashboard

The raw data is useful, but the real power comes from summarizing and visualizing it. A dashboard gives you a high-level view of your entire hiring funnel. We'll build a simple one using PivotTables and PivotCharts.

First, create a brand new sheet in your workbook and name it "Dashboard." This is where your reports will live.

Summarize Your Data with a PivotTable

A PivotTable is the fastest way to summarize your data. Let’s create a table that shows a count of candidates in each stage of the hiring process.

1. Go back to your "Candidate Tracker" sheet and click anywhere inside your data table.
2. Go to the Insert tab and click PivotTable.
3. Excel will automatically select your table range. Choose "Existing Worksheet" as the location for the report, and then select cell A1 on your "Dashboard" sheet. Click "OK."
4. The PivotTable Fields pane will appear. Drag the "Current Stage" field into the "Rows" area.
5. Drag the "Full Name" field into the "Values" area. It should default to "Count of Full Name," which is exactly what we want.

Instantly, you’ll have a summary table on your dashboard showing how many candidates are in each stage of your pipeline.

Visualize Your Funnel with a PivotChart

Now let's turn that summary table into a chart.

1. Click anywhere inside your newly created PivotTable.
2. Go to the PivotTable Analyze tab and click PivotChart.
3. A bar chart is a great choice here. Select a Clustered Bar chart and click "OK."

Excel will generate a chart linked directly to your PivotTable. You can move and resize it on your dashboard as you see fit. You can right-click the grey field buttons (like "Current Stage") on the chart and select "Hide All Field Buttons on Chart" for a cleaner look.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Add Slicers for Interactive Filtering

Slicers are interactive buttons that make filtering your dashboard incredibly easy.

1. Click on your PivotTable one more time.
2. On the PivotTable Analyze tab, click Insert Slicer.
3. Check the boxes for "Position Applied For" and "Hiring Manager." Click "OK."

Two slicer boxes will appear. You can arrange them on your dashboard, and now you and your team can filter the entire dashboard by simply clicking on a job title or name. Both the PivotTable and PivotChart will update instantly.

Final Thoughts

With these steps, you’ve moved beyond a simple spreadsheet and created a dynamic, interactive recruitment management tool. This tracker provides a centralized system for managing applicants, ensures data consistency with features like drop-down menus, and offers valuable insights through a visual dashboard. It's a scalable solution that can bring clarity and efficiency to your hiring process.

We know that manually updating even the best spreadsheet can still be time-consuming, especially when your hiring data is spread across different platforms. At Graphed , we automate the connection of your data sources - like a Google Sheet holding your recruitment tracker - with other business systems. Instead of building pivot tables, you can just ask questions in plain English like, "show me a breakdown of applicant sources for the marketing manager role," and get an instant, real-time chart. This allows your team to get valuable answers without ever having to touch a spreadsheet formula again.