A rota, also known as a schedule or shift schedule, is a plan that dictates when employees are working and what their duties will be. Most businesses use some form of a rota to ensure that their employees are working efficiently and effectively. People traditionally created rotas using pen and paper, but there are many benefits to creating them in Excel.
We will cover the definition of a rota, the advantages of using Excel for rota creation, and its benefits. We will also provide information on how to create a rota in Excel and the industries that can benefit from its use.
What information do you need to create a rota in Excel?
To create a rota in Excel, you will need the following information:
- Employee name
- Employee shift
- Task
- Date
- Time
Once you have this information, you can create a rota in Excel in minutes. Let’s create a rota for a small hotel. Open Excel workbook How To Create A Rota In Excel.xlsx.
In C1, press CTRL+; (hold down the CTRL key then press the semicolon key) to enter today’s Date.
In the worksheet tab enter the day of the week.
The manager wants to track the hours worked by employees and the number of employees on shift.
- Use the COUNTIF function in cell O4. Enter =COUNTIF(D4:N4,”x”). The manager works 11 hours today.
- In D11 enter =COUNTIF(D4:D10,”x”); the cell lists 4 for the number of employees working today.
- Left-click the fill handle in that cell, drag it along the bottom row to N11.
We need to total the hours for all employees, click in O11, hold down the ALT key and press the + (plus sign). Excel will total the numbers above that cell; this should be 52 hours.
Now that we have added the formulas the manager can see how many employees are on shift each hour how many hours each person is expected to work today.
To plan the rota for the rest of the week the manager can make a copy of today’s plan and amend it as necessary.
Right-click on the Data tab, choose Move or Copy.
Excel opens the Move or Copy dialog box, click move to end and Create a copy, OK.
Excel opens a copy of the tab; the new tab name has a (2) at the end.
In this new tab delete the data, cells D4 through N10. All the formulas should return to O.
To plan the rota for the rest of the week, the manager can create multiple copies of the blank template. They can then fill in the hours and make adjustments for absences or time off as needed.
Printed vs online rota
There are a few key benefits to using an online rota as opposed to a printed one.
- For starters, it’s much easier to make changes to an online rota. If someone falls sick or needs to take a last-minute holiday, you can simply log in and swap their shifts around without having to reprint the entire rota.
- An online rota is also much more flexible in terms of who can access it. The rota can be hosted on a webpage containing your team’s information. With a printed rota, team members must be on-site to view it.
- With an online rota, all you need is an internet connection, and you can check it from anywhere in the world.
- Finally, an online rota is more environmentally friendly; no paper is necessary.
Making a rota Read-only
It’s a good idea to restrict the ability to edit the rota to a couple of team members. Managers need to be aware of any changes to the schedule so they can adjust the rota to cover absences as necessary.
One option to prevent edits is to save the workbook as read-only. Those with edit rights will have a password that allows them to make changes.
On the ribbon click File. Select “Save As.” In the Save As window, click More options.
Click the “Tools” drop-down menu and select “General Options.”
In Password to modify enter Editor, tick Read-only recommended, OK.
Reenter the password, click Ok.
The Confirm Save As window opens, click Yes.
To check if the password is working, close the workbook.
Open the workbook, a Password box opens.
Employees can click Read Only to view the latest rota. The workbook opens as normal. Should an employee type in a cell, and try to save the changes, the employee will get the message below.
If a manager needs to edit the rota, they must enter the password when opening the workbook to gain write access. After entering the password there is another confirmation message. Manager should answer No to the message below if they wish to edit the workbook.
The benefits of creating a rota in Excel are clear – it is an easy to use, time-saving tool that can help you to organize your rota efficiently and effectively. If you are looking for a way to manage staff shifts, then Excel is the solution for you!
