Raw Data (Attendance Sheet from Software)
1. Introduction
The above data is received from software that maintains attendance & provides daily attendance in excel format, but this data is not useful to find out daily attendance in a short summary. it is showing very vast data which is looking very complicated. so here in the below video, I have explained how to convert the attendance data from vertical format to horizontal format properly.
2. Purpose
from the above data, we will prepare a useful MIS report which will summarize the attendance of each employee in one line. This report will be valuable for our office use,
3. Command or Formula Used
You can download this data sheet from the below given “Download”. Actually, in this data we have received Employee’s Code, Employee’s Name, Date of Attendance & Attendance Status like Present, Absent, WeekOff, Earning Leave, Casual Leave, etc.
So to make this MIS report we used Pivot Table to Summarize Employee Code, Employee Name & Dates, then we used Vlookup to find the Attendance status like present and absent, etc., and most important the use of Lock the Criteria with Dollar sign is very important. We were facing a problem that if there is no record of any person in particular data then our vlookup formula was showing an error, so to remove that error we used IFERROR Formula in Excel Sheet. So what are you waiting for, go down check the full video, and also subscribe to our channel on YouTube?
Formula: =IFERROR(VLOOKUP($A2&$B2&C$1,Attendance!$A:$E,5,0),””)
Also Learn: Use of Dollar Sign in Excel (in Hindi)
Output (Final Report)
Video Tutorial
Download Practice File
Join Our WhatsApp Group
Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]
Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.
