While working with Microsoft Excel, we may use the same data with different information. Grouping data enables users to combine any number of rows or columns. So that we may conceal, or more correctly, subset, the data underneath the chosen columns and rows. And, a chart is often a graphical representation of data. Users can better grasp data results by looking at charts. In this article, we will demonstrate two suitable methods to group data in an Excel chart.
How to Group Data in Excel Chart: 2 Suitable Methods
Large amounts of data and the links between different sections of the data are more easily understood via the charts. Typically, charts are easier to interpret than raw data. They are used in a wide range of industries. While we have appropriately formatted data and the names of the headers are listed in the column, we can utilize grouping in Excel.
To group data in Excel charts, we are going to use the following dataset. The dataset contains some product names and the month of sales and the revenue for each month.
1. Group Adjusting Lineup of Source Data in Excel Chart
A graph that is used for visualizing data is called a chart. A chart’s primary purposes are to provide facts and encourage a deeper study of a subject. We utilize a chart when a straightforward table cannot clearly show crucial connections or connections between sample points. Let’s see the instruction to group data in an Excel chart.
STEPS:
- In the first place, select the whole data in range B4:D11.
- Then, to start the process, first, go to the Insert tab on the ribbon.
- Next, click on the Insert Column or Bar Chart drop-down menu from the Chats category.
- Further, select the Clustered Column from the 2-D Column section.
- The Clustered Chart will appear shown in the screenshot below. The graph displays the revenues for various products for the given months.
- We organize the products according to the available product data for each month.
- For this, we insert a blank cell for separating the products. To insert a blank cell, right-click on your mouse and select Insert.
- This will also create some gaps in the chart.
- Further, as the product name is the same, we want to remove the duplicate name of the product. For this, select the cell which has the same product name and press the Delete key on your keyboard.
- Alternatively, we can merge the same product cells. For this, select the cells and go to the Home tab.
- In the Alignment group, click on the Merge drop-down menu and select Merge Cells.
- Now the dataset looks like this shown in the picture below.
- And, also the charts look like this. The various months and products are shown on the chart’s x-axis. The data are now in a group.
- Suppose we want to format the chart to visualize the group data properly.
- Double-click on the chart or press the Ctrl + 1, keyboard shortcut.
- To the right side of the spreadsheet, the Format Chart Area window will appear.
- Further, click on the Chart Options drop-down menu and select Series “Revenue”.
- The Format Data Series dialog box will display.
- Further, from the Series Options, make the Gap Width 0%. With this, all of the bars for a single category are gathered in one spot.
- Finally, select the same chart, then click the Fill option.
- Check the option that says ‘Vary colors by point’. See the colorful chart bars.
- And, That’s it! You can now visualize the group data in excel charts.
Read More: How to Edit Chart Data in Excel
2. Use Excel Pivot Chart to Group Data
A Pivot Chart represents data visually with a pivot table. Pivot tables and pivot charts are related to one another. Because a pivot table is connected to a pivot chart, the latter is far more adaptable than a standard chart. Changes made to the Pivot Table’s filters, sorts, and data configurations are shown on the chart. We can group data using Pivot Chart. For example, we are using the same dataset as the previous method. Let’s follow the procedures to group data in the excel pivot chart.
STEPS:
- To begin with, select the whole data.
- Then, go to the Insert tab from the ribbon.
- Next, from the Charts group, click on the Pivot Chart drop-down menu and choose PivotChart.
- This will open the Create PivotTable dialog box.
- Now, you can see the Table/Range field is filled with our previous selection (as we select the whole data before creating the pivot table) in the Select a table or range selection box under Choose the data that you want to analyze.
- Pick the Existing Worksheet from Choose where you want the PivotChart to be placed.
- In the Location field, put the cell where you want to place the pivot chart.
- Further, the PivotChart Fields will display on the right side of the spreadsheet.
- Select the Product and drag it into the Axis (Categories) section. And, drag the Month on the Legend (Series) section. Also, put the Revenue in the Values section by dragging the revenue.
- And, that’s all. Finally, you will able to see product data are in a group on the Excel Pivot Chart.
Clustered Column Chart to Clustered Bar Chart Conversion in Excel
A cluster bar chart is another name for a cluster bar chart. This is a particular kind of bar or line chart. This aids in representing comparative data across several categories more effectively than a bar chart. Let’s change the example of the first method’s clustered column chart into a clustered bar chart. The following are the requirements to alter the chart type.
STEPS:
- Firstly, select the chart, and by selecting the Chart Design and the Format tab will appear in the excel ribbon.
- Secondly, go to the Chart Design from the ribbon.
- Thirdly, click on the Change Chart Type from the Type category.
- This will display the Change Chart Type dialog box.
- Next, from the All Charts menu, select Bar.
- Further, click on the Clustered Bar.
- Then, click on the OK button to complete the process.
- Finally, by following this we can convert the Clustered Column Chart into Clustered Bar.
Things to Remember
- With group data in Excel Chart, we can perform the following prerequisites.
» Display a dispersion of data points.
» Enable comparison of both within and across groups.
» Examine how one element has changed in relation to another.
» Understanding the charts throughout various eras.
- Excel Charts are the ideal tool for data analysis since graphs depict data more accurately.
- The organization of the data affects how well the grouped chart works.
- The chart type may change after creating a group data chart.
- The data should arrange in a certain sequence, otherwise, the group data chart will not create properly.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above methods will assist you with Group Data in Excel Chart. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.
Related Articles
- How to Change Chart Data Range in Excel
- How to Edit Data Table in Excel Chart
- How to Change Data Source in Excel Chart
- How to Change X-Axis Values in Excel
- How to Change Chart Data Range Automatically in Excel
- How to Sort Data in Excel Chart
- How to Limit Data Range in Excel Chart
- How to Skip Data Points in an Excel Graph
- How to Remove One Data Point from Excel Chart
- How to Hide Chart Data in Excel
- How to Ignore Blank Cells with Formulas in Excel Chart
<< Go Back to Data for Excel Charts | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
