How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)

If you are looking for Excel Bar Chart ignore blank cells, you have come to the right place. Here, we will walk you through 4 easy and effective methods to do the task smoothly.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.

4 Methods to Ignore Blank Cells in Excel Bar Chart

The following dataset has the Month and Profit columns. We can see in the Profit column that the value for the months of March and May are blank in cells C7 and C9. Now, we have to insert a Bar chart using this data table. Here, we will demonstrate to you 2 methods for Excel Bar chart ignore blank cells. In this article, we used Excel 365. You can use any available Excel version.

Excel Bar Chart Ignore Blank Cells


1. Using 2D Bar Chart to Ignore Blank Cells 

In this method, first, we will insert a 2D Bar chart using our dataset. After that, we will use the Select Data feature to select Zero(0) as Show empty cells. As a result, we will be able to create an Excel Bar chart ignore blank cells.

Let’s go through the following steps to do the task.

Step-1: Inserting 2D Bar Chart

In this step, we will insert a Bar chart.

  • First, we will select the entire dataset >> go to the Insert tab.
  • Next from Insert Column or Bar Chart group >> select 2D Clustered Bar chart.

As a result, you can see the Bar chart.

  • At this point, we will click on the Chart Title to edit the Chart Title.

Excel Bar Chart Ignore Blank Cells

Hence, you can see a Bar chart with an edited Chart Title.

Step-2: Adding Data Label to Bar Chart

In this step, we will add Data Labels to our Bar chart.

  • In the beginning, we will click on the chart and select Chart Elements.
  • Moreover, mark Data Labels.

Excel Bar Chart Ignore Blank Cells

As a result, you can see Data Labels in the chart.

Here, you can easily notice that as the months March and  May had blank cells, the chart has no data labels for these two months. Therefore, the chart looks incomplete.

Step-3: Using Select Data Feature to Ignore Blank Cells

In this step, we will go through the Select Data feature to select Zero(0) as Show empty cells. Hence, the blank months in the chart will have 0 as the value.

  • First of all, right-click on the chart.
  • Furthermore, select Select Data from the Context Menu.

A Select Data Source dialog box will appear.

  • Later, click on Hidden and Empty Cells.

A Hidden and Empty Cell Settings dialog box will appear.

  • Next, Select Show empty cells as Zero.
  • Afterward, mark both the Show #N/A as an empty cell box and the Show data in hidden rows and columns box.
  • Moreover, click OK.

Excel Bar Chart Ignore Blank Cells

  • Along with that, click OK in the Select Data Source dialog box.

As a result, you can see in the month of March and May, is showing $0 instead of blanks.

Excel Bar Chart Ignore Blank Cells

Read More: How to Hide Chart Series with No Data in Excel (4 Easy Methods)


2. Inserting 3D Bar Chart to Ignore Blank Cells 

In this method, first, we will insert a 3D Bar chart using our dataset. After that, we will use the Select Data feature to select Zero(0) as Show empty cells. As a result, we will be able to create an Excel Bar chart ignore blank cells.

Let’s go through the following steps to do the task.

  • First, we will insert a 3D Bar chart.
  • To do so, we will select the entire dataset >> go to the Insert tab.
  • After that, select Recommended Charts.

An Insert Chart dialog box will appear.

  • Then, go to All Charts >> select Bar.
  • Afterward, from 3D Clustered Bar Group >> select the colored Bar Chart.
  • Moreover, click OK.

Excel Bar Chart Ignore Blank Cells

Hence, you can see the 3D Bar chart.

At this point, we will click on the Chart Title to edit the Chart Title.

Hence, you can see the 3D Bar chart with an edited Chart Title.

Excel Bar Chart Ignore Blank Cells

  • Next, we will follow Step-2 of Method-1 to add Data Labels to the 3D Bar chart.
  • Along with that, we will follow Step-3 of Method-1 to go through the Select Data feature to select Zero(0) as Show empty cells.

As a result, you can see in the month of March and May, is showing instead $0 of blanks.

Read More: How to Exclude Zero Values with Formula in Excel (3 Easy Ways)


3. Using a Table to Ignore Blank Cells in Excel Bar Chart

In this method, we will insert a Table in our dataset. After that, we will filter the blank cells out of the table. Then, we will insert a Bar chart. This will ignore blank cells from Excel Bar chart.

Let’s go through the following steps to do the task.

Step-1: Inserting Table

In this step, we will insert a Table using our dataset.

  • First of all, select the entire dataset.

You can select cell B4 and use the keyboard shortcut CTRL+SHIFT+END to select the entire dataset quickly.

  • After that, go to the Insert tab.
  • Next, from the Tables group >> click on Table.

