Anyone can use Excel Spreadsheet for this either as a sole proprietor or as a member of the school administration. The maintenance of data in handwritten registries can occasionally be exceedingly challenging and disorganized since errors are hard to spot and unable to be corrected. Do check out: attendance register format
The following is an illustration of how to create an Excel attendance sheet for each month. Follow the instructions below:
ADD SHEETS AS PER THE NUMBER
Add sheets as needed to make the desired number of months.
ADD THE LABEL
To input, the names of the staff or pupils, add the labels like “names.”
FORMAT THE DATES IN EACH SHEET
The next step is to add dates to the spreadsheet. After inserting the date inside a cell, right-click the mouse and choose the tab “format cells,” allowing you to choose your preferred date format by selecting “custom” underneath the number tab. When you’re done, choose the date column and navigate through all the rows until you reach the one you desire to capture. To eliminate weekends, use the “fill weekdays” checkbox.
FIX THE ATTENDANCE INPUT IN THE SHEETS
To use these quick forms for collecting attendance, it is crucial to configure the input like P for present and A to indicate absent.
- Choose a cell.
- Click “data” from the ribbon that opens, then click “data validation” to see the settings options. Click “list,” and then type “A” and “P” in the source column.
- Choose “OK” to save the changes.
- To input, the data, duplicate the validation in each cell.
LOCK OTHER CELLS EXCEPT WHERE ATTENDANCE HAS TO BE FILLED
After choosing the region, choose the protection option, then choose the locked option under the cell formatting menu. The entered data and formulas will be encrypted as a result.
EVALUATE THE DAYS ON WHICH THE EMPLOYEES AND STUDENTS WERE PRESENT
- Utilize the Excel formula here.
- =COUNT(dates)- COUNTIF(attendance range, “A”); till and except when the absence is previously noted in the worksheet, this will classify them as a present by default.
SECURE THE SHEET
Choose the “protect sheet” menu which will open underneath the review tab and then select all the authorities you wish to grant. Only the columns with rights are editable once the sheet has been completely locked. All months of the document may be used to complete these procedures. However, utilize the next steps if you would like to view all the information in one location:
- Set up a new spreadsheet and label the headings well with names and months.
- Write “=VLOOKUP($A3, INDIRECT(C$2&” in the formula!
- You should insert $A$3:$B$12″),2,0 in the adjacent cell after entering all the necessary headings.
- If you’d like, you could use the sum function to figure out the person’s entire workday.
- You will be presented with an Excel attendance management sheet. You could structure and alter it whatever you like, and you may change it to account for paid time off or pay.