How to Create a Dynamic Dashboard in Excel Using Call Center Performance Data
Excel is not just for crunching numbers and using formulas and pivot tables to analyze data. It can be used for so much more. Excel is often overlooked when we think of data visualizations and dashboards. With tools like Power BI and Tableau, who needs Excel, right?
Wrong!
Excel can be used for creating dashboards too!
Many companies use Excel for most/all of their data analysis needs, so it is important to know how to create dashboards that will help inform our stakeholders in a meaningful way.
Let's create a dashboard that will impress!
In this project, I will show you how to create an interactive dashboard using Call Center Performance data.
If you want to follow along, the CSV dataset can be found in my Github (as well as a link to the completed dashboard and calculations used).
The Data
Looking through the data, we can see that the dates range from Jan 1, 2015 to March 21, 2015, and the data comprises 5000 rows of call center data for 3 months. We can also see the timestamps in the Date column show that the call center is open from around 9am to 6pm (9 hours a day), which is when the sales agents take their calls. We also see different departments, the agent names, the speed of answering calls, the talk duration, and the corresponding satisfaction ratings (which range between 1 and 5).
Developing the Dashboard and Corresponding Calculations
Creating a Dynamic Week View
On a tab labeled "Calcs," we will start by adding a couple of rows that we will use for our dynamic dashboard:
On a new tab labeled "Dashboard," we can start by adding a rectangle box at the top and fill it with any color you choose:
Next, we want to add a scroll bar from the Developer tool. Why? We want to create a scroll bar such that when someone clicks on either arrow, the Week number changes (i.e a dynamic view of our data).
Change the shape of the scrollbar to a horizontal position and move it to the top left of the bar:
Since we have 3 months of data and we want each click of the scroll bar to be in weekly increments, the minimum number of weeks is 1 and the max is 12. We want the scroll bar to be controlled by a cell on the Calcs tab, which is A2 in our case.
This is how we will fill out the options:
We can test the scroll bar and see that every time one of the bars is clicked, it makes the week numbers go up by 1 increment, and corresponds to cell A2 in our Calcs tab:
We want people to know what week they are selecting, so let's add a text box to our dashboard, beside the scroll bar. We want the text box to link to our Calcs tab again, so in our Calcs tab, let's create an interactive formula that links to cell A2 from before. The formula in E2 in the Calcs tab is this:
Back on our Dashboard tab, we can write the formula in the text box we created:
Now, as the scroll bar is clicked, the week number shows up beside it as well.
Let's specify what a week actually corresponds to. If we look at the calendar for 2015, we can see that Jan 1, 2015 (the start date of our data) is on a Thursday. If we start our week on that day, then our first week would be only 4 days, which would not match any of the other weeks. So we want to start with Monday (Jan 5, 2015), that way the first week has a full 7 days.
In the Calc tab, in the Date section we created, we will create our start date as follows (note: make sure that A2 shows Week 1 before you do this):
"Helper" Column
Now we can create a "helper" column so that we do not have to create several long and complex formulas in our next steps. To do this, we will go to the Data tab, and create a column called "Date Check".
We are going to input the following formula into the first cell of this column, and then drag it down:
Since we specified that Week 1 starts on Jan 5, we should see all dates as False except for Jan 5 to Jan 11 (if Week 1 is selected), which is what we see. We can also test this by moving the scroll bar on the Dashboard to different weeks and going back to the data tab to make sure that only the dates that would fall within that week show as True.
Creating KPIs
On the Calcs tab, let's make a list of KPIs that we want to calculate:
For the Total Calls calculation, we can use a COUNTIF formula and the "Date Check" column we created in our Data tab. So now, since we created this helper column "Date Check," the values we want will automatically update as the week number updates.
To get the Calls Answered, we can use a SUMPRODUCT formula (a formula we will be using a lot in this project):
The rest of the formulas for the KPIs are shown here:
-> The "--" converts the number of TRUE cells in the Date Check part of the formula into 1's and 0's so that it can be used in the formula, otherwise, SUMPRODUCT considers blank cells as text cells and the calculation won't work. The calculation is then divided by B4 (i.e. Total Calls that we calculated earlier).
-> We could have used a SUMPRODUCT formula again here, but we already calculated the Total Calls and Calls Answered, so we can use those numbers instead.
-> Take the Total Calls (B4) and divide by 7 days worked in a week, calls occur for 9 hours a day (9am to 6pm), and there are 60mins in an hour.
-> This is the satisfaction score out of all calls answered.
-> To find calls of less than 180 seconds, we have to use the TIME formula. It is based on hour, mins, secs, so we want 3 mins (i.e. 180 seconds), so the TIME formula becomes TIME(0,3,0). Also include the number of Answered Calls, because if the call wasn't answered, then there is no satisfaction rating.
-> Divide the previous result by the Calls Answered (B5).
-> Similar to the "Calls of Less than 180 Seconds" formula above.
The resulting KPIs for Week 1 are:
Now we can go to our Dashboard tab and add some of these KPI's to the top section of our sheet. Like before, add a few rectangle boxes at the top and some text boxes. Link the data cells to the corresponding cell on the Calcs tab we just completed above.
Note these are dynamic values, so if we test changing the Weeks, the values change as well:
Note that the number of decimal places is excessive, so we can go back to the Calcs tab and adjust the number of decimal places, and it will change on our dashboard:
Data Per Agent
Next, let's create a table for our Dashboard that contains all the data for each Agent.
To get Total Calls for each agent:
=SUMPRODUCT((Data[Date Check]=TRUE)*(Data[Agent]=Calcs!$A15))
-> as we drag the formula down the column, the agent name changes.
The rest of the formulas are here:
-> Same as previous formula except we added one more condition (if the call was answered).
-> Adding another condition which is the Speed of Answer, and dividing by the Total Calls answered by that agent.
-> Divide by the Total Calls again to get the percent resolved.
-> Same formula as previous, but not dividing by Total Calls as we want a number, not a percentage.
We get this table of results:
We could just paste this Agent table into our Dashboard but we want some functionality when a radio button is clicked, for example. If we go to the Developer section on our Dashboard tab, we can insert a radio button:
Insert 4 radio buttons which we will move to the correct spots after.
Go to Format Control for one button and select these options and link them all to this Sorting Control cell we have on the Calcs tab. This formatting will then apply to all buttons, and all will be linked to the Sorting cell.
So each button is now labeled in order; if you click on the third button, the Sorting cell shows 3, and so on:
That is, if they want to sort by certain columns in our table, they can click the radio button of the column they want to sort by. But we still have to configure this.
"For Sorting" Table
So add another set of columns to our Agents table in the Calcs tab:
The For Sorting cell will use the formula:
=INDEX($B$15:$E$22,ROWS($G$15:G15),$B$2)
So selecting the first radio button will give back the results of Total Calls (column 1). Selecting the 2nd radio button will give results of the next column, Calls Answered (column 2), etc.
Notice we have some repeated numbers (53 appears twice in the "Calls Answered" column for example). We want them to appear as unique numbers, so in the next column under For Sorting, use this formula:
=H15+ROWS($H$15:H15)/1000000
In column 3 under For Sorting, this corresponds to the data in "Avg Speed of Answer" if the 3rd radion button was selected. We can use an IF formula if we wanted to sort this column in ascending order:
=IF($B$2=3,SMALL($I$15:$I$22,ROWS($I$15:I15)),LARGE($I$15:$I$22,ROWS($I$15:I15)))
In the next column under For Sorting we will use MATCH to find the position of the value in column 3 above in column 2. That is, where is the value in column 3 found in column 2 (remember column 3 is just column 2 sorted):
=MATCH(J15,$I$15:$I$22,0)
In column 5, use INDEX and that value we found in the MATCH column to find the corresponding Agent:
=INDEX($A$15:$A$22,K15)
In other words, when sorted by "3" (i.e. Avg Speed of Answer), Becky has the fastest speed because she shows up with the lowest time (at the top).
Why did we do all this? Because if there were some values that were the same between multiple agents, when we try to sort, we might not get the correct Agent name. So we had to make the values unique and then order them in ascending order and then find the agent rankings using MATCH and INDEX.
Now let's make a new table of agents using this sorted data. So just copy the column headers that we want, beside the For Sorting table, and we will use XLOOKUP to pull out the corresponding data from the table on the left.
Here are the formulas used:
This is our new sorted table:
Let's paste this table into our Dashboard below the radio buttons we created:
Now we can add another column under Call Resolution (%) to add some conditional formatting. When the value is above 80% it is a green flag, when it is above 70% it is a yellow flag, and anything below 70% is a red flag:
Call Resolution Trend Line
We also want to add a trend line on Call Resolution per day of the week. To do this, we need to create a new table on our Calcs tab:
Use =(cell) formula for the agent names to select them from the For Sorting table so that the names are in the same order:
To get the date for Monday, we use the SUMPRODUCT formula again. In order to force the date to be evaluated properly, we need to wrap the first part of the formula within an INT formula.
=SUMPRODUCT((INT(Data[Date])=Calcs!$D$2)*(Data[Agent]=Calcs!$A25)*(Data[Resolved]="Y"))
For every other day column, we can paste the same formula, but for Tuesday we need to add 1 to the date part of the formula (to move the date we want to search for by 1), add 2 for Wednesday, 3 for Thursday, and so on.
The rest of the formulas are:
The resulting table looks like this:
Now back to the Dashboard, we can use this table to insert a trendline using Sparklines. First, add another column to the one table we already made on the dashboard, called "CR Trend" (i.e. "Call Resolution Trend).
On the "Insert" tab, select the "Line" Sparkline. Select the data range as the numbers in the table we just created on our Calcs tab, and the location range as the column where we want to enter our sparklines:
We now have our CR trend lines:
Call Abandon Rates
Next, we want to create a chart of Call Abandon rates. On our Calcs tab, we will create a new table and input the different departments:
Here are the formulas used to fill in the columns:
-> That is, if the Abandoned Call % is higher than the 20% SLA Limit, the formula will return the Abandon Call %. If it is lower, then the result will be "N/A" meaning the limit was not breached.
Here is our completed table:
Select the table and on the Insert tab, choose a clustered column chart (just paste it on the Calcs page as we can copy and paste it to our Dashboard after).
The resulting chart looks like this and can be pasted into our Dashboard:
Satisfaction Scores Per Agent
Let's make a chart of Satisfaction scores for each agent. Start by creating a new table on the Calcs tab:
Insert the agent names by linking them from the For Sorting table, just like we did before.
The formulas we used are:
Resulting table:
Let's create a 2-D bar chart with this data.
Next, we want to create a pie chart of the overall satisfaction score. Create a new table on our Calcs tab:
In the first column, type 50, 20, 30, 100.
Select those numbers and insert a Donut Chart.
Why did we select the numbers above that we did? If we rotate the angle of the pieces to 270 degrees so that the large "100" portion is at the bottom, we can change the color to "no fill" so that the donut chart now looks like a half-donut shape.
For column 2 in our Satisfaction Chart table, we want to create a needle that is dependent on the score. Use these formulas (B9 is the Satisfaction Overall score we calculated in the first table we made, and the "2" is the needle size we want):
So we get this pie chart from that column:
As before, make all the slices no fill except for the part we want as a needle:
Align them on top of each other and paste them into the Dashboard (format the colors as you choose). Also, add a text box that links to the Satisfaction Score on the Calcs sheet so that we can see the number populate as well:
SLA Limits Data
Lastly, add another text box with some SLA data and have them link to the values we calculated on the Calcs tab:
Adjust the formatting and colors of the dashboard as you choose and the final Excel dashboard looks like this:
You can also view the full interactive dashboard and data files on my Github.
Insights and Recommendations
Overall, we notice that the percentage of calls answered in less than 3 minutes (180 seconds) hovers in the 30 to 40% range, which is quite high. If call answer speed is an important KPI for the company, agents should be trained in answering calls quicker to get that number lower. That could help increase the overall satisfaction scores from clients, as well.
So better training on call speed and how to successfully resolve customer issues should be a priority in order to improve satisfaction scores, bring in more business, and also keep the clients happy.
I hope you enjoyed this write-up! For more data projects and tips and tricks, follow or connect with me on LinkedIn!
Thanks for reading!
Manager @IBM ||14+ yrs total Exp || PM at IBM India Pvt Ltd in Insurance domain || Electronic Payments Industry(VISA Inc.)
1yHi Sarah, I have one question. What do we if our data changes every day. For example, share values of companies. DO i need to load those data every day in the excel by deleting the existing file and then refresh ?
Great job 👌😊
Data Analytics | Aviation | Biometrics | Python
2yREALLY nice job with this, Sarah. I mean… truly an exemplar. Thanks for setting the bar!
😃
Data Analyst I Data Scientist I Cloud Engineer
2yThis is clean and excellent. Great dashboard Sarah Rajani, CTP