How to Create an Excel Chart That Excludes Empty Rows
Question details
The user needs to create an Excel chart that automatically ignores or excludes blank and N/A rows present in the source dataset.
- Product
- Spreadsheet / Excel
- Device & OS
- not provided
- Scenario
- Creating a visualization, such as a top-five chart, from a data range that contains empty or N/A rows due to ties or unfiltered pivot tables.
- Observed behavior
- The chart reserves space for the empty rows because they are included in the source range, resulting in extra blank categories or gaps in the final visualization.
Ensure your source data has clear column headers, and identify which specific columns contain the empty or N/A values you want to exclude from your chart.
Filter Data and Create a PivotChart
The most effective way to exclude empty rows without using complicated formulas is to summarize your data with a PivotTable, filter out the blanks, and insert a PivotChart.
By default, standard charts display exactly what is in the source range. By routing your data through a PivotTable first, you can dynamically filter out invalid data, ensuring your final chart remains clean even if the source data updates.
Select your entire source data range. Go to the 'Insert' tab on the ribbon and click 'PivotTable'. Choose to place it on a New Worksheet or Existing Worksheet.
In the PivotTable Fields pane, drag the category you want to graph into the 'Rows' (or Axis) area, and your numeric data into the 'Values' area.
Click the drop-down filter arrow next to 'Row Labels' in the PivotTable. Uncheck the boxes for '(blank)' or '#N/A', then click 'OK'.
Click anywhere inside the filtered PivotTable. Go to the 'Insert' tab and select 'PivotChart'. Choose your desired chart type. The chart will now only display the qualifying categories.
Use AutoFilter and Chart Settings
If you prefer not to use a PivotTable, you can apply a standard data filter to your source range and configure the chart to ignore hidden rows.
Exclude Empty Rows Instantly with WPS Spreadsheet
WPS Office Spreadsheet offers highly intuitive PivotTable and charting tools that make visualizing data effortless. You can easily filter out N/A or blank rows and generate clean, professional charts in seconds.
- 1. Open your data: Launch WPS Spreadsheet and open the document containing your raw dataset.
- 2. Create a PivotTable: Select the dataset, go to the 'Insert' tab, and click 'PivotTable'.
- 3. Filter the data: Set up your rows and values, then use the drop-down filter on the row labels to exclude any blank or '#N/A' items.
- 4. Insert Chart: With the PivotTable selected, navigate back to 'Insert', click 'Chart', and choose your preferred visualization type. The chart will dynamically exclude the hidden rows.
Frequently Asked Questions
Why does my Excel chart show gaps for empty cells?
By default, standard Excel charts plot all cells included in the specified source range. If there are empty cells or N/A values within that range, the chart reserves space on the axis for them, resulting in visible gaps or empty categories.
Can I use formulas instead of a PivotTable to skip blanks in charts?
Yes, you can use dynamic array formulas like FILTER to extract non-blank rows into a new, contiguous range, and then build your chart from that new range. However, for most users, creating a PivotChart is a much simpler and quicker workaround.
Does deleting the empty rows fix the chart?
Yes. If you permanently delete the empty rows from the source data, the chart will immediately remove those categories. However, if your source data is automatically generated or linked to another system, filtering via PivotTables is a better, non-destructive solution.
