This tutorial provides a step-by-step example of how to create a chart in Excel and ignore blank axis labels.
Step 1: Enter Data with Some Blank Values
First, let’s enter the following dataset into Excel that contains some blank values for the axis labels:
If we highlight this range of data and insert a bar chart, the x-axis will have several blank values:
Step 2: Modify the Data to Remove Blank Values
In this step, we’ll create a new dataset that removes all of the blank values from the original dataset.
First, type the following formula into cell D2:
=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,3,ROW($A$2:$A$9)-ROW($A$1)/($A$2:$A$9""),ROWS($A$2:A2))),"")
Then drag the formula down to the other cells in column D until you encounter a blank value:
Next, type the following formula into cell E2:
=INDEX($B$2:$B$9,MATCH(D2,$A$2:$A$9,0))
Then drag the formula down to the remaining cells in column E:
We have successfully created a new dataset that contains only the rows where the value in the original “Store” column is not blank.
Step 3: Create the Chart with No Blank Axis Labels
We can now highlight the cells in the range D2:E6, then click the Insert tab along the top ribbon, then click the icon called Clustered Column within the Charts group:
Notice that the x-axis of the chart contains no blank labels since we used the modified dataset to create this chart.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Add Labels to Scatterplot Points in Excel
How to Change Axis Scales in Excel Plots
How to Add a Vertical Line to Charts in Excel