excel

An easy process to convert Monthly to Quarterly data in Excel

Introduction#

A frequent desire is to convert monthly data into quarterly data format. One simple method is to 1) sum the appropriate months into quarterly sums, then 2) identify those months as quarters, and finally 3) filter the quarterly data out of your monthly data. Here’s a relatively quick and easy method illustrated through the following example:

An easy way to perform a sometimes tedious routine task:

If you have your data with each month of data arranged in rows like so:

Example Data

start by creating quarterly sums in the adjacent columns, D & E in our example.

Start with the third row in the new column or the first quarter you want to create, in this example we’ll use March 31st (2000-03-31). Use the summation function over the prior two months and the quarter month, for March use:

=SUM(B2:B4)

Then use a similar function for the total in the next column E. It will have the following formula:

=SUM(C2:C4)

Don’t concern yourself that the sums are created for months that are not quarters. This is done to make the copying easier for you. You’ll not need those values anyway in the next few steps.

Finally, we need to identify quarters without a lot of work. If your Date column isn’t in an Excel Date format, such as text, you may want to convert it. In a new column, F in this example, begin this formula in the first row of data. We will mark the rows using the following formula:

=IF(MOD(MONTH(A2),3)=0, “Quarter”, “Month”)

Now copy the three formulas in the new columns D,E & F into the remaining cells in your table. It should look like this:

Sample Data with formulas added to three columns

Finally, select this table, choose Format As Table, pick a simple format, and Excel will produce a result like so:

enter image description here

The final step is to select the filter button over the new “Period” column in F and select only the “Quarters” values to filter on:

Filter Menu Choice on Period column

Your table should look like this now:

Filtered Quarterly Data

At this point I would suggest selecting only the columns you need from your final table and then using Paste->Special->Values to relocate your data.


This modified text is an extract of the original Stack Overflow Documentation created by the contributors and released under CC BY-SA 3.0 This website is not affiliated with Stack Overflow