Customer service dashboard template: fields and checks

A customer service dashboard template needs defined input fields, explicit reporting boundaries, and checks beside the metrics. Copy the worksheet layout and formulas below into a workbook, then replace the example records with your own ticket and account exports.

Set up four worksheets

Use the following sheet names so the formulas remain reusable. This is a copyable worksheet specification; each formula belongs in the field or cell named beside it.

Convert the account range to an Excel table named tAccounts and the ticket range to a table named tTickets. Structured references use table and column names and adjust when table data changes. The formulas below refer to those exact names.

The customer service dashboard workflow explains how to run the review. For the measure selection behind it, use KPIs for B2B customer service teams.

Fill controls and source tables

Enter real date-time values in the controls. This example uses UTC, calendar hours, and no paused clocks. The end is exclusive: a closure exactly at the cutoff belongs to the next period.

Create tAccounts with these columns. Team names stand in for the responsible service owners.

Create these input columns in tTickets. All example timestamps are midnight UTC in 2026. Leave unresolved dates empty. TargetHours is the ticket's applicable resolution commitment, and RepeatFlag is either 0 or 1 under a documented same-issue linking policy.

Keep tickets open at the start even when they were created in an earlier period. A creation-date-only export cannot reconstruct the backlog. Do not append successive snapshots as if they were separate tickets.

Add the calculation columns

Add each named column to tTickets and enter its formula in the first data row. Flags return 1 when true and 0 when false. Duration fields stay blank outside their relevant population.

The final two columns must each equal 1 on every record. Microsoft's COUNTIFS documentation describes counting records that satisfy the criteria. Here, repeated ticket IDs and missing or duplicated account matches become visible exceptions.

Before accepting these calculations, require nonblank TicketID and AccountID values. Check that OpenedAt is a timestamp, ResolvedAt is blank or a timestamp no earlier than OpenedAt, TargetHours is positive and populated, and RepeatFlag is 0 or 1. Reject invalid rows for correction; keep their count visible instead of silently excluding them.

Build the reusable account review

On Review, put A01 in A2 and B01 in A3. Use the following column layout and row-2 formulas, then fill down. SUMIFS adds values meeting the specified criteria, allowing the same helper flags to supply each account's counts.

B2  Opening
=SUMIFS(tTickets[OpenAtStart],tTickets[AccountID],$A2)

C2  New
=SUMIFS(tTickets[NewInPeriod],tTickets[AccountID],$A2)

D2  Closed
=SUMIFS(tTickets[ClosedInPeriod],tTickets[AccountID],$A2)

E2  Closing
=SUMIFS(tTickets[OpenAtEnd],tTickets[AccountID],$A2)

F2  MeanResolutionHours
=IF(D2=0,"",SUMIFS(tTickets[ResolutionHours],tTickets[AccountID],$A2)/D2)

G2  CompletedSLARate
=IF(D2=0,"",SUMIFS(tTickets[SLAMet],tTickets[AccountID],$A2)/D2)

H2  RepeatShareOfNew
=IF(C2=0,"",SUMIFS(tTickets[RepeatFlag],tTickets[AccountID],$A2,tTickets[NewInPeriod],1)/C2)

I2  OverdueOpen
=SUMIFS(tTickets[OverdueOpen],tTickets[AccountID],$A2)

J2  MovementResidual
=B2+C2-D2-E2

Covirage is not affiliated with Microsoft.

For the total row, sum counts in columns B:E and I. Set J4 to =B4+C4-D4-E4. Use the formulas below for F4:H4, preserving the correct denominators:

F4 =IF(D4=0,"",SUM(tTickets[ResolutionHours])/D4)
G4 =IF(D4=0,"",SUM(tTickets[SLAMet])/D4)
H4 =IF(C4=0,"",SUMIFS(tTickets[RepeatFlag],tTickets[NewInPeriod],1)/C4)

The completed durations are 72 and 24 hours, so the mean is 96 / 2 = 48.0. Both completed tickets met their targets: 2 / 2 = 100.0%. One of three newly opened tickets was a repeat: 1 / 3 = 33.3%. Round displayed hours and percentages to one decimal place after calculation.

Keep the checks beside the result

Every MovementResidual must equal zero. The total bridge is 1 + 3 - 2 - 2 = 0. The two closing tickets are four days and one day old; only the four-day ticket exceeds its 48-hour target. This explains why completed SLA attainment can be perfect while an open commitment is overdue.

Also compare total review counts with the corresponding helper-column sums. That catches an omitted account row even when each displayed account reconciles. Preserve the source filename and export cutoff with the workbook so the next reviewer can repeat the check.

Where it goes wrong

• Deleting formula columns during refresh. Replace input values while retaining the named helper columns and formulas.
• Changing target definitions. A calendar-hour template needs different calculations for contractual business hours or pauses.
• Reopenings without movements. The supplied bridge assumes no reopenings or cancellations. Add separate event counts when those occur.
• Averaging account percentages. Use summed numerators over summed denominators for the total.
• Reading repeats as customer health. Use a customer experience dashboard for broader experience measures; a repeat flag alone does not establish satisfaction or retention.

Analyze your own service worksheet in Covirage

Bring the workbook's ticket and account records to Covirage. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the account differences with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for customer service teams for the account questions this review supports.