This post provides a solution for quickly creating formulas to sum monthly data into quarters and full year totals using a free add-in.
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.
- Creates easy-to-read SUM formulas.
- Automatically determines 1st month cell when Q1 cell is selected.
Download
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.
Subscribe to our email newsletter below to stay updated with changes.
Related Help Articles
- How to Install an Add-in
- How to Setup the Quick Access Toolbar (QAT)
- How to Use the QAT for Keyboard Shortcuts
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.
See the guide on how to install an add-in (included with download). After the add-in is installed, you will see the Formula Tools menu on the Add-ins tab of the ribbon.
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
- Click the Quarter Formulas Button.
- 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. - 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. - 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?
Please leave a comment with any questions or suggestions.
very useful. Accountants/Finance are always adding qtrly totals, simple and effective tool especially after adding it to the quick access toolbar.
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.
please also advise some solution for this.
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
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!
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.
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.
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”
I got the same error when i tried downloading other add ins you have.
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?
Very Very Good site. lot off i got !!
Thank you Natarajtn!
Hi Jon,
Many Thanks for your efforts,I believe that site will benefit for me.
Thanks Muhaamad! I really appreciate the feedback. 🙂
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.
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!
Hi. Are we able to pull data from other worksheets or workbooks using the add in?
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?
It very nice