How to Create a Chart with Two-level Axis labels in Excel

This post will guide you how to create a column chart with two-level Axis labels based on your original data in Excel. How do I create a Two-level X axis labels in your Chart with Pivot table in Excel 2013/2016.

Assuming that you have a list of data, and you want to create a column chart with two-level X Axis labels. This post will introduce two ways to achieve the result. You need to change the original data in the First method, including sorting and merging cells. And a pivot table need to be created based on your data, then create a Column Chart based on Pivot table.

create chart with two level axis1

Create a Chart with Two-Level Axis Label


You need to change the original data firstly, and then create column chart based on your data. Just do the following steps:

Step1: select the first column (product column) except for header row.

create chart with two level axis2

Step2: go to DATA tab in the Excel Ribbon, and click Sort A to Z command under Sort & Filter group. And the Sort Warning dialog will open.

create chart with two level axis3

Step3: keep the Expand the selection option be checked, and click Sort button in the Sort Warning dialog box.

create chart with two level axis4

Step4: the selected cells should be sorted.

create chart with two level axis5

Step5: select the first same category of product in the first column, such as: Excel.

Step6: go to Home tab, and click Merge & Center command under Alignment group. And the Microsoft Excel warning dialog box will open. And Click Ok button. And the first product should be merged into one cell.

create chart with two level axis7

create chart with two level axis8

Step7: you need to repeat the step 5-6 to merger other adjacent cells with the same category of product.

create chart with two level axis9

Step8: select your current source data, and go to Insert tab, click Insert Column Chart command and select Clustered Column from the dropdown list box.

create chart with two level axis10

Step9: you would see that the column chart with two-level Axis labels has been created successfully.

create chart with two level axis11

 

Create a Chart with Two-Level Axis Label Based on Pivot Table


You can also create a Column Chart with two-level axis labels based on a pivot table in your worksheet, just do the following steps:

Step1: select your source data, and go to Insert tab, click PivotTable command under Tables group.

create chart with two level axis12

Step2: check the Existing Worksheet option and select a blank cell to place your pivot table in your current worksheet, and click Ok button.

create chart with two level axis13

Step3: choose product, data, sales fields in the PivotTable Fields pane. And the pivot table should be created based on your source data.

Step4: select your pivot table, and go to Insert tab, click Insert Column Chart command and select Clustered Column from the dropdown list box.

create chart with two level axis14

Step5: you would see that the column chart with two-level Axis labels has been created successfully.

 

Related Posts

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

How To Remove Special Characters in Excel
remove special character2

This post will show you how to remove special characters from text strings in Excel. And I am going to introduce two ways to remove special characters (@&)#%$) from a string in Excel. Removing Special Characters using Power Query If ...

How To Insert Comments in Protected Worksheet in Excel
insert commetns in protected workshee3

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel
convert text to upper cases1

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)
hide every other row1

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

How to Sort Data but Keep Blank Cells in Excel

In daily work, if we sort data with blank cells included in the same column, these blank cells are listed at the bottom automatically after sorting. If we want to keep the positions of these blank cells unchanged and only ...

How to Copy and Paste Only Values and Ignore Formula

When we copy a cell applied with a formula, we copy the formula of the cell rather than copy the value showing in the cell. In this article we will introduce you the way to copy only value ignoring applied ...

How to Sort Date by Day of Week in Excel

Except sort data by “A to Z” (alphabetical order, for numbers from small to large), we can also sort data by date, month or year if these conditions are given. In this article, we will show you the way to ...

How to Select All Non-Blank Cells of a Range

In daily work, we may meet the cases that select all blank cells or non-blank cells of a range. You may know the way to select all blank cells as they are “blanks”. But for non-blank cells, they may contain ...

Sidebar