How to Seasonally Adjust Data in Excel

Seasonality can make your data lie to you. A huge spike in November sales might look like a breakout success, but if you’re an e-commerce store, it’s probably just the holiday rush you see every year. This article will show you how to remove these predictable seasonal effects directly in Excel, so you can see the real, underlying trend in your business performance.

What is Seasonality and Why Does it Matter?

In data analysis, seasonality refers to predictable, repeating patterns or fluctuations that occur at regular intervals over a year. Think of them as the reliable ebbs and flows in your data driven by the calendar, holidays, weather, or other cyclical events.

Common examples include:

• Retail: Sales skyrocketing in the fourth quarter (Q4) due to Black Friday and Christmas, then dipping in Q1.
• Hospitality: Hotels in a vacation spot booking up during the summer but sitting empty in the winter.
• Fitness: Gym memberships surging in January as New Year's resolutions kick in.
• B2B SaaS: Software sales potentially slowing in the summer due to decision-makers being on vacation.

Failing to account for these patterns can lead to poor analysis and decision-making. You might mistakenly cut the budget for a "slow" summer marketing campaign without realizing it's performing better than last summer. Seasonally adjusting your data strips away this predictable noise, allowing you to see if you’re actually growing, stagnating, or declining. It helps you answer the question: "After accounting for the usual holiday bump, did our sales really go up this quarter?"

Getting Your Data Ready for Adjustment

Before you start, you'll need time-series data. This is simply data collected at regular intervals, like daily, weekly, monthly, or quarterly. For seasonal adjustments, monthly and quarterly data are most common.

The key is having enough historical data to establish a credible pattern. You’ll want at least two to three full years of data. Trying to identify a monthly pattern with only 12 months of data is impossible - you have nothing to compare it against.

Your data should be organized cleanly in two columns in Excel or Google Sheets:

• A Date or Period column (e.g., Jan-22, Feb-22, etc.)
• A Value column with the metric you want to analyze (e.g., Sales, Site Traffic, Leads).

Ensure there are no missing values. If a month has no data, you might need to estimate it (for instance, by averaging the previous and next month) or exclude that period, but fixing it is always preferable.

The Moving Average Method: A Step-by-Step Guide in Excel

The most common and intuitive way to seasonally adjust data in Excel is the Centered Moving Average (CMA) method. It sounds complex, but we'll break it down into five simple steps. Let’s assume we have 3 years of monthly sales data in our spreadsheet, with Dates in column A and Sales in column B.

Step 1: Calculate the Centered Moving Average (CMA)

First, we need to smooth out the data to find its central trend line. A moving average does this by averaging data points over a specific period. For monthly data with a yearly seasonal cycle, we’ll use a 12-month period.

The trick is that a 12-month moving average is not naturally centered. The average for Jan-Dec would fall between June and July. To fix this, we calculate a 12-month moving average, and then take a 2-month average of that average. This final result is the Centered Moving Average.

1. Calculate the 12-Month Moving Average. In a new column (C), titled "12-Month MA," click into cell C7 (this aligns the first average with the 7th month, June). Enter the formula:

=AVERAGE(B2:B13)

Drag this formula down to the end of your data.

1. Calculate the Centered Moving Average. In the next column (D), titled "CMA," click into cell D8. This formula will average the two adjacent moving averages, lining everything up perfectly with your original data. Enter:

=AVERAGE(C7:C8)

Drag this formula down. You will have blank cells at the very beginning and very end of your data — this is normal.

The "CMA" column now represents the underlying trend of your sales data with the short-term noise and seasonality smoothed out.

Step 2: Calculate the Seasonal Ratios

Now we compare the actual sales of a given month to its smoothed trend (CMA). This tells us how much higher or lower that month’s sales typically are compared to the average. This value is called the seasonal ratio.

In a new column (E), titled "Seasonal Ratio," click into the first cell that has a CMA value next to it (cell E8 in our example) and enter:

=B8/D8

