How to Create a Recruitment Dashboard in Excel with ChatGPT

Building a recruitment dashboard in Excel is one of the best ways to get a clear, data-driven view of your entire hiring process. This guide will walk you through creating one from scratch, using ChatGPT to handle the heavy lifting of writing formulas and suggesting the right visualizations, even if you’re not an Excel wizard.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, Get Your Data Organized

Dashboards are only as good as the data they use, so your first step is to create a clean, organized data source. A messy tracker with inconsistent naming or merged cells will trip you up before you even begin.

Create a dedicated Excel sheet named "Recruitment Data" or "Tracker." This sheet will act as the engine for your entire dashboard. The key is to structure it like a database: one candidate per row, with each column representing a specific piece of information.

Here’s a simple structure you can use:

• Candidate ID: A unique ID for each person.
• Candidate Name: Full name.
• Position: The role they applied for.
• Source: How they found you (e.g., LinkedIn, Referral, Indeed, etc.).
• Application Date: The day they applied.
• Stage: Their current status in the funnel (e.g., Applied, Screened, Interviewing, Offer, Hired, Rejected).
• Interview Date: The date of their first interview.
• Offer Date: If applicable, the date an offer was extended.
• Hire Date: If applicable, the date they were hired.
• Rejection Date: If applicable, the date they were rejected.

Pro-Tip: Use an Excel Table

Once you have your columns set up, convert your data range into an official Excel Table. Don't skip this step! It makes a massive difference.

Simply click anywhere inside your data range, go to the Insert tab, and click Table. Excel will automatically apply formatting and, more importantly, it will automatically expand to include new rows as you add candidates. This means your dashboard will update dynamically without you having to constantly adjust formula ranges.

For this tutorial, let’s assume you’ve named your Excel Table RecruitmentData.

Using ChatGPT to Build the Dashboard Engine

Now for the fun part. Instead of wrangling with complex COUNTIFS or AVERAGEIFS formulas yourself, you can ask ChatGPT to write them for you. Create a new sheet named "Dashboard" – this is where all your charts and key metrics will live.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Calculate Your Key Recruitment Metrics

Your dashboard should always start with a high-level summary of your most important KPIs (Key Performance Indicators). You’ll display these as "scorecards" at the top for a quick at-a-glance view.

Let's use ChatGPT to generate the formulas for a few core metrics. Just be clear and tell ChatGPT what your Excel Table is named (RecruitmentData) and what the relevant column headers are.

Metric 1: Total Applications

You need a simple count of all the applications received. Here's a prompt you could use:

I have an Excel table named `RecruitmentData`. I want to count the total number of applications. The `[Candidate ID]` column contains a unique ID for each applicant. Give me the Excel formula to count all the applications.

ChatGPT's likely output:

=COUNT(RecruitmentData[Candidate ID])

Metric 2: Number of Candidates Interviewing

Here you need to count how many candidates are currently in the "Interviewing" stage.

In my Excel table named `RecruitmentData`, I have a column called `[Stage]`. Write an Excel formula to count the number of candidates whose stage is "Interviewing".

ChatGPT's likely output:

=COUNTIF(RecruitmentData[Stage], "Interviewing")

Metric 3: Offer Acceptance Rate

This metric shows the percentage of candidates who accept your offers. You calculate it by dividing the number of hired candidates by the total number of candidates who received an offer (Hired + those who received an offer but were rejected or stayed in the "Offer" stage).

In my Excel table `RecruitmentData`, my `[Stage]` column tracks candidate status. Its values include "Hired," "Offer," and "Rejected."

Write an Excel formula to calculate the Offer Acceptance Rate. The formula should be:

(Total "Hired") / (Total "Hired" + Total number of candidates in the "Offer" stage)

ChatGPT's likely output:

=COUNTIF(RecruitmentData[Stage], "Hired") / (COUNTIF(RecruitmentData[Stage], "Hired") + COUNTIF(RecruitmentData[Stage], "Offer"))

Remember to format this cell as a percentage in Excel!

Metric 4: Average Time to Fill

Time to Fill tracks how many days it takes to fill a role, from initial application to hire date. This is one of the most powerful efficiency metrics.

