Tracking Inbound Call Abandonment Rate by Month

Inbound Call Abandonment Rate %

1. Identify the required columns in your dataset

• Confirm you have:
  • Call Date/Time
  • Outcome or Call Abandoned (Yes/No, or “Abandoned” as an outcome)
• Everything else is optional for this build.

2. Extract Month and Year from the call date

• Create a Month column from Call Date/Time:
  • Use the TEXT() function with a month format:

=TEXT([Call Date/Time],”mmmm”)

• Create a Year column from Call Date/Time:
  • Use the TEXT() function with a year format:

=TEXT([Call Date/Time],”yyyy”)

• You can delete these helper examples afterward if you already have month/year fields elsewhere.

3. Put your dataset into an Excel Table

• Convert the range to a table so structured references work cleanly.
• Note the table name (example shown in the video: Table_Calls).
• Confirm the table includes columns like:
  • Month
  • Year
  • Outcome (or the abandonment flag)

4. Create a unique list of Month and Year

• Build a list of unique Month-Year combinations using UNIQUE():
  • Select the Month and Year columns together (two-column range)
  • Apply =UNIQUE(range)
• Use keyboard selection shortcuts to grab the full range quickly:
  • Ctrl + Shift + Right Arrow
  • Ctrl + Shift + Down Arrow
• Copy the results and paste values:
  • Ctrl + C
  • Ctrl + Shift + V

5. Add calculation columns to your summary table

Create these headers next to your unique Month-Year list:

• Total Calls
• Abandoned Calls
• Inbound Call Abandonment Rate %
• Target %

6. Calculate Total Calls per Month-Year

• Use COUNTIFS() because you need multiple criteria:
  • Criteria 1: Month
  • Criteria 2: Year
• Example structure (using table structured references):

=COUNTIFS(Table_Calls[Month],B3,Table_Calls[Year],C3)

• Drag the formula down to fill all months.

7. Calculate Abandoned Calls per Month-Year

• Copy the Total Calls formula, then add one more criterion:
  • Criteria 3: Outcome equals Abandoned (or Yes)
• Example structure:

=COUNTIFS(Table_Calls[Month],B3,Table_Calls[Year],C3,Table_Calls[Outcome],$E$2)

• Put the word Abandoned (or your matching label) in a single cell (like E2) and lock it:
  • Press F4 on the reference so it stays fixed when you drag down
  • If you do not lock it, the reference will shift and break the logic
• Drag the formula down.

8. Calculate the Abandonment Rate percentage

• Divide abandoned calls by total calls:

=AbandonedCalls/TotalCalls

• Format as percent:
  • Ctrl + Shift + 5
• Drag down.

9. Create a dynamic Target % column

• Enter a single target value at the top (example used: 10%).
• In the target column for each row, reference that single target cell:

=TargetCellAbove

• This makes the entire target line update if you change the benchmark.

10. Build the X-axis label for the chart

• Create a “Month – Year” label so the chart axis reads cleanly.
• Combine Month and Year into one cell using concatenation:
  • Month + space + dash + space + Year
• Structure shown in the video:
  • Month & ” – “ & Year

11. Insert the combo chart

• Select the chart inputs:
  • Date label column (Month-Year text)
  • Abandonment Rate %
  • Target %
• Insert:
  • Insert → Recommended Charts
• If it does not look right:
  • Go to All Charts → Combo
  • Set:
    • Abandonment Rate % as a line
    • Target % as a line
  • Put the percentage lines on the correct axis so they are not scaled against call volume.

12. Format the target benchmark line

• Click the target line and open format options.
• Make it visually lighter:
  • Reduce line thickness
  • Change the line style (dotted/dashed)
• The goal is to see the benchmark without it dominating the chart.

13. Add data labels only where needed

• Add data labels to the line you want to emphasize (in the video, labels were added selectively to avoid noise).
• Use the chart “+” icon or Chart Elements:
  • Data Labels
• If labels are hard to read:
  • Click the labels
  • Apply a light fill color behind them so they stand out.

14. Title the chart

• Update the chart title to match the output:
  • Example used: Call Abandonment Rate Percentage by [time period]
• Keep it consistent with the KPI name used on the page.