Making Excel charts ignore Blank Cells with formulas can be useful for several data analysis activities. Here, we will go deeper and understand how it can be done and the ways to get this working.
Step 1: Select the Range
The first step is to select the range of cells that contain the data you want your charts to show (to ignore the blank cells). This range can either be a simple range of a few cells, or a range of many cells, regardless, the same steps need to be followed.
Step 2: Select the Formulas
Now it is time to select the formulas, by typing in the formula that you will be creating and inputting it into each of the cells you selected. Here are a few examples of some commonly used formulas in order to ignore blank cells:
COUNTIF formula:
This formula can be used to count the number of cells that are greyed out and not seen in the chart.
Excel-formula syntax: =COUNTIF(range,"<>")
IFERROR formula:
This formula can be used to replace any dodgy or problematic data with zeros. It is commonly used to avoid errors in a calculation.
Excel-formula syntax: =IFERROR(value,value_if_error)
IF formula:
This formula is the most common of them all, it can be used for any given situation where information needs to be calculated.
Excel-formula syntax: =IF(condition,value_if_true,value_if_false)
Step 3: Enter the Formula
The formulas provided above are now ready to be entered into each of the cells you selected. Once this is done, it is necessary to ensure that a chart is created that will ignore blank cells.
Step 4: Create a Chart
You can now start creating the chart! We suggest using the '3D Line Chart' or the '2D Line Chart' options, since they are the most appropriate options to use when wanting to ignore blank cells.
To make sure that charts are created correctly, click on 'Design I Chart Style', and then select 'Ignore blank cells'. This step is necessary to make sure that the created chart will ignore any problematic/ blank cells.
FAQ
What is the Excel-formula syntax for COUNTIF?
The Excel-formula syntax for COUNTIF is =COUNTIF(range,"<>").
What is the Excel-formula syntax for IFERROR?
The Excel-formula syntax for IFERROR is =IFERROR(value,value_if_error).
What is the Excel-formula syntax for IF?
The Excel-formula syntax for IF is =IF(condition,value_if_true,value_if_false).
What type of chart should be used to make sure that charts ignore blank cells?
The most suitable charts to use in order to ignore blank cells are 3D Line Charts and 2D Line Charts.
How do I create a chart that ignores blank cells?
In order to create a chart that ignores blank cells, click on 'Design I Chart Style' and then select the option 'Ignore blank cells'.