How to Enable Circular Calculations, Copy Formulas Without Changing References, and Display Three Significant Digits in WPS Spreadsheets
Question details
The user needs methods to handle three WPS Spreadsheets calculation-related tasks: enabling iterative calculation for circular references, copying formulas without changing relative references, and displaying values with three significant digits.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with an Excel-compatible workbook in WPS Office and needs to manage circular formulas, preserve formulas during copy operations, and format or round values to three significant digits.
- Observed behavior
- The user expects WPS Spreadsheets to support iterative calculations, formula copying without reference changes, and three-significant-digit display through settings, formulas, or custom number formats.
Save the workbook in an Excel-compatible format such as .xlsx or .xls, and identify the cells that contain circular references, formulas to copy, or values that need three-significant-digit formatting.
Enable iterative calculation for circular formulas in WPS Spreadsheets
Use this when a workbook intentionally contains circular references and WPS Spreadsheets shows a circular reference warning instead of calculating.
WPS Spreadsheets can calculate circular references when iterative calculation is enabled. This is useful for formulas that depend on their own result, such as certain allocation or convergence models.
Click the Menu button in the top-left corner of WPS Spreadsheets, then choose Options.
In the Options dialog, select the Calculation or Formulas tab, check Enable iterative calculation, set Maximum iterations and Maximum change as needed, and click OK.
Press F9 or edit one of the circular formula cells to let WPS Spreadsheets recalculate the workbook without the circular reference warning.
Copy formulas without changing relative references by temporarily replacing equals signs
Use this method when you need to paste the exact formula text to another range while keeping the original relative references unchanged.
Display numbers with three significant digits using ROUND or custom number format
Use this when calculations must show or store values rounded to three significant digits, either as display formatting or as actual rounded values.
Handle Circular References, Formula Copying, and Significant Digits Directly in WPS Spreadsheets
WPS Office provides Excel-compatible calculation settings, Find and Replace tools, and formula support so you can enable iterative calculations, copy formula text without shifting references, and display values with three significant digits without purchasing Microsoft Excel.
- 1. Download and install WPS Office: Download WPS Office Free from the official WPS site and run the installer to set up WPS Spreadsheets.
- 2. Open the Excel workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open your .xlsx or .xls workbook.
- 3. Enable iterative calculation if circular references are present: Click Menu > Options, open the Calculation or Formulas tab, check Enable iterative calculation, and click OK.
- 4. Copy formulas or display significant digits: Use Ctrl+H to temporarily replace = with | for formula copying, or enter =ROUND(A1,2-INT(LOG10(ABS(A1)))) for three significant digits.
- 5. Save the workbook in Excel-compatible format: Click Menu > Save As and choose .xlsx to keep formulas and formatting intact.
Frequently Asked Questions
Why does WPS Spreadsheets show a circular reference warning?
WPS Spreadsheets shows a circular reference warning when a formula refers to its own cell, directly or indirectly. You can allow the calculation by clicking Menu > Options, selecting the Calculation or Formulas tab, and enabling iterative calculation.
How can I copy a formula exactly without changing cell references?
Temporarily replace = with another character such as | using Ctrl+H, copy and paste the cells, then replace the character back with =. This keeps the relative references unchanged because the formula is treated as text during the copy.
What formula displays a number with three significant digits in WPS Spreadsheets?
Use =ROUND(A1,2-INT(LOG10(ABS(A1)))) to round the value in A1 to three significant digits. For display-only formatting, use Format Cells > Custom and enter a scientific format such as 0.00E+00.
Does WPS Spreadsheets support Excel circular reference workbooks?
Yes. WPS Spreadsheets supports Excel-compatible .xlsx and .xls circular reference workbooks when iterative calculation is enabled in Options, allowing the workbook to recalculate without switching to Microsoft Excel.
