Copy Data Into Empty Cells Without Overwriting Existing Excel Values
Question details
Copy source data into a destination range only where destination cells are blank, without replacing existing values.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The source range is A1:B10 and the destination range is D1:E10. Existing values in the destination must be retained while blank cells receive corresponding source values.
- Observed behavior
- A normal paste operation replaces all values in the selected destination cells, including values that should remain unchanged.
Make sure the source and destination ranges have the same number of rows and columns. Choose an unused helper range, such as F1:G10, so the formula can compare both ranges without creating a circular reference.
Use a helper formula to keep existing destination values
Use this method when you need to fill only blank destination cells while preserving values already entered in the target range.
A formula cannot both remain in a cell and preserve that cell's prior value. Calculate the combined result in a separate helper range, then paste the completed results back as values.
Choose a blank range with the same size as the source and target ranges. For example, select cell F1 for a helper range that will cover F1:G10.
In F1, enter =IF(D1<>"",D1,A1). The formula returns the existing value in D1 when it is not blank; otherwise, it returns the source value from A1.
Drag the fill handle from F1 across to G1 and then down through row 10, or use the Home tab > Fill command to fill the helper range.
Select F1:G10 and press Ctrl+C after confirming that existing destination values are retained and blank destination positions show the source data.
Select D1:E10, then use Home > Paste > Paste Special > Values. This replaces the destination with the calculated results while retaining the original nonblank values.
Preserve Existing Values While Filling Blank Cells in WPS Spreadsheet
WPS Spreadsheet lets you use an IF formula in a helper range to combine source and destination data safely. After reviewing the results, paste them as values into the target range to fill blanks without losing existing entries.
- 1. Open the worksheet in WPS Spreadsheet: Open the Excel workbook in WPS Office and select a blank helper cell, such as F1.
- 2. Create the comparison formula: Enter =IF(D1<>"",D1,A1) in F1 to keep D1 when it contains a value or use A1 when D1 is blank.
- 3. Fill the helper range: Drag the fill handle to extend the formula across and down so it matches the source and destination range size.
- 4. Paste the final values: Copy the helper range, select the destination range, and choose Home > Paste > Paste Special > Values.
Frequently Asked Questions
Why can’t I use a formula directly in the destination cell to preserve its old value?
A formula placed in the destination cell replaces the old value in that cell. Referencing the same cell in its own formula creates a circular reference, so use a separate helper range instead.
What does =IF(D1<>"",D1,A1) do?
It checks whether D1 is nonblank. If D1 contains a value, the formula returns D1; if D1 is blank, it returns the corresponding source value from A1.
How do I make the helper formula permanent in the destination range?
Copy the helper results, select the destination range, and use Home > Paste > Paste Special > Values. This pastes fixed values rather than formulas.
Will this method work if the source and target ranges are different sizes?
The compared ranges should have matching dimensions for each source cell to align with its destination cell. Adjust the ranges or process matching sections separately if their sizes differ.
