How to Build an Operations Dashboard in Excel

An operations dashboard in Excel needs to answer: how much came through, how much of it needed attention, and where the workload is concentrated. Here’s how to build it from a recurring operations export (tickets, orders, shipments — the shape is usually similar: one row per unit of work, with a category, a status, and a timestamp).

Set up the source table with an exception flag

Paste the export onto a Data sheet and convert to an Excel Table. Add one helper column that computes whether a row is an exception, using whatever rule applies — over an SLA threshold, a failed status, a missing field:

=IF([@ProcessingHours]>24, "Exception", "Normal")

Doing this once, in the source table, means every PivotTable and chart built afterward can group or filter by it directly.

Volume by category

Insert a PivotTable: Category in Rows, Count in Values. Build a PivotChart from it — a horizontal bar reads best if category names are long (order type, ticket type, shipment method).

Exceptions

Build a second, smaller PivotTable filtered to Exception rows only, with Category in Rows and Count in Values. Put this next to the volume chart, not buried below it — exception count is usually the first thing an operations lead checks, before total volume.

Add conditional formatting (Home > Conditional Formatting > Highlight Cell Rules) to the exception count cells so anything above a threshold is colored automatically, without anyone needing to scan the numbers.

Workload by category over time

A stacked column PivotChart — Date (grouped by day or week) on the axis, Count as the value, Category as the stack — shows both total volume and how its mix shifts over time in one chart, which is more useful than a single trend line for spotting a specific category driving a spike.

A slicer for date range and category

One slicer on Date (grouped by week) and one on Category, connected to every PivotTable via Report Connections. This is what lets someone check “just last week, just shipments” without rebuilding anything.

Excel vs. a live operations dashboard

Excel handles this well for a daily or weekly review owned by one person. It gets heavier once the export needs combining from multiple sources, or several people need to filter the same dashboard independently.

See the operations use case for a version built to answer exception questions directly instead of filtering a PivotTable by hand.