Excel: How to Convert Monthly Data to Quarterly Data

Often you may want to convert monthly data into quarterly data.

For example, you might have a dataset that contains information about total sales each month during a particular year and you want to convert this monthly data into quarterly data:

Fortunately this is easy to do in Excel and the following step-by-step example shows how to do so.

Step 1: Enter Original Data

First, we will enter the following dataset into Excel that shows the total sales each month at some retail store:

Step 2: Create List of Quarters

Next, we can type out a list of quarters into column D:

Step 3: Calculate Sum of Each Quarter

Lastly, we can type the following formula into cell E2 to calculate the sum of sales for the first quarter:

=SUM(OFFSET(B$2,3*ROWS(B$2:B2)-3,,3))

We can then click and drag this formula down to each remaining cell in column E:

Excel convert monthly data to quarterly data

From the output we can see:

The total sales for the first quarter (Jan, Feb, Mar) was 502.
The total sales for the second quarter (Apr, May, Jun) was 622.
The total sales for the third quarter (Jul, Aug, Sept) was 619.
The total sales for the fourth quarter (Oct, Nov, Dec) was 738.

We have now successfully converted the monthly data to quarterly data.

Note: In this example we used the SUM function to calculate the sum of sales for each quarter, but you could instead use a different function such as the AVERAGE if you’d like to calculate the average sales each quarter.