You’ve been struggling for weeks to create the perfect Employment Equity Dashboard. After countless hours, infinite cups of coffee, blood, sweat and tears, you’re finally ready to present your Dashboard to your management team.
You muster up the courage. You click the send button and of it goes. Within minutes you receive your first response…”Please automate”. And then the next response, “Nice, but please automate”. One after the other, “automate”, “automate”…ugh.
Back to the drawing board.
But no need to fret…..Excel to the rescue!!
In a prior blog post I shared a quick tutorial on “automating” some of the manual data processes involved in compiling an annual Employment Equity Report.
In this post, you can download a free Employment Equity Dashboard, that “automatically” completes most of the EEA2 in accordance with the source data sheet.
In addition, this Dashboard makes provision for interactive charts, that you can change on the fly.
If you’d like to learn more about the “=countifs” function, “Named Ranges” and “absolute references” – used in the creation of this Dashboard – please feel free to follow along in the tutorial below.
You are more than welcome to download the Dashboard here: EE_Dashboard_2017, and get started with your EE Dashboard journey.
This template contains the following sheets:
- EE_FrontPage – This is the “Landing Page” of the Employment Equity Dashboard that includes a few generic graphs to get you started. You can create more graphs as you go along. (For tips and tricks relating to Slicers and PivotCharts, stay tuned for future tutorials)
- EE_Pivots – A collection of pre-populated PivotTables, used to create some the graphs on the FrontPage.
- Employee_Data – Basic employee data used throughout the EE Dashboard. You can replace this data with your own, but just make sure to maintain the same formatting.
- Training_&_Development – Data relating to training that employees attended, in line with EE targets. You can replace this data with your own, but just make sure to maintain the same formatting.
- Recruitment – Data relating to our fictional Company’s staff recruitment. You can replace this data with your own, but just make sure to maintain the same formatting.
- Promotions – Data relating to employee promotions. You can replace this data with your own, but just make sure to maintain the same formatting.
- Terminations – Data relating to employee’s who were terminated. You can replace this data with your own, but just make sure to maintain the same formatting.
- EEA2_SECT_A – Section A of the EEA2. Certain areas make use of checkboxes – such as the Business Type area.
- EEA2_SECT_B_to_E – Sections B to E of the EEA2 report. Most of these tables are generated automatically based on the source data sheets mentioned above.
- EEA2_SECT_F – Section F of the EEA2. This sheet makes use of drop-down menus, in order to complete – “Yes”, “No”, “X”.
Important to Note – Disclaimer:
- Some tables could not be automated and will still require manual data processing, including but not limited to, Annual Numerical Targets and Goals.
- For this tutorial, we’ll pretend the submission year is 2016.
- All information contained in the Excel template is purely fictional. Should there be any resemblance to anyone you may or may not know – within this template – it is purely coincidental.
- The template may only be used for educational purposes and I accept no responsibility for any losses incurred as result of using this template.
- You should always verify on the Department of Labor’s website whether you are using the most recent Employment Equity templates.
What is “=countifs” ?
“=countifs” is an advanced function used in Excel to count data, based on certain parameters.
Excel also makes provision for a standard “=count” and “=countif” function.
Say you have a basket of 5 green, 5 red and 5 yellow apples. You want Excel to only count the green apples and ignore the red ones.
If you only used the “=count” function in Excel, then Excel would simply count the number of apples, regardless of their color.
To count only the green apples, we have to “tell” Excel to -count- the apples, only if they are green.
if an apple is green, please count the apple along with other green apples and provide me with the total number of green apples. if the apple is not green, please ignore the apple and dont count it. --- =countif(apple,"green")
You have therefore given Excel a parameter of “green”. The “=countif” function will try its best to comply with this parameter.
We want to count only the number of Females in our Excel spreadsheet below:
In this spreadsheet, in the Gender column, we use the letters “M” & “F” to specify if an employee is either “M-ale” or “F-emale”.
This is what we told Excel to do:
=count all the cells in this column if the cells contain the letter F
In this spreadsheet, the cells we are referring to are in column E – under Gender.
If I want to select all the cells in column E then our reference would be “E:E”, meaning the first to last cell of column E.
=count all the cells (E:E) in column E if the cells (E:E) contain the letter F
The letter “F” is classified as a text variable, meaning it is a letter of the alphabet. Because it is a letter (and not a number) we have to place it between two ” ” for Excel to understand what it is.
In the first example, the letter F was placed between ” “, and the result was a 110. This indicates that we have 110 Female employees.
In the second example, the letter F was not placed between ” “, and the result was 0.
Note: We type the “=countif” formula/function in the cell where want the answer to be displayed. In this instance, we wanted the answer to be displayed in cell G2. So we typed the formula into cell G2. The answer “110” is therefore displayed in cell G2.
=countif(E:E,"F") - Correct =countif(E:E,F) - Incorrect
But what if we have multiple parameters? What if we wanted to count all Black South African Males in the Company? That’s a whopping 3 parameters!!
Luckily, we don’t have to add multiple “=countif” formula’s together manually, because Excel has a built-in function called “=countifs” – note the “s” at the end.
This is how “=countifs” work:
Our “=countifs” formula would look something like this:
If an employee meets all 3 criteria, he will be counted. If not, he will be skipped.
What is a “Named Range”?
When we have several tables, that each contain multiple columns, spread over numerous spreadsheets, it becomes very difficult to analyze data and include these columns in formulas/functions if we can’t remember their exact location.
To make our lives a little easier, and to simplify formulas, we make use of “Named Ranges”. We give a column a “special name”.
Although column D has its own heading – “Race”, I have more than one spreadsheet that contains this heading.
To make this column unique, or to differentiate it from the other race columns in the other sheets, I gave the whole column a new name: “EmplD_Race”.
“EmplD_” – The sheet is called “Employee_Data” + “Race” – Because it is the “Race” column = “EmplD_Race”
All you have to do, is highlight the whole column that you wish to assign a Named Range to and then type the unique name in the box above – as indicated by the red arrow. (Remember to press “Enter” after you typed the name of the named range)
If we were to create a formula, it would look something like this:
If I do not make use of “Named Ranges”, my formula’s will still work, but they will be very difficult to debug if something were to go wrong.
I want to count all African Males in my database:
As you can see, the formula is quite a mess and would be very difficult to debug if were to add even more parameters.
If you intend to play around with “=countifs”, or any other formula for that matter, I would strongly suggest you start making use of named ranges.
Removing / Editing Named Ranges
- Select the “Formulas” tab on the Ribbon.
- Click in “Name Manager” under the “Formulas” tab.
- The Name Manager window should popup.
- You will see a list of all named ranges in your workbook.
- Select the Named Range you wish to edit and then click the “Edit” button, or
- Select the Named Range you want to delete and then click the “Delete” button. You will only delete the name of the named range, not the actual data in the data sheet.
When you are done, you can simply click “Close”.
The Dashboard provided for this tutorial, contains quite a few formulas in order to “automate” to a certain degree.
You are welcome to scratch around, alter formulas, alter data, change colors or insert your own data.
I will run through one example of a “=countifs” formula used within the Dashboard. If you get stuck along the way, you are more than welcome to get in contact with me via InMail or in the comment section below.
Let’s jump in.
We’ll be completing Section B 1.1 of the EEA2.
You’ll find it under the EEA2_SECT_B_to_E sheet in the EE Dashboard.
The first cell we would like to complete has 3 parameters:
Below are the parameters for our first “=countifs” (red arrows):
- [Occupational Level] must be “Top Management”
- [Race] must be “African”
- [Gender] must be “Male”
Or in programming terms:
Below are the parameters for our second”=countifs” (purple arrows):
- [Occupational Level] must be “Top Management”
- [Race] must be “Coloured”
- [Gender] must be “Male”
Or in programming terms:
In the above examples, we provided Excel with the parameters between ” “. But because we are utilizing an already developed Dashboard, we will use specific cells in our Dashboard as the parameters (instead of specifying the parameters between ” “).
Note: Because we defined Named Ranges, we can type the Named Range’s name directly into the formula. The moment you start to type the Named Range’s name, a drop-down window will appear with all the named ranges in your workbook.
You can simply select the named range you want to use and double-click it.
Let’s analyse our first formula:
This formula will count all African Males that are in Top Management positions, from our “Employee_Data” sheet.
EmplD_Occlevel,B12 : If the Occupational Level in the Employee Data sheet is = to the Occupational level in cell B12 (in this case - Top Management), then proceed to the next parameter.
EmplD_Gender,C10 : If the Gender in the Employee Data sheet is = to the Gender in cell C10 (in this case - Male), then proceed to the next parameter.
EmplD_Race,C11 : If the Race in the Employee Data sheet is = to the Race in cell C11 (in this case - A for African), and all other parameters have been met, then count the employee.
The final addition to our formula is to add absolute cell references.
An absolute reference makes it possible for us to copy and paste formulas, to different cells, without have the rows and/or columns change within our formula.
From This: =COUNTIFS(EmplD_OccLevel,B12,EmplD_Gender,C10,EmplD_Race,C11) To This =COUNTIFS(EmplD_OccLevel,$B12,EmplD_Gender,$C$10,EmplD_Race,$C$11)
Did you not the “$’ sign in the second formula?
What this “$” sign is “telling” Excel, is that as I copy the formula downwards – to the other cells -, the race and gender values will still remain in cell C10 and C11 respectively. The occupational level value will remain in column B, but the row will differ – That is why there is a “$” sign before B and not before 12: i.e. $B12 and not $B$12.
If we do not “tell” Excel to use absolute references, then our formula’s will end up looking like this:
=COUNTIFS(EmplD_OccLevel,B12,EmplD_Gender,C10,EmplD_Race,C11) =COUNTIFS(EmplD_OccLevel,B13,EmplD_Gender,C11,EmplD_Race,C12) =COUNTIFS(EmplD_OccLevel,B14,EmplD_Gender,C12,EmplD_Race,C13)
As you can see, in the second formula, Excel will think that the Gender value is now in cell C11, which is not true. The Gender value is still in cell C10.
The correct formulas will look something like this:
=COUNTIFS(EmplD_OccLevel,$B12,EmplD_Gender,$C$10,EmplD_Race,$C$11) =COUNTIFS(EmplD_OccLevel,$B13,EmplD_Gender,$C$10,EmplD_Race,$C$11) =COUNTIFS(EmplD_OccLevel,$B14,EmplD_Gender,$C$10,EmplD_Race,$C$11)
The only value that is allowed to change is the Occupational Level ($B12, $B13, $B14) – which is in different rows.
I know it’s a lot to take in at once. But as soon as you start to play around with the Dashboard, you’ll get the hang of it pretty soon.
Try adding a =countifs formula to the template, that will test for Foreign National employees.
For tips and tricks on how to use Slicer Graphs, like the ones on the Dashboard FrontPage, stay tuned for upcoming tutorials.
I hope that this tutorial (and the Dashboard) was both educational and helpful.
I would love to hear your comments and suggestions in the comments section below or via InMail.
Until next time…