The multi-category chart contains categories and subcategories. You can create a multi-category chart in Google Sheets using either the Bar Chart or Column Chart.
Here in this tutorial, I am going to create a two-column multi-category chart – the first column contains the category, and of course, the second column the subcategory. There is a third column with numbers.
The multi-category chart in Google Sheets is not similar to the chart that you can create in Excel. But there are similarities.
To create a chart with multiple categories and subcategories, you should know how to format the chart data.
Before that, let’s see how the multiple category and subcategory chart varies in Excel and Google Sheets.
Excel:
Google Sheets:
In these charts, “Cable,” “Transformer,” and “Street Lighting Pole” are categories, and “Laying,” “Testing,” etc., are subcategories.
I like the first multi-category chart that I have created in Excel. In Google Sheets, there is no way to create a chart that matches that. You have to get satisfied with the existing one.
Now, you can learn how to create a multi-category chart in Google Sheets.
Steps to Create a Multi-category Chart in Google Sheets
A well-formatted data, I mean, the proper arrangements of values in rows and columns, is the essence of every chart.
Here is the data for the above chart. You should format your data as per this sample.
The first column should contain the category followed by the subcategory and the values.
I have put percentage values in the third column. It indicates job percentage. That means each category has values of 100% that are distributed to subcategories.
Such distribution is not necessary, though. You can put any number straightaway like this.
Here you can see some veg and non-veg items and their price. This data is also suitable for creating a multi-category chart in Google Sheets.
Once you have formatted your data, as shown above, you can start the procedure to create the chart.
Steps:-
- Select your data.
- Go to the menu “Insert” and click on “Chart.”
- Within the Chart editor panel, select “Bar chart” under Setup > Chart type.
That’s all. This way you can easily create a multi-category chart in Google Sheets. Enjoy!
Here is a google sheet data I would like to chart. I can not seem to find a way to make a chart.
Each person has three test results in three different categories, i.e., Person 1 has three tests (UA1, UA2, 18W) in the “Approach” category.
A line chart would be nice, but a bar would do.
Thanks for your help.
Category >> | Approach |Meet |Master |
Tests >> |UA1 UA2 18W |UA1 UA2 18W |UA1 UA2 18W |
Person 1 |41 41 60 |31 14 18 |7 7 4 |
Person 2 |28 49 59 |3 16 14 |0 3 1 |
Person 3 |40 24 49 |16 8 17 |5 3 3 |
Person 4 |58 71 62 |16 36 34 |7 14 14 |
Hi, everett,
You just transpose your data and create the chart.
If it’s in A1:J6, you can use the following formula in cell A10 for the chart.
=TRANSPOSE(A1:J6)I’ve included a sample sheet with your data and my suggested chart. Please check the end of the tutorial.
Unsure if this is the right spot, but I have a graphing/charting question/issue.
I have 3 sets of data in one table, all dependent on one variable.
When one variable is chosen, the associated data set populates, leaving the other 2 blanks (or with zero, I’ve tried it several ways).
The intent was to have a combo chart. Stacked 100% and vertical column (non-stacked) with graphs only appearing for visible data when the variable is chosen.
However, Google Sheets seems to forget my choices or throw errors or simply go blank after I format the graph and test the other variables. Is there a way to fix this??
Hi, Kou,
I will appreciate a sample/demo sheet with more details on the problem and of course the sample data.