Attendance Software Data Not Managed Properly in Excel | Convert Vertical Data to Horizontal Format in Excel

Raw Data (Attendance Sheet from Software)

Attendance Software Data Not Managed Properly in Excel

 

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)

Convert Vertical Data to Horizontal Format in Excel

Video Tutorial

 

Download Practice File

✪ DOWNLOAD FILE 

 

Leave a Comment