Use Symbols instead of Text in Pivot Slicers
Instead of text, symbols can be used on Pivot slicers to create a custom look for a dashboard report.
Consider a data set with details of persons and their base salaries, along with other information as shown in the table below:
You might be asked to create a PivotTable that shows all cost centers with the average base salary that’s associated with it, which can be filtered according to sex and FTE.
Instead of using the regular text for slicers, we’re going to use symbols instead.
To achieve this, you first create the PivotTable and the regular slicers. After this, the symbols can be added to replace the text on the slicers.
Creating the PivotTable
The aim here is to create a situation where any new data that gets added into the table will be automatically considered by the PivotTable after refreshing it – instead of expanding the selection.
To help us here, we will transform the source data into an official Excel table format.
First, click on any cell within the table and press CTRL + T.
Since the table has headers, make sure you tick that option.
You can change the format of the table by selecting one of the various options under the Table Styles section of the Design tab.
For this example, I have cleared all formatting.
Rename the table by clicking any cell and replacing the name at the top left corner:
To add a PivotTable, click on Summarize with PivotTable under the Design tab.
A Create PivotTable window will pop up, which will prompt you to indicate the source data, as well as where to put the PivotTable report.
For this example, I want to put it in cell J2.
The PivotTable options will be displayed on the right side, where you can select the fields you want to display in the report.
Select and drag these fields as follows:
- Rows: Cost center
- Values: Base salary
Since we want to the base salary column to show the average instead of the sum, click on the drop down arrow and select Value Field Settings.
Indicate Average. If you want to further change the number formatting, click on Number Format.
If you want to show the values with a thousands separator and without decimal places, select #,##0 from the custom formatting types. This can also be found in the Numbers category and ticking thousands separator.
For this example, we want to change the layout of the report to show the values in a Tabular form.
To do this, select any cell in the PivotTable and click on Report Layout under the Design tab.
Select Show in Tabular Form.
Instead of Grand Total, I have changed this cell to “Total”.
To change the formatting of the PivotTable, select one of the options from the PivotTable Styles.
I have made some minor changes, including changing the background color to white and renaming the Average of Base Salary to Average Base salary.
I have also dragged it lower to leave space for the slicers at the top. This is done by selecting the entire PivotTable and then dragging it to another position. This is how it currently looks like:
Adding regular slicers
We want to add slicers to filter sex and FTE. To do this:
- Right mouse click on Sex on the PivotTable window.
- Select Add as Slicer.
You can then change the formatting according to your desired style.
This is done by selecting a Slicer Style under the Options tab.
You can also resize it as necessary.
You will notice that when you select F or M, the data in the PivotTable changes.
Repeat this to add a slicer for FTE.
Adding symbols to slicers
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:
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
