Employee Relations

How to Create a Basic Excel Shift Rota

person working on laptop

Are paper timesheets giving you a headache? If so, then why not create your own basic excel shift schedule template?


This is probably the most simple excel shift rota to build. As such, it lacks a lot of sophisticated functionality and requires manual data entry. If you want to make things really easy, we suggest using RotaCloud – their software integrates perfectly with People HR’s HR software, and building shift rotas is a piece of cake.


For now though, here is the basic way to create an excel shift schedule template (that also works for most spreadsheet programs):


 * You will need 22 columns in total.

 * Label the first column ‘Name’.

 * Label the next three columns: Monday IN. Monday OUT. Monday HOURS.

 * Repeat step 3 for each day of the week.


From here, you can simply use this as your template and manually enter the data yourself. To add a bit of automation, however, you can use a FORMULA to ask the HOURS cell to calculate the total hours worked for each person on each day. To do this, you’ll need to:


 * Select the HOURS cell.

 * Type the following: =SUM(

 * Click the IN cell

 * Type the following: –

 * Click the OUT cell

 * Type the following: )


The end result 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.


Demo: Discover how People HR can help you manage timesheets and track attendance 



What Happens Next?


Providing you have done everything right, you should be able to simply add each employee’s name to the NAME column, then add their start and finish time for each day of the week – the hours for each day will automatically pop up.


Of course, we highly recommend avoiding spreadsheets altogether and trying an automated HR solution, such as People HR’s HR system or RotaCloud’s shift softwareWatch this 4-minute video to discover more ways to make HR easier