How to Forecast Call Volume in Excel

Predicting how many calls your support or sales team will receive is crucial for smart staffing, but it can feel like trying to guess the weather. Get it wrong, and you're either paying agents to sit around or leaving customers waiting in long queues. This guide will show you how to build a reliable call volume forecast right inside Excel, using your historical data to make educated predictions about the future.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Forecast Call Volume?

A good forecast is more than just a number, it's a strategic tool. It helps you prepare your team for what's coming, ensuring you have the right number of people working at the right times. Accurately forecasting call volume directly impacts your bottom line and customer satisfaction.

The core benefits include:

• Optimized Staffing: Avoid being overstaffed during slow periods and understaffed during peak times. This saves money on payroll and reduces agent burnout.
• Better Budgeting: Predict your operational costs with greater accuracy, from salaries to software licenses.
• Improved Customer Experience: When you're properly staffed, wait times go down, service levels go up, and customers are happier. This leads to higher retention and better reviews.
• Proactive Management: Spot trends and anticipate surges in calls due to marketing campaigns, product launches, or seasonal demand before they happen.

Gathering Your Historical Call Data

Before you can predict the future, you need to understand the past. The quality of your forecast depends entirely on the quality of the data you feed it. Your goal is to gather at least 3-6 months of historical data, though a full year is ideal to capture annual seasonality.

What Data to Collect

At a minimum, you'll want to collect the total number of inbound calls per day. If you can get more granular data, such as calls per hour, that's even better for scheduling shifts. Here's a checklist:

• Date: The full date for each data point (e.g., 01/15/2024).
• Call Volume: The total number of calls received during that period.
• Day of the Week: This helps identify weekly patterns (e.g., Mondays are always busiest).
• Time Interval (Optional): The specific hour or 30-minute block the calls were received in.
• External Factors (Notes): Keep track of marketing campaigns, product issues, service outages, or holidays. These events cause spikes or dips and provide valuable context.

Where to Find It

You most likely have this data already. Check the reporting and analytics sections of your existing tools:

• Call Center Software: Platforms like Talkdesk, Aircall, or Genesys have robust reporting dashboards where you can export call logs.
• Phone System (VoIP): Services like RingCentral or Nextiva track all call history.
• CRM: Tools like Salesforce Service Cloud or HubSpot Service Hub often have built-in call tracking and reporting features.

Look for an "Export to CSV" or "Export to Excel" button. This will give you the raw data you need for your forecast.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Structuring Your Data in Excel

Once you have your CSV file, open it in Excel and clean it up. Your goal is to have a simple, two-column table. This structure is what Excel's forecasting formulas expect.

1. Create a new sheet in your workbook.
2. In Column A, list the dates in chronological order. Make sure Excel recognizes them as dates (right-click -> Format Cells -> Date).
3. In Column B, list the corresponding total call volume for each date.

Your table should look something like this:

Method 1: Using the FORECAST.ETS Function

Now for the fun part. While older tutorials might point you to the simpler FORECAST function, Excel's FORECAST.ETS is much more powerful. It uses an algorithm called Exponential Triple Smoothing (ETS), which is specifically designed for time-series data and automatically accounts for seasonal trends.

Don't worry, you don't need to be a data scientist to use it. Excel does all the heavy lifting.

Step-by-Step Forecasting

Let's say your historical data runs from January 1st to March 31st and you want to forecast call volume for April. Assume your data is in cells A2:B91.

1. Set up your future dates. In the cells directly below your historical dates (e.g., starting in cell A92), type out the dates you want to forecast for (e.g., 04/01/2024, 04/02/2024, etc.).
2. Enter the formula. Click into the cell next to your first future date (e.g., B92). Here, you'll type the FORECAST.ETS formula.

The formula looks like this:

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

You only need to worry about the first three parts for a basic forecast.

Breaking Down the Formula

Let's apply it to our example. In cell B92, you would type:

