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).

Word frequency

How often do repeat certain words in the given text? Which one of them is most often repeated? The answer to this question can be obtained using a few simple techniques, which will be more talk in the text that follows. To begin with, a news post is taken from the link http://www.iib.rs/srl/bizit2018-srl/and we will analyse it to measure the frequency of words occurrence. The selected text should be copied to cell A1 of the new worksheet.

Categories and subcategories

Excel can be a good tool for filling questionnaires and for this purpose are often used structured tables where we keep the results. When the result is one of a few, in advance known, values the input is done by selecting it from the list. Sometimes there is a need to, after choosing value in the first list, affect the items that appear in the second. For example, choosing a category affects the display of related subcategories. How can we achieve this?

Filtering by lists

Slicers are great feature, but unfortunately they can not be used to filter traditional Excel reports. If we use the conditional aggregation functions, in order to calculate the desired values, as well as the data validation technique, we can make simple but effective filters by selecting the values from the list. By selecting the criteria from the list, the automatic update of the values in the report is performed. Let’s see how this works!

Parallel periods

The Power Pivot add-in allows you to make different calculations depending on the time period in which the sales occurred, by writing formulas in DAX language. To use these features you need to have one of the latest versions of Microsoft Office Pro Plus. Since many do not have this feature, it remains only to deal with the traditional lookup functions about which application I’ll write in following „recipe“.