How to Forecast in Excel

If you’re curious about how a business will do in the future, you can use its historic data to make smart guesses. Excel has a simple forecasting tool to help you see what might happen next. This tool isn’t just easy to use; it’s also very useful for financial modeling. Whether you’re interested in future sales or just curious about trends, Excel makes it easy to see what might be coming.

I’ll demonstrate Excel’s easy tool to create a future forecast. We’ll use it to make a sales forecast. For historic data, we’ll use the last ten years of quarterly sales for Home Depot.

Home Depot’s fiscal year ends on January 31st.

I’ve included the quarter numbers to the left. This makes it easier for us to recognize the quarters. But, we won’t use them in the upcoming tools.

Step 1: Visualize the Data with a Line Chart

When you get a dataset like the one mentioned above, it’s helpful to first draw it on a line chart. This chart type is great for tracking changes over time. It does this by connecting data points with lines. This clearly shows any increases or decreases. This visual layout makes it easy to spot how sales have changed from one quarter to the next.

Highlight the dates and sales values. Create a quick line chart by selecting Insert (tab) -> Charts -> Line Chart.

Look at the highs and lows in the chart. You might notice patterns that repeat every year, which shows us how the sales trends change with the seasons.

Simply drawing a straight trendline to forecast future quarters isn’t enough. It doesn’t account for these seasonal variations in the data.

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Step 3: Use the Excel Forecast Sheets

The Forecast Sheet is in the Data tab under the Forecast group. It’s been there since Excel 2016.

Forecast Sheet in Excel
  • Set Up Your Forecast: Click on the sales data you prepared. Then click the Forecast Sheet button.
forecasting in excel
  • Preview Your Forecast: Excel will show you what your sales might look like in the future. You can see this as a line or bar chart. The actual past sales are shown in blue, and the predicted future sales in orange.
forecasting in excel
  • Adjust the Details: The thick orange line (or bar) is your main forecast. The thinner lines around it are the confidence bounds, which tell us where future sales are likely to be 95% of the time.

In the bar chart version, the upper and lower confidence bounds are represented as Vertical Error Bars.

If your confidence level is set to 95% (the default setting in Excel), then you can expect that 95% of your future data points will fall within the upper and lower bounds shown.

Step 4: Customize Your Forecasting Sheet

  • Change the End Date: If you want to see further into the future, just pick a new end date for the forecast.

Forecasting Sheet – Advanced Options

Click the Options button at the bottom left to see more ways to customize your forecast.

forecasting sheet advanced options

Key Settings You Can Change:

  • Forecast Start: Choose when your forecast begins. Start before the last known data. This lets you compare the forecast to actual results. You can check its accuracy. But remember, using fewer data points may reduce the forecast’s reliability. The more data you use, the better the forecast will be.
  • Confidence Interval: This setting shows the range where 95% of future data points are expected to fall. A higher percentage means you can be more confident in the predictions.
  • Seasonality: This number represents the length of the repeating pattern in your data. For example, if you’re looking at monthly data in a quarterly cycle, the seasonality is 4.
  • Timeline Range: Select the cells that include the dates.
  • Values Range: Select the cells that contain the numbers you’re forecasting.
  • Fill Missing Points Using: Excel can guess missing data points. It uses the average of nearby points. If more than 30% of data is missing, this may not be accurate. Alternatively, you can set missing points to zero.
  • Aggregate Duplicates Using: If your data has multiple values for the same time, Excel usually averages them. You can choose other methods like median, count, or sum instead.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Creating the Forecasting Sheet

Clicking CREATE in the Create Forecasting Worksheet dialog box produces a new forecasting sheet with two objects:

  • Table: This includes past dates and sales, along with future predictions and their lower and upper confidence limits. These limits show the range where future sales are most likely to fall.
  • Chart: This displays the data from the table. The actual sales are shown in blue, and the predicted sales are in orange.
forecasting sheet

Any changes you make in the table will automatically update the chart. This means if you adjust future sales numbers or dates, you’ll see the chart change in real time.

Understanding the Excel Forecast Function

When you create a forecasting sheet in Excel, it uses a powerful function to predict future values. Let’s look into how forecasting in Excel works and what you can see on your sheet.

Examining the Excel Forecast Function

  • Locating the Function: If you click on any cell in the table that shows forecasted values, you’ll see a formula in the Formula Bar. Excel uses the FORECAST.ETS function to generate these predictions.
Excel forecast.ets function
  • How It Works: This function applies an algorithm called Exponential Triple Smoothing. It’s designed to handle data that shows patterns or cycles, like seasonal sales trends.

Why FORECAST.ETS Is Effective

  • Handling Data Gaps: This function can still produce accurate forecasts even if up to 30% of your data points are missing.
  • Flexibility with Time Intervals: It works well with data collected at regular intervals—whether those are years, quarters, months, or days.

How FORECAST.ETS Works

The FORECAST.ETS function uses historical data to predict future values. Here’s a breakdown of how it operates:

  • Exponential Smoothing: This technique weighs more recent data points more heavily than older ones, assuming that recent patterns are more likely to continue into the future.
  • Triple Smoothing: The function adjusts for three components—level, trend, and seasonality:
    • Level: The average value in the series.
    • Trend: The increasing or decreasing value over time.
    • Seasonality: Repeating patterns or cycles in the data, such as higher sales during the holiday season.

By using the FORECAST.ETS function, Excel simplifies complex statistical methods into a simple tool. You can use it even if you don’t have a background in forecasting or statistics. This makes it a valuable asset for anyone needing to plan based on predictable data trends.

Download the Practice Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real time. Master the Excel Forecasting Tool with a hands-on example. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.