Pivot Table Issues

Fix Excel PivotTable and Data Validation Drop-Down Updates in WPS Office Spreadsheets

Huda Qurayshi Nov 13, 2026 869 views

Question details

The user needs to make Excel data validation drop-down lists and PivotTables in WPS Office Spreadsheets include newly added rows and display the latest values after source data changes.

Fix Excel PivotTable and Data Validation Drop-Down Updates in WPS Office Spreadsheets

Product: WPS Office Spreadsheets
Device & OS: not provided
Scenario: The user maintains a workbook where new rows are added to the source data and expects both the data validation drop-down list and the PivotTable to reflect those additions.
Observed behavior: New rows are not appearing in the data-validation list, and the PivotTable is not displaying the latest values until the source range is corrected or the PivotTable is refreshed.

Before you start

Open the workbook in WPS Office Spreadsheets and identify the exact source range used by the data validation list and PivotTable. Confirm that newly added rows are inside that range or that the source data has been converted to a Table.

Solution 1Recommended

Expand the Data Validation Source Range or Convert Source Data to a Table

Use this solution when new rows are outside the current data validation source range and you want the drop-down list to include them automatically.

If the source is a fixed range such as A1:A100, rows added below row 100 are ignored. Converting the range to an Excel Table makes the data validation list pick up new rows automatically.

Locate the data validation source

In WPS Office Spreadsheets, select the cell containing the drop-down list, go to the Data tab, and click Data Validation to view the current Source range.

Expand the source range manually

If the source is a fixed range such as $A$2:$A$100, change it to include the newly added rows, for example $A$2:$A$500, and click OK.

Or convert the source data to a Table

Select the source data, go to the Insert tab, click Table, and confirm the range. Then update the Data Validation source to reference the Table column instead of the old fixed range.

Test with a new row

Add a new row to the Table or expanded range, then open the drop-down list to confirm the new entry appears.

Expand the Data Validation Source Range or Convert Source Data to a Table

Data validation range updated: After converting to a Table, new rows are included automatically because the data validation source uses a structured Table reference.

Solution 2

Refresh the PivotTable to Show the Latest Values

Use this solution after adding or changing source data so the PivotTable picks up new rows and updated values.

WPS Spreadsheets how-to

Refresh Excel PivotTables and Data Validation Lists in WPS Office Spreadsheets

Use WPS Office Spreadsheets to expand or convert the source data for data validation lists, then refresh PivotTables so they show the latest rows and values without changing your Excel file workflow.

1. Open your Excel workbook in WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the data validation list and PivotTable.
2. Convert the source data to a Table: Select the source range, click the Insert tab, choose Table, and confirm the range so new rows are included automatically.
3. Update the data validation source: Select the drop-down cell, open the Data tab, click Data Validation, and point the source to the Table column.
4. Refresh the PivotTable: Right-click inside the PivotTable and choose Refresh, or select Refresh All from the Data tab.
5. Verify the results: Add a new row and open the drop-down list, then confirm the PivotTable shows the latest values.

Update Excel data validation drop-downs by using Tables or expanded rangesRefresh PivotTables in a few clicks to show newly added rowsFull compatibility with Excel .xlsx and .xls filesFree and lightweight spreadsheet editing with a familiar ribbon UISeamless migration from Microsoft Excel without retraining

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel data validation drop-down not include newly added rows?

Usually the source range is fixed. In WPS Office Spreadsheets, open Data > Data Validation and either expand the range or convert the source data to a Table so the list uses a structured reference that grows automatically.

How do I refresh a PivotTable in WPS Office Spreadsheets?

Click inside the PivotTable, right-click, and choose Refresh. To update all PivotTables, go to the Data tab and click Refresh All.

Will converting my source data to a Table break existing Excel formulas or charts?

No. Table references remain compatible with Excel, and formulas and charts can continue to reference the Table column. You may need to update a fixed data validation range to use the Table column.

Why is my PivotTable not showing the latest values even after I add data?

PivotTables do not update automatically. Right-click the PivotTable and select Refresh, or use Data > Refresh All after changing the source range or adding rows.

Can I edit and save Excel files with WPS Office without losing the PivotTable?

Yes. WPS Office Spreadsheets supports Excel .xlsx and .xls files, including PivotTables and data validation lists, so you can edit and save without losing those features.

Excel & Data Problems