With the advent of big data and the digitalization of society, HR departments are currently undergoing a transformation process and must adapt, particularly through the use of KPIs (Key Performance Indicators), which are presented in the form of dashboards. Excel is becoming the most important tool for creating and communicating these dashboards.
Our expert Pierre-Alexandre Grolleau reveals 7 tips on how to create HR reports with pivot tables in Excel.
Tip no. 1:
Recognizing and identifying HR needs
Every report must respond to a need or a request. Before starting any work with Excel, it is necessary to ask yourself the following questions: What do you want to visualize? What is the strategic interest in tracking a particular indicator?
This essential phase of reflection in advance is a prerequisite for the relevance of your future reporting.
Tip no. 2:
Don’t be afraid of using pivot tables
Pivot tables are often frightening. Yet they are very easy to use and accessible to everyone. Here are some explanations:
• All columns in your database are listed there.
• The line labelling corresponds to what you want to put in a line.
• The column labelling corresponds to what you want to put into a column.
• The filter corresponds to your filter criterion.
• The value corresponds to the calculation you want to perform.
To complete each of these items, simply click and drag. What could be easier! Sometimes 5 clicks are enough to create an effective report or dashboard.
Tip no. 3:
Update your pivot table
As the saying goes: too many pivot tables kill the pivot table.
While pivot tables are a great tool, extreme use of pivot tables can lead to errors and make your HR reports obsolete. Pivot tables do not update automatically and do not necessarily take all databases into account. If you use them, you should therefore update the tables as a reflex. To do this, simply go to your pivot table, then to the “Analysis” tab in the Excel multifunction menu and click on “Refresh”.
It is also advisable to use functions and formulas to maintain your HR tools in Excel.
Tip no. 4:
Optimize your reporting with the help of Excel formulas that are indispensable for the HR department
It is not absolutely necessary to be an Excel expert if you work in human resources. Nevertheless, learning certain functions is essential, as every HR manager will have to deal with them one day. Here is a list:
• VLOOKUP: This allows you to search for elements in a table or area line by line. Example: Search for the name of an employee using their employee ID.
• IF: This allows you to make logical comparisons between a value and the expected result. Ex: If an employee’s salary is €27,000, then enter the salary group “between €20k and €30k”.
• SUMIF: This allows you to add the values of the cells that match the search criteria. Example: Add up the salaries of all male employees.
• SUMIFS: This allows you to add the values of cells that fulfil several specified criteria. Ex: Add up the salaries of all female employees who work in Caen.
• COUNTIF: This can be used to count the number of cells that fulfil a criterion. Ex: Counts the number of female employees.
• COUNTIFS: This can be used to count the number of cells that fulfil several criteria. Ex: Count the number of male employees working in Nantes.
These six formulas are your survival kit. You can use them to fulfil many of your requirements, create reports and complete your pivot tables in Excel.
Tip no. 5:
Improve your HR reports thanks to help and dialogue boxes.
Excel offers numerous help functions that are available via tooltips, dialogue boxes or the formula insertion tool. If you encounter difficulties, take the time to read them in order to understand and solve your problem. This way, you will make progress and optimize your HR reports by finding more flexible formulas than those presented in tip no.4. For example, take a look at the formulas OFFSET or SUMPRODUCT! 😉
Tip no. 6:
Assign names for your cell ranges
Excel offers you the option of naming cell ranges. For example, it is more intuitive to read “Personnel ID” instead of “Sheet1!$A$1 :$A$1852” in your formulas. To do this, go to the “Formulas” tab and then to “Name manager” or “Set names”.
By creating and using names, you can check, change and, above all, automate your formulas and therefore your HR reports more easily.
Tip no. 7:
Switch from automatic to manual calculation
The creation of reports can generate large Excel files with long loading times.
To avoid spending all day watching your files load, you should change the calculation methods instead. By default, Excel is set to update formulas automatically. Change it to manual by clicking on the “Formulas” tab and then on “Calculation options”. This way, the spreadsheet will only perform the calculations at your request. To do this, simply press the F9 key on your keyboard or save your document.
With these tricks, you’ll be able to create almost any type of report, so get started! In HR departments, few employees are proficient in Excel. Dare to stand out from the crowd at work with this easy-to-learn skill.
With MEDIAplus you can become an expert in Excel, if you need more information about our e-learning solutions, feel free to contact us!