Chart & Visualization Issues

How to Create a Dynamic Excel Chart That Excludes Blank Cells in WPS Spreadsheets

Nimra Malik Nov 13, 2026 869 views

Question details

The user wants to build a clustered stacked bar chart in WPS Spreadsheets that updates automatically as new patient records are added and excludes blank rows from the chart range.

How to Create a Dynamic Excel Chart That Excludes Blank Cells in WPS Spreadsheets

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user manages patient data in a worksheet and needs a chart that expands with new records while skipping blank patient rows.
Observed behavior: The desired chart should update automatically when new records are added and should not include blank categories or empty series values.

Before you start

Open the workbook in WPS Spreadsheets and confirm that the data has headers in row 1, records starting in row 2, and no entirely blank rows inside the data block before the final used row.

Solution 1Recommended

Create dynamic named ranges with OFFSET and COUNTA and apply them to the chart

Use this method when you want the clustered stacked bar chart to expand automatically as new patient records are added and to exclude blank rows from the category and series ranges.

Dynamic named ranges use OFFSET to define a range whose height is determined by COUNTA. Because COUNTA counts only non-empty cells in the category column, fully blank rows are not included in the chart range.

If the chart will contain more than 10,000 records, replace the 10000 row reference with a larger number or use a structured Excel Table where possible.

Open Name Manager

In WPS Spreadsheets, open the workbook that contains the patient data, then click Formula > Name Manager to define a new named range.

Define the category named range

Click New, enter the name XValues, and in the Refers to box type =OFFSET('Data Sheet'!$A$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1). Confirm with OK.

Define series named ranges

Create three more named ranges, for example SeriesB, SeriesC, and SeriesD, using the same OFFSET formula but referencing columns B, C, and D respectively.

Create or select the chart

Select your data headings and a small sample row, then insert the clustered stacked bar chart from Insert > Chart. Right-click the chart and choose Select Data.

Apply the named ranges to the chart

In the Select Data Source dialog, set the category axis range to =YourWorkbookName!XValues and edit each series values to =YourWorkbookName!SeriesB, =YourWorkbookName!SeriesC, and =YourWorkbookName!SeriesD. Click OK.

Create dynamic named ranges with OFFSET and COUNTA and apply them to the chart

Blank rows must be completely empty: The category range is based on COUNTA in column A. If a row has a blank patient name but still contains values in B:D, that row may still appear in the series data; delete or clear the entire row to exclude it completely.

Create a Self-Updating Chart in WPS Spreadsheets with Dynamic Named Ranges

Use WPS Spreadsheets to build a dynamic clustered stacked bar chart that automatically expands as you add patient rows and skips blank cells. It supports Excel-compatible formulas and .xlsx files, so you can keep the same workflow without a subscription.

1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook containing the patient data, and make sure the chart data starts in row 2 with headers in row 1.
2. Create dynamic named ranges: Open Formula > Name Manager and define XValues, SeriesB, SeriesC, and SeriesD using OFFSET and COUNTA formulas as shown above.
3. Insert and link the chart: Insert a clustered stacked bar chart from Insert > Chart, right-click it, choose Select Data, and apply the named ranges to the category axis and each series.
4. Save the workbook: Save the file in .xlsx format so the dynamic chart and named ranges remain compatible with both WPS Spreadsheets and Microsoft Excel.

Automatically expands chart ranges when new patient records are addedExcludes blank rows so the chart stays clean and readableUses familiar OFFSET and COUNTA formulas that work with .xlsx and .xls filesFree, lightweight WPS Spreadsheets interface with no manual range updates

microsoft office alternative - wps office

Frequently Asked Questions

Why does my chart still show blank categories after I apply the named ranges?

Check that the named range uses the correct sheet name and starting cell, that the chart series references include the workbook name, and that blank rows are fully empty in all columns used by the chart.

Can I use the OFFSET and COUNTA method for a regular bar chart or line chart in WPS Spreadsheets?

Yes. The same dynamic named ranges can be used for any chart type because they simply return a non-blank range; only the chart type changes in the chart setup.

What if my data grows beyond row 10,000?

Edit the named range formulas and replace $A$10000 with a larger row reference, such as $A$50000, or use a structured Excel Table so the range expands automatically without rewriting the formula.

Will the chart update immediately when I enter a new patient record?

Yes. WPS Spreadsheets recalculates the named ranges automatically, so the chart reflects new non-blank rows as soon as the formula recalculates.

Can I open and edit this dynamic chart in Microsoft Excel later?

Yes. Save the workbook in .xlsx format, and the named ranges, chart links, and OFFSET/COUNTA formulas remain compatible with Microsoft Excel.

Excel & Data Problems