Paying a monthly fee to be told your CV is "62 percent matched" gets old quickly, especially when the tool will not show you which words it actually counted. The core of what those scanners do to keywords is a lookup and a bit of arithmetic. You can build it yourself in about twenty minutes, and because you wrote the rules, you will know exactly what the number means.
Before anything else, be honest about what this sheet is.
What it does, and what it does not do
It approximates keyword coverage. It tells you which terms from a specific job posting appear somewhere in your CV, how often, and in which section. That is genuinely useful, because the most common reason a good candidate looks weak on paper is that they described real work using different vocabulary than the employer used.
It does not replicate any real applicant tracking system's ranking. Real systems use things your spreadsheet has no access to: recruiter boolean searches run after the fact, knockout questions, dates and durations, semantic matching between related terms, and scoring rules that differ from one employer's configuration to the next. Two people with identical keyword coverage can rank very differently. Treat your total as a self-check, not a simulation.
It also cannot tell you whether your CV parses. Whether the software can read your file at all is a separate question, and no keyword count will surface a problem there. The quick manual version: open your PDF, select all, paste into a blank plain-text document, and see whether your job titles, dates and bullets survive in a readable order. Do that once per CV template, not per application.
The posting we will use
Throughout, I am scoring against one real-shaped posting: a mid-level Marketing Analyst role at a retail group. It asks for SQL, Google Analytics 4, Looker Studio, campaign attribution, A/B testing, budget reporting and "clear communication with commercial stakeholders", lists Python as nice to have, and mentions "retail" or "e-commerce" four separate times.
Step 1: build the columns
Open a blank sheet and create these headers in row 1. Nine columns, left to right.
- Keyword or phrase - the exact term as the posting writes it, one per row.
- Source - where you found it: title, requirements, responsibilities, about the team, benefits.
- Category - hard skill, tool, qualification, soft skill, or job title. This makes the missing-terms list easier to read later.
- Required or preferred - copy the posting's own framing. "Must have" and "essential" are required; "ideally", "a plus", "nice to have" are preferred.
- Weight - a number, set in step 3.
- In my CV - 1 or 0, calculated.
- Count - how many times it appears in your CV, calculated.
- Section - summary, experience or skills, calculated.
- Score - weight multiplied by the match, calculated.
An optional tenth column for how many times the term appears in the posting is worth adding if you want your weights to be defensible rather than instinctive.
Step 2: fill the keyword column
Finding the right terms is its own skill and there are guides for it; the short working method is to read the posting six times, each pass looking for one thing only.
- The title and its variants. "Marketing Analyst" goes in, and so do the neighbours the employer might also use: "Marketing Data Analyst", "Analytics Analyst". If your current title is "Digital Insights Executive", the gap between that and the posting's title is often your biggest scoring hole.
- The requirements list. Every noun that names a skill, tool or qualification, split into individual rows. "SQL and Looker Studio" is two rows, not one.
- The responsibilities verbs. Postings say "build dashboards", "forecast spend", "run experiments". Those verb-plus-object pairs are what you want your bullets to echo.
- Tools and certifications named explicitly. Product names are the highest-value rows in the sheet because they are unambiguous and easy to match.
- Anything repeated. "Retail" appearing four times is not decoration; it is the vocabulary the reader lives in.
- The "about the team" paragraph. Most people skim it. It is where you find phrases like "we sit inside the commercial function", which tells you that "commercial stakeholders" beats "business users" as your wording.
Thirty to forty rows is a healthy list for one posting. Under fifteen and you have not read closely enough.
Step 3: set the weights
Keep the scheme crude on purpose. Three, two, one.
Weight 3 for anything required and named in the title, or required and repeated more than twice. SQL, Google Analytics 4 and the title term itself get a 3.
Weight 2 for required items mentioned once: Looker Studio, A/B testing, budget reporting.
Weight 1 for preferred items, soft skills and terms from the culture paragraph. Python sits at 1, and so does "collaborative".
Two rules do most of the work: required outranks preferred, and a term the employer repeated outranks one buried in a nice-to-have line. Resist inventing half-points. The precision would be false.
Step 4: paste your CV into one cell
Create a second tab called CV text. Copy your CV and paste it as plain text into cell A1 of that tab, using paste-special without formatting so line breaks do not scatter it across rows. If it lands in several cells, undo, and paste into the formula bar instead.
Better still, use three cells: A1 for your summary, A2 for your experience section, A3 for your skills list. That costs thirty seconds and gives you the section column for free.
Step 5: the formulas
Written for Google Sheets, which uses the English names. In French Excel these are NB.SI, SOMME, NBCAR and SUBSTITUE; in Spanish Excel, CONTAR.SI, SUMA, LARGO and SUSTITUIR.
Column 6, the match. Use a COUNTIF that looks inside the CV cell for the keyword, wrapping the keyword reference in wildcard asterisks so it matches the term anywhere in the text rather than requiring the whole cell to equal it. Point it at the whole CV range so any of your three section cells counts. It returns 1 when the term is present and 0 when it is not, and it ignores capitalisation.
Column 7, the count. There is no direct function for counting occurrences inside a cell, so use the classic length trick: take the length of the CV text, subtract the length of that same text with every instance of the keyword substituted out for nothing, then divide the difference by the length of the keyword. The answer is the number of occurrences.
Column 8, the section. Run the same wildcard COUNTIF three times, once against each section cell, and wrap the results in an IF chain that returns the section names. Seeing that SQL appears only in your skills list and never in a bullet is one of the more useful things this sheet will tell you.
Column 9, the score. Weight multiplied by column 6. Nothing cleverer.
The totals. Above the rows, put a SUM of column 9 for your score, a SUM of column 5 for the maximum possible, and divide one by the other for a percentage. Then add a COUNTIFS that counts rows where required equals yes and the match equals zero. That last number is the one worth looking at.
Step 6: reading the result
Broad bands, and nothing more precise than this is honest. Below 50 percent you are describing your work in a vocabulary the employer does not share. Between 50 and 70 you have the substance and are losing terms at the edges. Above 70, keyword work has little left to give and your time is better spent on the cover letter.
The percentage is the least interesting output. The list of required terms scoring zero is the point of the exercise. Filter on those two conditions and you get a short, specific list of things to fix, which is what you actually wanted when you first considered paying for a scanner.
What the sheet cannot judge
Three things sit outside the arithmetic, and they matter more than the total.
Never add a term you cannot substantiate. If Looker Studio scores zero because you have never opened it, the correct action is to leave it at zero, not to type it into a skills list. A row of unearned keywords survives exactly as far as the first interview question.
Rewrite real bullets rather than bolting on a list. If you built dashboards in Tableau and the posting says Looker Studio, you do not have that keyword and cannot fake it. But if you ran "split tests" and the posting says "A/B testing", that is the same work under a different name, and the fix is to reword the bullet you already have.
Watch for synonym misses. These are the profitable ones. "Stakeholder management" versus "working with the business", "attribution" versus "channel performance analysis". Your sheet reports a zero and you assume a gap, when you already do the work and simply named it differently.
Reusing it across applications
Duplicate the keyword tab for each new role and name it by employer and date. The CV text tab stays where it is, so every new tab scores against your current CV automatically. After five or six applications a pattern appears: the same three terms score zero everywhere, which tells you something about your CV rather than about any single posting.
If you also keep an application tracker spreadsheet, the two sit well side by side - one tells you what to fix before you apply, the other tells you what happened after.
When a paid scanner is worth it
Sometimes. What you are buying is the part your sheet cannot do: a parsing check that shows how your file is actually read, warnings about columns, headers, text boxes and graphics that break extraction, and in the better tools a database of postings for the same title, so you see the terms that recur across a market rather than in one advert.
If you are applying to twenty roles with a template you have never tested for parsing, a month of a scanner is reasonable. If you are applying to three carefully chosen roles with a clean single-column CV, this spreadsheet does the job and keeps the money.