Excel for Human Resources

Excel is used to solve many challenges, and among other things it can also serve as a human resources management software. You will find out how to link an employee to a Personnel Data in Excel, and how to easily search them. Many of you who read my blog loved Serbian TV series “Military Academy”. In this “recipe” I used data on several actors from this series (except for names and photos, most of it is fictitious).

Let’s make three worksheets: KARTICA (we will search HR data here), ZAPOSLENI (personnel data about employees) and SLIKE (photos and employee names). In the ZAPOSLENI worksheet, we will create a simple table where you need to enter staff data. In the second column of the table of the worksheet SLIKA, we will copy the names of emloyees from the ZAPOSLENI worksheet. In the first column we will add photos. Every photo needs to be stuck in the cell to the left of the name column. With formatting options, we can arrange and align photos.

Worksheet KARTICA is used to browse employee data. In the top we will leave an empty space for image placeholder. Then we need the field by which the selection is made. We will use the Data Validation option to associate the desired Excel cell with a list of employees’ names and surnames.

Finally, in the table below we will display employee data. Employee ID is calculated using the following formula:

=MATCH($B$2;ZAPOSLENI!$B$2:$B$6;0)

The MATCH function will return the regular number of the employee to the list, which is actually the ID. In the event that the ID is not a number we would write a slightly more complex formula using the combinations of functions INDEX and MATCH. After having the ID, it is easy to write several VLOOKUP formulas, using which we find the personnel data, depending on the selected name and surname.

To add a photo to the worksheet KARTICA you need to select one of them in the SLIKA worksheet, more precisely to position it in the cell in which it is located. Then copy the contents of the cell, go to the place in the worksheet KARTICA where we should add the image. Finally, open the Paste Special menu and add the image to the position as a link. If you go to the link you will see a formula that gives you the knowledge that it shows on the cell from where we retrieved the image:

=SLIKE!$A$1

Our goal is to change the image and personnel data each time we change the employee in the electoral list. In order to do this, we need to add a reference to a photo that changes depending on the selection. We do this by entering into the Name Manager, adding a new name, and entering the formula as a prefix:

=INDIRECT(“SLIKE!A”&MATCH(KARTICA!$B$2;SLIKE!$B$1:$B$5;0))

This formula forms the address of the cell in which the photo is located, and then it is assigned the image “slika”. In the end, it is only necessary to click on the photo in the SLIKA worksheet, and then enter the “= slika” instead of the previous formula. Additionally, we can create and format the report until it gets the desired form.