You can create a multi-category chart in Google Sheets using either a Bar Chart or a Column Chart. However, the subcategory labels won’t appear below the category labels on the axes. Instead, they will be displayed within the chart grid.

When creating this chart, arranging your source data correctly is crucial. The process is similar in Excel and Google Sheets, but there’s a significant difference: Excel natively supports categories and subcategories on the x-axis for column charts and the y-axis for bar charts. While Google Sheets can plot the chart similarly, the subcategory labels won’t appear on the axes as they do in Excel. Instead, they are shown within the chart grid, as mentioned above.

Before we proceed, let’s look at how the chart appears in Excel versus Google Sheets:

Excel:

Google Sheets:

In these charts, “Cable,” “Transformer,” and “Street Lighting Pole” are categories, while “Laying,” “Testing,” etc., are subcategories.

I prefer the multi-category chart I created in Excel. Unfortunately, Google Sheets doesn’t offer an exact equivalent. You’ll have to work with the current limitations.

Now, let’s learn how to create a multi-category chart in Google Sheets.

Data Preparation for Multi-Category Chart in Google Sheets

A well-formatted dataset, with values properly arranged in rows and columns, is the foundation of any chart, and the multi-category chart is no exception.

Here is the data used for the example charts. You should format your data similarly.

Sample Data:

1. Enter the categories in the first column, leaving empty rows between each category. The number of empty rows, plus the category row, should match the number of subcategories.
2. Enter the subcategories corresponding to each category in the second column.
3. Add the data point values in the third column.

In the sample data, you’ll see a billing breakup for certain activities. For example:

• Column A contains the job categories.
• Column B lists the payment breakdown (subcategories).
• Column C provides the percentage of payment released (data points).

If you’d like another example, here it is:

Once your data is formatted as shown above, you’re ready to create the multi-category chart.

Steps to Create a Multi-Category Chart in Google Sheets

In this example, we’ll create a multi-category bar chart. If you’d prefer a column chart, the steps are almost identical except for the chart type selection.

1. Select your data.
2. Go to the menu Insert and click on Chart.
3. In the Chart editor panel, under Setup > Chart type, select Bar chart. This will create a multi-category bar chart.
   • For a column chart, select Column chart instead.
4. If you encounter any issues, make a copy of my sample sheet linked below and check the chart settings by double-clicking the chart.

Sample_Sheet

Resources

• How to Format Data to Make Charts in Google Sheets
• Choosing a Suitable Chart Type for Your Data in Google Sheets
• Get a Target Line Across a Column Chart in Google Sheets
• Floating Column Chart in Google Sheets – How to
• Add Legend Next to Series in Line or Column Chart in Google Sheets
• Reducing the Width of Columns in Column Charts in Google Sheets
• Bar or Column Chart with Red Colors for Negative Bars in Google Sheets
• Google Sheets Bar and Column Chart with Target Coloring
• How to Change Data Point Colors in Charts in Google Sheets