=FORECAST.ETS(A92, $B$2:$B$91, $A$2:$A$91, 1)

• A92 is the target date—the date you want a prediction for.
• $B$2:$B$91 is your historical call volume data range. Using dollar signs ($) locks this range so it doesn't shift when you drag the formula.
• $A$2:$A$91 is your historical date range, also locked.
• 1 is the optional seasonality parameter. Entering 1 tells Excel to automatically detect seasonal patterns (like weekly cycles).

Drag to Fill

Once you've entered the formula in B92, press Enter. Excel will calculate the forecasted call volume for April 1st. Click on cell B92, then drag the fill handle (small square at the bottom-right corner) down along your future dates. Excel will fill in forecasted values for each day.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Adding Nuance: Special Events and Adjustments

Excel's formulas are smart, but they can't predict a billboard campaign you're launching next month or that your main competitor is having a service outage. A good forecast combines statistical analysis with human knowledge.

Review your forecasted numbers and ask yourself:

• Are there upcoming holidays? Public holidays usually cause a significant dip in call volume. You might want to manually adjust the forecasted numbers for those days downward.
• Are we launching a marketing campaign? A major promotion or email blast will almost certainly drive more calls. Based on past campaigns, you might manually increase the forecast by 15-20%.
• Is there a known product issue or bug? If your engineering team is rolling out a patch for a known problem, you can expect support calls related to it to decrease.

Keep a separate column for your notes or manual adjustments to track why you changed the algorithm's output. This makes your model smarter over time.

From Forecast to Staffing Plan

You have your forecasted call volume. Now what? The next step is to translate that number into a staffing plan. To do this, you need one more key metric: Average Handle Time (AHT).

AHT is the average time an agent spends on a call, including talk time, hold time, and after-call work. You can get this from your call center software. Let's say your AHT is 6 minutes (360 seconds).

Calculating Staff Hours

You can use a simple formula to determine the total agent hours needed for the day:

Staff Hours Needed = (Forecasted Calls * AHT in Seconds) / 3600

For example, if your forecast for next Monday is 350 calls and your AHT is 360 seconds:

(350 * 360) / 3600 = 35 hours

This means you need 35 total hours of agent "on-call" time to handle that volume. However, this is just the raw number. It assumes an agent is on a call 100% of their shift, which is impossible. You need to account for breaks, meetings, training, and idle time (this is often called "shrinkage"). A safe industry benchmark is to plan for a utilization rate of about 85%. So, you'd divide your calculated hours by 0.85 to get a more realistic staffing target:

35 / 0.85 ≈ 41.2 hours

This tells you that you'll need to schedule just over 41 'logged-in' agent hours, which you can break into shifts (e.g., five 8-hour shifts with some overlap).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Visualizing Your Forecast in a Chart

Numbers in a spreadsheet are useful, but a visual chart makes trends immediately obvious. Creating a line chart in Excel is simple and highly effective.

1. Select all of your data, including the headers for both your historical and forecasted dates and call volumes.
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts section, click the Line Chart icon and select the first 2-D Line option.

Excel will generate a chart showing your historical call volume as a solid line, with your forecast continuing as a dotted line. This visual helps you see if your forecast aligns with past trends and makes it easier to present your findings to your team or manager.

Final Thoughts

Creating a call volume forecast in Excel transforms your staff planning from reactive guesswork into a data-driven strategy. By gathering historical data, using the FORECAST.ETS function, and applying a layer of business context, you can build a reliable model that improves efficiency and customer satisfaction.

While Excel is a powerful tool for forecasting, this process often starts with the time-consuming step of manually exporting and cleaning data from your call center software or CRM. Here at Graphed, we built our platform to eliminate that busy work. We connect directly to your data sources like Salesforce, HubSpot, and Google Analytics, keeping your dashboards updated in real-time. Instead of wrestling with formulas, you can just ask questions in plain English, like "Show me a chart of our projected call volume for next quarter," and get an instant, interactive visualization.