In some cases, you may want to create a chart that shows more than one level of axis labels. This is easy to do as long as you structure the source data in a particular way. Here we have a list of the busiest US airports. We have columns for region, city, airport code, and activity, which represents total take offs and landings. Holding down the control key, it’s a simple matter to plot activity by city in a column or bar chart. But what if I want to group the cities in by region? Well, if I visit Select Data, I can click Edit under Category Axis Labels, and then expand the range to include region. As you can see, this adds a level to the axis labels, though it’s not very legible at the moment. To straighten out the labels, I need to restructure the data. First, I’ll sort by region and then by activity. Next, I’ll remove the extra, unneeded entries from the region column. The goal is to create an outline that reflects what you want to see in the axis labels. Now you can see we have a multi level category axis. If I double-click the axis to open the format task pane, then check Labels under Axis Options, you can see there’s a new checkbox for multi level categories axis labels. It’s important to know that this checkbox only appears when the axis labels range includes another set of values. If I remove region…the checkbox is gone. I’ll undo that. You can add more labels to create more than two levels. For example, I can use select data to include the airport codes as well. However, note that label values need to be in adjacent ranges. If I try to use the control key to exclude city, Excel doesn’t include both region and codes. The solution is to move codes next to region. Note that I’m holding down the shift key here to insert cells. Now I can edit the axis labels again and get the axis to display correctly. There were a lot of steps in that process, but now that the data is structured properly, Excel will plot a multi-level axis correctly from the start. If I delete this chart, then select both region and code, along with activity. Excel will build a two-level axis correctly on the first try.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
