Excel: How to Create Chart & Ignore Blank Axis Labels

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.

The following tutorials explain how to perform other common tasks in Excel:

May 29, 2024
May 13, 2024
April 25, 2024

One Reply to “Excel: How to Create Chart & Ignore Blank Axis Labels”

1. Maze says:

Sorting will also remove blanks, or hiding the columns. Or filtering on blanks and deleting the rows. Faster than the formula method.