January 26th, 2026
How to Use FORECAST in Excel Using Historical Data
By Simon Avila · 19 min read
I spent weeks researching how businesses apply predictive analytics, from forecasting revenue to managing risk. Here's how it works, which techniques matter for business teams, and the tools that help you plan ahead.
What is forecasting in Excel?
Predictive analytics is an advanced form of data analytics that uses historical data, statistics, and machine learning to predict future outcomes. It goes beyond reporting past results and focuses on what’s likely to happen next, such as future revenue, demand, or customer behavior.
Teams use predictive analytics to plan ahead and reduce risk. Finance teams forecast cash flow and flag invoices that may go unpaid, sales teams estimate future revenue, and operations teams spot inventory issues early. These predictions help teams act sooner and make more confident decisions.
When to use Excel forecasting
Excel forecasting works well when you need quick predictions without specialized software. Here's when it makes sense for business users:
You have clean historical data: At least 12 months of consistent sales, revenue, or performance metrics.
Your trends are relatively stable: Steady growth or predictable seasonal patterns without major disruptions.
You need fast answers: Quick projections for budget planning, presentations, or initial estimates.
Your team already uses Excel: No need to learn new software or pay for additional tools.
You're forecasting single metrics: One product line, one revenue stream, or individual campaign performance.
Excel works best for simple monthly or quarterly forecasts where trends are predictable. I think more complex scenarios with multiple variables work better with specialized analytics platforms. But for the scenarios covered here, Excel's built-in forecasting delivers solid results.
How to prepare your data for forecasting
Your forecast accuracy depends on how you structure your data before running any calculations. Excel needs a column for your timeline and one for your values. The timeline column holds dates or time periods like months or quarters. The values column holds the numbers you want to forecast, such as sales or revenue.
Here’s how to set your data up correctly:
Use consistent time intervals: Monthly data should have one entry per month. Weekly data needs one entry per week. Mixing intervals breaks Excel's pattern recognition.
Arrange dates chronologically: Start with your oldest data point and work forward. Excel reads top to bottom.
Keep values in number format: Remove dollar signs, percentage symbols, and text from your values column. Excel can't calculate forecasts from formatted text.
Fill or remove gaps: Excel's forecasting works best with complete datasets. The Forecast Sheet can estimate some missing values, but you'll get more reliable predictions when gaps are minimal. Fill missing data by averaging nearby values or remove incomplete periods entirely.
I always convert my data range into an Excel table before forecasting. Select your data, press Ctrl+T (or Command+T on Mac), and Excel automatically expands the range as you add new rows. This saves time when updating forecasts monthly.
Here’s a tip: A common mistake people make is including totals or summary rows in their data ranges. Excel treats these as data points and skews the forecast. Keep your raw data separate from any calculations or summaries.
How to forecast in Excel using FORECAST.LINEAR
FORECAST.LINEAR predicts future values using linear regression, which fits a straight line through your historical data points. This method works best when your data shows consistent growth or decline without sudden spikes or major fluctuations.
You'll need your historical data already set up in two columns: dates in column A and values in column B. Then, add the future dates you want to forecast below your historical data.
Here's how to use FORECAST.LINEAR:
Click the cell where you want your first forecast: This should be in the same column as your historical values, next to your first future date.
Type the formula: Start with =FORECAST.LINEAR( and Excel will show you the required inputs.
Select your future date: Click the cell containing the date you want to forecast. This is your x value.
Add a comma and select your historical values: Highlight all the cells containing your past data (sales, revenue, etc.). These are your known_y's.
Press F4 to lock the range: This keeps the range fixed when you copy the formula down. You'll see dollar signs appear like $B$2:$B$13.
Add another comma and select your historical dates: Highlight all your past dates. These are your known_x's. Press F4 again to lock this range too.
Close the parentheses and press Enter: Excel calculates your forecast for that date.
Copy the formula down: Click the small square in the bottom-right corner of the cell and drag down to fill forecasts for all your future dates.
Your formula should look something like this: =FORECAST.LINEAR(A14, $B$2:$B$13, $A$2:$A$13)
I use FORECAST.LINEAR for quick monthly sales projections when I need a simple trend line. It takes about two minutes to set up once your data is clean.
How to forecast in Excel using the Forecast Sheet
To create a visual forecast, select your historical data, go to the Data tab, click Forecast Sheet, and Excel will generate charts and predictions automatically. You'll get a complete worksheet showing your historical values, forecasted numbers, and confidence intervals that indicate prediction reliability.
This method works well when you need to present forecasts visually or want to see prediction ranges rather than single-point estimates.
Here's how to create a Forecast Sheet:
Select your data range: Highlight both columns, including your dates and values. Make sure you're selecting only historical data, not future dates.
Open the Forecast Sheet tool: Click the Data tab in the ribbon, then click Forecast Sheet in the Forecast group.
Review the preview: Excel generates an instant preview showing your historical data as a solid line and forecasted values as a dotted line. The shaded area shows the confidence interval, or the range where your actual results will likely fall.
Choose your chart type: Click the bar chart or line chart icon in the top-right corner of the preview window. Line charts work better for most time-series data.
Set your forecast end date: Excel automatically forecasts a few periods ahead. Change the date in the Forecast End box if you need predictions further into the future.
Click Create: Excel opens a new worksheet with your forecast chart at the top and a data table below showing both historical and predicted values.
The new worksheet displays your dates in one column and historical values in another. It shows your forecasted values in a third column, plus two separate columns showing upper and lower confidence bounds. The chart links directly to this table, so any edits you make to the numbers update the visualization immediately.
I use Forecast Sheet when I need to show forecasts in presentations. The visual confidence intervals help explain prediction uncertainty to team members who aren't familiar with forecasting.
How to customize your Forecast Sheet
Before clicking Create in the Forecast Sheet tool, click the Options button to adjust how Excel generates your forecast. These settings help you get more accurate predictions for your specific data patterns.
Here are the key customization options:
Forecast Start: Choose where your forecast begins. Setting this before your last historical data point lets you compare predicted values against actual results to test accuracy.
Confidence Interval: This shows the range where your actual values will likely fall. The default is 95%, which means 95 out of 100 future data points should fall within the shaded range. Lower percentages create narrower ranges but less certainty.
Seasonality: Excel automatically detects repeating patterns in your data, like holiday sales spikes or summer slowdowns. If detection fails or seems wrong, choose "Set Manually" and enter the number of periods in your pattern. For monthly data with yearly patterns, enter 12.
Fill Missing Points Using: If your data has gaps, Excel can either treat them as zeros or use interpolation to estimate missing values based on surrounding data points. Interpolation usually works better unless the gap represents an actual zero.
Aggregate Duplicates Using: When multiple values exist for the same date, Excel can average them, use the maximum, or apply other calculations. Average works well for most business scenarios.
Tip: The "Include Forecast Statistics" checkbox adds a separate worksheet with technical accuracy metrics. Most business users don't need this, but it's helpful if you're comparing multiple forecasting approaches.
How to forecast in Excel using AI (Copilot)
Copilot allows you to forecast in Excel using AI. You describe what you want to forecast in plain English, and Copilot analyzes your data. Then, it generates predictions with visualizations without you needing to pick specific functions or formulas.
This approach works well when you're not sure which forecasting method fits your data. It’s also good when you want quick exploratory forecasts without building formulas manually.
Here's how to use Copilot for forecasting:
Open the Copilot pane: Look for the Copilot button in your Excel ribbon. If you don't see it, check with your IT administrator about access.
Make sure your data is ready: Copilot needs clean historical data in columns, just like the other methods. Label your columns clearly so Copilot understands what it's analyzing.
Type your forecasting request: Use natural language to describe what you need. Be specific about the time period and what you're forecasting.
Review the results: Copilot generates predictions and often creates charts automatically. It usually explains the steps it took so you can understand and verify the approach.
Here are some example prompts that work well:
"Forecast monthly sales for the next six months based on the last year of data"
"Show me quarterly revenue predictions for 2026 using our 2024-2025 performance"
"Predict inventory needs for the next quarter based on seasonal patterns"
I've used Copilot when exploring multiple forecasting scenarios quickly. It's faster than manually testing different methods, but I always double-check the results against my own calculations for important decisions.
Important tip: Copilot in Excel requires a compatible Microsoft 365 subscription. It's not available in standalone Excel purchases. If you're using Excel through your company, your IT team controls who can access Copilot, so you may need to request permission. Some Copilot features are available to individual Microsoft 365 subscribers through Excel for web and mobile.
Limitations of Excel forecasting
Excel forecasting relies on manual data entry, struggles with large datasets, and lacks real-time automation. These constraints become more obvious as your data complexity grows or your forecasting needs expand.
Here are the limitations you may experience:
Manual updates eat up time
Excel can't pull fresh data automatically from your CRM, marketing platforms, or sales databases.
You’ll need to add new data to your spreadsheet manually. Then you have to refresh your forecasts. For monthly forecasts, this means scheduling time each month. You spend that time copying data, checking formulas, and regenerating predictions.
I've spent entire mornings updating forecasts. That's why, if you're managing complex predictions, it's better to use a more advanced tool.
Errors compound fast
One misplaced formula, accidentally deleted cell, or wrong data import can throw off your entire forecast. Excel won't catch these mistakes automatically, so you'll have to keep your eyes peeled. I've seen quarterly forecasts look 10x better because of a single transposed number.
Large datasets slow everything down
Excel starts lagging when you're working with thousands of rows or years of daily data. Opening files takes longer, formulas recalculate slowly, and charts take time to refresh.
Copilot can run some analysis in the cloud, which helps with complex calculations, but I find that large Excel files still slow down regardless of which forecasting method you use.
Seasonality detection needs help
While the Forecast Sheet can detect seasonal patterns automatically, it doesn't always get it right. Promotional calendars, industry-specific cycles, and irregular events require manual adjustments. I've found this takes trial and error to perfect.
Ready to move beyond Excel's forecasting limits? Try Julius
Learning how to forecast in Excel teaches you the basics, but maintaining accurate predictions across multiple data sources gets time-consuming fast. Unlike Excel, Julius connects directly to your databases. We designed it to generate forecasts through natural language queries, no formulas required.
Julius is an AI-powered data analysis tool that can turn historical data into forecasts, charts, and reports without manual spreadsheet updates.
Here’s how Julius helps:
Catch outliers early: Julius highlights suspicious values and metrics that throw off your results, so you can make confident business decisions based on clean and trustworthy data.
Quick single-metric checks: Ask for an average, spread, or distribution, and Julius shows you the numbers with an easy-to-read chart.
Built-in visualization: Get histograms, box plots, and bar charts on the spot instead of jumping into another tool to build them.
Recurring summaries: Schedule analyses like weekly revenue or delivery time at the 95th percentile and receive them automatically by email or Slack.
Smarter over time with the Learning Sub Agent: Julius's Learning Sub Agent automatically learns your database structure, table relationships, and column meanings as you use it. With each query on connected data, it gets better at finding the right information and delivering faster, more accurate answers without manual configuration.
One-click sharing: Turn a thread of analysis into a PDF report you can pass along without extra formatting.
Direct connections: Link your databases and files so results come from live data, not stale spreadsheets.
Ready to see how Julius can help your team make better decisions? Try Julius for free today.
Frequently asked questions
How accurate is Excel forecasting?
Excel forecasting produces reliable results when you have clean historical data with consistent patterns. Accuracy decreases significantly when your data includes irregular spikes, sudden trend changes, or outliers that Excel's formulas can't account for. Test your forecast reliability by predicting periods you already have data for and comparing those predictions to actual values.
