Excel guide · 6 min read
How to calculate a seasonal index in Excel
=AVERAGE(B2:D2) and the index is =E2/AVERAGE($E$2:$E$13). An index of 1.5 means that month sells 50% more than an average month. Divide actual sales by the index to deseasonalize them, and multiply a flat monthly forecast by it to spread a year's forecast across the months.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Month | 2023 | 2024 | 2025 | Average | Index | Clean avg | Final index | 2025 deseas. | 2026 fcst | 2026 total | 6600 |
| 2 | Jan | 210 | 225 | 240 | 225 | 0.5184 | 225 | 0.50855438707 | 471.92592593 | 279.70491289 | ||
| 3 | Feb | 230 | 240 | 255 | 241.66666667 | 0.5568 | 241.66666667 | 0.5462250824 | 466.84051724 | 300.42379532 | ||
| 4 | Mar | 340 | 365 | 390 | 365 | 0.84096 | 365 | 0.82498822791 | 472.73401826 | 453.74352535 | ||
| 5 | Apr | 480 | 505 | 540 | 508.33333333 | 1.1712 | 508.33333333 | 1.1489562078 | 469.99180328 | 631.9259143 | ||
| 6 | May | 620 | 660 | 700 | 660 | 1.52064 | 660 | 1.4917595354 | 469.24452862 | 820.46774447 | ||
| 7 | Jun | 700 | 745 | 790 | 745 | 1.71648 | 745 | 1.6838800816 | 469.15454884 | 926.13404489 | ||
| 8 | Jul | 660 | 400 | 745 | 601.66666667 | 1.38624 | 702.5 | 1.5878198085 | 469.19681692 | 873.30089468 | ||
| 9 | Aug | 540 | 575 | 610 | 575 | 1.3248 | 575 | 1.2996389892 | 469.36111111 | 714.80144404 | ||
| 10 | Sep | 390 | 410 | 440 | 413.33333333 | 0.95232 | 413.33333333 | 0.93423324439 | 470.97446237 | 513.82828441 | ||
| 11 | Oct | 280 | 300 | 315 | 298.33333333 | 0.68736 | 298.33333333 | 0.67430544655 | 467.14734637 | 370.86799561 | ||
| 12 | Nov | 240 | 255 | 270 | 255 | 0.58752 | 255 | 0.57636163868 | 468.45588235 | 316.99890127 | ||
| 13 | Dec | 300 | 320 | 340 | 320 | 0.73728 | 320 | 0.72327735049 | 470.08246528 | 397.80254277 |
Download this sheet as an Excel practice file (.xlsx)
Average each calendar month, then divide by the overall average
The sample is three years of monthly units for a shop selling outdoor furniture: one row per month (A2:A13) and one column per year (B to D). Sales peak in June and drop to a low in January, every year. In E2, =AVERAGE(B2:D2) averages January across the three years; fill it down to E13.
The index in F2 is =E2/AVERAGE($E$2:$E$13): January's average over the average of all twelve monthly averages. Lock the second range with $ so it stays put as you fill down. A value above 1 is a strong month, below 1 a weak one. The raw indices run from 0.518 in January to 1.716 in June.
If your data is one long list (a date and a sales figure per row) instead of a grid, add a month number column with =MONTH(A2) and use AVERAGEIFS: =AVERAGEIFS($C$2:$C$37,$B$2:$B$37,1) averages every January. Swap the 1 for a cell that holds the month number and fill down twelve rows. Use whole years only: two Januaries and three Junes would bias the comparison.
Result: spills 12 rows × 1 column
| 0.518 |
| 0.557 |
| 0.841 |
| 1.171 |
| 1.521 |
| 1.716 |
| 1.386 |
| 1.325 |
| 0.952 |
| 0.687 |
| 0.588 |
| 0.737 |
0.518, 0.557, 0.841, 1.171, 1.521, 1.716, 1.386, 1.325, 0.952, 0.687, 0.588 and 0.737: the raw indices, January to December.
One odd year: fix it before it skews the index
Look at July 2024: 400 units, against 660 and 745 in the other years. The shop ran out of stock that month. A plain average treats that as part of the season, so July's index comes out at 1.386, barely above August's 1.325, although in the two normal years July outsold August by more than 20%. And because the stockout also lowers the overall average, every other month's index is pushed up slightly.
You have two honest fixes. The quick one is a median of the three years, =MEDIAN(B8:D8), which ignores the one low value. Its catch is that with three years the median picks a single year (here 2023, the oldest and lowest level), so it isn't a perfect replacement either. The cleaner fix is to leave the bad month out and add a note saying why: column G repeats the average for every month except July, where =AVERAGE(B8,D8) uses only 2023 and 2025. That's 702.5 units, and the final index in H8 becomes 1.588.
Do this only for causes you can name: a stockout, a one-off promotion, a store closure. A month that's just high or low with no reason is part of the noise, and removing it makes the indices look more certain than they are.
Result: spills 1 row × 2 columns
| 1.386 | 1.588 |
1.386 with the stockout year included, 1.588 with July 2024 left out: the stockout knocked about 13% off July's index.
Normalize so the twelve indices average exactly 1
Seasonal indices should average 1 (add up to 12 for months, 4 for quarters). If they don't, a forecast spread with them won't add back up to the yearly total. The final index in H does it by dividing by the average of its own column: =G2/AVERAGE($G$2:$G$13).
The usual mistake is to clean or change one month and keep dividing by the old overall average. Divide the clean averages in G by the raw overall average in E (434.03 instead of the clean 442.43) and the twelve indices add up to 12.23, not 12. A year's forecast spread with them comes out about 1.9% too high.
With a full grid and plain averages, the indices already average 1, so normalizing changes nothing. It matters as soon as you use medians, drop a month, mix in a partial year or round the indices for a report. Check it with =AVERAGE(H2:H13), which should show 1.
Result: 12.23232
About 12.23: what the indices add up to if the clean averages are divided by the raw overall average. Normalized against their own average, they add up to exactly 12.
Deseasonalize to see the real trend
Dividing actual sales by the index removes the season, so months become comparable. In I2, =D2/H2 deseasonalizes 2025: January's 240 units becomes 471.9 and June's 790 becomes 469.2. Every month of 2025 lands between 466.8 and 472.7, which says the year ran at a steady level of about 470 a month, and the swings in the raw numbers were all season.
This is what makes month-to-month comparisons fair. A store manager who sees sales fall from 790 in June to 745 in July might worry; deseasonalized, July is 469.2, right on the level. It also exposes the stockout fix: with the raw index, 2025's July deseasonalizes to 537.4, which looks like a big jump that never happened.
For a growth figure, compare deseasonalized levels from one period to the next, or use year-over-year growth on the raw numbers, which compares the same month and cancels the season the simple way.
Result: 466.8 to 472.7
466.8 to 472.7: 2025 deseasonalized, a flat level of about 470 units a month once the season is taken out.
Reseasonalize: spread a yearly forecast across the months
To turn a yearly forecast into months, divide it by 12 for a flat monthly figure and multiply by each month's index. With the 2026 total of 6,600 units in L1, J2 is =$L$1/12*H2. January gets 279.7 units and June 926.1. Because the indices average exactly 1, the twelve months add back up to 6,600.
The same idea works the other way round for a rolling forecast: forecast the deseasonalized level (with a moving average or exponential smoothing), then multiply by the index of the month you're forecasting. Smoothing the raw data instead would read every spring rise as a trend.
A note on the method: averaging each calendar month assumes growth inside a year is small. If sales grow fast (say 30% a year), December will look stronger than January partly because of growth, not season. In that case, use the ratio-to-moving-average method: divide each month by a centered 12-month moving average first, then average those ratios by month.
Result: 6600
6,600: the twelve monthly forecasts add back up to the yearly total, because the indices were normalized.
Common mistakes
Using a partial year
Three Januaries and two Decembers mean each month is averaged over a different period. Use whole years, or trim the data to them.
Keeping a stockout or promotion in the average
One odd month moves that month's index and, through the overall average, every other month. Leave it out with a note, or use a median.
Not normalizing after a change
If the indices don't average 1, a spread yearly forecast won't add up. Divide each index by the average of all twelve.
Deseasonalizing with the wrong month
Divide each month by its own index. With dates, look up the index with XLOOKUP on MONTH(date) rather than relying on row order.
FAQ
How do you calculate a seasonal index?
Average each month across several years, then divide each month's average by the average of all months. In Excel: =AVERAGE(B2:D2) per month, then =E2/AVERAGE($E$2:$E$13).
What does a seasonal index of 1.2 mean?
That month typically sells 20% more than an average month. An index of 0.8 means 20% less.
How many years of data do I need for seasonal indices?
At least two full years, and three or more is better, so that one unusual year doesn't decide the pattern. Use whole years so each month has the same number of values.
How do I deseasonalize data in Excel?
Divide each actual by its month's seasonal index: =D2/H2. The result shows the underlying level, so you can compare months fairly or spot a trend.
Can I calculate quarterly seasonal indices?
Yes, the same way with four rows instead of twelve: average each quarter across the years and divide by the average of the four. Normalized quarterly indices add up to 4.
Functions used
AVERAGEMean of numbersAVERAGEIFSAverages with several conditionsMONTHMonth numberMEDIANMiddle valueSUMAdds numbersMINSmallest numberMAXLargest numberROUNDRounds to digitsHSTACKJoins arrays side by sideXLOOKUPModern lookup in any direction
Related guides
- How to calculate a moving average in Excel
- How to calculate year-over-year growth in Excel
- How to build a rolling forecast in Excel
- How to do an exponential smoothing forecast in Excel
- How to calculate an average in Excel
- How to calculate customer lifetime value in Excel
- How to calculate Cp and Cpk in Excel
Updated