Call Center Performance Template

This comprehensive tutorial will show you how to create an Excel call center performance template.

Our goal is to build a one-page call center template for tracking the actual status of KPIs and design a user-friendly contextual help for better UX using a VBA macro. Before we start, we’ll introduce the most used indicators in our example. Using these metrics, we can track and trace the overall service performance.

  • Time to answer: this performance dimension is usually expressed in seconds. This is the time from when a call is started until a customer service agent responds.
  • Abandon rate of incoming calls: we measure this key performance indicator as a % of the number of callers who are cut off before they touch an agent who answers their call.
  • FCR: Use this formula to calculate First Call Resolution = [issue solved by first call] / [total issues]

How to create a call center performance template?

We’ll put the weekly results by agents (Time to answer, Abandon rate, FCR) into the top left area. Bottom-left section contains the KPI setup.
Part 3 will show the selected period (average of the branch) from the actual week to the actual week + 3 months. Finally, the individual variance will be displayed in the bottom-right section.

call center performance template excel overview final

Detailed instructions to create a call center template:

1. We divided this model into three separate worksheets for the template, the main sheet, the input section, and the calculation area. It is one of the easiest ways to build a clear and structured model.

Implementing the unprocessed data and the calculated value is straightforward: The main worksheet with all the figures is linked directly to the data table.

2. In this example, we’ll use a weekly basis. Let us see the data worksheet. With fixed duration, the calculated KPI is based on a single duration. You must specify the start and end dates – for example, from 1 January 2017. to 31 January.

If you specify only a start date, the performance indicator will be calculated from the selected date to the current date. If you specify only the end date, we will calculate the KPI using the method mentioned earlier to the specified end date.

call center performance template raw data table excel

3. On the calculation sheet, we’re going to use the VLOOKUP formula to find values from the data sheet by values based on the selected week.

call center performance template excel calculation sheet

4. Check the bottom-left corner! Let us see the metrics. You are creating targets that help you determine progress toward your goals. Each cell is linked separately to the calculation sheet.

call center performance template excel setup initial

5. Okay, let us see how we build the top right section of the template! This part is a weekly performance scorecard. We get the values from the linked worksheet ‘calc’.

call center performance template conditional formatting

We can visually determine when something is breaking a business rule using conditional formatting and icon sets. Next, we’ll use shapes to highlight the variances.

6. Apply conditional formatting for the selected cells.

Execute the following steps:

  • Select the given range. (In this case, the time to answer row)
  • Jump to the Home tab, and in the Styles group, choose Conditional Formatting.
  • Click Icon Sets, Shapes, Traffic lights
  • Enter the values using formulas and click OK
call center performance template top left format shapes

7. Repeat the last step and apply conditional formatting for the abandon rate and FCR.

8. A brand new and useful feature available in Office 2016 is Icons. To insert a new icon to improve the template’s visual quality, find Excel Icons in the Insert tab of the ribbon in the Illustrations group. This feature is available in subscription versions of Office 365.

call center performance template insert shapes

9. Choose your favorite picture and put it on the sheet. There’s a gallery of icons available, and they’re grouped into categories to make them easily searchable.

call center performance template select insert icon

10. We’ll use the spin button form control to display the results weekly. Go to the Developer tab and click Insert. Then, click to spin button form the control icon.

call center performance template insert spin button

11. Right-click on the Spin Button. The format control pop-up box will appear. Next, click on the ‘Format Control’ tab.

This will open a Format Control dialogue box. Go to the ‘Control’ tab, and make the following changes to create a dynamic list: Minimum Value: 1, Maximum Value: 48, Incremental Change: 1, Page Change: 10

call center 010-format-spin button

12. On the Cell link box, link the cell which contains the actual week’s value. In this example, select cell $C$4 on the data sheet. Now validate it using the arrow button.

call center performance template excel validate cell

13. Let’s see the chart section! We’ll track the weekly performance using easily readable charts. To create charts, select the $G$5:$G$16 range on the calculation sheet.

Go to the Insert tab on the ribbon and select the Recommended Chart section.

call center performance template recommended chart

What are recommended charts in Excel? Select the source range in your worksheet and click this button to get a customized set of diagrams that Excel thinks will fit best with your data.

14. Go to the All Charts menu and select the combo chart. Choose the chart type axis for your data series. For example, choose the clustered column type for series1 and the Line chart type for series2.

call center performance template excel choose combo chart

Click OK to insert a combination chart.

call center performance template table raw chart

15. Copy the chart to the main worksheet area and clean it up! You should remove the unnecessary elements: Right-click on the chart and select Format Chart Area.

Use this method on the Plot Area too.

16. First, format the bar chart! Right-click on the bar chart and choose the Format Data Series option.
On the “Series option,” use these values: 33% for gap width and 0% for Series Overlap. Choose the right color too.

call center performance template bar chart formatting

17. In the following step, format the line chart! Right-click on the line chart and choose the Format Data Series option.
On the “Series option,” use a solid line and red color. Finally, set the compound type to dash. Finally, the average line is done.

call center performance template average line dotted

18. Repeat Step 13 to Step 17 to create the combo charts for abandon rate and FCR.

19. Here are the formatted charts below:

call center performance template formatted charts excel

20. Optional steps to visualize agents’ variance (+/-) on the selected (actual) week. Go to the bottom right corner.

On the Home tab, click the arrow next to Conditional Formatting, and then click Data Bar. Select a formatting style. In this example, we’ll use light red fill for negative variance and light green fill for positive variance.

After applying a quick style, select cells or range, click Conditional Formatting on the ribbon, and then click Manage Rules to update the selected rules manually.

call center performance template variance chart option

Download the free call center template if you are in a hurry!

Additional resources: