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.

**Additional Resources**

The following tutorials explain how to perform other common operations in Excel:

How to Group Data by Quarter in Excel

How to Sum by Quarter in Excel

How to Convert Date to Quarter and Year in Excel