Formula Errors

Fix a CONCATENATE Formula That Stops Working in Excel

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

The user needs an Excel formula to combine values from two columns across multiple rows.

Fix a CONCATENATE Formula That Stops Working in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Combining values from columns J and K into a separate output column.
Observed behavior
A whole-column CONCATENATE formula may not populate the expected rows.
Before you start

Identify the first data row and an empty output column. The examples use J2 and K2 as source cells and L2 as the first output cell; adjust these references for your worksheet.

Solution 1Recommended

Use a Row-Specific Formula and Fill It Down

Use this approach to combine each pair of cells across Excel versions.

1
Enter the formula

Select L2 and enter =J2&" "&K2, then press Enter. This joins the two values with a space.

2
Fill the remaining rows

Select L2 and drag its fill handle down through the last data row. Excel adjusts the references for each row.

3
Check calculation and cell format

If the results do not update, select Formulas > Calculation Options > Automatic. If a formula appears as text, select the affected cells, choose Home > Number Format > General, then press F2 and Enter on the formula cell and fill it down again.

Use a Row-Specific Formula and Fill It Down
Combine spreadsheet values

Combine Column Values in WPS Spreadsheet

Use a row-specific formula in WPS Spreadsheet and fill it down to combine values from two columns.

  1. 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose your Excel workbook.
  2. 2. Enter the formula: Select an empty output cell such as L2, enter =J2&" "&K2, and press Enter.
  3. 3. Fill down: Select L2 and drag the fill handle down to the last row that needs a combined value.
  4. 4. Save the workbook: Select File > Save As and choose an Excel format such as XLSX if you need to share the file with Excel users.
Combine values row by row with a simple formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceFill formulas down across multiple rows
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show my formula instead of the result?

The cell may be formatted as Text. Select the cell, choose Home > Number Format > General, then press F2 and Enter to re-enter the formula.

How do I combine two cells without a space?

Enter =J2&K2 in an output cell, then drag the fill handle down to apply the formula to other rows.

Why does my dynamic array formula show a #SPILL! error?

Check that the cells below the formula are empty and that the formula is outside an Excel table. Clear any blocking values and try again.

Why are my combined values not updating?

In Excel, select Formulas > Calculation Options > Automatic. Also check that the formula references the intended source cells.