# 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: 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.