call center template

In this comprehensive tutorial we’ll show you how to create a user-friendly call center performance template in Excel.

Our Objective

Build a one-page call center template for tracking the actual status of key performance indicators and design a user friendly contextual help system using a VBA macro. Before we start, we’ll introduce you 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 usually expressed in second this is the time from when a call is started until it is responded by a customer service agent.
  • 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 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 contain the KPI setup.
Part 3 will show the selected period (average of the branch) from the actual week to actual week + 3 months. Finally, the individual variance will be displayed on the bottom-right section.

call center performance template excel overview final

Detailed instructions to create call center template:

1. We are 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.

The implementation of the unprocessed data and calculated value is very easy: The main worksheet with the all figures is linked directly to the data table.

2. In this example we’ll use 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 specified date to the current date. If you specify only the end date the KPI will be calculated from the introduction of the process to the specified end date.

call center performance template raw data table excel

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

call center performance template excel calculation sheet

4. Check the bottom-left corner! Let us see the metrics. 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 template! This part is a weekly performance scorecard. We get the values from the linked worksheet ‘calc’.

call center performance template conditional formatting

With conditional formatting, we can visually determine when something is breaking a business rule. 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 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 are Icons. To insert a new icon to improve 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 into 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. To display the results week by week we’ll use spin button form control. Go to Developer tab and click Insert. Click to spin button form control icon.

call center performance template insert spin button

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

This will open a Format Control dialogue box. Go to ‘Control’ tab, and make the following changes to create 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 contain 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 easy readable charts. To create charts select the $G$5:$G$16 range on the calculation sheet.

Go to 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 charts that Excel think will fit best with your data.

14. Go to All Charts menu and select the combo chart. Choose the chart type axis for your data series. Choose the clustered column type for series1 and 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 main worksheet area and clean it up! You should to 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. As a next step format the line chart! Right click on the line chart and choose the Format Data Series option.
On the Series option use solid line and red color. Finally set the compound type to dash. 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 the variance (+/-) by agents 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 you have applied 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 hurry!