Excel Seasonal Indexes: Step-by-Step Guide

This guide shows you how to calculate seasonal indexes in Excel, which measure how a typical period (like a month or quarter) compares to the overall average. You’ll learn the classic method using moving averages and centered moving averages, which works for monthly or quarterly data with a clear seasonal pattern. You need a column of historical values (at least two full years of data is best) and Excel’s built-in functions like AVERAGE and the Analysis ToolPak add-in for regression if you want to deseasonalize a trend.

What You’ll Need

Excel (2016 or later works fine; the steps are the same in most versions)
At least 24 months (or 8 quarters) of historical data in one column
Your data sorted by time period, oldest to newest
The Analysis ToolPak add-in enabled (File > Options > Add-ins > Go > check Analysis ToolPak) if you plan to use regression for trend removal

Step-by-Step

Organize your data – Put your time periods in column A (e.g., Jan-2022, Feb-2022…) and your actual values in column B. Leave column C through F empty for the calculations.
Calculate the moving average – In cell C13 (if your data starts in row 2), enter the formula =AVERAGE(B2:B13) for monthly data (12-month moving average) or =AVERAGE(B2:B5) for quarterly data (4-quarter moving average). Drag this formula down to the last row that has 12 (or 4) values above it. This gives you a 12-month or 4-quarter moving total, centered on the period between the 6th and 7th month (or 2nd and 3rd quarter).
Center the moving average – Because the moving average from step 2 is centered between periods, you need a centered moving average (CMA) to align it with actual periods. In cell D7 (for monthly data, where the first CMA goes), enter =AVERAGE(C7:C8) and drag down. This averages two consecutive moving averages, placing the result exactly on a real period. For quarterly data, use =AVERAGE(C3:C4) in D3.
Calculate the ratio-to-CMA – In column E, next to each CMA, enter =B7/D7 (adjusting row numbers to match your data). This ratio is the seasonal index for that specific period, expressed as a decimal (e.g., 1.15 means 15% above average). Drag down for all rows that have a CMA.
Average the ratios by period – Create a summary table (e.g., in columns H and I) listing each month (Jan–Dec) or quarter (Q1–Q4). Use =AVERAGEIF($A:$A, H2, $E:$E) if your period labels are text, or use a simple AVERAGE with IF logic if they are dates. This gives you the raw seasonal index for each period across all years.
Normalize the indexes – Sum all the raw averages. They should be close to 12 (for monthly) or 4 (for quarterly) but rarely exact. Divide each raw average by the sum and multiply by 12 (or 4) so the final indexes sum exactly to 12 (or 4). This ensures the average seasonal index equals 1.0.
Interpret the results – A seasonal index of 1.20 for December means December sales are typically 20% above the average month. An index of 0.85 for February means February is 15% below average. To seasonally adjust a value, divide the actual value by its seasonal index.
Optional: Remove trend for a cleaner index – If your data has a strong upward or downward trend, the ratio-to-CMA method already removes most of it because the CMA captures the trend. For a more refined index, run a linear regression on the deseasonalized data (actual / seasonal index) to get a trend line, then recalculate ratios as actual / trend value.

Common Mistakes

Using an odd-length moving average – For monthly data, a 12-month average is even, so it centers between months. If you use 13 months, it centers directly on a month and you don’t need step 3, but mixing even/odd lengths causes misalignment.
Forgetting to center – Skipping step 3 and using the uncentered moving average directly gives ratios that are offset by half a period, making your seasonal indexes wrong.
Not normalizing – If your indexes sum to 12.4 instead of 12, your seasonal adjustment will systematically over- or under-correct. Always normalize.
Too little data – With only one year of data, you have no way to average out random noise; each seasonal index is based on a single observation and will be unreliable.
Ignoring outliers – A one-off spike (e.g., a pandemic month) will distort the average for that month. Consider removing or adjusting extreme outliers before calculating.

Tips

Use Excel’s FORECAST.ETS.SEASONALITY function to automatically detect the season length if you’re unsure whether your data is monthly or quarterly.
Format your ratio column as a percentage to make the indexes easier to read at a glance.
Keep your raw data in a separate sheet from your calculations to avoid accidental overwrites.
If you have missing months, fill them with an estimate (e.g., average of adjacent months) before starting, or the moving average will be misaligned.
For quarterly data, the same steps work with 4-period moving averages—just adjust the row offsets accordingly.
Use conditional formatting on the final index table to quickly see high and low seasons.

Troubleshooting

My CMA column has blanks at the start and end – This is normal. You lose the first 6 and last 6 months (or first 2 and last 2 quarters) because you need data on both sides to center. If you need indexes for those periods, you’ll need more historical data.
My ratios look wildly inconsistent (e.g., 0.5 and 2.0 for the same month) – This usually means your data has a strong trend that the CMA isn’t fully capturing, or there are outliers. Try removing the trend first with a linear regression, then recalculate.
The AVERAGEIF formula returns #DIV/0! – This means no ratios match the period label in H2. Check that your period labels in column A and H are exactly the same (e.g., “Jan” vs “January”).
My normalized indexes don’t sum to 12 – Double-check your division: each index should be divided by the sum of raw averages, then multiplied by 12. If you multiply by 12 before dividing, you’ll get the wrong result.
Excel says my formula has a circular reference – Make sure your summary table is not in a cell that is also referenced in the moving average range. Keep the summary table far to the right or on another sheet.
My data is weekly, not monthly – The same method works, but you’ll need a 52-week moving average (which is even, so centering is needed) and you’ll have 52 seasonal indexes. The normalization step then sums to 52.

Frequently Asked Questions

How many years of data do I need? At least two full years, but three to five years is better. More years smooth out random fluctuations, giving more stable seasonal indexes.

Can I use this method for daily data? Yes, but you’d need a 365-day moving average, which is computationally heavy and requires several years of data. For daily data, consider using Excel’s FORECAST.ETS function instead, which handles seasonality automatically.

What’s the difference between a seasonal index and a seasonal factor? They’re the same thing. Both are multipliers that tell you how much a period deviates from the average. An index of 1.10 means 10% above average.

Should I remove outliers before calculating? Yes, if an outlier is due to a one-time event (strike, natural disaster, promotion) and not a recurring pattern. Replace it with an average of surrounding periods or exclude that year from the average for that month.

How do I forecast with seasonal indexes? First, calculate a trend forecast (e.g., using linear regression on deseasonalized data). Then multiply the trend forecast by the seasonal index for the target period. This gives a seasonally adjusted forecast.

What if my seasonal indexes don’t change much from 1.0? That means your data has little seasonality. In that case, a simple moving average or exponential smoothing might be more appropriate than seasonal decomposition.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →