Excel Skills for Customer Service Jobs

The Excel skills for customer service work are mostly about tickets: counting them, filtering them, spotting the overdue ones and measuring how fast the team responds. You don't need complex formulas. COUNTIF, filters and conditional formatting cover most of the job. This guide is for support agents, team leads and anyone applying to customer service roles who wants to practise the spreadsheet tasks those teams actually do.

What customer service teams use Excel for#

Customer service teams often export data from their help desk or phone system into Excel to answer questions the system's own reports don't. Common files include:

• Ticket logs: ticket ID, agent, channel, status, priority and hours to respond.
• Call logs: calls per agent per day and call outcomes.
• Schedules and attendance: who is on shift and who was absent. The guide to building an attendance tracker in Excel covers this.
• Weekly reports: tickets opened and closed, open backlog and average response time.

The examples below use this ticket log in A1:E8. Hours is the time to first response.

Counting tickets with COUNTIF#

COUNTIF counts the cells in a range that match one condition. It is the single most useful function in customer service reporting.

=COUNTIF(D2:D8,"Open")

returns 3: T-202, T-204 and T-207. Text conditions go in double quotes. Numbers with an operator go in quotes too:

=COUNTIF(E2:E8,">24")

returns 2, the tickets that took more than a day to answer (30 and 26 hours). The COUNTIF function guide lists every type of condition.

To make the share of tickets closed, divide one count by another: =COUNTIF(D2:D8,"Closed")/COUNTA(A2:A8) gives 4 ÷ 7, about 57%. Format the cell as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%).

Counting with two conditions: COUNTIFS#

COUNTIFS counts rows that meet every condition, in range-and-criteria pairs. Use it for questions like "how many tickets has Maya closed?":

=COUNTIFS(B2:B8,"Maya",D2:D8,"Closed")

returns 2 (T-201 and T-203). Put agent names in a column and point the formula at those cells, such as =COUNTIFS($B$2:$B$8,G2,$D$2:$D$8,"Open"), then fill down to build a per-agent table of open tickets.

Average response time#

AVERAGE gives the mean of a column, and AVERAGEIF averages only the rows that match a condition.

• =AVERAGE(E2:E8) returns about 10.43 hours (73 ÷ 7).
• =AVERAGEIF(D2:D8,"Closed",E2:E8) returns 2.75 hours, the average for closed tickets only.

Notice how far apart the two numbers are. Two slow open tickets pull the overall average up. When you report averages, say what's included, and consider the median (=MEDIAN(E2:E8) returns 5) when a few extreme values distort the picture.

Filtering a ticket log#

A filter hides the rows you don't need, so you can see one agent, one channel or one status at a time. Click inside the table and press Ctrl+Shift+L (Mac: ⌘+Shift+F) to turn on the filter buttons. Click the arrow on Status and tick only Open.

When a filter is on, COUNTA still counts hidden rows. SUBTOTAL counts only the visible ones:

=SUBTOTAL(3,A2:A8)

With the Open filter on, this returns 3. With no filter, it returns 7. The 3 tells SUBTOTAL to count non-empty cells, like COUNTA.

Highlighting overdue and duplicate tickets#

Conditional formatting changes a cell's colour when a rule is true, so problems stand out without extra columns.

1. Select E2:E8.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
3. Type 24 and pick a red fill.

The 30 and 26 turn red. To colour the whole row of every open ticket, select A2:E8, choose New Rule > Use a formula, and enter =$D2="Open". The $ keeps every cell checking column D.

Duplicate ticket IDs happen when the same case is logged twice. Highlight Cells Rules > Duplicate Values marks them. See how to highlight duplicates in Excel for formula-based options.

Looking up customer details and keeping data clean#

A lookup pulls one customer's or ticket's details by ID, so you don't scroll through hundreds of rows. With ticket IDs in column A and agents in column B:

=XLOOKUP("T-204",A2:A8,B2:B8,"Not found")

returns Lena. XLOOKUP needs Excel 2021 or Microsoft 365. In any version, =VLOOKUP("T-204",A2:E8,2,FALSE) gives the same answer, and FALSE makes sure you get an exact match.

Lookups and counts only work when the data is typed the same way every time. "Open", "open " with a trailing space and "OPEN" look alike to a person, but the trailing space stops a match. Three habits help:

• Use a dropdown list for Status and Channel, so agents pick a value instead of typing it (Data > Data Validation > List).
• Clean pasted text with =TRIM(A2), which removes extra spaces.
• Keep one row per ticket and one type of data per column, with no merged cells.

How to prepare for a customer service Excel test#

Customer service Excel tests, where they exist, usually stay practical: enter data, sort, filter, count with COUNTIF and calculate an average. Practise each task until it takes a minute or two. The skills overlap a lot with Excel skills for admin jobs, so that guide is worth a read too. If you're moving into a team lead role that tracks deadlines, Excel skills for project managers shows the next step, and Excel skills for marketing jobs covers customer data from the campaign side.

Practise it#

Counting and filtering are quick to learn by doing. Practise COUNTIF in a live spreadsheet: you type the formula, and it checks it instantly. Then filter a table and count visible rows with SUBTOTAL, and highlight overdue values with conditional formatting. To check your basics under time pressure, try the Excel screening quiz.