Employee database template for Excel
Employee database template for Excel
The question always lands at the worst possible moment. Someone hands in their notice on a Friday, the replacement starts on Monday, and a few days later the accountant asks for the health and work-risk enrolment certificate of the person who has already gone. Nobody knows which folder it went into. When it finally turns up, the name is spelled differently, the position has changed and the start date does not match the contract.
In most small businesses the data is not missing. It is scattered: one list in the payroll file, one in the email where the enrolment arrived, one in the area manager notebook and one in the head of whoever has been doing this for years. Each version disagrees with the others, and none of them is the official one.
This template fixes one thing and fixes it properly: it keeps a personnel master file with one row per person, where contract, social security and status sit in the same place and can be filtered. It is not the monthly payroll sheet and it is not a shift planner. It is the sheet you open when someone asks who works here and on what contract.
⬇ Download the template (Excel .xlsx)What this format is, and who it is for
It is a master record, not a calculation sheet. Each row is a person; each column is a fact somebody will need at some point in the year. The sheet holds a worker employment identity: who they are, what ID number they carry, what position they fill, which contract they came in on, which health plan and work risk plan they are enrolled with, and whether they are still with the company.
It suits workshops, warehouses, restaurants, small accounting offices, clinics and any business carrying a few dozen people on payroll, where HR is one person who also runs payroll and pays the contributions. It also suits the business that grew and found out the list in an email thread no longer holds: once the filter stops working because the same position is written three different ways, it is time for a master file.
Above all, it suits the moment a review arrives. An auditor, a bank, a large customer asking for the staff list with contract and social security details. With an orderly master file that request is answered by filtering and exporting. Without one, it is answered by asking for a week of grace that nobody has.
What the template contains
The sheet is built in blocks, in the same order you would ask about a person: first who they are, then what they do, then how to reach them, then their social security, and finally whether they are still on board. That sequence is not arbitrary: it mirrors every enrolment or contract form you will have to fill in anyway.
| Block | What it holds | Typed or calculated |
|---|---|---|
| Sheet | A single sheet, with a frozen header row and one row per person | Typed |
| Identification | Code, ID number and full name | Typed |
| Role | Position, department, contract type and hire date | Typed |
| Contact | Phone and email | Typed |
| Social security | Health plan and work risk plan | Typed |
| Status | Status and notes | Typed; the status comes from a drop-down |
| Footer | Total records, active and left | Calculated |
| Lists and formatting | Status list, contract-type list and colour on the status column | Set once |
The only block with real calculation is the footer. Everything else is data entry, and that is the trap: because no formula warns you about a mistake, the quality of the master file depends entirely on how each cell is filled.
The columns, one by one
Fourteen columns, no complicated formulas. What needs care is the entry rule, because that is what keeps the filter useful a year from now.
| Column | What you type | What is calculated, or the rule it follows |
|---|---|---|
| Code | A short, unique identifier per person: one series letter plus a running number | Nothing. It is never reused, not even after the person leaves |
| ID number | The number exactly as printed on the identity document, no dots and no spaces | Nothing. The cell is set to text so leading zeros survive |
| Full name | Given names and surnames in a single field, always in the same order | Nothing. If the order changes between rows, the filter stops working |
| Position | The job title as written in the contract | Nothing. Spell it the same way in every row: to a filter, upper and lower case are different positions |
| Department | The area or process the person reports into | Nothing. Keep a short, stable list even if the area gets renamed |
| Contract type | Chosen from the list: fixed term, open-ended, project-based, apprenticeship | Nothing. The list rules out hand-typed variants |
| Hire date | The real start date, stored as a date and not as a sentence | Not calculated here, but every length-of-service question later starts from this cell |
| Salary | The contract figure, digits only, no currency symbol and no thousands separators | Nothing. It is an editable field: you update it when it changes, and the history does not live in the sheet |
| Phone | A contact number, with the area code if it is a landline | Nothing |
| One address per person, the one they actually read | Nothing | |
| Health plan | The name of the health provider the person is enrolled with | Nothing. Copy it from the enrolment certificate, not from memory |
| Work risk plan | The name of the occupational risk insurer | Nothing. If the provider changed, update the cell and note it under Notes |
| Status | Active or left, chosen from the list | Feeds the footer: each value is counted separately |
| Notes | Short remarks: rehires, position changes, leaving date | Nothing. It is not a diary: if the note runs past one line, it belongs somewhere else |
If a column does not apply to your operation — no company email addresses, say — leave it empty rather than deleting it. Removing a column breaks the footer and takes down the filter everyone else already relies on.
How the master file is counted: the worked example
The template does no money arithmetic: the salary is typed, not derived. What it does calculate is the control on the master file, and that control is what prevents surprises. The example shipped with the sheet holds three records: two people still active and one who has left.
| Item | How it is obtained | Result |
|---|---|---|
| Records in the master file | Every row carrying a full name is counted | 3 records |
| Active staff | Rows whose status reads active are counted | 2 active |
| People who left | Rows whose status reads left are counted | 1 who left |
| Reconciliation check | Active plus left must equal the record count | 2 + 1 = 3, and it ties |
The footer is not decoration: it is the quickest proof that the master file is still sound. If the record count stops matching the sum of active and left, one of three things happened. Somebody typed the status with a different word, a row was left half-filled, or the same person appears twice. In the example the sum ties, which is exactly what shows the status list is properly wired to the count.
One detail gets overlooked almost every time: the person who left stays on the sheet. When someone goes, you do not delete the row; you change the status and write the leaving date under notes. Delete it and the record drops out of the count and out of the history, and a year later nobody can explain why headcount fell in a month when no one resigned.
Step by step
- Save a copy of the template under the company name and the period. Always work on the copy, never on the file you just downloaded.
- Start with the people who are active. One row per person, without grouping positions or departments: a master file is not an org chart.
- Assign the code before typing anything else: a series letter plus a running number. Never reuse the code of somebody who has already left.
- Enter the ID number as text, with no dots or spaces, and check that no leading zeros were lost.
- Fill in position, department, contract type and hire date with the contract in front of you. The date goes in as a date, not as a phrase.
- Copy the health plan and the work risk plan from the enrolment certificate. If you do not have it at hand, leave the cell empty and say so under notes: an empty cell is honest, an invented one is not.
- Read the footer. Records must equal active plus left. If it does not tie, filter by status and find the row that fell outside the list.
Mistakes that keep happening
- Two rows for the same person, one for the rehire and one for the previous position. The count inflates and the filter returns two different histories.
- Deleting the row of whoever leaves instead of marking them as left. The history is gone and the footer lies.
- Typing the ID number with dots, or leaving it as a number: the program converts it, eats the zeros and the result is useless for an enrolment form.
- Dates stored as text — the month spelled out, for instance — so nothing can be sorted or filtered by range later.
- Folding the position or the department into the name field to save space. It is the fastest way to make the whole sheet useless.
- Leaving status hand-typed: active, in company, current, on staff. Four ways to say one thing and a footer that only counts one.
- Using notes as an agenda: long paragraphs that blow up the row height and hide the data that matters.
- Not updating the leaving date the same day. The master file becomes an old snapshot and someone ends up paying contributions for a person who no longer works there.
When to move to software
A sheet like this holds up while it stays a snapshot of the present. It starts to fail when it needs a history. The first symptom is simultaneous editing: two people open the file on the same day, one saves their version and the other loses what they had typed. The second is the question about the past: somebody wants to know what position a person held on an earlier date, and the sheet only shows today.
There is also an access limit that hardly anyone considers until they need it. A shared file does not distinguish between the person who may see salaries and the one who only needs the phone list. Once that distinction matters — and in HR it almost always does — the file has stopped being the right tool. That is where a system such as Kardex Tauro earns its place: it keeps the change history, enforces permissions and answers questions about past dates without depending on loose copies.
Until then, the sheet does its job. The rule is simple: if the master file is updated once a month and read every day, the template is enough; if it is updated daily and read by several people from different desks, you are already in software territory.
The screenshot shows the master file with its columns in order, the three records from the example and the footer with the counts. Before you start filling it in, check three things: that the status column is colour-coded and agrees with the footer, that the ID numbers sit flush left — the sign that they are stored as text — and that there are no half-finished rows between one record and the next. Get those three right and the rest is discipline.
Why it pays to keep it current
A personnel master file is not the prettiest document in the business, but it is the one that answers the awkward questions: who started this week, who left last month, which provider the injured worker is enrolled with, how many people are really on payroll. Once that information lives in a single sheet with a single set of rules, it stops being a memory problem and becomes a fact you can look up.
Download it, load your real people into it, and update it on the same day anyone joins or leaves. With that habit, the sheet maintains itself.
⬇ Download the template (Excel .xlsx)
