In this example, a dynamic and interactive dashboard was designed for a company that did not use Python, Power BI (at the moment) or Tableau, to monitor the evolution of weekly vacancies across all the group. This economic group has more than 10,000 employees across 7 different companies and the HR division was having hard times with keeping up with how many vacancies the group had, in what status they were, the reasons behind candidate rejection, occupied and authorized dotation, and many other KPIs more.
For the elaboration of this dashboard, a combination of VBA, formatting, pivot tables and advanced formulas are used. With the “Update” button located on the top right corner, the user is able to, by a VBA macro, capture all available vacancies for that week from a shared folder on the company’s network. This vacancies were generated with another VBA macro made by myself.
To show the values in the dashboard, the cells take values from another sheet (hidden, to prevent data mishandling by users and guarantee correct funcionting of the tool) that is full of Pivot Tables and calculated fields. The dashboard allows the user to choose between the company of its interest, and further filter depending on what vicepresidency, area or department they want to see by the use of slicers, which is the first thing shown in the video.
Lastly, for confidentiality reasons, the names of the companies, vicepresidencies, areas and departments were masked. Excel’s default language is set to spanish, as this was the client preferred language to use.