Excel Bar Chart Ignore Blank Cells

At this point, a Create Table dialog box will appear.

Afterward, make sure My table has headers is marked.

  • Along with that, click OK.

As a result, you can see the Table.

Here, we change the Table header’s Text color to Black so that they look more visible.

Excel Bar Chart Ignore Blank Cells

Step-2: Filtering Data Table

In this step, we will Filter out the Blank cells from the table.

  • To do so, first, we will click on the drop-down arrow of the Profit column.

Therefore, you can see a dialog box appears.

Here, in the Number Filters group, you can see all the numerical values are selected including the Blanks.

Excel Bar Chart Ignore Blank Cells

  • Afterward, we will unmark the Blanks.

As a result, you can see, that the table now contains no blank cells.

Excel Bar Chart Ignore Blank Cells

Step-3: Inserting Bar Chart

In this step, we will insert Bar chart using the Table.

  • In the beginning, we will select the entire Table >> go to the Insert tab.
  • Afterward, from Insert Column or Bar Chart >> we select a Clustered Bar Chart.

Therefore, you can see the Excel Bar chart ignoring blank cells.

Excel Bar Chart Ignore Blank Cells

Read More: How to Ignore Blank Series in Legend of Excel Chart


Similar Readings


4. Using Combined Functions to Ignore Blank Cells

Here, the following dataset has the Date and Profit columns. You can see that there is a number of blank cells in both the Date and the Profit columns. Here, we will use the combination of the INDEX, AGGREGATE, IFERROR, ROW, and ROWS functions to remove the blank cells from the Date column. Along with that, we will use the combination of the INDEX and MATCH functions to remove the blank cells of the Profit column. After that, we will insert a Bar chart. As a result, we will get an Excel Bar chart ignore blank cells.

Let’s go through the following steps to do the task.

Step-1: Finding Date in Data Preparation Table by Using Index Function

In this step, we will use the INDEX function to find the date in the Data Preparation Table.

Excel Bar Chart Ignore Blank Cells

  • First of all, we will type the following formula in cell F5.
=INDEX($B$5:$B$13,1)

Formula Breakdown

  • INDEX($B$5:$B$13,1) the INDEX function → the INDEX function returns the value of an item in an array or table that is chosen by the indexes of a column and row number.
  • $B$5:$B$13 → is the array
  • 1 is the specified row number.
  • INDEX($B$5:$B$13,1) → becomes
    • Output: 44562
    • Explanation: 44562 is the Date in cell F5.
  • After that, press ENTER.

Then, you can see the result in cell F5.

Here, a number is shown in cell F5 instead of a Date. We have to format cell F5 with Date to bring out the Date.

  • Next, select cell F5 >> go to the Home tab.
  • Afterward, click on the downward arrow of the Number Format box.

Excel Bar Chart Ignore Blank Cells

  • Furthermore, select Short Date.

Hence, you can see in cell F5, Date is showing.

Excel Bar Chart Ignore Blank Cells

Step-2: Adding Serial to Date Column

In this step, we will add a Serial column to our Data Preparation Table. The Serial column indicates serial from 1 to 9. Here, the previous formula of cell F5 was

=INDEX($B$5:$B$13,1)

Next, we will replace 1 with E5.

As a result, the formula becomes

=INDEX($B$5:$B$13,E5)

Formula Breakdown

  • INDEX($B$5:$B$13,E5) the INDEX function → returns the value of an item in an array or table that is chosen by the indexes of the column and row number.
  • $B$5:$B$13 is the array
  • E5 is the specified row number.
  • INDEX($B$5:$B$13,E5) becomes
    • Output: 1/1/2022
    • Explanation: 44562 is the Date in cell F5.
  • After that, press ENTER.
  • Then, you can see the result in cell F5.
  • Afterward, drag down the formula with the Fill Handle tool.

Excel Bar Chart Ignore Blank Cells

Here, you can see that the cells F7, F9, F11 have date values that are not real dates. This is because the cells B7, B9, and B11 have blank cells.

Excel Bar Chart Ignore Blank Cells

Step-3: Adding Row Number by Using AGGREGATE and ROW Functions

In this step, we will add Row Number in our Data Preparation Table. We will use the combination of AGGREGATE and ROW functions to do the task.

  • First of all, we will type the following formula in cell H5.
=AGGREGATE(15,3,ROW($B$5:$B$13),E5)

