Excel: How to Convert Quarterly Data to Annual Data

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

For example, you might have a dataset that contains information about total sales each quarter during various years and you want to convert this quarterly data into annual 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 quarter at some retail store during three consecutive years:

Step 2: Find Unique Years

Next, we can type the following formula into cell D2 to get a list of all unique years from the original dataset:

=UNIQUE(RIGHT(A2:A13, 4))

The following screenshot shows how to use this formula in practice:

We now have a list of all unique years from the original dataset.

Note: This formula uses the RIGHT function to extract the four characters from the right of each cell in column A, then uses the UNIQUE function to get a list of unique four character combinations, i.e. unique years.

Step 3: Calculate Sum of Each Year

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

=SUMIF(A2:A13, "*"&D2&"*", B2:B13)

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

Excel convert quarterly data to annual

From the output we can see:

The total sales for all quarters in 2018 was 736.
The total sales for all quarters in 2019 was 807.
The total sales for all quarters in 2020 was 938.

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

Note: In this example we only had three unique years, but you can use these exact same formulas to convert quarterly data to annual data for any number of years.