Excel is a very powerful software. We can perform numerous operations on our datasets with many different Excel tools and features. Using various charts and graphs, you can present survey results very clearly and effectively. But sometimes, blank series may be present in our dataset. And creating a chart including those blank series is not desired in most cases. Moreover, they may result in errors and can confuse the viewers. In this article, we’ll show you the step-by-step procedures to Ignore Blank Series in Legend of Excel Chart.
Download Practice Workbook
Download the following workbook to practice by yourself.
Step by Step Procedures to Ignore Blank Series in Legend of Excel Chart
There are many different charts and graphs in Excel that we can choose from to apply to our datasets. It depends on the user’s requirements. In this article, we’ll insert the 2-D Clustered Column Chart. This chart is useful when comparing certain outputs. Therefore, follow the steps below carefully to accomplish the job.
STEP 1: Input Data
- In this example, we’ll input the name of the products and year-wise sales.
- Now, delete the sales series for the year 2019.
- Next, delete the Name header cell.
- This will avoid any complications while plotting the chart.
Read More: How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
STEP 2: Insert Excel Chart
We will show how to insert a chart in Excel in this step. So, learn the following process to carry out the operation.
- Firstly, select the range B4:F8.
- Then, go to the Insert tab.
- After that, click the Chart drop-down icon as marked below.
- Subsequently, choose the 2-D Clustered Column chart.
- Now, click the chart.
- Consequently, in the Chart Design tab, select Switch Row/Column.
- As a result, you’ll get a 2-D clustered column chart.
- Here, the products are the Y-axis labels.
- And Years are the Legends.
- Look at the below figure to understand better.
Read More: How to Hide Chart Series with No Data in Excel (4 Easy Methods)
STEP 3: Format Chart
However, you can modify and edit the chart to your requirements. In this step, you’ll know how to format the chart.
- First of all, click the Chart Title and edit the name.
- Next, select each of the series in the chart and fill it with your desired colors.
- Hence, it’ll look like the one demonstrated below.
Read More: Hide Zero Data Labels in Excel Chart (4 Easy Ways)
- Exclude Zero Values with Formula in Excel (3 Easy Ways)
- How to Use XLOOKUP to Return Blank Instead of 0
- Leave Cell Blank If There Is No Data in Excel (5 Ways)
- How to Use VLOOKUP to Return Blank Instead of 0 (7 Ways)
STEP 4: Ignore Blank Series in Legend
Our main target is in this step. We’ll ignore the blank series in the legend. Therefore, follow the process.
- In the beginning, select the legend with a single click on it.
- Then, click the desired year.
- Here, we click 2019.
- Afterward, double-click on the mouse.
- As a result, the Context Menu will pop out.
- Choose Delete Series.
- Thus, it’ll return the chart deleting 2019 from the legend and the chart plotting too.
- So, you won’t see any gaps in the product columns.
- But, if you just want to delete 2019 from the legend only, choose the Delete option in the context menu.
- Consequently, you’ll get the chart as displayed below.
Our chart is finally ready to present. We’ll make the last modification to make it appear more eye-catching.
- At last, we will remove the Gridlines.
- Select the Gridline and press Delete.
- Hence, the following chart gives us a comparative study of the sales of each product throughout the years.
Henceforth, you will be able to Ignore Blank Series in Legend of Excel Chart following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
- How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)
- Excel IFERROR Function to Return Blank Instead of 0
- Apply VLOOKUP to Return Blank Instead of 0 or NA
- How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)
- Hide Rows with Zero Values in Excel Using Macro (3 Ways)
- How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)