Paystub Excel Template
While all employees enjoy receiving their paycheck, the pay stub that accompanies the money is also essential. This Paystub Excel Template provides details of their pay from the pay period. And while pay stubs are not mandated by federal regulations, over half of the states require pay stub records. Of those, eleven states require employers to provide a physical copy of a pay stub, instead of a digital one.
But, even if you don’t operate your business in one of those states, the Fair Labor Standards Act requires you to keep accurate records of hours worked and payments made. In fact, the law lists fourteen different basic records the employer must maintain for each non-exempt worker.
These records are the information typically contained on a pay stub. So even if you don’t hand your employees a copy of them, you still need to keep a record. That means you need a system for tracking employer pay, deductions, and hours worked.
This Excel payroll calculator can help you streamline the record-keeping process with a simple database. And, if you want to create pay stubs to print them out and give them to your employees, there’s a straight-forward method of doing that.
While Excel is primarily a spreadsheet, it also has the ability to be used as a simple database. The key to making this employee database excel template work properly is in those database features. Then, the pay stub uses VLOOKUP to pull data from the database.
Template Contents
When you download the ZIP file for this employee database excel template, you will receive two different spreadsheets:
- PayStub Excel Template with Database by Employee Name
- PayStub Excel Template with Database by Employee ID
Both of these spreadsheets contain multiple worksheets. In each pay stub template download, you will find:
- Directions for use,
- A setup sheet to customize your template,
- An employee salary database to enter employee records, and
- A salary slip template so you can print pay stubs for each employee
The only difference between the two files is on the Salary Slip Template. They are nearly identical in use, but one key difference sets them apart.
In the PayStub Excel Template with Database by Employee Name spreadsheet, you create employee salary slips based on the employee name.
In the PayStub Excel Template with Database by Employee ID spreadsheet, you create them by using the employee ID number.
You will need to decide which makes the most sense for your employee database management system. Once you decide, you can begin customizing the template and putting it to use.
Directions
At the back of each template, you will find a worksheet with directions for use. These worksheets contain a simplified version of these written directions.
There are brief directions for the Setup Worksheet, Salary Slip Workbook, and the Database Worksheet. You will also find some helpful notes about all three, so you can use the worksheets more efficiently.
The pay stub template comes with sample data to make it easier for you to see how the template works, and how you can create pay stubs using your own data. Simply delete sample data to create a blank form.
After you download and unzip the template, you can quickly scan these directions. That way you have a better idea of what you will be doing to prepare your employee pay stubs.
Template Setup
Once you’ve read the How to Use worksheet, you can navigate to the Setup page. This is where you customize your dates, titles, and categories.
The pay stub template covers a twelve-month period, with one paycheck each month. You can adjust the month names and transfer dates to meet your company needs. The transfer date is the date your company sends the money to your employees. This will typically be one to three business days before the money is added to your employees’ bank accounts with a direct deposit.
In the other section, you can track earnings and deductions. This database can track up to eight of those.
In the “Salary Titles” column, you can document what earnings and deductions need accounted for. Then in the “Salary Category” column, use the drop-down menu to indicate whether each particular salary title is an earning or a deduction.
Any changes you make in the Setup form will be reflected in the Excel database and in the employee pay stub generator.
Employee Salary Database
Once you have the correct months, transfer dates, salary titles, and salary categories, you have completed the initial setup process. Now you can begin working on the staff database.
An Overview
When you first open the database, it can be a little overwhelming. Since this form is meant to track employee pay for a full year, there are twelve different monthly sections to record pay. There is also a section for adding basic information about each employee. Here is a zoomed-out image of part of this massive database.
This template has space for 50 employees. You can add additional rows, but you will need to do so before the final row of the database. That way the formulas automatically get added as well.
If you do not need 50 rows, you can also delete rows from the database. Follow the directions in this post to insert or delete rows:
Insert or Delete Rows and Columns
Freeze Panes to Always View Column and Row Headers
Since this worksheet is so large, you may find it beneficial to freeze panes. This locks the rows and columns you select, so they are always visible, even when you scroll. Freezing the panes can help expedite your data entry, since you won’t be trying to remember what each cell is for or have to scroll back over to the headings.
To lock panes, please follow the directions in this article:
Freeze Panes to Lock Rows and Columns
Colored Headings
To help keep you organized and distinguish each section of the database, there are colors on the heading row. The section in purple is for recording employee information. Then each month alternates between teal and brown, to help you easily see where each one ends and the next one begins.
Minimize and Maximize Month Groups
To make the Excel spreadsheet more accessible, you can also minimize months that you are not using. Above each monthly section, in the column heading row, there are plus (+) and minus (-) signs. You click on these to expand or minimize each group.
How to Use
Once you have frozen the panes and minimized any months you are not working with, you are ready to begin entering information in the purple section of the form.
For each employee, you record their:
- Employee ID
- Name
- Title
- Department
- Directorate/Manager
- Bank Name
- Bank Account Name
- Bank Account Number
You will want to use care when entering this information, to ensure precise data entry.
Next, you can navigate to the month group for the current month. Here, you will enter all of the numbers for your employees’ pay and benefits. There is a column for each of the earnings and deductions you included on the Setup page. If you need to make any changes to these column headings, change them on the Setup page. That way the changes are reflected on the whole database and not just the current month.
Once you enter all of the data for the pay period, you can begin to generate employee pay slips.
Salary Slip
The salary slip worksheet is designed to be used as a payroll stub template and allows you to print a customized employee payroll stub for each member of your team.
Here is what the pay stub template looks like:
You will want to update the cell that says, “The Spreadsheet Page” in the upper left-hand corner to show your company name. You can type it in directly, or upload a logo.
Once you change that cell, the pay stub is ready to use. It pulls all of the pay information you entered for this employee and creates a confidential salary slip.
This includes:
- Earnings (salary, meals, transportation, health allowance, etc.),
- Deductions (insurance, tax, etc.),
- Gross pay (the template uses fixed salary, not an hourly wage),
- Net pay,
- Pay period (default is monthly but you can customize the template to fit bi-weekly pay period if needed),
- Transfer date (the date when pay direct deposit was made),
- Banking details, and
- Position details (their title, department, and manager).
You will need to generate a separate pay stub for each employee, and print them one at a time. This is a simple process, requiring only one manual entry.
To begin printing pay stubs, you need to know which version of the payroll processing workbook you are using.
Generate a Pay Slip by Employee Name
If you are using the PayStub Excel Template with Database by Employee Name, you will see a drop-down arrow next to the Employee Name field:
You simply select the correct employee from the drop-down options, and all of the rest of this person’s information will automatically populate. Then this form is ready to print.
Then, select a new name and print the next pay stub. You will repeat this select and print process for each employee in the company.
Generate a Pay Slip by Employee ID Number
If you are using the PayStub Excel Template with Database by Employee ID, you will see a drop-down arrow next to the Employee ID field:
You simply select the employee ID number that you want to view, and then print the form. You can do this for each employee.
Add Employee Pay Information Each Month
Once you finish printing payroll stubs for each employee, you can minimize the month you just completed. Then, when it’s time to run the payroll template again, you can maximize the next month.
To prepare payroll each month, you need to:
- Update any employee personal or payment information,
- Update the pay period,
- Add any new employees,
- Enter all deductions and expenses,
- Generate a pay stub for each employee, and
- Print a pay stub for each employee.
By keeping a full year of records in this single database, you can easily go back and answer any questions about employee pay. You will have the information you need readily available.
Keep the Form Confidential
Since this is an official record of employee salary, you need to keep your database secure. It’s not something you want just anyone making changes to.
To ensure no unauthorized changes are made, you may consider protecting the document with a password and granting only your payroll personnel access. To create a protected worksheet, follow the directions in this post:
Protect a Worksheet
Protecting a worksheet will prevent people from making changes. But they will still be able to open the file and view confidential information. To keep unauthorized personnel from opening the sheet, you can protect the Excel document at the file level.
Depending on the technological system you have set up, you may consider:
- Encrypting the file,
- Requiring a password to open the file, or
- Restricting access to the file with Information Rights Management permissions.
For more details on security for Excel spreadsheets, you can read this post:
Protection and Security in Excel
Track Your Employee Pay
Once you get your protections set up, you will have a confidential way to track employee pay. This will help you abide by state and local employment laws, and record pay details for each employee.
Each year, you can begin a new template, and give employees a chance to update their payment information. Then you can begin using the template to generate pay stubs for each person on the payroll.
-
-
-