
Blank vs. Zero: The Data-Quality Errors That Quietly Break a Skills Matrix
The Heat Map That Said Nobody Could Run the Press Brake
A plant manager opens the skills heat map before a staffing decision. The press brake row is a wall of red. Five of the eight people on second shift show a zero, so the conclusion looks obvious: send people to training, or hire. Then a supervisor looks at the sheet and says, "Dana has run that machine for six years." Dana's cell is empty. The formulas that build the report read the empty cell as zero, and the heat map colored it red.
Nothing in that workbook was broken in the usual sense. No formula threw an error. The data simply meant two different things in one column, and the report could not tell them apart. That is the pattern behind many skills data quality problems: the sheet looks fine, the numbers add up, and the answer is wrong.
By the end of this article you will be able to name six common errors that corrupt a skills matrix and run a short audit that finds them before they reach a report.
Blank vs. Zero: Two Different Facts in One Cell
A blank cell means "we don't know." A zero means "we assessed this person and they have no capability." Those are different facts, and a matrix that stores both as an empty or zero-looking cell throws one of them away.
The damage runs in a predictable direction. Treat every blank as a zero and:
- Average proficiency drops. A skill with three rated people and five unrated ones averages across eight, not three.
- Gaps look bigger. If a role requires level 3, a blank scores as a shortfall of 3, the largest possible gap.
- Coverage looks smaller. Counts of "people who can do this" undercount anyone nobody got around to rating.
Now run it the other way. If a spreadsheet function skips blanks but counts zeros, the same matrix reports different averages depending on which cells someone typed a 0 into. Two people can build the same report from the same file and disagree.
A worked example, with round numbers chosen only to show the method. Say a skill has eight people on the roster. Three are rated 4, 3 and 2, and five are unrated. Averaging only the rated people gives 3.0. Counting the five blanks as zeros gives 9 ÷ 8, or about 1.1. Same data, and the second reading says the team is far weaker than the first. Neither number is "the" answer. The honest report shows the 3.0, shows that only three of eight were rated, and flags the five unknowns.
The fix is a rule, not a formula: pick a visible marker for "not assessed" (the text "NA" works, or a dedicated status column), reserve 0 for a real assessed zero if your scale has one, and write that down where everyone who edits the sheet will see it. Our guide to keeping a skills matrix up to date covers the maintenance habits that make a rule like this stick.
The Other Errors That Quietly Skew a Skills Matrix
Blank-versus-zero gets the headline, but it travels with five relatives. Each one is easy to introduce and hard to see.
Duplicate skill names. "Forklift," "Fork Lift," "Powered Industrial Truck" and "PIT operator" are one skill typed four ways. A count of who is forklift-capable splits across four columns, and each column looks thinly staffed. Free-text skill entry guarantees this over time, because every editor spells things their own way.
Mixed rating scales. One department rates 1-5, another 1-4, a third uses Beginner/Intermediate/Expert. Merged into one sheet, a 3 means something different in each block. Averages across the whole matrix blend scales that were never comparable.
Text that looks like numbers. A rating typed as "3 " with a trailing space, or pasted in as text, is ignored by most calculations. The cell shows a 3; the formula sees nothing. The sum quietly leaves that person out.
Stale or undated ratings. A rating from three years ago and one from last month sit side by side with equal weight. Without an assessed-on date you cannot tell which numbers are current, and you cannot tell a blank that means "never rated" from a rating that has simply aged out.
Orphaned rows and columns. People who left, skills nobody uses anymore, and test rows someone forgot to delete all stay in the totals. Headcount-based ratios drift without any single bad entry to point at.
None of these needs a sophisticated attack on your data. They accumulate through ordinary use, which is why they deserve a routine rather than a one-time cleanup.
Why Spreadsheets Make Skills Data Quality Hard to See
Skills data quality depends on rules, and spreadsheets have no built-in way to enforce a rule across a whole sheet. Anyone with edit access can type anything into any cell, and the file keeps no history of who changed what. That is fine for a short grocery list. It is a problem for a matrix that feeds staffing and training decisions.
The research on spreadsheet reliability is blunt. Panko's work, summarized in Powell, Baker and Lawson's 2008 paper "Errors in Operational Spreadsheets," found that 94% of spreadsheets contain at least one error, with an average cell error rate of about 5.2%. A 2024 academic study by Poon and colleagues in Frontiers of Computer Science, reported by Phys.org, likewise found that 94% of spreadsheets used in business decision-making contain errors. Panko's 2016 paper, "What We Don't Know About Spreadsheet Errors Today," adds that because per-cell errors compound across dependent formulas, an error becomes near-certain once a spreadsheet has around 100 root formulas.
Those studies are about spreadsheets in general, not skills matrices specifically, and your own error rate may differ. But a skills matrix has the ingredients they describe: many hands editing, many cells, and summary formulas that depend on all of them. For the wider case on what outdated sheets cost, see the cost of an outdated skills spreadsheet.
A One-Hour Skills Data Audit
This audit of your skills data runs on your existing matrix without buying anything. Work on a copy, and keep a log of what you change.
- Count the blanks. For each skill column, count rated cells and empty cells. Any column where most cells are empty is not a measurement of capability yet. It is a to-do list.
- Separate unknown from zero. Find every 0 in the sheet and ask of each one: was this person assessed? If nobody can say, change it to your "not assessed" marker. Then check your averages and gap formulas to confirm they skip that marker instead of counting it.
- List unique skill names. Sort the skill names alphabetically and read them aloud. Merge spellings and abbreviations into one canonical name per skill, and note the merges so the history is not lost.
- Check the scale. Confirm every rated cell sits inside one defined scale. Flag anything outside it, such as a 6 on a 1-5 scale, a decimal, or a word where a number belongs.
- Find the text-numbers. Use a filter or a type check to catch ratings stored as text. Convert them and re-run the totals.
- Check the dates and the roster. Compare the people on the sheet to your current roster, remove or archive departures, and note which ratings carry no date.
Then re-run your headline report and compare it to the previous version. The difference between the two is the measurable size of your data-quality problem. If the heat map looks very different afterward, you now know how much of the old picture was artifact. Reading the cleaned output well is its own skill, covered in how to read a skills heat map.
If you are still building the matrix itself, a skills matrix Excel template with a defined scale and a reserved "not assessed" value makes steps 2 and 4 far shorter, because the rules are built in from the first cell.
Making the Fix Last
An audit finds problems; it does not stop new ones. Three habits keep the data clean between audits:
- Constrain entry. Use dropdown lists for ratings and skill names so that spelling variants and text-numbers cannot be typed in.
- Date every rating. An assessed-on column turns "stale" from a feeling into a filter.
- Assign an owner. One named person is responsible for the audit routine, with a recurring calendar slot. Shared responsibility for a shared sheet usually means nobody checks it.
If you want the audit packaged, the Skills-Data Audit & Blank-vs-Zero Integrity Workbook takes your skills matrix as input and returns the data-quality problems it finds, including the blank-vs-zero errors that silently corrupt every read.
Be honest about the limits of any spreadsheet-based fix. Dropdowns and checks help, but the file still has no access control or change history, which is part of why matrices degrade as an organization grows. If you are weighing when a spreadsheet stops being enough, skills gap analysis in Excel versus software lays out the trade-offs. And if your team is small, stay in Excel: with a clear scale, a "not assessed" marker and a yearly audit, a spreadsheet can be perfectly adequate.


