Maruf Islam
Typical Excel files contain hundreds of date columns and values respective to those dates. As a result, every now and then users need to group dates in an Excel chart or table. Without grouped data, the Pivot Table/Chart allows data to organize into group dates (that can be Days, Months, Quarters, or Years).
Let’s say we have a dataset that depicts day-wise orders as shown in the below picture. And we want to group the dates in Excel Charts.
In this article, we demonstrate direct Pivot Chart insertion, Format Axis options of a 2D Chart, and grouped data to create group dates in Excel Chart.
Download Excel Workbook
3 Easy Ways to Group Dates in Excel Chart
In reality, we may have an Excel Chart, to begin with, or have data to create a random Excel Chart. In both cases, users need to understand the data types and thus respective Chart options. Follow the latter section to organize the Chart in a way that group dates in Excel Chats.
Method 1: Applying Pivot Chart to Group Dates in Excel
In case, users don’t have an existing Excel Chart, they can insert one. Also, meddling with an Excel PivotChart is not that complicated.
🔄 Inserting a Pivot Chart
Step 1: First highlight the range then go to the Insert tab > Click on PivotChart (in the Charts section).
Step 2: The Create Pivot Chart window appears. Excel automatically selects the range. Mark New Worksheet as the Choose where you want the PivotChart to be placed option. At last, click on OK.
🔺 Excel takes a moment and displays the Pivot Chart along with a Pivot Table. As you know, there can’t be any Chart without a Data Source. So, Excel automatically generates a Pivot Table to insert a Pivot Chart as depicted in the picture below.
🔄 Grouping Dates in Excel Pivot Chart
Now, in the PivotChart Fields side window, tick all the fields. Afterward, place the Order Date in Axis Category, Fruits in Legend, and Sum of Total Amounts in Values.
After placing all the fields in respective areas, Excel displays the PivotTable along with the PivotChart.
🔄 Final PivotChart
You can furnish the PivotChart according to your need, The final depiction of the PivotChart may look like the following picture.
🔄 Further Grouping of the Dates in PivotChart
As we mentioned earlier, PivotTable allows users to group dates maintaining multiple time frames (i.e., Days, Months, Quarters, or Years).
Step 1: Place the cursor in any date cell then right-click on it. The Context Menu appears. From the Context Menu, click on the Group option.
Step 2: Excel brings up the Grouping dialog box. In that dialog box, you get Grouping By options such as Days, Months, Quarters, and Years. Choose one of them to reorganize the PivotChart.
🔄 Depiction of the PivotChart Grouped By Months
As the data has only a one-month of date expansions, the depiction of the PivotChart is similar to the picture below.
🔄 Depiction of the PivotChart Grouped By Years
Similar to Months, the data contain entries within a single year. So, the representation will be the same as depicted below.
Read More: How to Use Excel Pivot Table to Group Dates by Month and Year
Similar Readings
- How to Group Dates by Filter in Excel (3 Easy Methods)
- How to Group by Week and Month in Excel Pivot Table (with Easy Steps)
- How to Group Data by Month in Excel (2 Useful Methods)
Method 2: Group Dates Using Format Axis Options in Excel Chart
For Charts expanding to months, or years, have the Date Axis option in their Axis Types. In those cases, users either have Charts or have the data for Charts and can group dates by months or years.
Suppose we have a revised data source similar to the picture below.
We already inserted a 2D Column Chart and we want to group dates in that Excel Chart. Follow the below instructions to do so.
Steps: Double-click on the dates, Format Axis side window appears. From the options, select Date Axis under Axis Types. Type 6 in the Major Unit dialog box (as we have data that are 6 months apart). Choose any of the options (i.e., Days, Months, or Years). However, here the Months option is chosen. The same option is chosen for the other 2 options such as Minor and Base.
🔺 The monthly depiction of the data Chart resembles the image below.
🔺 If Years is chosen in the dialog boxes, the depiction changes to something like the following screenshot.
Therefore, it’s needless to say that Chart’s final outcome depends on the options you choose in the Format Axis window. Depending on your data type select your preferred options and group dates in Excel Chart.
Read More: How to Group by Year in Excel Pivot Table (3 Easy Methods)
Method 3: Using Grouped Data to Group Dates in Excel Chart
Some data types don’t allow grouping using Format Axis options. In those cases, users need to organize their data as grouped data and then be able to group dates in Excel Chart.
The sorted data of the used dataset may look like the image below.
Steps: Select the desired range then move to Insert > Select 2-D Column Chart (from the Charts section).
🔺 Excel inserts the 2D-Column Chart as shown in the image below.
🔺 Furnish the Chart according to your priorities, you see Excel groups the data date-wise.
Any kind of grouped date data can result in grouped dates in Excel Chart.
Read More: How to Group Dates in Excel Slicer (4 Ways)
Conclusion
In this article, we demonstrate PivotChart, Format Axis option as well as typically grouped data Chart insertion to group dates in Excel Chart. Each method requires different types of data sources and without the exact data source, it’s impossible to achieve the desired result. Hope this article clarifies all your confusion regarding group dates in Excel Chart. Comment, if you have further inquiries or have anything to add.
