Concept
1. Read the JD in three passes
- Must-haves: words in "Requirements" / "Must have" (e.g. "Advanced Excel", "VLOOKUP", "Pivot", "MIS reporting").
- Nice-to-haves: "Preferred" / "Good to have" (e.g. "Power BI", "SQL", "VBA").
- Context words: industry and tasks ("sales MIS", "inventory", "reconciliation", "dashboards").
2. Build a keyword checker in Excel
Sheet layout:
- A2:A30 — keywords from the JD (one per row)
- C1 — your resume text (paste everything into one cell; or into C1:C60 and join with
=TEXTJOIN(" ",TRUE,C1:C60)in C1 of another sheet)
In B2:
=IF(TRIM(A2)="", "", IF(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"~","~~"),"*","~*"),"?","~?"), $C$1)), "✓ found", "✗ missing"))
Match score:
=IFERROR(COUNTIF(B2:B30, "✓*") / SUMPRODUCT(--(LEN(TRIM(A2:A30))>0)), 0)
Aim for most must-haves present. SEARCH isn't case-sensitive; for words like "VBA" vs "vba" that's what you want.
3. Synonyms and variants
ATS products vary; this checker only finds text substrings. Include the JD's wording where it's true for you:
| JD says | Make sure your resume shows |
|---|---|
| Advanced Excel | the specific skills too: XLOOKUP, pivot tables, Power Query |
| VLOOKUP | "VLOOKUP/XLOOKUP" only if you can demonstrate both |
| MIS reporting | the phrase "MIS reports" in a bullet |
| Dashboards | "dashboard" in a project/experience bullet |
| Macros | "VBA macros" |
| Data cleaning | "data cleaning (Power Query)" |
| Power BI (nice-to-have) | only if true — or "Power Pivot/DAX (Excel)" as related experience |
4. Where to put keywords
- Skills section: grouped, using the JD's terms.
- Summary: 2–3 top keywords naturally.
- Bullets: keywords inside real achievements carry more weight with humans than a skills list.
- Job title line: if your title was "Executive – Reports", it's fine to write "Executive – Reports (MIS)" when that describes the work.
5. LinkedIn
- Headline: role + 3 keywords ("MIS Analyst | Excel · Power Query · Dashboards").
- About: 3–4 lines with your best result and keywords.
- Skills: add up to the platform's limit; pin your top skills; ask colleagues for endorsements of the important ones.
- Recruiters search LinkedIn with the same keywords as ATS — consistency between resume and profile matters.
6. Honesty line
Add a keyword only if you can answer a basic interview question about it. "Power BI" with zero practice will be found out in the first technical question — write "learning Power BI" or leave it out.
7. Tailor in 15 minutes per application
- Paste JD keywords into the checker.
- Reorder your Skills to match the JD's order of importance.
- Swap one or two bullets for more relevant ones from your master resume.
- Adjust the summary's first line to the job title.
- Check score, save as
Firstname_Lastname_<Role>.pdf.
Checker limits: Fill B2 down to B30. Blank/space-only keywords stay blank; an empty list scores 0%. The formula treats *, ? and ~ literally. It still matches substrings (for example, SQL inside MySQL); manually review matches and synonyms. This is keyword coverage, not an ATS ranking or hiring probability. If joining text from another sheet, use =TEXTJOIN(" ",TRUE,Resume!C1:C60) to avoid a circular reference.
Common mistakes
Keyword stuffing (lists of 60 tools, white-text keywords — recruiters notice). One generic resume for every job. Keywords you can't defend.