{"id":24264,"date":"2021-12-24T14:35:25","date_gmt":"2021-12-24T13:35:25","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=24264"},"modified":"2024-02-06T16:06:15","modified_gmt":"2024-02-06T15:06:15","slug":"group-by-fiscal-years-quarters","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-by-fiscal-years-quarters\/","title":{"rendered":"Group by Fiscal Years &#038; Quarters"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/11\/Group-by-Fiscal-Years-Quarters-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-25353\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/11\/Group-by-Fiscal-Years-Quarters-1.png\" alt=\"Group by Fiscal Years &amp; Quarters\" width=\"800\" height=\"450\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/11\/Group-by-Fiscal-Years-Quarters-1.png 800w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/11\/Group-by-Fiscal-Years-Quarters-1-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/11\/Group-by-Fiscal-Years-Quarters-1-768x432.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" title=\"\"><\/a><\/p>\n<p>Grouping by calendar years and quarters is straightforward as this is provided out of the box. What if you want to <strong>segregate data and group by fiscal years and quarters<\/strong>? Luckily there is a solution which will ease your work\u00a0 &#8211;<\/p>\n<p>Let us have this setup in our <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Table\" data-wpil-keyword-link=\"linked\">Pivot Table<\/a> data:<\/p>\n<ul>\n<li>The Fiscal Year starts in July<\/li>\n<li>Fiscal Quarters:\n<ul>\n<li>1st Quarter &#8211; July, August, September<\/li>\n<li>2nd Quarter &#8211; October, November, December<\/li>\n<li>3rd Quarter &#8211; January, February, March<\/li>\n<li>4th Quarter &#8211; April, May, June<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>I will show you how we can set this up!<\/p>\n<p><em><strong>Exercise Workbook:<\/strong><\/em><\/p>\n<div class=\"after-post-box\">\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Group-by-fiscal-years-quarters.xlsx<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<hr \/>\n<p>We will be using the help of <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formulas\" data-wpil-keyword-link=\"linked\">formulas<\/a> to add two columns to our data source: <strong>Fiscal Year and Fiscal Quarter<\/strong>.<\/p>\n<p>Before moving forward let&#8217;s get acquainted with the Excel formulas that we will be using here &#8211; <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/year-formula-in-excel\/\" target=\"_blank\"><strong>YEAR()<\/strong>,<\/a> <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/month-formula-in-excel\/\" target=\"_blank\">MONTH()<\/a><\/strong> and <strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/choose-formula-in-excel\/\" target=\"_blank\">CHOOSE()<\/a><\/strong>.<\/p>\n<ul>\n<li><strong>YEAR()<\/strong> &#8211; This formula extracts the YEAR from the date<\/li>\n<li><strong>MONTH()<\/strong> -This formula extracts the MONTH from the date<\/li>\n<li><strong>CHOOSE()<\/strong> &#8211; This formula extracts a\u00a0 specific value from the list based and the position provided.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Let us start with the group by Fiscal Years first. Since our fiscal year starts in July, then our <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a> will be:<\/p>\n<h4><strong>=YEAR(E2) + (MONTH(E2) &gt;= 7)<\/strong><\/h4>\n<p>If the month is July or later, then we add one to the year. That will get us the next year as the Fiscal Year.<\/p>\n<p>For example, <strong>December 21, 2012,<\/strong> will have the fiscal year of <strong>2013<\/strong>, as it is July or later.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Group by Fiscal Years &amp; Quarters\"  class=\"alignnone size-full wp-image-24279\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-01.jpg\" alt=\"Group by Fiscal Years &amp; Quarters\" width=\"575\" height=\"492\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-01.jpg 575w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-01-300x257.jpg 300w\" sizes=\"(max-width: 575px) 100vw, 575px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Let us work on the Fiscal Quarter next. Since our first fiscal quarter starts in July, then our formula will be:<\/p>\n<h4><strong>=CHOOSE(MONTH(E2),3,3,3,4,4,4,1,1,1,2,2,2)<\/strong><\/h4>\n<p>This formula will simply match the Month number to the sequence of:<\/p>\n<ul>\n<li>3rd Quarter &#8211; January (3), February (3), March (3)<\/li>\n<li>4th Quarter &#8211; April (4), May (4), June (4)<\/li>\n<li>1st Quarter &#8211; July (1), August (1), September (1)<\/li>\n<li>2nd Quarter &#8211; October (2), November (2), December (2)<\/li>\n<\/ul>\n<p>For example, <strong>December 21, 2012<\/strong>\u00a0will have a fiscal quarter of <strong>2 <\/strong>as it will match the last part of the formula:<\/p>\n<h4><strong>=CHOOSE(MONTH(E2),3,3,3,4,4,4,1,1,1,2,2,<span style=\"color: #ff0000;\">2<\/span>)<\/strong><\/h4>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Group by Fiscal Years &amp; Quarters\"  class=\"alignnone size-full wp-image-24280\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-02.jpg\" alt=\"Group by Fiscal Years &amp; Quarters\" width=\"960\" height=\"551\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-02.jpg 960w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-02-300x172.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-02-768x441.jpg 768w\" sizes=\"(max-width: 960px) 100vw, 960px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> Once the columns are ready, right-click on your Pivot Table and select <strong>Refresh<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Group by Fiscal Years &amp; Quarters\"  class=\"alignnone size-full wp-image-24281\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-03.jpg\" alt=\"Group by Fiscal Years &amp; Quarters\" width=\"491\" height=\"380\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-03.jpg 491w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-03-300x232.jpg 300w\" sizes=\"(max-width: 491px) 100vw, 491px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span> The new Fiscal columns are now there, drag the\u00a0<strong>FISCAL YEAR\u00a0<\/strong>and <strong>FISCAL QUARTER <\/strong>to <strong>Rows.\u00a0<\/strong>Then drag\u00a0<strong>SALES<\/strong> to <strong>Values<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Group by Fiscal Years &amp; Quarters\"  class=\"alignnone size-full wp-image-24282\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-04.jpg\" alt=\"Group by Fiscal Years &amp; Quarters\" width=\"681\" height=\"616\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-04.jpg 681w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-04-300x271.jpg 300w\" sizes=\"(max-width: 681px) 100vw, 681px\" \/><\/p>\n<p>You now have your Pivot Table ready and group by <strong>Fiscal Years and Quarters<\/strong>!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Group by Fiscal Years &amp; Quarters\"  class=\"alignnone wp-image-24283 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-05.jpg\" alt=\"Group by Fiscal Years &amp; Quarters\" width=\"660\" height=\"534\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-05.jpg 660w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2021\/06\/Group-by-fiscal-years-quarters-05-300x243.jpg 300w\" sizes=\"(max-width: 660px) 100vw, 660px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>Make sure to download our FREE PDF on the<strong><a href=\"https:\/\/app.monstercampaigns.com\/c\/czseo0viyrmspcx5ndve\/\" target=\"_blank\" rel=\"noopener nofollow\">\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Grouping by calendar years and quarters is straightforward as this is provided out of the box. What if you want to segregate data and group by fiscal years and quarters? Luckily there is a solution which will ease your work\u00a0 &#8211; Let us have this setup in our Pivot Table data: The Fiscal Year starts [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":25353,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[4],"tags":[834,833,832],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24264"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=24264"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24264\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25353"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=24264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=24264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=24264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}