A dynamic chart with slicer in Excel lets anyone click a department and instantly see how many employees fall into each age group. In this tutorial you build it from a plain employee list: a pivot table groups ages into 5-year bands, a pivot chart turns the counts into rows of people icons, a styled Department slicer drives the filter, and a small IF formula keeps the chart title in sync with the selection. No VBA, no complex formulas, and it works in Excel 2013, 2016, 2019, 2021 and Microsoft 365.
Watch the Video Tutorial
In the video PK builds the whole chart from the raw data: pivot table, age grouping, pivot chart with picture bars, a custom slicer style, the double border and the dynamic title.
What You Will Build
- A pivot table that counts employees per age band: 20-24, 25-29, 30-34, 35-39, 40-44 and 45-50.
- A bar-style pivot chart where every employee is drawn as one person icon, with a callout label showing the count.
- A Department slicer (Finance, HR, IT, Operation) with a custom black and gold style and no header.
- A dynamic chart title such as Employee Counts by Age groups – HR, driven by one formula.
- A clean dashboard card with a light blue double border and divider lines.
The Employee Data
The data sits on a sheet named Data with three columns:
- A – EMP Name (EMP-1, EMP-2, …)
- B – Department (Finance, HR, IT or Operation)
- C – Age (In Years), from 20 to 50
The sample has 259 employees. That is all you need: the pivot table does the counting and grouping for you, so there are no helper columns.
Part 1 – Group Ages with a Pivot Table
Step 1: Insert the pivot table
Click inside the data, go to Insert › PivotTable and click OK to place it on a new sheet. In the field list, drag Age (In Years) to Rows and EMP Name to Values. Because EMP Name is text, Excel uses Count of EMP Name, which is exactly the employee count you want.
Step 2: Group the ages into 5-year bands
Right-click any age in the row labels and choose Group. Excel fills Starting at with 20 and Ending at with 50, the smallest and largest ages in the data. Change By from 10 to 5 and click OK. You now get six rows: 20-24, 25-29, 30-34, 35-39, 40-44 and 45-50. The last band ends at 50 because that is the ending value.
Step 3: Remove the grand totals
Right-click the pivot, open PivotTable Options › Totals & Filters and untick Show grand totals for rows and for columns. A Grand Total row would otherwise appear as a huge extra bar on the chart.
With all departments included, the counts are 49, 45, 45, 33, 42 and 45 (259 in total).
Part 2 – Build the Pictograph Pivot Chart
Step 4: Insert a clustered bar chart
Select the pivot table and go to Insert › Bar › Clustered Bar. Because the source is a pivot table, Excel creates a pivot chart, which is what lets the slicer control it later.
Step 5: Hide the field buttons and clean up
On the PivotChart Analyze tab open Field Buttons and choose Hide All. The grey filter buttons disappear and it looks like a normal chart. Then delete the legend and the value axis, and untick Gridlines from the + button next to the chart.
Step 6: Move the chart to its own sheet
Cut the chart (Ctrl+X), add a new sheet, name it Chart and paste it there. Rename the pivot sheet to Support. You end up with three tabs: Support, Chart and Data. Later you can hide Support, but add the slicer first.
Step 7: Insert the Department slicer
Select a cell in the pivot table (the Analyze tab only appears when the pivot is selected), click Insert Slicer, tick Department and click OK. Cut the slicer, go to the Chart sheet and paste it next to the chart. Turn off gridlines on that sheet from the View tab. Click Finance or HR and the bars already change.
Step 8: Replace the bars with people icons
Insert or copy a person icon, select it and press Ctrl+C. Click the bars once to select the whole series and press Ctrl+V. Each bar is now filled with the icon, but stretched. To fix it:
- Right-click the bars and choose Format Data Series.
- Under Fill & Line › Fill select Stack and Scale with.
- Set Units/Picture to 1.
Now one icon equals one employee, so a band with 14 people shows exactly 14 icons. Set the Gap Width to about 80-85% in the Series Options to make the icons taller.
Step 9: Add callout data labels
Click + › Data Labels. To make them stand out, right-click a label, choose Change Data Label Shape and pick an arrow callout. On the Home tab give the labels a fill colour, remove the shape outline and make the text bold.
Part 3 – Style the Slicer
Step 10: Create a custom slicer style
Select the slicer, open the Slicer (Options) tab and choose New Slicer Style from the styles gallery. Format two elements:
- Selected Item with Data: black fill, white bold font.
- Unselected Item with Data: a light colour that matches the callouts (the video uses gold).
Click OK, then apply your new style from the gallery. PK builds a new style rather than picking a built-in one because he does not want the default outer border around the slicer.
Step 11: Hide the slicer header
Right-click the slicer, open Slicer Settings and untick Display header. Resize the slicer so the four buttons fit neatly in the space to the right of the bars.
Part 4 – Finish the Dynamic Chart with Slicer
Step 12: Make room and add the border
Click the plot area and drag its handle to make it narrower, leaving space on the right for the slicer. Then right-click the chart, open Format Chart Area › Border and choose a solid light blue line with a width of 2.5 pt and the Double compound type.
Step 13: Add the divider lines
With the chart selected, go to Insert › Shapes › Line and draw a horizontal line under the title, holding Shift so it stays straight. Give it the same light blue, 2.5 pt, double formatting. Draw a second, vertical line between the bars and the slicer. Drawing the lines while the chart is selected places them inside the chart, so they move with it. Finally, drag the slicer into the right-hand panel.
Step 14: Link the chart title to a formula
Go to the Support sheet and drag Department into the pivot’s Filters area. Cell B1 now shows what the slicer has selected: (All), (Multiple Items) or a single department. In F1 enter:
=IF(OR(B1="(All)",B1="(Multiple Items)"),"Employee Counts by Age groups","Employee Counts by Age groups - "&B1)Now click the chart title, type = in the formula bar, click cell F1 on the Support sheet and press Enter. Make the title black and bold.
How the title formula works
B1is the report filter cell of the pivot, which mirrors the slicer selection.OR(B1="(All)",B1="(Multiple Items)")is TRUE when nothing or more than one department is selected.- In that case the title is the general Employee Counts by Age groups; otherwise it appends the department name with
&B1.
Select HR and the chart shows 10, 9, 16, 6, 9 and 10 employees with the title Employee Counts by Age groups – HR. Select Finance and you get 14, 11, 7, 8, 10 and 6. You can now hide the Support sheet.
Common Errors and Fixes
Group is greyed out or shows an error
Grouping numbers needs every value in the field to be a number. If any age is stored as text or a cell is blank, Excel refuses to group. Convert the ages to numbers and refresh the pivot.
The icons look stretched
The fill is still on Stretch. Switch it to Stack and Scale with and set Units/Picture to 1.
The title does not change with the slicer
Either Department is not in the pivot’s Filters area, or the title is typed text instead of a link. The formula bar must show a reference to the Support sheet when the title is selected.
The slicer filters nothing (addition)
A slicer only controls pivots it is connected to. Right-click it, open Report Connections and tick the pivot table that feeds the chart.
Tips
- Put 20-24 at the top (addition): bar charts list categories bottom-up. Right-click the vertical axis and tick Categories in reverse order to read youngest to oldest from the top.
- Keep it current (addition): convert the data to an Excel Table (Ctrl+T) before creating the pivot, then use Data › Refresh All after adding employees.
- Hold Ctrl while clicking slicer buttons to compare two departments; the title switches to the general text automatically.
- Microsoft explains slicers in detail in Use slicers to filter data.
Prefer not to use a pivot table? The dynamic chart with option buttons version builds the same pictograph with COUNTIFS formulas and Form Control option buttons, and its practice file uses the same employee data.
Want It Ready-Made?
If you want the finished workbook with the chart, styled slicer and dynamic title already built, get the Dynamic Chart with Slicer for Employees Count by Age group on NextGenTemplates.com. For a complete workforce view, see the HR Dashboard in Excel or browse all HR and Payroll templates. To master pivot tables, slicers and pivot charts step by step, join the video course Excel Pivot Tables and Dashboards.
Related Tutorials
- Dynamic Chart with Option Buttons for Employees Count by Age Group
- Pivot Table Styles, Slicer Styles and Timeline Styles in Excel
- Stylish Pivot Chart with Slicer in Excel
- Quick Dynamic Sales by Employees Chart with Slicer in Excel
- Infographics with Line Chart in Excel: Use Icons as Markers
Frequently Asked Questions
How do I group ages into ranges in a pivot table?
Put the age field in Rows, right-click any age and choose Group. Excel suggests the smallest and largest age as start and end; type the band size in the By box, for example 5, and click OK.
Can a slicer control a normal chart?
A slicer filters pivot tables, Excel Tables and pivot charts. That is why this tutorial builds the chart from a pivot table: the slicer filters the pivot, and the pivot chart follows.
How do I make one icon equal one employee?
Paste the icon into the bars, then in Format Data Series under Fill choose Stack and Scale with and set Units per Picture to 1.
How do I make the chart title change with the slicer?
Add the Department field to the pivot Filters area, build the title text in a cell with an IF formula that reads that filter cell, and link the chart title to the cell by typing an equals sign in the formula bar. Step 14 shows the formula.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, Power Query and Power BI since 2016 on his YouTube channel.
Conclusion
A dynamic chart with slicer turns a long employee list into a one-click age profile for any department. The pivot table does the grouping, Stack and Scale turns the bars into people, a custom style makes the slicer look like part of the design, and one IF formula keeps the title honest. Reuse the same pattern for any count by group.
