How to Create a Recruitment Dashboard in Google Sheets with ChatGPT

Tracking your recruitment efforts shouldn't feel like navigating a maze of spreadsheets. If you want to understand your hiring pipeline without expensive software, you can build a powerful recruitment dashboard right in Google Sheets. This article will show you exactly how to structure your data and use ChatGPT to write all the formulas you need to visualize your key hiring metrics.

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

Why Google Sheets for a Recruitment Dashboard?

While dedicated Applicant Tracking Systems (ATS) are powerful, they often come with a hefty price tag and a steep learning curve. For many teams, especially smaller businesses or those just starting to formalize their hiring process, Google Sheets is the perfect solution. It's free, easily accessible from any device, and built for collaboration.

The main challenge? Turning a static sheet of names and dates into a dynamic dashboard full of insights. Writing complex formulas to calculate metrics like "time to hire" or to visualize your candidate pipeline can be intimidating. That’s where ChatGPT comes in. You can describe what you want to measure in plain English, and it will generate the exact Google Sheets formulas for you, acting as your personal data analyst.

First, Plan Your Dashboard: What Metrics Should You Track?

Before you open a single spreadsheet, you need to decide what you want to measure. A good dashboard tells a story about your hiring process, highlighting successes and bottlenecks. Start by focusing on a few essential metrics that give you a high-level view of your recruitment health.

Here are some of the most common and valuable recruitment KPIs:

• Time to Fill: The number of days from when a job is first posted to when a candidate accepts the offer. This helps you understand the efficiency of your hiring process. A long "time to fill" might mean you need to speed up your interview scheduling or decision-making.
• Cost per Hire: The total cost of hiring a new employee (including advertising fees, recruiter salaries, etc.) divided by the number of hires. This metric is crucial for managing your recruitment budget effectively. (For simplicity in this tutorial, we will focus on Time to Fill and other non-financial metrics.)
• Source of Hire: Where your best candidates are coming from (e.g., LinkedIn, employee referrals, job boards, social media). Tracking this tells you which channels are delivering the best ROI so you can double down on what works.
• Candidate Pipeline Stages: The number of candidates currently at each stage of your hiring process (e.g., Applied, Phone Screen, First Interview, Final Interview, Offer Extended). This visualizes your pipeline’s health and helps you spot where candidates are dropping off.
• Offer Acceptance Rate: The percentage of candidates who accept a formal job offer. A low rate could indicate that your compensation packages aren't competitive or there’s a disconnect in the final stages of the interview process.

For this walkthrough, we will build a dashboard that tracks Total Applicants, Time to Fill, Source of Hire, and the Candidate Pipeline.

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: Set Up Your Raw Data in Google Sheets

The foundation of any good dashboard is clean, well-structured data. All your tracking and calculations will break if this part isn’t right. Create a new Google Sheet and name the first tab "Raw Data" or "Applications."

Create a header row with the following columns. It’s important to keep these column names simple and consistent.

• A: Candidate Name
• B: Role Applied For
• C: Application Date
• D: Source (e.g., LinkedIn, Referral, Indeed)
• E: Stage (A dropdown menu is great here!)
• F: Hired Date
• G: Rejected Date
• H: Notes

Pro-Tip: Use Dropdown Menus for Consistent Data

To keep your data tidy, use data validation for the "Source" and "Stage" columns. This creates a dropdown menu and prevents typos that can throw off your formulas.

1. Select the entire "Stage" column (Column E).
2. Go to Data > Data validation.
3. For "Criteria," choose "List of items."
4. Enter your recruiting stages, separated by commas: Applied, Phone Screen, Interview, Offer, Hired, Rejected.
5. Click "Save." Now, you'll have a neat dropdown for every cell in that column.

Step 2: Use ChatGPT to Generate Your Formulas

Now for the fun part. Create a new tab in your Google Sheet and call it "Dashboard." This is where we'll display our KPIs and charts. We’ll use ChatGPT to write the formulas that will pull data from your "Raw Data" sheet and perform the calculations.

The key to using ChatGPT effectively is to be specific in your prompts. Tell it exactly what you want to calculate, which sheet and columns contain the data, and what criteria to use.

Calculating Total Applicants

Let's start with a simple one. We want to count every candidate listed in our "Raw Data" sheet.