Formula Breakdown

  • AGGREGATE(15,3,ROW($B$5:$B$13),E5) → the AGGREGATE function has multiple options for avoiding hidden rows along with error values.
  • 15 is the SMALL function_num .
  • 3 is the options that ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.
  • ROW($B$5:$B$13) → the ROW function returns the array for the AGGREGATE function.
  • E5 → is the ref_1.
  • AGGREGATE(15,3,ROW($B$5:$B$13),E5) → becomes
    • Output: 5
    • Explanation: 5 is the Row Number in cell H5.
  • Next, press ENTER.
  • Then, you can see the result in cell H5.
  • Afterward, drag down the formula with the Fill Handle tool.

Excel Bar Chart Ignore Blank Cells

As a result, you can see the complete Row Number column.

Step-4: Updating Formula in Cell H5

Here, in cells H7, H9, and H11, we do not want Row Number 7, 9, or 11. For instance, here, we want 8 in cell H7 instead of 7. This is because cells B7, B9, and B11 have blank cells. As a result, we want the Row Number to skip these blank cells and update.

  • To do so, we will type the following formula in cell H5.
=AGGREGATE(15,3,ROW($B$5:$B$13)/($B$5:$B$13<>""),E5)

Formula Breakdown

  • AGGREGATE(15,3,ROW($B$5:$B$13)/($B$5:$B$13<>””),E5) → the AGGREGATE function has multiple options for avoiding hidden rows along with error values.
  • 15 is the SMALL function_num .
  • 3 is the options that ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.
  • ROW($B$5:$B$13)/($B$5:$B$13<>””) → the ROW function returns the array for the AGGREGATE function
  • E5 → is the ref_1.
  • AGGREGATE(15,3,ROW($B$5:$B$13)/($B$5:$B$13<>””),E5) → becomes
    • Output: 5
    • Explanation: 5 is the Row Number in cell H5.
  • Next, press ENTER.
  • Then, you can see the result in cell H5.
  • Afterward, drag down the formula with the Fill Handle tool.

Excel Bar Chart Ignore Blank Cells

As a result, you can see the values 7, 9, and 11 are missing in the Row Number column. As a result, the Row Number is now updated and they ignore blank cells.

Step-5: Modifying Formula in Cell H5 by Using ROWS Function

In this step, we will replace E5 of the formula of cell H5 with a formula written with the ROWS function. This is because we want to remove the Serial column from our Data Preparation Table. As a result, the Row Number will no longer depend to the Serial column.

  • In the beginning, type the following formula in cell H5.
=AGGREGATE(15,3,ROW($B$5:$B$13)/($B$5:$B$13<>""),ROWS($B$5:B5))

Excel Bar Chart Ignore Blank Cells

Formula Breakdown

  • AGGREGATE(15,3,ROW($B$5:$B$13)/($B$5:$B$13<>””),ROWS($B$5:B5)) → the AGGREGATE function has multiple options for avoiding hidden rows along with error values.
  • 15 is the SMALL function_num .
  • 3 is the options that ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.
  • ROW($B$5:$B$13)/($B$5:$B$13<>””) → the ROW function returns the array for the AGGREGATE function
  • ROWS($B$5:B5 → the ROWS function returns the ref_1.
  • AGGREGATE(15,3,ROW($B$5:$B$13)/($B$5:$B$13<>””),ROWS($B$5:B5)) → becomes
    • Output: 5
    • Explanation: 5 is the Row Number in cell H5.
  • Next, press ENTER.
  • Then, you can see the result in cell H5.
  • Afterward, drag down the formula with the Fill Handle tool.

Here, you can see the values are the same in the Row Number column, however, the formula is different.

Step-6: Changing Formula in Cell H5 to Number Rows Serially

Here, the Row Number column starts from 5. However, we want the Row Number column to start from 1, and they must go serially.

  • To do so, we will type the following formula in cell H5.
=AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>""),ROWS($B$5:B5))

Excel Bar Chart Ignore Blank Cells

Formula Breakdown

AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””),ROWS($B$5:B5)) → the AGGREGATE function has multiple options for avoiding hidden rows along with error values.

  • 15 is the SMALL function_num .
  • 3 is the options that ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions.
  • ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””)→ returns the array for the AGGREGATE function
  • ROWS($B$5:B5 → the ROWS function returns the ref_1.
  • AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””),ROWS($B$5:B5)) → becomes
    • Output: 1
    • Explanation: 1 is the Row Number in cell H5.
  • After that, press ENTER.
  • Next, you can see the result in cell H5.
  • Afterward, drag down the formula with the Fill Handle tool.

Therefore, you can see the Row Number now starts from 1 and goes serially.

