Payroll register template in Excel

Payroll register template in Excel

Every time a pay period closes, the same question comes back: how much was paid to each person, for which position, and with which deductions. When that information lives in loose papers, in email threads and in a different sheet for every month, the answer arrives late and almost always incomplete. This payroll register template in Excel gathers that record in a single sheet, employee by employee and period by period.

The file brings the columns of a register of payroll already paid, automatic totals, a summary block for the period and a small table of net pay by position. It is an Excel file (.xlsx) ready to download and use the same day.

What this payroll register is and who it is for

Let us be clear from the first line: this format is a register of payroll already paid, not a payroll settlement. The file does not calculate benefits, does not calculate contributions and does not apply any table. Deductions and contributions are keyed in exactly as they are delivered by whoever settles the payroll; the sheet receives them, sorts them and adds them up.

It works for any business that pays a team and wants its supporting record in order: shops, workshops, restaurants, service firms, construction sites and administrative offices. It is also useful for the accountant who receives payroll information and needs to know where each figure comes from before recording it.

The starting point is simple: if someone asks how much a person was paid in the period, the answer should sit in one row, with the salary, the other earnings, the deductions, the contributions and the net pay. That is exactly what this template puts in plain sight.

A register, not a settlement

The settlement is the document where the payment is defined: there the values are set according to the criteria of whoever settles the payroll. This register serves a different purpose: it records what has already been defined and paid, so it can be consulted later, compared from one period to the next and used to answer a review with the support in hand. That is why the file has no opinion about the values: it receives them and keeps them in order.

Who it is meant for

The template is meant for whoever keeps control without a large system: the owner of the business, the human resources person who builds the monthly record, the accounting assistant who consolidates several locations, or the outside accountant who receives the information and wants to check the figures before passing them to the books. In every case the idea is the same: one row per employee and period, with everything that was paid in view.

What the file includes

The file is built to work straight through, with the input columns kept apart from the ones that calculate on their own, and a summary block at the end. This is what it brings:

The columns of the sheet

The sheet reads from left to right: first the period and who the person is, then the position and the days paid, then the salary with the other earnings, and at the end the result. These are the columns:

How the calculation works: one period with three employees

The exercise can be followed row by row. In the period there are three employees: one in sales, one in administration and one in production. Each one is recorded with a salary and, where they exist, other earnings. The total earned comes from the addition; the deductions and the contributions are keyed in exactly as they were delivered by whoever settled the payroll; and the net pay is what remains after subtracting them.

The totals for the period confirm the addition: salaries 4,300,000, other earnings 250,000, total earned 4,550,000, deductions 340,000, contributions 850,000 and net pay 3,360,000. The summary block shows those same numbers in order, and the small table by position splits the net among sales, administration and production.

Notice something important: the sheet does not deduct or contribute on its own. It only adds and subtracts what it was given. If a value comes through differently in the settlement, the register will repeat it differently; that is why checking every row against the pay slip is part of the job, not an optional step.

Step by step to fill it in

1. Download the file and decide which period you are recording; write it the same way in every row of that block.
2. Write each employee's name always in the same form, together with the full identification number.
3. Choose the position from the list; if a job does not exist yet, add it to the list before you keep typing.
4. Record the days paid, the salary for the period and the other earnings for each person.
5. Key in the deductions and the contributions exactly as delivered by whoever settles the payroll.
6. Check that the total earned and the net pay match the pay slip, and note in the comments anything that departed from the usual pattern.

Tips and common mistakes

• The name written two ways. One spelling in one row and another spelling in the next read as two different people when you filter, and that person's net ends up split in two.
• The position typed by hand. If the job is written free, the small table by position ends up with a long list of variants and loses its usefulness.
• Mixing periods in one block. Keep one block per period and separate them clearly; that way the foot totals always mean something.
• Confusing total earned with net. Total earned is what was accrued before deductions and contributions; net is what is actually paid. Mix them up and the control loses its point of comparison.
• Leaving deductions or contributions blank. If a person had no deductions, write zero: an empty cell reads as missing data, not as the absence of a deduction.
• Fixing only one column. If the salary is adjusted and the deductions and contributions are not reviewed, the net ends up off balance without anyone noticing.
• Not keeping the pay slip. The register puts the information in order, but the support is still the pay slip; file it together with the period.

When it is worth moving to software

The template performs very well while payroll is a control exercise: a few employees, one or two locations, one person who reviews and signs. Over time the signs appear that the sheet is falling short: several cost centres, payments on different days, commissions calculated separately, people joining and leaving in the same month, and more and more questions about what was paid and when.

At that point it is worth resting the record on a system that takes the information and keeps it available by employee, by position and by period without rebuilding the sheet every month. Kardex Tauro is the program we use for that: it takes the movements, orders them and leaves the reports ready, while the template keeps working as a backup and as a control over what was paid. If your operation is still simple, stay with the file and fill it in with discipline; when the volume grows, that same tidy register is the best base to migrate to Kardex Tauro without losing history.

Keep in mind as well that this register is an internal control: it does not replace any official record or any filing, and it makes no promise of legal validity. Its value is day-to-day control: how much was paid, to whom and for which position.