A dynamic chart with option buttons in Excel lets you pick a department with one click and see its employees counted by age group, drawn as rows of people icons. This is the formula version of the dynamic chart with slicer: instead of a pivot table, four Form Control option buttons feed a small support table built with VLOOKUP and COUNTIFS, and the chart title updates with the selection. It needs no VBA and no pivot refresh, and it works in Excel 2010 and every later version.
⬇️ Download the Practice File
Chart, Support and Data sheets · .xlsx (no macros) · Excel 2010 and later
Watch the Video Tutorial
In the video PK inserts the option buttons, links them to a support sheet, counts each age band with COUNTIFS and turns a plain bar chart into a pictograph with a dynamic title.
What You Will Build
- Four option buttons (HR, Operation, IT, Finance) made with Form Controls, so only one department can be active.
- A support table that converts the button number into a department name with VLOOKUP.
- Age bands 20-24 to 45-50 counted live with one COUNTIFS formula.
- A pictograph bar chart where one icon equals one employee.
- A dynamic title such as Employees count by Age groups – HR.
How the Practice File Is Laid Out
- Data: EMP Name in column A, Department in B and Age (In Years) in C, for 259 employees aged 20 to 50.
- Support: the helper cells. G1:H4 is the button list (1 HR, 2 Operation, 3 IT, 4 Finance), C3 is the option buttons’ cell link, D3 holds the selected department, A5:D10 is the age-band table and D1 is the chart title.
- Chart with Option button: the finished chart grouped with the four buttons.
The video builds the same thing with slightly different helper cells. The steps below follow the downloadable file, so you can check every formula as you go.
Part 1 – Insert and Link the Option Buttons
Step 1: Insert the first option button
On the chart sheet go to Developer › Insert › Option Button (Form Control) and draw it. Right-click it, choose Edit Text and type HR. If the Developer tab is missing, switch it on in File › Options › Customize Ribbon.
Step 2: Copy it for the other departments
Copy and paste the button three times and rename them Operation, IT and Finance, exactly as the department names appear in the data. Option buttons on the same sheet work as one set: selecting one clears the others.
Step 3: Link the buttons to a cell
Right-click any button, choose Format Control, and on the Control tab set the Cell link to Support!$C$3. You only need to do it once; all buttons in the set share the link. C3 now returns 1 for the first button you created, 2 for the second, and so on.
Part 2 – Build the Support Table
Step 4: Turn the number into a department
An option button only returns its position. To get the name back, type the list in G1:H4 in the same order you created the buttons (1 HR, 2 Operation, 3 IT, 4 Finance) and put this in D3:
=VLOOKUP(C3,G1:H4,2,0)Click Finance and D3 changes to Finance. The 0 forces an exact match, so 3 can only return IT.
Step 5: Create the age bands
The youngest employee is 20 and the oldest is 50, so use 5-year bands. Put the lower bound in column A and the upper bound in column B (45-50, 40-44, 35-39, 30-34, 25-29, 20-24 in rows 5 to 10), and build the label in C5:
=A5&"-"&B5Fill it down to C10. The file lists the oldest band first on purpose: a bar chart draws the first row at the bottom, so this order puts 20-24 at the top of the chart.
Step 6: Count employees with COUNTIFS
In D5 enter the formula and fill it down to D10:
=COUNTIFS(Data!C:C,">="&A5,Data!C:C,"<="&B5,Data!B:B,$D$3)How the COUNTIFS formula works
Data!C:C,">="&A5counts ages greater than or equal to the lower bound. The operator is text, so it is joined to the cell with&.Data!C:C,"<="&B5adds the upper bound on the same column, so only ages inside the band are counted.Data!B:B,$D$3keeps only the selected department.$D$3is locked with F4 so it does not move when you fill down.
With HR selected you get 10, 9, 16, 6, 9 and 10 for 20-24 through 45-50. With Finance you get 14, 11, 7, 8, 10 and 6.
Step 7: Build the title text
In D1 join a fixed label with the selected department:
="Employees count by Age groups - "&D3Part 3 – Create the Dynamic Chart with Option Buttons
Step 8: Insert the bar chart
Select C4:D10 and go to Insert › Bar › Clustered Bar. Delete the horizontal (value) axis and the gridlines, make the age labels bold and black, and resize the plot area. Cut the chart and paste it on the chart sheet next to the buttons.
Step 9: Fill the bars with a person icon
Insert a person icon (Insert › Icons in newer Excel, or paste your own image) and copy it. Click the bars once to select the series and press Ctrl+V. Then right-click the bars, choose Format Data Series › Fill & Line › Fill, keep Picture or texture fill, choose Stack and Scale with and leave Units/Picture at 1. Every icon is now one employee. The file uses a gap width of 83% to make the icons a good size.
Step 10: Add data labels
Add data labels from the + button and make them bold and black, so the exact count sits at the end of each row of icons.
Step 11: Link the chart title
Click the chart title, type = in the formula bar, click Support!D1 and press Enter. Make it black and bold.
Part 4 – Design the Card and Group It
Step 12: Border and divider lines
Right-click the chart, open Format Chart Area › Border and choose a solid line in a darker shade of the icon colour, 2.5 pt, compound type Double. With the chart selected, insert two lines from Insert › Shapes (hold Shift to keep them straight): one under the title and one separating the buttons panel. Give them the same colour, width and double style, and format the vertical axis line to match.
Step 13: Place, align and colour the buttons
Drag the buttons into the right-hand panel. If they disappear behind the chart, right-click and choose Bring to Front. Select all four and use Format › Align › Align Left and Distribute Vertically. Give them a fill colour that matches the icons, as in the orange design above.
Step 14: Group everything
Select the four buttons, right-click and choose Group. Then select that group together with the chart and group again. Now the chart and buttons move and resize as one object. Click Operation, IT or Finance to test it, then hide the Support sheet if you like.
Common Errors and Fixes
The wrong department appears
The order in G1:H4 does not match the order in which the buttons were created. Button numbers follow creation order, not position on the sheet. Reorder the list so number 1 is the first button you drew.
Clicking a button changes nothing
Check Format Control › Cell link. If it is empty or points to another cell, C3 never changes. Also make sure calculation is set to Automatic in Formulas › Calculation Options (addition).
COUNTIFS returns 0 for every band
Usually the department name in D3 does not match the data, for example an extra space in a button list entry, or the ages are stored as text. Compare the names exactly and convert text ages to numbers.
Tips
- Two sets of buttons (addition): if you add another set, for example for gender, put each set inside a Group Box form control so they work independently.
- Skip the lookup table (addition):
=INDEX(H1:H4,C3)returns the same department as the VLOOKUP, since C3 is already the position. - Wider bands: change the bounds in columns A and B (for example 20-29, 30-39) and the labels and counts update automatically.
- Keep the department captions identical to the data, including spelling, because COUNTIFS compares the text exactly (apart from upper and lower case).
- Microsoft’s COUNTIFS function reference covers multiple criteria and operators in detail.
Want It Ready-Made?
If you prefer the slicer version already built and styled, get the Dynamic Chart with Slicer for Employees Count by Age group on NextGenTemplates.com, or browse all HR and Payroll templates. To get confident with COUNTIFS, VLOOKUP, INDEX and the other formulas behind dynamic charts, join the video course Excel + AI Mastery: 100 Essential Formulas and Real-World Projects.
Related Tutorials
- Dynamic Chart with Slicer in Excel: Employees by Age Group
- Excel Form Controls: Combo Box, Spin Button and Option Buttons
- Fully Dynamic Chart in Excel with Scroll Bar and Spin Button
- Dynamic Chart with Form Control Scrollbar
- Dynamic Chart for Top Selected Employees with GROUPBY and TAKE
Frequently Asked Questions
What does an option button return in Excel?
A Form Control option button writes the position number of the selected button to its linked cell: 1 for the first button created, 2 for the second, and so on. Use VLOOKUP or INDEX on a small list to turn that number into a name.
Option buttons or slicer: which should I use?
Option buttons work with normal formulas and charts, allow exactly one choice and need no refresh. A slicer needs a pivot table or an Excel Table but allows several selections at once. Both versions are covered on this site.
How do I count employees between two ages?
Use COUNTIFS on the age column twice, once with a greater than or equal to condition on the lower bound and once with a less than or equal to condition on the upper bound, plus a third condition on the department. Step 6 shows the exact formula.
Why are my option buttons linked together?
All Form Control option buttons on a sheet belong to one set unless they sit inside a Group Box. That is why setting the cell link on one button sets it for all of them.
Do I need VBA for this chart?
No. The file is a normal .xlsx. The option buttons, VLOOKUP, COUNTIFS and the linked chart title do all the work.
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 option buttons in Excel needs only a cell link, a VLOOKUP and one COUNTIFS formula, and the result looks as polished as any dashboard visual. Download the practice file, click through the four departments and then reuse the same pattern for any count by band, from customers by age to orders by value.
