Fix an Excel Concatenation Formula That Works on One Sheet but Not Another
Question details
Determine why a CONCAT or ampersand formula works in a new worksheet but not in an existing one.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to join text or cell values with the same formula across worksheets.
- Observed behavior
- The concatenation formula fails or behaves differently in the existing worksheet.
Compare the formula and referenced cells on both worksheets. Note whether the affected cell displays the formula itself, an error, or an outdated result.
Change Text-formatted cells to General and re-enter the formula
Use this fix when Excel displays a formula as text instead of calculating it.
Select the affected cell and open Home > Number Format. If it shows Text, change it to General.
Double-click the cell or select it and press F2, then press Enter. Changing the format alone does not convert an existing text entry into a formula.
Select the cell and inspect the formula bar. Remove any leading apostrophe or space before the equals sign, then press Enter.
If formulas appear across the worksheet, select Formulas > Show Formulas to turn that display mode off.

Check calculation settings and referenced cells
Use these checks if the formula calculates but returns an old result or an error.
Check CONCAT compatibility and test the ampersand operator
Use this approach if CONCAT returns a name error or the workbook is opened in an older Excel version.
Join cell values with a formula in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use the ampersand operator to combine text and cell values.
- 1. Open the workbook: In WPS Spreadsheets, open the workbook and select the worksheet you want to edit.
- 2. Set the cell format: Select the destination cell and choose General from the number format menu on the Home tab.
- 3. Enter the formula: Type ="Hello "&C9 in the cell, replacing C9 with the cell containing the value you want to join, and press Enter.
- 4. Save the workbook: Select File > Save or Save As and choose an Excel format if you need to share the workbook with Excel users.

Frequently Asked Questions
Why does Excel show my concatenation formula instead of its result?
The cell may be formatted as Text, or Show Formulas may be enabled. Change the cell format to General under Home > Number Format, re-enter the formula, and check Formulas > Show Formulas.
Why does CONCAT return a #NAME? error?
The Excel version opening the workbook may not support CONCAT, or the function name may be misspelled. Check the formula bar and try the ampersand operator or CONCATENATE.
Why does my formula return an error when C9 contains an error?
Concatenation formulas can propagate errors from referenced cells. Select C9, identify and fix its error, and then check the formula result again.
How do I make the formula update automatically?
In Excel, open Formulas > Calculation Options and select Automatic. Also confirm that the formula references the cells you intend to use.