Your Prompt to ChatGPT:

I have a Google Sheet with a tab named 'Raw Data'. Give me a formula to count the total number of applicants. The candidate names are in Column A, starting from A2.

ChatGPT's Response and Formula:

=COUNTA('Raw Data'!A2:A)

Now, go to your "Dashboard" tab, pick a cell (like B2), give it a label in the cell next to it (like "Total Applicants" in A2), and paste this formula in. It will instantly count all the non-empty cells in the candidate name column, giving you the total number of applicants.

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

Calculating Average Time to Fill

This is a slightly more complex metric. We need to find the average number of days between the "Application Date" and the "Hired Date" for all candidates who have been hired.

Your Prompt to ChatGPT:

In my 'Raw Data' sheet, 'Application Date' is in Column C and 'Hired Date' is in Column F. Give me a Google Sheets formula to calculate the average 'Time to Fill' in days. The calculation should only include rows where the 'Stage' in Column E is 'Hired'.

ChatGPT's Response and Formula:

=ARRAYFORMULA(AVERAGE(IF('Raw Data'!E2:E="Hired", 'Raw Data'!F2:F - 'Raw Data'!C2:C, "")))

Paste this formula into another cell on your "Dashboard" tab (e.g., B3, with the label "Avg. Time to Fill" in A3). This formula checks each row: if the stage is "Hired," it subtracts the application date from the hired date, and then it averages all those results.

Building the Pipeline Funnel and Source Breakdown

Next, we want to see how many candidates are in each stage of our pipeline and where our hires are coming from. The COUNTIF function is perfect for this.

You can create a small table on your Dashboard tab. In column D, list out your pipeline stages ("Applied," "Phone Screen," "Interview," "Offer"). We'll put the formulas in column E.

Your Prompt to ChatGPT:

In my 'Raw Data' sheet, candidate hiring stages are in Column E. On my 'Dashboard' sheet, I have the stage name 'Applied' in cell D2. Give me a formula to count how many candidates in the 'Raw Data' sheet are at the 'Applied' stage. I want to be able to drag this formula down for other stages listed in D3, D4, etc.

ChatGPT's Response and Formula:

=COUNTIF('Raw Data'!E:E, D2)

Paste this formula into cell E2 of your "Dashboard." Because we used "D2" as a cell reference, you can click the small blue square in the corner of the cell and drag the formula down. It will automatically adjust to D3, D4, etc., counting the candidates for each corresponding stage. You can do the exact same thing to analyze your Source of Hire!

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 3: Visualize Your Data with Charts

Numbers are great, but charts make your data easy to understand at a glance. Let’s create a pie chart to visualize the source of your hires and a bar chart for your candidate pipeline.

Your Prompt to ChatGPT:

I now have a summary table on my 'Dashboard' sheet in Google Sheets. From D2 to D5, I have the recruitment stage names ('Applied', 'Phone Screen', etc.). From E2 to E5, I have the count for each stage. How do I create a bar chart from this data?

ChatGPT can't create the chart for you, but it will give you perfect step-by-step instructions. Essentially:

1. Select the data range (in this case, D2:E5).
2. Go to Insert > Chart.
3. In the Chart editor that pops up, select "Bar chart" or "Pie chart" as the chart type.
4. Customize the titles and colors under the "Customize" tab.

You can now position these charts on your dashboard. They will update automatically as you add or update candidate information in your "Raw Data" sheet, bringing your static spreadsheet to life.

Final Thoughts

By pairing the simple flexibility of Google Sheets with the formula-writing power of ChatGPT, you can build a surprisingly effective recruitment dashboard without spending a dime. It’s an empowering way to get the insights you need to make smarter hiring decisions, optimize your process, and prove the value of your recruitment efforts.

While this manual setup is powerful, it still relies on you keeping the raw data sheet up-to-date and managing formulas. As we developed tools to overcome this, we built a solution to completely automate it. With Graphed, we connect directly to your data sources - whether it's a recruiting system, a CRM, or even a simple Google Sheet - and keep everything automatically refreshed. Instead of asking ChatGPT for formulas to copy-paste, you simply ask us what you want to see (like "Show me a pie chart of our top hiring sources this quarter") and we build the live, interactive dashboard for you in seconds.