How to Create a Basic Excel Shift Rota

If paper rotas are giving you a headache, creating a rota in Excel might be the best way to get your business's timesheets started online. Excel rota’s are the simplest form of online timesheets that can benefit both your management team and your staff.

This is probably the most simple Excel rota you can create. As such, it does lack a lot of sophisticated functionality such as extensive formulas, and it requires some manual data entry. However if all you need are the key time tracking elements, creating a basic rota in Excel is the best place for you to start.

In your staff rota, there are pieces of employee information that you should include no matter what. This will help you have complete visibility of the employees that are on the sheet, making a regimented HR process that will make your team's life easier. Here are the pieces of information you need:

• ​Employee name
• Date​
• Shift time (start & end)
• Task
• Employee title

You can add your own additional information as you start to customise and expand the Excel spreadsheet. 

How to create a rota in Excel?​​

Here is the basic way to create an Excel rota:

You will need 22 columns in total

1. ​Label the first column ‘Name
2. ​Label the next three columns: Monday IN. Monday OUT. Monday HOURS​
3. ​Repeat step 3 for each day of the week​
4. ​Under the ‘Name’ column, add an employee name.
5. ​Under the employee name, you can add their job title (if needed)
6. ​From here you can manually add in the employee shift start and end times throughout the week
7. ​If needed you can add in the task or duty the employee needs to do that shift against the ‘job title’ row
8. ​In a final step, you can customise your Excel rota with formatting that makes it easier to read

​How to add automation to your Excel rota template?

​To add a bit of automation into your rota in Excel you can use a FORMULA to ask the HOURS cell to calculate the total hours worked for each person on each day. This will make it easy to see the total amount of hours that your employee has worked each day, and ergo across the entire week.

To do this, you’ll need to:​

1. ​Select the HOURS cell
2. ​Type the following: =​
3. ​Click the OUT​ cell
4. ​Type the following: –
5. ​Click the IN​ cell
6. ​Press enter and repeat across all HOURS cell

The end result Excel formula will probably look something like this: =SUM(A2-A3).

​You can apply this formula for each person in the NAME column, and for each day of the week. At the very end of the spreadsheet, you can add all of the employee hours together using a formula to know exactly how much each employee has worked per week.

1. ​At the end of the week, add a TOTAL HOURS column​
2. ​Add an equals (=) to the end cell
3. ​Click on each HOURS cell of that employee, typing in a plus symbol (+) between each
4. ​Press enter, and you should have the total hours that employee has worked that week
5. Repeat for every employee​

Now that you’ve gotten to grips with creating a rota in Excel for one week, you can duplicate the sheet to ensure you have a rota for every week in the year. The Excel rota template can be amended as your business changes, but make sure to keep your old sheets. If you delete your week sheets, you won’t have a copy for audits, whether by yourself or by an external company.

Excel rota frequently asked questions

Why should I use Excel for staff rotas?​

​Excel rotas are easy to access and amend, and most often businesses have already invested in Microsoft software and therefore are able to use Excel for free. Excel formulas can be used to create a bespoke rota spreadsheet, allowing you to make rolling rotas or customised calendar rotas. You can also use other free to access spreadsheet softwares, or softwares such as RotaReady.

Is an online rota better than a paper rota?

Yes, definitely. An online rota such as an Excel rota or a software is much easier to amend than a paper rota, allowing you to swap shifts and make comments about sickness in a matter of seconds. You can also share an online rota much easier with your employees, so they have full visibility too. And with the benefit of it being environmentally friendly by not using lots of paper, it’s definitely worth transferring your rotas online!​

Next steps

From our guide, you should be able to create rotas in Excel as and when you need it, making your working life, and your employee’s working lives much easier. And with additional research you might be able to make it even more efficient.

However, if you’re looking for an even easier solution, PeopleHR’s timesheet software is the choice for you. Our timesheet software offers a flexible way to complete rotas, manage employee time and diligently track tasks. Whether you want to keep it simple with clocking in and out, or take the next step with geo-fencing clocking in, we have the tailored rota system to suit your business.

​Get started with our timesheet software for free today with a free trial!

​Further reading

If you found this article interesting and helpful, you may want to continue reading with a few of our other articles:

• ​Where should you be storing employee records?
• How HR software can reduce business costs and support employees: An honest look
• What is TOIL and how does it work?

​​​​​​​​​​​

By Jake Fields New Business Sales Representative

Jake Fields is a New Business Sales Representative at Access PeopleHR. With a diverse background spanning customer service, training, and sales, he is a seasoned professional in all things HR software. Jake's global training experience has cultivated strong client relationships across a range of industries. A true people person at heart, his mission is to provide tailored solutions and support individuals throughout their HR journey.