How to create an axis with subcategories

Excel 2016
Excel offers a lot of different tricks to create great informative charts and diagrams. A lot of the useful adjustments related to the axis. E.g., you can hide an axis, use primary and secondary axis, change the presentation of data on the axis, etc.

One of such tricks is showing an axis with subcategories:

An axis with subcategories in Excel 365

To create an axis with subcategories, do one of the following:

  • If you have a data with structured categories, just select them and create the chart you like:
    A data with structured categories in Excel 365

    Excel automatically "understands" the structured data as axis data with subcategories:

    A chart with data with structured categories in Excel 365
  • If you already have a chart, do the following:

For example:

A chart in Excel 365

   1.   Add the new category or subcategory to your data.

For this example:

A new chart data in Excel 365

   2.   Do one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click in the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

   3.   In the Select Data Source dialog box, under Horizontal (Category) Axis Labels, click the Edit button:

Select Data Source dialog box in Excel 365

   4.   In the Axis Labels dialog box, choose cells with categories and subcategories for this axis and click OK several times:

Axis Labels dialog box in Excel 365

Excel changes an axis:

Changed chart in Excel 365

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more