How to Create an Excel Pareto Chart Without Zeros or Blank Cells
Question details
The user needs to prepare a filtered data range for a Pareto chart so that zero values and empty cells are excluded from the analysis.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has category and value columns and wants to build a Pareto chart that reflects only meaningful, non-zero, non-blank entries.
- Observed behavior
- The standard chart source includes zero values and empty cells, which can add empty categories or make the Pareto analysis misleading.
Open the worksheet containing the category and value columns for the Pareto chart, and identify which column holds the numeric values used to detect zeros. Have an empty area on the same sheet ready for the filtered helper range.
Create a Filtered Helper Range and Build the Pareto Chart from It
Use this method when you want the chart source to update automatically and your spreadsheet version supports the FILTER function.
A helper range keeps the original data intact while giving the chart a clean source that contains only rows with meaningful values. This is especially useful for Pareto analysis because zero-value and blank categories can otherwise crowd the chart or distort the visual ranking.
Click a blank cell where you want the filtered output to start, such as D2, and make sure there is enough space below and to the right for the returned rows.
Type =FILTER(A2:B100,(B2:B100<>0)*(B2:B100<>""),"") and press Enter. This returns all rows where the value column is neither zero nor blank.
Select the helper range including the headers and all returned rows. This range is what the chart will use as its source.
Go to the Insert tab, open the chart type options, and choose Pareto. If Pareto is listed under Statistic Chart or Combo Chart, select it from that location.
Check that the chart displays only categories from the helper range and that zero-value or blank categories no longer appear.
Manually Filter the Source Data and Build the Chart from the Visible Rows
Use this method when your version does not support the FILTER function or when you only need to create a one-time Pareto chart.
Create a Zero-Free Pareto Chart in WPS Office Spreadsheet
WPS Office Spreadsheet lets you create a filtered helper range and insert an Excel-compatible Pareto chart that ignores zeros and blank cells. Its familiar spreadsheet tools and formula support make it easy to prepare clean charts while keeping full compatibility with Microsoft Excel files.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel-compatible workbook that contains the category and value data for the Pareto chart.
- 2. Create the filtered helper range: Click a blank cell and enter =FILTER(A2:B100,(B2:B100<>0)*(B2:B100<>""),"") if your WPS version supports dynamic arrays.
- 3. Select the helper range: Highlight the helper range including the headers and all returned rows so the chart uses only meaningful data.
- 4. Insert the Pareto chart: Go to Insert > Chart, open the statistics or all charts dialog, and choose Pareto to add the chart.
- 5. Save your file: Click Menu > Save As and choose .xlsx to keep the workbook Excel-compatible.
Frequently Asked Questions
Why does my Pareto chart still show empty categories after I filter the worksheet?
A chart can still reference hidden or unfiltered rows depending on the source range. Create a helper range with FILTER, or paste only visible filtered rows into a clean range, and then build the chart from that range.
Can I create this chart in an older version of Excel that lacks the FILTER function?
Yes. Turn on AutoFilter, filter the value column to exclude zeros and blanks, copy the visible rows, paste them into a clean area, and insert the Pareto chart from the copied data.
How do I exclude blank cells but keep genuine zero values?
Change the filter formula condition to remove only blanks, for example =FILTER(A2:B100,B2:B100<>"",""). The helper range will then keep zero-value rows while excluding empty cells.
Will the Pareto chart update automatically if I change the source data?
If you use the FILTER-based helper range, the chart updates when the formula recalculates. If you use a manual copy, you need to repeat the filter and copy steps after data changes.
Does WPS Office support dynamic array formulas like FILTER?
Recent versions of WPS Office Spreadsheets support dynamic array formulas such as FILTER. If the function is not available in your installed version, use the manual AutoFilter method.