Hence, the Row Number is the complete serial number for the INDEX function.

Excel Bar Chart Ignore Blank Cells

Step-7: Copying Formula from Cell H5

In this step, we will copy the formula from cell H5. And after that, in the formula of cell F5, we will paste the copied formula. Here, the previous formula in cell F5 was

=INDEX($B$5:$B$13,E5)

Here, we will replace the cell reference E5 with the copied formula. This will end the dependence of the Date column on the Serial column.

  • In the beginning, we will copy the formula of cell H5.

Here, we simply select the formula and press CTRL+C to copy the formula.

  • After that, we replace E5 from the formula of cell F5 by pasting the copied formula.

The new formula becomes

=INDEX($B$5:$B$13,AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>""),ROWS($B$5:B5)))

Formula Breakdown

  • INDEX($B$5:$B$13,AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””),ROWS($B$5:B5))) → the INDEX function returns the value of an item in an array or table that are chosen by the indexes of a column and row number.
  • $B$5:$B$13 → is the array
  • AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””),ROWS($B$5:B5)) → is the specified row number.
  • INDEX($B$5:$B$13,AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””),ROWS($B$5:B5))) becomes
    • Output: 01-01-2022
    • Explanation: 01-01-2022 is the Date in cell F5.
  • Along with that, we will use the IFERROR function in the formula of cell F5 to ignore any sort of error.

As a result, the formula becomes

=IFERROR(INDEX($B$5:$B$13,AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>""),ROWS($B$5:B5))),"")

Excel Bar Chart Ignore Blank Cells

Formula Breakdown

  • IFERROR(INDEX($B$5:$B$13,AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””),ROWS($B$5:B5))),””) → the IFERROR function ignores the error present in a formula.
  • IFERROR(INDEX($B$5:$B$13,AGGREGATE(15,3,ROW($B$5:$B$13)-ROW($B$4)/($B$5:$B$13<>””),ROWS($B$5:B5))),””) the IFERROR function ignores the error present in a formula → becomes
    • Output: 01-01-2022
    • Explanation: 01-01-2022 is the Date in cell F5.
  • After that, press ENTER.

Next, you can see the result in cell H5.

  • Afterward, drag-down the formula with the Fill Handle tool.

As a result, you can see the Date column ignoring the blank cells.

Excel Bar Chart Ignore Blank Cells

Step-8: Deleting Serial and Row Number Columns

In this step, we will delete the Row Number and Serial columns. This is because our Date column does not depend on these columns anymore.

  • First of all, select the entire Row Number column. You can simply click on Column H to select the Row Number column.
  • After that, right-click on it.
  • Moreover, select Delete from the Context Menu.

  • Repeat the following process to delete the Serial column as well.

Therefore, you can see the Data Preparation Table now contains Date and Profit columns.

Excel Bar Chart Ignore Blank Cells

Step-9: Completing Profit Column

In this step, we will complete the Profit column by using the combination of INDEX and MATCH functions.

  • First of all, we will type the following formula in cell F5.
=INDEX($C$5:$C$13,MATCH(E5,$B$5:$B$13,0))

Formula Breakdown

  • INDEX($C$5:$C$13,MATCH(E5,$B$5:$B$13,0)) → the INDEX function returns the value of an item in an array or table that is chosen by the indexes of the column and row number.
  • $C$5:$C$13 → is the array
  • MATCH(E5,$B$5:$B$13 → the MATCH function returns the specified row_number.
  • 0 means exact match.
  • INDEX($C$5:$C$13,MATCH(E5,$B$5:$B$13,0)) → becomes
    • Output: 1200
    • Explanation: 1200 is the Profit of cell F5.
  • Next, press ENTER.
  • Then, you can see the result in cell H5.
  • Afterward, drag down the formula with the Fill Handle tool.

Excel Bar Chart Ignore Blank Cells

As a result, you can see the complete Profit column.

  • Next, we delete the last three columns of the Data Preparation Table as they do not contain any values.

As a result, the Data Preparation Table has no blank cells.

Step-10: Inserting Bar Chart

In this step, we will insert Bar chart using the following dataset.

  • Here, we will follow Step-1 of Method-1 to insert Bar chart and edit the chart title.

As a result, you can see the Bar Chart ignore blank cells.

Excel Bar Chart Ignore Blank Cells

Read More: How to Hide Zero Data Labels in Excel Chart (4 Easy Ways)


Practice Section

You can download the above Excel file to practice the explained methods.

Excel Bar Chart Ignore Blank Cells


Conclusion

Here, we tried to show you 4 methods for Excel Bar chart ignore blank cells. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo