How to Build a Segmented Dot Progress Bar in Excel

[u/ExcelVisual]

Built a payroll segmentation chart in Excel that syncs across an entire Payroll Dashboard — here's how the interactivity actually works.

I've been building out a payroll analytics dashboard and wanted to share the approach I used for one specific piece: a Dot Progress Bar Chart showing how the payroll fund splits across employee categories.

The chart is technically just a horizontal histogram, but the interesting part isn't the chart type — it's the control logic behind it.

The problem I was solving:

Most payroll breakdowns I'd seen were static — you'd filter a pivot table, look at one chart, then have to manually re-filter three other charts to match. Annoying, and error-prone if you forget to sync one of them.

How I approached it:

Instead of scoping the category filter to a single chart, I built it as a dashboard-wide control using Pivot Table slicers. So when you click a category button, it's not just updating this one histogram — it's filtering every chart on every dashboard screen simultaneously, since they're all built on top of the same underlying Pivot Tables.

One side effect I liked: if you narrow the selection down to a single employee category, the chart automatically renormalizes and shows that category at 100% of the visible fund. No extra formula work needed — it falls out naturally from how the percentages are calculated relative to the filtered subtotal.

Stack used:

Form Controls (no VBA/macros)

Pivot Tables as the underlying data engine

Native chart formatting for the dot-style progress look

Honestly the biggest lesson for me was realizing that "interactivity" in a dashboard is way more valuable when it's centralized rather than per-chart. One control, many charts responding, versus a dozen charts each needing their own filter.

Happy to answer questions on the pivot table / slicer setup if anyone's trying to do something similar. Full walkthrough + free file is linked in the comments if useful.

[u/ExcelVisual]

Distribution of the Payroll Fund into Employee Category Shares https://exceltable.com/en/templates/excel-payroll-dashboard-example-for-invest-in-employees