How to Create an Excel Worksheet Containing Formulas Only in WPS Office
Question details
The user wants to copy an Excel worksheet or range into a new worksheet while keeping only formulas and removing constant values in WPS Office Spreadsheets, especially when Paste Special does not produce the desired formula-only result.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Preparing a formula-only copy of an existing worksheet for auditing, sharing, or template reuse.
- Observed behavior
- The copied worksheet currently contains both formulas and constant values; the goal state is a worksheet where formulas remain and entered constants are removed.
Open the workbook in WPS Office Spreadsheets and create a new worksheet in the same workbook. If you need to keep the original data unchanged, duplicate the workbook first and perform the removal steps on the copy.
Copy a Worksheet and Remove Constants with Find & Select > Go To Special
Use this solution when you need to keep all formulas in a copied worksheet and delete only typed constant values, especially when Paste Special does not produce a formula-only copy.
WPS Spreadsheets treats formulas and constants as different cell types. The Go To Special > Constants command selects cells that contain directly entered values rather than formulas, so deleting the selected cells leaves formula cells intact.
In WPS Office Spreadsheets, right-click the source worksheet tab, choose Move or Copy, select Create a copy, and click OK. To copy a range instead, select the range, press Ctrl+C, click the target worksheet, and press Ctrl+V.
If you copied an entire worksheet, click the Select All button at the intersection of row and column headers or press Ctrl+A. If you copied a range, select that exact pasted range.
On the Home tab, click Find & Select in the Editing group, then choose Go To Special from the drop-down menu.
In the Go To Special dialog, select Constants, keep Numbers, Text, Logicals, and Errors checked as needed, and click OK. Press Delete or right-click the selected area and choose Delete, then choose Shift cells up or Shift cells left if prompted.
Check that only formulas remain and all constant values have been removed. Use File > Save or Ctrl+S to save the workbook before closing.
Create a Formula-Only Excel Worksheet in WPS Office Spreadsheets
WPS Office Spreadsheets can accomplish this task directly by copying the worksheet, selecting constants with Find & Select > Go To Special, and deleting them so formulas remain. The edited workbook stays compatible with Microsoft Excel .xlsx format for easy sharing.
- 1. Download and launch WPS Office Spreadsheets: Install WPS Office from the official website, open the program, and create a new spreadsheet or open the workbook you want to copy.
- 2. Copy the source worksheet: Right-click the sheet tab, choose Move or Copy, select Create a copy, and click OK to duplicate the worksheet in the same workbook.
- 3. Select constants via Find & Select: On the Home tab, click Find & Select > Go To Special, choose Constants, and click OK.
- 4. Delete constants and save: Press Delete to remove the selected constant cells while keeping formulas, then press Ctrl+S to save the formula-only worksheet.
Frequently Asked Questions
Why does Paste Special not always create a formula-only copy in WPS Spreadsheets?
Paste Special can paste formulas, values, formats, or column widths, but it does not automatically identify and remove existing constants from a copied range. The Find & Select > Go To Special > Constants method selects only directly entered constant cells so you can delete them while formulas remain.
Will deleting Constants remove cells that contain dates or text?
Yes. In the Go To Special dialog, Numbers, Text, Logicals, and Errors are constant categories. If a date or text value is typed directly into a cell, it is selected as a constant and deleted. Cells beginning with an equal sign are formulas and are not selected by Constants.
Can I use this method on only part of a worksheet?
Yes. Select the copied range before opening Go To Special. The Constants selection applies only to the currently selected range, so formulas outside the selection are not affected.
What should I do if I delete constants but formulas show #REF! errors?
Check whether the deleted constant cells were referenced by formulas. If formulas depend on those constants as direct cell references, removing them can create #REF! errors. Press Ctrl+Z to undo and keep any input cells that formulas must reference.
Does this work the same way in WPS Office on desktop and mobile?
The desktop WPS Office Spreadsheets application includes the complete Find & Select > Go To Special feature. Mobile editing is simpler, so for precise formula-only copying use the desktop version for the most reliable result.
