Build a job application tracker spreadsheet with working formulas
In this article (6 sections)
Related programme: the Data Analytics with Generative AI course (3–4 months; ₹90,000 (inclusive of GST) · EMI from ₹4,000/month).
A useful job application tracker has one row per application with columns for date applied, company, role, source, status, last contact date and next action, plus three formula columns: a follow-up due date, an overdue flag and days since applying. A small summary block then calculates how many applications reached interview by source, which tells you where to spend next week's effort.
This is also a genuine Excel exercise. If you are applying for analyst roles, a tracker you built and can explain is a small piece of evidence in itself.
The columns
| Col | Header | Type | Notes |
|---|---|---|---|
| A | Applied on | Date | The date you submitted |
| B | Company | Text | |
| C | Role | Text | Use the posting's exact title |
| D | Source | Dropdown | Naukri, LinkedIn, Referral, Company site, Cold email, Campus, Other |
| E | Job ID / link | Text | Paste the URL or ID; you will need it for referrals and follow-ups |
| F | Status | Dropdown | Applied, Screening, Interview, Assignment, Offer, Rejected, Withdrawn, No response |
| G | Last contact | Date | Last time either side communicated |
| H | Next action | Text | "Send thank-you", "Follow up with HR", "Prepare SQL round" |
| I | Follow-up due | Formula | See below |
| J | Flag | Formula | See below |
| K | Days since applied | Formula | See below |
| L | Resume version | Text | Which tailored file you sent |
| M | Notes | Text | Interviewer names, questions asked, salary range discussed |
Freeze the header row and turn the range into a table (Insert, Table in Excel) so formulas fill down automatically.
Dropdowns for Source and Status
In Excel, select the Status column, then Data, Data Validation, Allow: List, and enter:
Applied,Screening,Interview,Assignment,Offer,Rejected,Withdrawn,No responseGoogle Sheets has the same under Data, Data validation, with a dropdown option. Dropdowns matter because the summary formulas count exact text; "interview" and "Interview " (with a trailing space) would be counted separately without them.
The formula columns
Assume data starts in row 2.
I2, follow-up due. Seven days after the last contact, unless the application is closed:
=IF(OR(F2="Offer",F2="Rejected",F2="Withdrawn",G2=""),"",G2+7)Format column I as a date.
J2, flag. Shows a word when a follow-up is overdue:
=IF(AND(I2<>"",I2<TODAY()),"FOLLOW UP","")Add conditional formatting to colour cells in column J that are not empty.
K2, days since applied:
=IF(A2="","",TODAY()-A2)Format as a number, not a date, or it will display as a date in January 1900.
The summary block
Put this on a second sheet or to the right of the table. Replace ranges if your data goes beyond row 500.
| Cell | Label | Formula |
|---|---|---|
| Summary!B2 | Total applications | =COUNTA(Tracker!B2:B500) |
| Summary!B3 | Currently in interview | =COUNTIF(Tracker!F2:F500,"Interview") |
| Summary!B4 | Offers | =COUNTIF(Tracker!F2:F500,"Offer") |
| Summary!B5 | Follow-ups overdue | =COUNTIF(Tracker!J2:J500,"FOLLOW UP") |
Then a response-rate table by source. Put the source names in A8:A14 and, in B8:
=COUNTIF(Tracker!$D$2:$D$500,A8)In C8, applications from that source that got any response beyond silence:
=COUNTIFS(Tracker!$D$2:$D$500,A8,Tracker!$F$2:$F$500,"<>Applied",Tracker!$F$2:$F$500,"<>No response")In D8, the rate, safe against division by zero:
=IFERROR(C8/B8,0)Format D as a percentage and fill B8:D8 down to row 14.
Reading the numbers (hypothetical example)
Suppose after four weeks the table shows:
| Source | Applications | Responses | Rate |
|---|---|---|---|
| Naukri | 40 | 4 | 10% |
| 25 | 3 | 12% | |
| Referral | 6 | 3 | 50% |
| Cold email | 10 | 1 | 10% |
These figures are invented for illustration, not market averages. The point is the decision they support: if referrals convert far better for you, next week move time from mass portal applications to preparing referral requests, using the drafts in asking for a referral. Small numbers are noisy, so look for large differences, not a two-point gap.
Keep the tracker honest
- Update it the moment something happens. A tracker updated weekly from memory drifts.
- Move silent applications to "No response" after about three weeks so the in-progress counts stay true.
- Never delete rejected rows; they are the data for your review.
- Keep notes on questions asked in each round. Patterns in the questions you struggled with are the most useful output of the whole sheet, and they feed straight into the review log in handling rejection.
Building this tracker uses COUNTIFS, data validation, conditional formatting and date arithmetic, the same Excel foundations covered in the Data Analyst programme.
Pankit Kumar has 10 years in Data Science & AI, building and shipping production systems in regulated pharma and clinical environments. He is a freelance trainer at Boston Institute of Analytics, AnalytixLabs and Scaler, and has taught this material to thousands of working professionals.
This article is part of our Data Analytics with Generative AI programme — 3–4 months. The full analyst stack — Excel, SQL, Power BI and Python pipelines — then a generative-AI layer you can prove is right.
Explore Data Analytics with Generative AI