A solution for quickly creating Excel SUM formulas to sum monthly data into quarters and full year totals using a free add-in. Creating quarterly sum formulas is typically a time consuming process because you can not copy/drag the formulas across to produce totals for Q2 to Q4.

### The Problem

When using the SUM function to sum monthly data into quarterly totals, you can NOT create the formula for the 1st quarter and then copy/drag the formula to the right.

The cell references will only move one column to the right instead of three columns.  If we copy the formula above for Q1 to the right, the resulting formula will give us the total for Feb-Apr.  The correct formula should be a total for Apr-Jun.

### The Solution

The Quarter Formulas Add-in creates the correct formulas for the quarters and full year in just a few clicks.  And it's FREE!

### And it Gets FASTER!

You can add the Quarter Formulas button to the Quick Access Toolbar to launch it with a keyboard shortcut.  Then you will be creating all the formulas in under 2 seconds!

These articles will explain how to setup the Quick Access Toolbar (QAT) and use the QAT for keyboard shortcuts.

### Benefits

• Four quarter and full year SUM formulas created in 2 seconds.
• No typing or complex formulas needed.
• Automatically determines 1st month cell when Q1 cell is selected.

The add-in file and installation guide are included in the zip file. Â The add-in is compatible with Excel 2007, 2010, 2013. Â Please let me know if you are interested in a version compatible with Excel 2003.

### Instruction Guide

This add-in will save you lots of time when creating quarter and full year formulas, and it is very easy to use.

The Formula Tools button is a drop-down menu that includes a button for the Quarter Formulas function.  More functions will be added to this menu in the future.  See below for instructions on how to setup a keyboard shortcut to launch the function.

#### Report Structure Requirements

The Quarter Formulas add-in requires all the month data be in 12 consecutive columns.  The four quarter and year total formulas will be created in five consecutive columns.  You will choose the starting cell (Q1) that the formulas will be created in.  The created formulas do NOT have to be next to the monthly data.

#### Creating the Formulas

1. Click the Quarter Formulas Button.
2. Select the 1st Quarter cell where you want the formulas to be created and click OK.
– Formulas for Q2-Q4 and Full Year sum will be created to the right of this cell.
– The selection defaults to the active (selected) cell in the worksheet.  Selecting the Q1 cell prior to clicking the Quarter Formulas button will save you a step.
3. Select the cell that contains month 1 data and click OK.
– This is the 1st month in the 1st quarter (January if you are on a calendar year).
– The selection defaults to 12 cells to the left of the Q1 cell selected in the previous step.  If the quarterly total cells are directly to the right of the monthly data then you will not have to select the month 1 cell.  This will save you another step.
4. The SUM formulas will be created in the Q1 cell and the four cells to the right of it.
– Copy the formulas down to fill all the rows in your report.

### What do you think?

• don hahn says:

• sonu says:

Hi Jon,

The above addin is not working in below mentioned two caes:

1) where the monthly data of product is from top to bottom i.e

example
Months Product 1 Product 2 Product 3 Product 4 Product 5
1-Jan-13 300000 150000 150000 100000 150000
1-Feb-13 300000 150000 150000 100000 150000
1-Mar-13 250000 100000 300000 250000 150000
1-Apr-13 150000 150000 100000 150000 100000
1-May-13 150000 350000 300000 250000 150000
1-Jun-13 100000 350000 300000 250000 150000
1-Jul-13 150000 250000 150000 150000 100000
1-Aug-13 150000 100000 150000 100000 150000
1-Sep-13 150000 250000 150000 150000 100000
1-Oct-13 100000 150000 100000 150000 100000
1-Nov-13 150000 350000 300000 250000 150000
1-Dec-13 100000 350000 300000 250000 150000

2) if i would like to do half year/yearly totals,then also this addin will not be useful.

please confirm if my understanding is correct.

thanks

• Hi Sonu,

You are correct on both points. Currently, the add-in only works to sum monthly data that is laid out horizontally. The vertical sum and half yearly totals could be additional enhancements to the add-in. I will let you know when that functionality is available.

Thanks,
Jon

• Kendra says:

Thank you so much !!! You have saved me hours of work!

• Hi Kendra, I am glad you found it useful. Please feel free to share with your friends & colleagues. Thanks!

• Roy says:

Hi, this is pretty cool, thanks for providing it. I must say it would be more usefull for me if it was possible to do multiple years in one go, without the yearly total in the 5th column. So if it just keeps going untill there’s no more data, for more than 4 quarters.

Thanks

• Great suggestion Roy. I will add it to the to-do list.

• Ohm says:

This is so useful add-in for me. It’s very simple and effective!!

One suggestion, if you allow me, is to be able to link monthly data from different sheet. When I tried linking to different sheet, it creates the formular but it links monthly data from its own worksheet.

• Thanks Ohm! That’s a great suggestion. I will update it when I get a chance and let you know.

• Sonny says:

Hi Jon,
I’m unable to open the zip file for some reason after i download it.

Its giving me an error saying “It doesn’t appear to be a valid archive”
Can you please fix it. THis add in can be really useful as i’ve lot of quarter end formulas i need to add.

• Hi Sonny,
Are you getting the error when unzipping the file? Do you know what version of Windows and Excel you are using?

• NATARAJTN says:

Very Very Good site. lot off i got !!

Hi Jon,

Many Thanks for your efforts,I believe that site will benefit for me.

• Thanks Muhaamad! I really appreciate the feedback. ðŸ™‚

• JR says:

Thanks for the add-in. I found it very useful. Love this site especially the way you take care not to provide lots of general excel information but only curated most useful ones.

• Michael says:

Hi Jon,

Is there a way to change the Quarters in Pivot Tables/Charts to a Fiscal year from a calendar years?

• Hi Michael,

Unfortunately, we cannot display fiscal quarters with the pivot table Grouping feature. To display fiscal quarters in the pivot table we need to add a column to the source data that contains or calculates the fiscal quarter for each row of data. I explain more about these methods in my article on grouping dates in pivot tables. I will also create an article on how to write a formula to calculate fiscal quarters. Thanks!

• MD says:

Hi. Are we able to pull data from other worksheets or workbooks using the add in?

• Nancy says:

Add is good but i dont want sum total rather I need quarterly summation of net months (i.e. after initial 12 months). Do I need to re apply macro or there is another easy way out?

• Nasir muzemil says:

It very nice

Generic filters
Exact matches only

#### Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List