Fix Excel CONCATENATE Formulas That Paste as Blank Cells
Question details
The user needs to copy a range of CONCATENATE formulas while preserving either the displayed text or working formulas at the destination.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copying and pasting a range of cells containing CONCATENATE formulas to another location.
- Observed behavior
- The pasted cells appear blank even though the formulas remain. Values may appear briefly before disappearing, while copying a single working formula succeeds.
Check that the source range displays the expected text before copying it. Decide whether the destination needs fixed text or formulas that continue to update.
Paste the CONCATENATE Results as Values
Use this method when you only need the displayed text and do not need formulas at the destination.
Select the cells containing the CONCATENATE results and copy them.
Select the upper-left cell where you want to paste the results.
Open Home > Paste and choose Values, or right-click the destination and select a Values paste option.

Check Formula References and Automatic Calculation
Use this method when the pasted cells must retain formulas and update as their source data changes.
Paste CONCATENATE Results as Values in WPS Spreadsheets
Use WPS Spreadsheets to copy displayed CONCATENATE results and paste them as fixed text, without carrying the formulas into the destination.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the range containing the CONCATENATE results.
- 2. Copy the results: Select the source range and copy it.
- 3. Select the destination: Click the upper-left cell of the destination range.
- 4. Paste values: Open the Paste menu and choose Paste Special > Values, then check that the text appears in the destination cells.

Frequently Asked Questions
Why do pasted CONCATENATE formulas show blank cells?
The pasted formulas may refer to different cells than intended, or the workbook may need recalculation. Select a destination cell and inspect its references in the formula bar, then check Formulas > Calculation Options.
How do I copy the text without copying the formulas?
Copy the source range, select the destination, and choose Home > Paste > Values. The pasted text will no longer update from the original source cells.
How can I keep part of a formula reference fixed when copying?
Add dollar signs to the reference in the formula bar. For example, $A$52 keeps both the column and row fixed, while A52 changes relative to the pasted location.
Can I use INDEX if the source references need a different pattern?
Yes. An INDEX formula can select source cells according to a row or column pattern that ordinary relative references do not follow. Build and test the formula against the intended source range before filling it down.




