How to Group Dates in Excel Chart (3 Easy Ways)

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.

Dataset-How to Group Dates in Excel Chart

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).

Pivot Chart Insertion-How to Group Dates in Excel

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.

Create PivotChart

🔺 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.

PivotChart Fields


🔄 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.

Pivot Chart Depiction


🔄 Final PivotChart

You can furnish the PivotChart according to your need, The final depiction of the PivotChart may look like the following picture.

Final Pivot Chart


🔄 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.

Pivot Table Group

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.

Group By options


🔄 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.

Pivot Chart By Month-How to Group Dates in Excel


🔄 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.

Pivot Chart By Year-How to Group Dates in Excel

Read More: How to Use Excel Pivot Table to Group Dates by Month and Year


Similar Readings


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.

Dataset-Months-How to Group Dates in Excel


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.

Format Axis window-How to Group Dates in Excel

🔺 The monthly depiction of the data Chart resembles the image below.

Inserted Chart

🔺 If Years is chosen in the dialog boxes, the depiction changes to something like the following screenshot.

Format Axis-How to Group Dates in Excel

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.

Grouped Data-How to Group Dates in Excel

Steps: Select the desired range then move to Insert > Select 2-D Column Chart (from the Charts section).

Grouped Data-How to Group Dates in Excel

🔺 Excel inserts the 2D-Column Chart as shown in the image below.

Inserted 2D-Column Chart

🔺 Furnish the Chart according to your priorities, you see Excel groups the data date-wise.

Grouped Dates

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.


Related Articles

 

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo