Calculation Issues

Fix Excel CONCATENATE Formulas That Paste as Blank Cells

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

The user needs to copy a range of CONCATENATE formulas while preserving either the displayed text or working formulas at the destination.

Fix Excel CONCATENATE Formulas That Paste as Blank Cells
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.
Before you start

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.

Solution 1Recommended

Paste the CONCATENATE Results as Values

Use this method when you only need the displayed text and do not need formulas at the destination.

1
Copy the source range

Select the cells containing the CONCATENATE results and copy them.

2
Choose the destination

Select the upper-left cell where you want to paste the results.

3
Paste values

Open Home > Paste and choose Values, or right-click the destination and select a Values paste option.

Paste the CONCATENATE Results as Values
Formulas are not retained: Pasting values replaces the destination formulas with their current results, so those cells will not update when source data changes.
Work with spreadsheet results

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the range containing the CONCATENATE results.
  2. 2. Copy the results: Select the source range and copy it.
  3. 3. Select the destination: Click the upper-left cell of the destination range.
  4. 4. Paste values: Open the Paste menu and choose Paste Special > Values, then check that the text appears in the destination cells.
Paste concatenated text without retaining formulasOpen and save Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.