'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix PivotTable Refresh Error: Requires at Least Two Rows in Excel
Question details
The user is experiencing an error when attempting to refresh a PivotTable or PivotChart, indicating that the source data is insufficient.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating or refreshing a PivotTable or PivotChart after data has been modified, deleted, or moved.
- Observed behavior
- An error message appears stating that the command requires at least two rows of source data, preventing the PivotTable from successfully refreshing.
Ensure your original source data has not been accidentally deleted and that the worksheet containing your raw data is still accessible before troubleshooting the PivotTable.
Check and Update the PivotTable Source Data Range
This error occurs when the PivotTable's defined data source range shrinks to a single row or an empty cell. Manually reselecting the full dataset including headers and data rows resolves the issue.
A valid PivotTable must reference at least two rows: one row for the column headers and at least one row containing the actual data records. If rows were deleted from your original data, the source reference might have broken.
Click anywhere inside your existing PivotTable to activate the PivotTable Tools on the top ribbon menu.
Navigate to the 'PivotTable Analyze' (or 'Options') tab on the ribbon, and click the 'Change Data Source' button.
In the 'Change PivotTable Data Source' dialog box, check the 'Table/Range' field. You will likely see that it points to an invalid range, such as a single row or an empty block.
Delete the incorrect range text. Use your mouse to highlight your complete dataset in the source worksheet, making sure you include the very top header row and all subsequent data rows beneath it.
Click 'OK' to apply the updated range. The PivotTable should immediately update and the refresh error will no longer appear.
Manage PivotTables Effortlessly in WPS Office
WPS Spreadsheet provides a highly compatible, fast, and intuitive interface for building, modifying, and refreshing PivotTables. Easily define dynamic data ranges to prevent row selection errors during complex data analysis.
- 1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the existing PivotTable.
- 2. Access PivotTable Tools: Click directly on the PivotTable to reveal the 'PivotTable Tools' tab in the top ribbon menu.
- 3. Update the Data Source: Click on 'Change Data Source', highlight your full data range (including headers and data rows), and click 'OK'.
- 4. Refresh Data: Click the 'Refresh' button on the ribbon to successfully update your PivotTable analysis.
Frequently Asked Questions
Why does my PivotTable source range shrink or change on its own?
If you delete rows at the top or bottom of your dataset, the static cell references in the PivotTable's source range can shift or shrink. This often leaves the PivotTable pointing to too few rows, triggering the two-row requirement error.
How can I prevent the 'requires at least two rows' error in the future?
Convert your source data into an official Table (by pressing Ctrl+T or inserting a Table). When you use a structured Table as your PivotTable source, the range dynamically expands and contracts as you add or remove rows, entirely avoiding manual range selection errors.
Can a PivotTable work if my source data contains blank rows?
Yes, a PivotTable can process datasets with blank rows, displaying them as '(blank)' in your report. However, if the entire selected source range is completely blank or is missing header labels, it will trigger an error. The first row selected must always contain headers.
