Sometimes we have some blank cells in our dataset. When we plot them in an Excel chart, we can’t get the desired chart for these blank cells. In that case, we can ignore them by formulas. In this article, we will show how to ignore Excel chart blank cells with formulas effectively. I hope you find this article interesting.
How to Ignore Blank Cells with Formulas in Excel Chart: 2 Suitable Examples
To ignore Excel chart blank cells with formulas, we have two different examples through which you can have a clear idea of how to do it. In these two examples, we try to use the combinations of several functions. Finally, we will get our desired result. Both of these methods are fairly easy to use. Both of them will give us fruitful solutions.
1. Using Combined Formula
Our first method is based on using the combination of several functions. Here, we take a dataset that contains the date and profit. In that dataset, you will see several blank cells on both the date and profit columns. We will use the combination of IFERROR, INDEX, AGGREGATE, and ROW functions to eliminate the blank cells from the date column. We will use the INDEX and MATCH functions to eliminate the blank cells of the profit column. Then, we will create the line chart where all the blank cells will be ignored.
To understand the method clearly, let’s follow the steps.
Step 1: Find Date in Data Preparation Table
Here we can find the date in the data preparation table by using the INDEX function
- First, we want to prepare a data preparation table where we try to eliminate all the blank cells using the formulas.
- Then, select cell E6.
- Write down the following formula.
=INDEX($B$5:$B$14,1)
🔎 Breakdown of the Formula
INDEX($B$5:$B$14,1): The INDEX function returns a value of an array or table that is chosen by the given row and column. Here, we provide the array and row number. So, the INDEX function returns the value of the first row of the given array which is the first date.
- Then, press Enter to apply the formula.
- It will show a number in cell E6 instead of a date.
- To convert it into a date, select cell E6.
- Then, go to the Home tab in the ribbon.
- From the Number group, click on the downward arrow of the Number Format box.
- Then, select the Short Date option.
- As a result, it will change the number and convert it into the required date.
Step 2: Add Serial to Data Preparation Table
In this step, we want to add a new column known as Serial. We want to use that serial number as a reference in the formula
- Create a Serial column from 1 to 10.
- Then, change the formula of cell E6.
- Replace 1 with cell G6.
- Then, the formula of cell E6 will be like the following.
=INDEX($B$5:$B$14,G6)
- Press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
- As a result, you will see the cells E8, E11, and E13 have date values that are not real dates. Because we don’t have dates in cells B7, B10, and B12.
Step 3: Add Row Number in Data Preparation Table
In this step, we want to add another column known as row number. Here, we will use the combination of AGGREGATE and ROW Functions.
- First, we need to add another column known as Row Number.
- Select cell H6.
- Then, write down the following formula.
=AGGREGATE(15,3,ROW($B$5:$B$14),G6)
🔎 Breakdown of the Formula
AGGREGATE(15,3,ROW($B$5:$B$14),G6): Here, the AGGREGATE function has several options to ignore blank rows with error values. 15 denotes SMALL function number, 3 denotes ignore hidden rows, error values, nested SUBTOTAL, and AGGREGATE Functions. The AGGREGATE function uses this function and options in the given array and returns a number value.
- Press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column.
- As we have blank cells in the dataset, we want to skip those rows in the Row Number column. That’s why we need to do some modifications to our formula.
- First, select cell H6.
- Then, write down the following formula instead of the previous one.
=AGGREGATE(15,3,ROW($B$5:$B$14)/($B$5:$B$14<>""),G6)
🔎 Breakdown of the Formula
AGGREGATE(15,3,ROW($B$5:$B$14)/($B$5:$B$14<>””),G6): Here, the ROW function returns the array for the AGGREGATE function. Then, the AGGREGATE function uses the function and options in the given array. Finally, it returns a number value.
- Then, drag the Fill Handle icon down the column.
- After that, we will use the ROWS Function to remove the need for the Serial We want to delete the serial column from the data preparation table, that’s why we want to do this.
- Select cell H6.
- Then, delete the previous formula and write down the following formula.
=AGGREGATE(15,3,ROW($B$5:$B$14)/($B$5:$B$14<>""),ROWS($B$5:B5))
- Then, press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
- Here, the row number starts from 5. But we want to start row number from 1.
- In that case, you have to do some more modifications in cell H6.
- Then, select cell H6.
- After that, write down the following formula instead of the previous one.
=AGGREGATE(15,3,ROW($B$5:$B$14)-ROW($B$4)/($B$5:$B$14<>""),ROWS($B$5:B5))
🔎 Breakdown of the Formula
AGGREGATE(15,3,ROW($B$5:$B$14)-ROW($B$4)/($B$5:$B$14<>””),ROWS($B$5:B5)): Here, we want to start row number from 1. The AGGREGATE function has several options to ignore blank rows with error values. 15 denotes SMALL function number, 3 denotes ignore hidden rows, error values, nested SUBTOTAL, and AGGREGATE Functions.
ROW($B$5:$B$14)-ROW($B$4)/($B$5:$B$14<>””): It denotes that if there is a blank cell, it will skip it and go to the next. The AGGREGATE function uses this array and finally returns the row number from 1.
- Then, press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
- Now, we want to copy the formula from cell H6 and paste it into cell E6.
- The Formula in cell E6 is
=INDEX($B$5:$B$14,G6)In this formula, we only replace the G6 and put the formula of cell H6.
- When we replace this in cell E6, then the whole formula will be like the following.
=INDEX($B$5:$B$14,AGGREGATE(15,3,ROW($B$5:$B$14)-ROW($B$4)/($B$5:$B$14<>""),ROWS($B$5:B5)))
🔎 Breakdown of the Formula
INDEX($B$5:$B$14,AGGREGATE(15,3,ROW($B$5:$B$14)-ROW($B$4)/($B$5:$B$14<>””),ROWS($B$5:B5))): Here, the AGGREGATE function provides the row number. The INDEX function uses that row number in the given array and returns the required date.
- We need to use the IFERROR function along with this formula to eliminate any type of error.
- Then, write down the following formula in cell E6.
=IFERROR(INDEX($B$5:$B$14,AGGREGATE(15,3,ROW($B$5:$B$14)-ROW($B$4)/($B$5:$B$14<>""),ROWS($B$5:B5))),"")
🔎 Breakdown of the Formula
IFERROR(INDEX($B$5:$B$14,AGGREGATE(15,3,ROW($B$5:$B$14)-ROW($B$4)/($B$5:$B$14<>””),ROWS($B$5:B5))),””): Here, the IFERROR function denotes if there is an error, it will return blank. Otherwise, it will return the required date. As cell B5 doesn’t contain a blank, the IFERROR function will return the INDEX function value.
- Then, drag the Fill Handle icon down the cell.
- After that, delete both Serial and Row Number Because there is no need to keep them.
Step 4: Find Profit in Data Preparation Table
we need to complete the profit column by using the INDEX and MATCH functions.
- First, select cell F6.
- Write down the following formula.
=INDEX($C$5:$C$14,MATCH(E6,$B$5:$B$14,0))
- Then, press Enter to apply the formula.
- After that, drag the Fill Handle icon down the column.
Step 5: Establish Line Chart Using Data Preparation Table
In this step, we will prepare a line chart using the data preparation table.
- First, select the range of cells E6 to F12.
- Then, go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts option.
- Then, the Insert Chart dialog box will appear.
- Select Line Chart from there.
- Finally, click on OK.
- After modifying the chart style, we will get the following line chart where the blank cells are ignored. See the screenshot.
- To modify the horizontal axis, right-click on the axis.
- From the Context Menu, select Format Axis.
- Then, the Format Axis dialog box will appear.
- Select the Text axis from the Axis Options section.
- Finally, we get our desired line chart. See the screenshot.
Read More: How to Skip Data Points in an Excel Graph
2. Combining IF, ISBLANK and NA Functions
Our second method is based on the combination of IF, ISBLANK, and NA Functions. In this method. We want to replace the blank cells with the #NA. Because of this, the Excel chart will ignore blank cells. To show the method correctly, we take a dataset that contains months and their corresponding profit value.
To understand the method clearly, you need to follow the steps.
Steps
- First, select the range of cells B4 to C16.
- Then, go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts option.
- Then, the Insert Chart dialog box will appear.
- Select Line Chart from there.
- Finally, click on OK.
- After Modifying the chart style and axis, we get the following result.
- As we don’t use any formula in the blank cells, it puts blank cells also in the Excel chart.
- To solve the problem, we need to create another column named Modified Profit.
- First, select cell D5.
- Then, write down the following formula in the formula box.
=IF(ISBLANK(C5),NA(),C5)
🔎 Breakdown of the Formula
IF(ISBLANK(C5),NA(),C5): Here, the ISBLANK function checks cell C5 whether it is blank or not. If it is blank, then the IF function returns the NA function otherwise returns cell C5.
- After that, press Enter to apply the formula.
- Then, drag the Fill Handle icon down the column. Here, you will see #NA replaces all the blank cells.
- Now, select the range of cells D5 to D16.
- Then, go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts option.
- Then, the Insert Chart dialog box will appear.
- Select Line Chart from there.
- Finally, click on OK.
- Here we have our Line chart where all the blank cells are ignored.
- Then, to modify the horizontal axis of the chart, right-click on the chart.
- From the Context Menu, select the Select Data option.
- Then, the Select Data Souce dialog box will appear.
- Click on the Horizontal Axis Labels.
- Then, select the Month column in the Axis Labels dialog box.
- Finally, click on OK.
- Finally, after modifying the chart style, we get our desired chart where the blank cells are ignored. See the screenshot.
Read More: How to Limit Data Range in Excel Chart
Download Practice Workbook
Download the practice workbook below.
Conclusion
We have shown two examples to ignore Excel chart blank cells with formulas. In this article, we basically utilize several effective Excel functions and their combination. By combining them neatly, we found our desired result. I hope we covered all possible areas about ignoring blank cells with formulas.
Related Articles
- How to Change Chart Data Range in Excel
- How to Edit Data Table in Excel Chart
- How to Change Data Source in Excel Chart
- How to Edit Chart Data in Excel
- How to Change X-Axis Values in Excel
- How to Change Chart Data Range Automatically in Excel
- How to Sort Data in Excel Chart
- How to Group Data in Excel Chart
- How to Remove One Data Point from Excel Chart
- How to Hide Chart Data in Excel
