How to create an axis with subcategories

Excel 365
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 2016

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 2016

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

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

For example:

A chart in Excel 2016

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

For this example:

A new chart data in Excel 2016

   2.   Do one of the following:

  • Under Chart Tools, on the Design tab, in the Data group, choose Select Data:
    Select Data in Excel 2016
  • Right-click in the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 2016

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

Select Data Source dialog box in Excel 2016

   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 2016

Excel changes an axis:

A chart with data with structured categories in Excel 2016

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