Applicant tracker in Excel: stage and next step
An applicant tracker in Excel, for a recruiter, is one row per application. The columns that stop a file from going quiet are the opening, the stage, the date of the last update, and the next action. Days in the current stage are today minus that last update. DATEDIF or a plain subtraction does it. COUNTIFS counts applications by opening and by stage, so you can see which role is still sitting in interviews. A dropdown on the stage column keeps the words identical. If the words drift, the count is wrong. This sheet is the working list for the person who moves candidates from one stage to the next. It is not a headcount file and it does not score people on attributes you did not type. The steps below set the columns, the days since the application, the follow-up flag, and the count by opening. Elyx can build that table from the list you paste. You still decide the next action. The trial lasts 7 days, with no card. When a flag looks wrong, check the cell before the formula. Sort the column the formula reads and look for blanks and for words that are not in the dropdown. A blank is invisible to COUNTIF and COUNTIFS. A date stored as text does not compare with a real date, so a late flag or a days count stays empty. Correct the cell, then read the flag again. Leave finished rows on the sheet. They are the history you divide by when you count a period. Deleting them to tidy the file removes that base. Filter them out of the view instead. If you open a new month, copy the sheet and name the old one with the month. The formulas stay as they are. Before you send the total, read three rows against the source you typed from. If those three match, the total is the total of this sheet. Pick the rows yourself. A formula that is right on the row you already calculated by hand is the one to copy down. Write the unit in the header: days, hours, or pieces. A number without a unit gets divided by the wrong base later. If two people maintain the sheet, the dropdown is the agreement on the words. Change the list in one place, not the cells one by one. Keep the source column next to the formula column so the check does not mean opening another file.
The Problem
Applications sit in an inbox, a spreadsheet exported last week, and notes from a hiring manager. The question you cannot answer quickly is which files have had no update, and which opening has too many people stuck at the same stage. One row per application, one stage list, and a date for the last update make that question a formula. The sheet does not send the email. It shows the row that needs one.
Benefits
One row per application, with the opening, the source, the stage, the last update, and the next action in separate columns.
Days since the last update are today minus that date, so a quiet file is visible without rereading the notes.
COUNTIFS counts applications by opening and by stage from the dropdown words.
A follow-up flag marks rows whose last update is older than the number of days you chose.
The same person applying to two openings is two rows. The count stays honest because the opening is on the row.
Step-by-Step Tutorial
Create the table structure
Create a new Excel workbook and define the main columns for tracking job applications. Set up headers in row 1 with columns for: Candidate Name, Position Applied, Application Date, Status, Last Contact Date, Days Since Application, and Notes. Use bold formatting and a light background color to make headers stand out.
Use Ctrl+T to convert your data range into a structured table, which enables automatic formula updates and filtering capabilities.
Add sample candidate data
Populate the template with realistic recruitment data. Enter candidate names, positions (e.g., 'Senior Developer', 'Marketing Manager'), and application dates. Include at least 8-10 sample records to demonstrate the template's functionality and provide a realistic working example.
Use consistent date formatting (MM/DD/YYYY) across all date columns to ensure formulas calculate correctly and data remains sortable.
Create the Status column with data validation
Set up a dropdown list in the Status column to standardize application tracking. Define status options such as 'Applied', 'Screening', 'Interview Scheduled', 'Interviewed', 'Offer Extended', 'Hired', or 'Rejected'. This ensures consistent data entry and makes filtering easier.
Use Data > Data Validation > List to create dropdown menus. This prevents typos and makes your reports more reliable.
Calculate days since application with DATEDIF
In the 'Days Since Application' column, insert a formula that automatically calculates how many days have passed since each candidate applied. This helps you identify stale applications that need follow-up. The formula will update daily without manual intervention.
=DATEDIF(C2,TODAY(),"D")Place this formula in column F starting at F2. DATEDIF counts complete days, making it perfect for tracking application age in recruitment workflows.
Add conditional formatting for status tracking
Apply conditional formatting to visually highlight application statuses. Use color coding: green for 'Hired', yellow for 'Interview Scheduled', orange for 'Screening', and red for 'Rejected'. This provides instant visual feedback on your pipeline at a glance.
Use Home > Conditional Formatting > Highlight Cell Rules to create rules based on cell values. This makes your dashboard more intuitive and easier to manage.
Create a summary dashboard with COUNTIF
Build a summary section below your main table to track key recruitment metrics. Use COUNTIF formulas to count applications by status, giving you a quick overview of your pipeline. This helps you identify bottlenecks and measure recruitment progress.
=COUNTIF(D:D,"Interviewed")Create a separate area with labels like 'Total Applications:', 'Interviewed:', 'Offers Extended:', etc. This provides executive-level visibility into your recruitment funnel.
Calculate position-specific application counts
Add formulas to count how many applications you've received for each position. This helps you understand which roles are attracting the most interest and manage your recruiting efforts accordingly. Use COUNTIF with position names as criteria.
=COUNTIF($B$2:$B$100,"Senior Developer")Create a separate table listing each position and its application count. This helps you allocate resources effectively and identify high-demand roles.
Add a follow-up indicator formula
Create a column that automatically flags candidates who haven't been contacted in more than 7 days. Use an IF statement combined with DATEDIF to identify candidates requiring follow-up action. This ensures no applicant falls through the cracks.
=IF(DATEDIF(E2,TODAY(),"D")>7,"Follow-up needed","")Use conditional formatting with this column to highlight 'Follow-up needed' entries in red, creating an action list for your team.
Create a conversion rate summary
Calculate your recruitment conversion rates by comparing candidates at each stage. Add formulas to show what percentage of applicants move from screening to interviews, and from interviews to offers. This metric helps you optimize your hiring process.
=COUNTIF(D:D,"Interviewed")/COUNTIF(D:D,"Applied")Format this result as a percentage (Right-click > Format Cells > Percentage) to make it immediately understandable for reporting.
Set up filters and freeze panes for easy navigation
Enable AutoFilter on your table headers to allow filtering by position, status, or date range. Freeze the header row so it remains visible when scrolling through candidates. This makes the template user-friendly even with hundreds of applications.
Use View > Freeze Panes to lock row 1, and Data > AutoFilter to enable dropdown arrows on headers. This transforms your template into a professional tracking tool.
Template Features
Application Status Pipeline Tracking
Automatically categorizes candidates through recruitment stages (Applied, Screened, Interviewed, Offered, Hired, Rejected) with color-coded visual indicators to quickly identify where each candidate stands in the funnel
Days-to-Fill Calculation
Automatically calculates the number of days between application date and hiring decision, helping recruitment specialists measure hiring cycle efficiency and identify bottlenecks
=IF(D2="","",TODAY()-A2)Automated Pipeline Count Summary
Dynamically counts candidates in each stage (Applied, Screened, Interviewed, etc.) to provide real-time visibility into pipeline health without manual updates
=COUNTIF($C$2:$C$500,"Interviewed")Interview Schedule Conflict Detection
Flags scheduling conflicts when multiple candidates are assigned the same interview date and time, preventing double-booking and coordination errors
=COUNTIFS($E$2:$E$500,E2,$F$2:$F$500,F2)>1Source Performance Analysis
Counts hires by the source you typed on the row, so you can see which source name appears most often among the rows marked hired
=COUNTIFS($G$2:$G$500,"LinkedIn",$C$2:$C$500,"Hired")Automated Follow-up Reminders
Identifies candidates requiring follow-up based on application date and current status, ensuring no promising candidates slip through due to forgotten communications
=IF(AND(TODAY()-A2>7,C2="Screened"),"Follow-up Due","")Concrete Examples
Tracking candidate pipeline for a single job opening
Sarah, a recruitment specialist at a tech startup, is hiring for a Senior Software Engineer position. She needs to monitor 47 applications across different stages and identify bottlenecks in her hiring process.
Applied: 47 candidates | Phone Screen: 12 candidates | Technical Interview: 8 candidates | Final Interview: 3 candidates | Offer Extended: 1 candidate. Timeline tracked from submission date to current stage. Candidates include: John Smith (Applied 2024-01-15, Phone Screen passed), Maria Garcia (Applied 2024-01-18, Technical Interview scheduled), David Chen (Applied 2024-01-10, Rejected after phone screen).
Result: A dashboard showing conversion rates between stages (25.5% phone screen rate, 66.7% technical interview progression), average time-to-hire per stage (5 days for phone screen, 8 days for technical interview), and visual funnel chart identifying that the bottleneck is the final interview stage with only 3 candidates remaining.
Comparing multiple open positions and candidate quality metrics
Thomas, a senior recruitment specialist managing 6 concurrent job openings across different departments, needs to allocate his time and resources efficiently by comparing pipeline health and candidate quality across all positions.
Marketing Manager: 28 applications, 8 qualified | Sales Representative: 62 applications, 12 qualified | HR Coordinator: 15 applications, 5 qualified | Product Manager: 41 applications, 18 qualified | Customer Success Lead: 33 applications, 9 qualified | Data Analyst: 52 applications, 14 qualified. Each position tracked with average interview ratings (1-5 scale).
Result: A comparative analysis showing Product Manager has the highest qualification rate (43.9%) despite lower application volume, while Sales Representative has the lowest conversion (19.4%) despite highest volume. A prioritization matrix recommending Thomas focus on Sales Representative sourcing strategy while Product Manager can move to final interviews faster. Time-to-fill projection for each role.
Count by opening and stage
Two openings are on the same sheet. You want the number of applications still in interview, per opening, from the stage you typed.
Opening A: 18 applications, 7 in interview, 4 with a last update older than 10 days. Opening B: 11 applications, 2 in interview, 1 with a last update older than 10 days.
Result: COUNTIFS returns 7 and 2 for the interview stage. A second count, on the days since the last update, returns 4 and 1. Those are counts of the rows you typed, not a hiring rate you can publish.
Pro Tips
Conditional Formatting for Pipeline Visibility
Use conditional formatting to color-code candidate status by stage (Applied, Screening, Interview, Offer, Hired). This gives you instant visual feedback on your pipeline health without scrolling. Apply it to your Status column with rules like: Green for 'Hired', Yellow for 'Interview', Red for 'Rejected'. Use Format > Conditional Formatting > Color Scales or Icon Sets for professional dashboards.
Dynamic Candidate Aging Formula
Track how long candidates have been in each stage with a formula that calculates days elapsed. This helps identify bottlenecks and candidates at risk of declining offers. In column 'Days in Stage', use: =TODAY()-[Application Date] to see which candidates need follow-up urgently. Combine with conditional formatting to flag candidates over 14 days in screening.
=TODAY()-[Application Date]Pivot Table for Recruitment Metrics
Create a pivot table (Data > Pivot Table) to analyze conversion rates by source, recruiter, or position. Rows: Source | Columns: Status | Values: Count of Candidates. This reveals which job boards convert best and which stages need process improvement, critical for optimizing your recruitment ROI.
VLOOKUP for Duplicate Detection & Candidate History
Use VLOOKUP to check if a candidate has applied before, preventing duplicate processing and enabling you to track repeat applicants. In a helper column: =IFERROR(VLOOKUP([Email],Archive!$A:$E,2,FALSE),"New Candidate"). This saves time and ensures you never miss a returning talent.
=IFERROR(VLOOKUP([Email],Archive!$A:$E,2,FALSE),"New Candidate")