How to Make a Rota

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.

rota template

In C1, press CTRL+; (hold down the CTRL key then press the semicolon key) to enter today’s Date.

enter todays 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.
See also  How To Insert Variance-Covariance Matrix

shift fill handle

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.

how many employees on shift

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.

move or copy

Excel opens the Move or Copy dialog box, click move to end and Create a copy, OK.

Move or Copy dialog box

Excel opens a copy of the tab; the new tab name has a (2) at the end.

new sheet

In this new tab delete the data, cells D4 through N10. All the formulas should return to O.

formula return to zero

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.
See also  Create Dynamic Drop-Down Lists with INDIRECT in Excel

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.

save as more options

Click the “Tools” drop-down menu and select “General Options.”

general options

In Password to modify enter Editor, tick Read-only recommended, OK.

setup up the password

Reenter the password, click Ok.

reenter password

The Confirm Save As window opens, click Yes.

confirm save as

To check if the password is working, close the workbook.

Open the workbook, a Password box opens.

password box

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.

read only message

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.

open read only

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!