This formula divides the Actual Sales (B8) by the Centered Moving Average (D8). Drag this formula down. A value of 1.25 means that month’s sales were 25% above the trend, while 0.80 means it was 20% below.

Step 3: Calculate the Average Seasonal Index for Each Period

Our goal now is to find a single, reliable seasonal factor for each month. To do this, we'll average all the ratios we calculated for each specific month. For example, we'll average the ratios for all the Januarys, all the Februarys, and so on.

This is easily done with the AVERAGEIF function.

1. First, create a small helper table somewhere else on your sheet with the months "January" through "December" listed.
2. Let's say your full date list (Jan 1, 2021, etc.) is in Column A, and your Seasonal Ratios are in Column E. In your new helper table, next to "January," enter:

=AVERAGEIF($A$2:$A$37, "*"&"January"&"*", $E$2:$E$37)

You can create a text-based month column from your dates using =TEXT(A2, "mmmm") to make this step even easier — then your AVERAGEIF criteria would just be the cell containing "January". This formula looks through all the dates, finds every "January," and averages its corresponding seasonal ratio.

1. Drag this formula down for all 12 months. What you have now is the unadjusted seasonal index for each month.

Step 4: Normalize the Seasonal Indices

Ideally, our seasonal indices should all add up to 12 (or 4 for quarterly data). If they don't, it means our adjustment will accidentally inflate or deflate the grand total of our data. We need to normalize them.

1. Sum up your 12 unadjusted seasonal indices.
2. Calculate the normalization factor with:

=12 / [Sum of your indices]

1. In a new column in your helper table, multiply each unadjusted index by this normalization factor. The formula will be:

=[January's Unadjusted Index] * [Normalization Factor Cell]

(Be sure to lock the reference to the normalization factor cell with dollar signs, like $G$15, before dragging.)

You now have your final, Normalized Seasonal Indices! A December index of 1.18, for example, means December sales are typically 18% higher than the monthly average for the year.

Step 5: Deseasonalize Your Data

This is the final and easiest step. We can now use these normalized indices to remove the seasonal effect from our original data.

1. Go back to your main data table. Find the correct normalized seasonal index for each corresponding month. An easy way to do this is with a VLOOKUP formula. In a new column (F), titled "Seasonally Adjusted Sales," enter:

=VLOOKUP(TEXT(A2,"mmmm"), [Your Normalized Index Table], 2, FALSE)

This will pull the correct index (from the second column of your index lookup table) for each month. Let’s assume this formula is in column F.

1. Finally, in column G, create your "Deseasonalized Sales" column by dividing your original sales by the index:

=B2/F2

That’s it! The values in this last column are your original sales data with the predictable seasonal effects removed. Now, a jump in sales from October to November truly reflects underlying growth, not just the holiday shopping season kicking in.

Visualizing the Adjusted Data

Calculations are great, but the real "aha!" moment comes from seeing the results.

Create a line chart in Excel that plots both your Original Sales and your Deseasonalized Sales series on the same graph against the Date axis.

• The original sales line will have dramatic, predictable peaks and troughs, showing the noisy reality on the surface.
• The deseasonalized sales line will be much smoother. It filters out the noise and clearly shows the real long-term trend, making it easier to see periods of genuine growth or decline.

This chart is a powerful reporting tool because it tells a much more honest story about your business's performance over time.

Final Thoughts

Removing seasonality from your data in Excel is a powerful skill that transforms noisy, confusing metrics into a clear signal of your business's true trajectory. By following the moving average method, you can accurately deseasonalize your data and gain more meaningful insights into your performance without the distortions of predictable cycles.

This manual spreadsheet process is effective, but as you've seen, it can be tedious and prone to formula errors. At Graphed, we created a way to handle complex analysis like this automatically. Once you connect your data sources, you can ask questions in plain English like, "show me a seasonally adjusted chart of my Shopify revenue over the last 3 years," and our AI instantly handles these calculations and builds a live, interactive visualization for you, saving you from any spreadsheet-wrangling.