For Sex, we want to add the corresponding symbols.
For FTE, we just want to distinguish between full-time and part time using two symbols.
Anything that is less than 1 FTE will be considered as part time, and anything that equals 1 will be shown as full time.
To do this, bring in the symbols in the original data source.
Since we want to have symbols for male and female, as well as full time or part time, we will be adding conditional statements to insert the appropriate symbol.
The shortcut key for the male symbol ♂ is ALT + 11.
For the female symbol ♀, it is ALT + 12.
If ALT does not work, use Unichar values or use the Add symbol button in the Insert tab.
These are the Unichar values for the symbols I want to use:
To use these, the syntax is =UNICHAR(number).
Cell B3 then becomes:
Cell B3 = UNICHAR(A3)
Dragging this down, you get the corresponding symbols shown below.
The last two are the symbols I want to use for part time and full time workers, respectively.
Highlight and copy these cells, then right click and paste them as values.
Add one new column between Sex and FTE.
Rename this column as Sex symbol.
Use the IF() function to display the appropriate symbol depending on the value of column D.
Cell E2 = IF(D2=”M”, “♂”,”♀”)
Do the same for FTE by adding a new column between FTE and Base Salary.
The symbols to be used are ⬛ for fulltime and ⬕ for part-time.
Rename it as FTE Symbol.
Use an IF() statement to display the corresponding symbol.
Cell G2 = IF(F2=1,”⬛”,”⬕”)
Go back to the PivotTable.
Right click on it and select Refresh.
You will now see the Sex Symbol and FTE Symbol fields, which you can now use for the slicers.
Since you will no longer use the regular slicers, delete the ones you have already made.
Right click on Sex symbol and select Add as slicer.
Do the same for FTE Symbol.
Change the formatting as desired.
For this example, I have made the following modifications:
- Changed the columns to 2. This is found under the Options tab.
- Resized the slicer.
- Removed header by right clicking on the slicer and select Slicer Settings. Untick Display header.
- Repositioned the slicer above PivotTable
- Change Slicer style. This is found under the Options tab.
- If you want to create a slicer style you can save and use for other slicers, duplicate a current style.
- Rename the style. If you want to remove the border, select Whole Slicer and click on Format. Under the Border tab, click on None. You can also do other modifications to this style and select OK.
Once you have made modifications to the overall look of the PivotTable, it would look like this: