Before we get started:
- You can download the Excel source data for this tutorial here: EE_Source_Data
- If you are not familiar with Employment Equity documents, legislation or processes you can find more information from the links below:
- Employment Equity Act
- EEA 2 – Employment Equity Report
- EEA4 – Income Differential Statement
- EEA9 – Occupational Levels
Disclaimer: All information contained in the Excel template/source data file is purely fictional. Should there be any resemblance to anyone you may or may not know – within this template/source data – it is purely coincidental. The template/source data may only be used for educational purposes.
You should always verify on the Department of Labor’s website whether you are using the most recent Employment Equity templates.
What is Employment Equity?
(If you are familiar with EE you can skip this part)
The Employment Equity Act of South Africa has the following objectives:
“Promoting equal opportunity and fair treatment in employment through the elimination of unfair discrimination; and implementing affirmative action measures to redress the disadvantages in employment experienced by designated groups, to ensure their equitable representation in all occupational levels in the workforce.” – DoL
Designated employers are therefore required to “tell” the Department of Labour – on an annual basis – how they intend to suppor these objectives.
One of the ways in which designated employers “tell” the Department of Labour is by submitting their Employment Equity Report, on an annual basis.
Who must submit?
The Law requires that all designated Employers should submit Employment Equity reports (and related documentation) on an annual basis:
- All designated employers with 50 or more employees. Employers with fewer than 50 employees who are designated in terms of the turnover threshold applicable to designated employers (Schedule 4 of the Employment Equity Amendment Act No. 47 of 2013).
- Employers who have become newly designated on or after the first working day of April, but before the first working day of October, must only submit their first report on the first working day of October in the following year.
- Employers who voluntarily wish to comply in terms of section 14 of the EE Act.
- All designated employers must report annually irrespective of their size. – DoL
When and how must they submit:
- Manual Submission Date: 3 October 20xx (i.e. 3 October 2016)
- Electronic Submission Date: 15 January 20xx (i.e. 15 January 2017)
How can Excel help?
Well, Excel can’t submit your annual EE report on your behalf, but it can significantly shorten your time spent on manual data processing.
Both the EEA2 and EEA4 require an extensive amount of people data before submission.
The traditional way of completing the EEA2 and EEA4 was with manual printouts, paper and pen, then word templates and then luckily Excel spreadsheets.
But even with Excel spreadsheets, many employers still rely – quite significantly – on manual data processing (because of a lack of HRM software). In other words, extracting employee data into Excel, filtering the employee data, manually summarizing the filtered results and adding them to the EEA2 and EEA4 tables.
It is here that PivotTables shine.
PivotTables “automate” the processes involved in summarizing and reporting data.
The big constraint with manual data processing is that when your source data changes, i.e. your employee data, you have to manually change your EEA2 and EEA4 reports.
This is very time consuming, and quite prone to human error (especially with the amount of pressure that modern day HR practitioners experience).
PivotTables might not completely replace the need for manual data processing (certain sections in the EEA2 will require manual inputs), but when utilized correctly, PivotTables save time and reduce the risk of human error in manual data processing.
PivotTable Source Data
In order to effectively utilize PivotTables we have to ensure that our source data makes provision for all of the fields required by the EEA2.
Remember “GIGO” – “Garbage in, Garbage out”.
Below is an extract of the EEA2 report that provides an indication of what our source data should include:
Our source data should therefore make provisions for an employee’s:
- Occupational Level for each employee (we’ll talk about the EEA9 in a second);
- Gender (Male/Female);
- Race (A-Africans, C-Coloureds, I-Indians, W-Whites);
- Nationality (South African / Foreign National);
- Employment Status/Category (Permanent / Temporary) – For our example we’ll only have permanent employees;
- Disability; &
- Job/Position Grade.
One of the main documents that you will require during this process is the EEA9 – Occupational Levels document :
The EEA9 provides an indication of which occupational level “belongs” to, or correlates with a certain job/position grade.
Matching the occupational levels with the job/position grade isn’t always an exact science, especially in organizations that don’t make use of official position/job grade models/methodologies. But for the most part, it tends to remain as per the EEA9.
Step 1: Format Source Data
- Create a spreadsheet titled “Employee_Data“
- Create the following table headings in your spreadsheet:
- Employee_Number, Employee_Name, Employee_Surname, Race, Gender, Job_Grade, Occupational_Level, Employment_Category, Nationality, Disabled.
- Complete the spreadsheet by adding your own information or make use of the source data provided.
The Occupational Levels have been matched against the employee’s Job Grade.
(In this example we assigned the levels manually, but it would be a lot faster if we used V-lookups.)
In the [Disabled] column, add the number “1” if an employee has been classified as Disabled or a “0” if the employee is not classified as Disabled.
In the [Race] column, we use the letter “A” for African employees, “I” for Indian employees, “C” for Coloured employees and “W” for White employees.
In the [Gender] column, we use the letter “M” for Male and “F” for Female.
In our example, we made use of the Paterson job grading system/method.
Step 2: Create the PivotTable
We’ll be creating one PivotTables in this tutorial.
- 1x PivotTable for Section B 1.1 of the EEA2 – All Employees (including disabled)
Pivot Table 1: Section B 1.1 – All Employees
- Click in Cell [A1] in the “Employee_Data” spreadsheet.
- Click on “Insert” (in the Ribbon).
- Click the “PivotTable” button.
- The PivotTable wizard should pop-up.
- Click “OK”.
If done correctly, you should see the following:
- On the left-hand side of your screen, you should see a blank PivotTable.
- On the right-hand side of your screen, you should see a list of PivotTable Fields.
- Right-click anywhere on the blank PivotTable (in this case you will see that I right-clicked on cell A9. But anywhere within the PivotTable is fine).
- Once you’ve right-clicked a menu should pop-up. Click on “PivotTable Options…”
- The PivotTable options menu should pop-up.
- Click on the “Display” tab.
- Make sure that “Classic PivotTable layout” is checked.
- Click “OK”
Your PivotTable’s look should now have changed:
———————————————————————————————————-Step 3: Complete the PivotTable Fields
Remember, we want our PivotTable to resemble the format of the EEA2 report.
We’ll be working in the PivotTable Fields window quite a bit. Feel free to resize the window if you feel it’s too compact.
In the PivotTable Fields window (on the right-hand side of your screen), do the following: (don’t worry if your PivotTable is freaking out while you do this. We’ll fix that in a bit).
All the fields in the PivotTable Fields window can be dragged. If you left-click on a field (at the top) and hold the mouse button, you are able to drag the field for as long as you hold the mouse button. Once you let go of the mouse button, the field will be dropped.
- Drag the “Nationality” field into the “Columns” box.
- Drag the “Gender” field into the “Columns” box
- Drag the “Race” field into the “Columns” box.
- Drag the “Employee_Number” field into the “Values” box.
- Drag the “Disabled” filed into the “Filters” box.
- Drag the “Occupational_level” field into the “Rows” box.
- If you accidentally dragged a field into the wrong box, simply “pick it up” and drag it into the correct box (or back to the top area).
- If you don’t want a specific field, just drag it back to the top area of the PivotTable Field window.
- You can also change the order in which fields are dragged into a box by simply dragging a field above or below another field (this will impact the look of your PivotTable):
Before we continue to Step 4, we have to make one more change.
- In the [Values] block, click on the dropdown menu next to “Sum of Employee_Number” and then click “Value Field Settings..” in the popup window.
- The “Value Field Settings” window should appear
- Click on “Count” in the “Summarize Values By” tab.
- Click “OK”
We want the PivotTable to count the number of employees in each category. Because we used their employee numbers in the [Values] box, Excel automatically wants to add the numbers together, so we must “tell” Excel to count the number of employee- numbers instead of adding them together.
If we had used employee surnames in the [Values] box, Excel would have automatically counted them. By default, text is not added together.
In this example, I used employee numbers, because 99,99% of the time an employee would have an employee number. Their name or surname might not have been captured, but their employee number sure will.
Your final PivotTable Fields window should look like this:
Step 4: Format your PivotTable
Now that our PivotTable has been generated, it should look quite messy and nothing like the EEA2.
But that’s ok. 🙂
Your PivotTable might look something like this:
We will need to fix the following display aspects:
- Correct the order in which the Occupational Levels are displayed (as per the EEA2)
- Correct the order in which Gender is displayed (Male/Female)
- Resize all the Columns.
Correct Occupational Levels
- Left click on the “Top Management/Executive” field/row to select it.
- Hover the mouse cursor over the green outline of the “Top Management/Executive” field/row.
- Once the cursor changes to resemble the cursor in the picture above, click and hold the left mouse button and drag the field to the top, above the “Professionally Qualified and Experienced Specialists and Mid-Management” field.
- Once at the top, release the mouse button to drop the “Top Management/Executive” field in the correct spot
- Proceed to drag and drop all the fields/rows to the correct spot.
If done correctly, it should look something like this:
Correct Nationality and Gender
We can see that a few things look out of place:
- We want the details of “South African” nationals to be displayed first and then the details of “Foreign Nationals”.
- In terms of “Gender”, we want the data of all “Males” to be displayed first and the details of all “Females”.
- Under “Foreign Nationals”, we don’t want the race to be visible, only gender.
- Side Note: You will see that our total employee count is 200, which is aligned with our source data contained in the “Employee_Data” sheet.
To Fix It
We will follow the exact same steps that we followed for “Correct Occupational Levels“, but instead of dragging fields/rows up or down, we’ll be dragging columns left or right
The first Column we want to move is the “South African” column:
- Left click on the “South African” field/column-heading to select it.
- Hover the mouse cursor over the green outline of the “South African” field until the cursors changes to resemble the cursor in the picture above. Once the cursor above appears, click and hold the left mouse button and drag the field to the left, in front of the “Foreign National” column.
- Once you start dragging the “South African” column, a thick green line will appear.
- Once the thick green line is in front of the “Foreign National” column, release the mouse button.
You can proceed to drop and drag all the columns into the correct spot in accordance with EEA2.
If done correctly, it should look something like this:
- We now have “South African” nationals displayed before “Foreign Nationals:.
- Under [Gender], we have “Male” displayed before “Female”.
Correct Columns Widths
We want all of our Columns to be relatively the same size, just to make things a little easier to read.
We will have a few column headings that overlap, but that’s fine.
- Hover the mouse cursor over column B (or the column that contains your “Nationality” field – in this case it happens to be column B). Once the cursor changes to an arrow – as in the image above – left-click and hold the mouse button. Start dragging the cursor to the right.
- Once you start dragging the cursor to the right the cursor should change from an arrow to a + sign.
- Continue dragging the cursor until you reach the last column.
- If you did this correctly, all the columns should be selected – grayed-out as in the image above.
- Once you reach the last column, release the left mouse button.
- Right-click on any of the column letters (from column B – P). In this image, I right-clicked on column O.
- Once you’ve right-clicked, a formatting window should appear. Left click on “Column Width”.
- The column width window should popup.
- Enter the number “7” in the open block.
- Press “OK”
Your PivotTable should look something like this:
With all the data till selected, click on “Center” in the text formatting area in the ribbon, to center all the data in the various cells.
And this is what your final product should look like:
With a bit of added formatting, you could end up with your final result looking like this:
And we could go into even more detail with PivotTable formatting, but you get the idea 🙂
I hope that this tutorial was both helpful and educational.
If you’d like to learn more, or just looking for some general tips and tricks, feel free to send me an InMail or Comment in the section below.
Until next time..