Function Problems

Fix Excel Paste Special Formulas Copying Manual Values

Huda Qurayshi Oct 10, 2026 871 views

Question details

The user needs a way to copy a range of cells and paste only the formulas, but Excel's Paste Special command is incorrectly copying manually entered values along with the formulas.

How to Fix Excel Paste Special Formulas Copying Manual Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to duplicate formula logic into a new area of a worksheet without bringing over the static, manually entered data from the source range.
Observed behavior: When selecting 'Paste Special' > 'Formulas', Excel pastes both the formulas and the static, manually entered values, failing to leave the non-formula cells blank.

Before you start

Before proceeding, confirm that your worksheet is not protected and that you have a backup of your data, especially if you plan to use VBA macros to automate the paste behavior.

Solution 1Recommended

Use 'Go To Special' to Clear Constants After Pasting

Since Excel lacks a reliable built-in feature to paste only formulas, the best no-code workaround is to paste everything as formulas and then delete the static values.

This method relies on Excel's 'Go To Special' feature, which can instantly identify and select all cells in a range that contain static, manually typed data rather than formulas.

Copy and Paste Special

Copy your source range, right-click the destination cell, select 'Paste Special', choose 'Formulas', and click 'OK'.

Keep the Destination Selected

Do not click away. Ensure the newly pasted data range remains fully highlighted.

Open Go To Special

Press F5 or Ctrl+G on your keyboard to open the 'Go To' dialog box, then click the 'Special...' button in the bottom left corner.

Select Constants

In the Go To Special menu, select the 'Constants' radio button. Leave all sub-options (Numbers, Text, Logicals, Errors) checked, and click 'OK'.

Delete Manual Values

Excel will now highlight only the cells containing static values. Press the 'Delete' key on your keyboard to clear them, leaving only your formulas intact.

Use 'Go To Special' to Clear Constants After Pasting

Quick Formatting Tip: If you also want to retain the original formatting, perform a second 'Paste Special' over the same range and select 'Formats'.

Solution 2

Create a Custom VBA Macro

If you frequently need to paste only formulas and leave other cells completely blank, you can use a VBA script to automate the exact behavior you need.

Free Microsoft Office alternative

Try WPS Office for a Seamless Spreadsheet Experience

If Excel's limitations or recurring software behaviors disrupt your workflow, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for handling advanced spreadsheet functions and complex datasets without the hefty price tag.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open your Spreadsheet: Launch WPS Spreadsheets and open your existing Excel (.xlsx) files directly.
3. Edit and Save: Enjoy familiar formula functions, edit your data efficiently, and save with perfect formatting retention.

Highly compatible with Microsoft Excel (.xls, .xlsx, .csv) formatsLightweight installation and lightning-fast software performanceFree built-in tools for data analysis and visualizationFamiliar tabbed user interface for a seamless, zero-learning-curve migration

microsoft office alternative - wps office

Frequently Asked Questions

Is there a built-in Excel command to paste only formulas and leave other cells blank?

No, Excel does not have a native feature that explicitly isolates formulas from static values during a paste operation. The 'Paste Special > Formulas' option pastes formulas but defaults to pasting manually entered text and numbers as well.

Does the 'Skip Blanks' option in Paste Special solve this problem?

No. The 'Skip Blanks' checkbox only prevents blank cells in your copied source range from overwriting existing data in the destination range. It does not separate formulas from static values.

How can I quickly remove static values from a large dataset while keeping formulas?

You can use the 'Go To Special' feature. Highlight the dataset, press F5, click 'Special...', select 'Constants', and click OK. Once all static values are highlighted, press the Delete key.

Excel & Data Problems