I have an Excel table named `RecruitmentData`. It has two date columns: `[Application Date]` and `[Hire Date]`. Give me an Excel formula to calculate the average difference in days between these two dates, but *only* for rows where the `[Stage]` is "Hired". The result should be a whole number.

ChatGPT's likely output:

=AVERAGEIFS(RecruitmentData[Hire Date], RecruitmentData[Stage], "Hired") - AVERAGEIFS(RecruitmentData[Application Date], RecruitmentData[Stage], "Hired")

This formula is a bit more complex, but ChatGPT handles it perfectly, saving you time and guesswork.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Get Advice on Visualizations

While ChatGPT can't click the buttons in Excel to create charts for you, it is an excellent advisor on which charts to use and how to prepare your data for them. The foundation of any good chart in Excel is a small summary table that aggregates your main data.

Visualization 1: The Hiring Funnel

A funnel chart or bar chart is perfect for visualizing your entire recruitment pipeline. First, you need a summary table pulling counts for each stage. Let’s create this in our “Dashboard” sheet. Then, ask ChatGPT for guidance.

I'm creating a recruitment dashboard in Excel and want to visualize my hiring process. My core stages are: Applied, Screened, Interviewing, Offer, and Hired. What is the best chart type to use, and how should I set up the summary data table to create it?

ChatGPT will likely recommend a Bar Chart for clarity or a Funnel Chart (if your Excel version supports it). It will also tell you to arrange your data in a simple two-column table:

Your Summary Table Setup:

• Column 1: Stage (Applied, Screened, Interviewing, Offer, Hired)
• Column 2: Count (Use the COUNTIF formulas we practiced above for each stage)

Once you have this small table, select it and choose Insert > Recommended Charts, and pick the Bar Chart option.

Visualization 2: Candidates by Source

You need to know where your best candidates are coming from. A Horizontal Bar Chart is ideal for this because it's easy to read and compare sources.

I want to create a chart in my Excel dashboard that shows the number of hired candidates from each application source (e.g., LinkedIn, Referral, Indeed). My `RecruitmentData` table has a `[Source]` column and a `[Stage]` column. How can I create a summary table for this using formulas? What chart type should I use?

ChatGPT will tell you to use a Horizontal Bar Chart. For the data setup, you need to create a summary table using a COUNTIFS formula. This formula needs to count candidates who were both "Hired" and came from a specific source.

Your Summary Table Setup:

• Column 1: Source (list your sources: LinkedIn, Referral, etc.)
• Column 2: Hired Count

To get the Hired Count for "LinkedIn," the formula would be:

=COUNTIFS(RecruitmentData[Stage], "Hired", RecruitmentData[Source], "LinkedIn")

Repeat this for each source, then insert a Horizontal Bar Chart based on this summary table.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Putting It All Together on Your Dashboard

Now you have all the pieces: high-level metrics and data-driven charts. It's time to assemble your dashboard.

1. Layout: On your "Dashboard" sheet, dedicate the top section to your "scorecard" metrics (Total Applications, Offer Rate, Time to Fill, etc.). Make the text large and easy to read.
2. Add Charts: Below the scorecards, arrange your charts. Place the Hiring Funnel chart and the Source of Hire chart side-by-side.
3. Make it Interactive with Slicers: Slicers are user-friendly filters that allow you or your team to drill down into the data with a single click. Click inside one of your dashboard charts, then go to Chart Analyze > Insert Slicer. A box will appear with all your Table columns. Check the box for "Position" and "Source."

Now, you'll have interactive buttons on your dashboard. Clicking on "Senior Developer" in the Position slicer will instantly filter all your charts and metrics to show data only for that role. This is what transforms a static report into a powerful analytical tool.

Final Thoughts

By pairing organized Excel data with ChatGPT's formula and visualization expertise, you can quickly build a powerful and interactive recruitment dashboard without spending days tangled in tutorials. The key is to start with clean data in an Excel Table and use simple, direct prompts to guide ChatGPT.

While this method is a huge leap forward from manual reporting, we know that constantly exporting and cleaning CSVs for analysis can still be a drag. That’s why we built Graphed to remove this friction entirely. We connect directly to your data sources - like your applicant tracking system or HR platforms - and allow you to build real-time, interactive dashboards just by describing what you want to see in plain English. No more formulas, no more manual updates, just straight to insights.