Cellwise Start free

Excel guide · 6 min read

How to calculate a seasonal index in Excel

Quick answerA seasonal index is a month's average sales divided by the average of all months. With one row per month and one column per year, the month average is =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.

RowABCDEFGHIJKL
1Month202320242025AverageIndexClean avgFinal index2025 deseas.2026 fcst2026 total6600
2Jan2102252402250.51842250.50855438707471.92592593279.70491289
3Feb230240255241.666666670.5568241.666666670.5462250824466.84051724300.42379532
4Mar3403653903650.840963650.82498822791472.73401826453.74352535
5Apr480505540508.333333331.1712508.333333331.1489562078469.99180328631.9259143
6May6206607006601.520646601.4917595354469.24452862820.46774447
7Jun7007457907451.716487451.6838800816469.15454884926.13404489
8Jul660400745601.666666671.38624702.51.5878198085469.19681692873.30089468
9Aug5405756105751.32485751.2996389892469.36111111714.80144404
10Sep390410440413.333333330.95232413.333333330.93423324439470.97446237513.82828441
11Oct280300315298.333333330.68736298.333333330.67430544655467.14734637370.86799561
12Nov2402552702550.587522550.57636163868468.45588235316.99890127
13Dec3003203403200.737283200.72327735049470.08246528397.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.

=ROUND(F2:F13,3)

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.

=HSTACK(ROUND(F8,3),ROUND(H8,3))

Result: spills 1 row × 2 columns

1.3861.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.

=SUM(G2:G13/AVERAGE(E2:E13))

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.

=ROUND(MIN(I2:I13),1)&" to "&ROUND(MAX(I2:I13),1)

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.

=SUM(J2:J13)

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

Related guides

Updated