Forum Discussion
Do not paste into cells that already contain data
To achieve the behavior where you copy a range of data to a target area in Excel but do not overwrite existing data in the target cells, you can use a combination of formulas and conditional logic. Here's a step-by-step guide:
Example Setup:
- Source Range:
- Assume your source range is in cells A1:B10.
- Target Range:
- Assume your target range is in cells D1:E10.
Steps:
- Copy Values from Source Range:
- Select the source range (A1:B10).
- Right-click and choose "Copy" or press Ctrl + C.
- Paste Special (Values Only) in Target Range:
- Select the first cell in the target range (D1).
- Right-click and choose "Paste Special."
- Choose "Values" from the options.
- Conditional Logic to Avoid Overwriting:
- In the target range, you can use a formula to conditionally paste values only if the target cell is empty.
- For example, in cell D1, you can use the following formula:
=IF(D1<>"", D1, A1)
- Drag the formula down and across for the entire target range (D1:E10).
Explanation:
- The IF function checks if the target cell (e.g., D1) is not empty (<>"").
- If the target cell is not empty, it keeps the existing value in the target cell.
- If the target cell is empty, it pastes the corresponding value from the source range (A1). The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Hi - I came across this whilst looking for a solution. I don't see how you can perform step 3 in your solution after selecting 'Values' in step 2. The dialog box is open and you have to paste. At what stage do you write the formula you provided?
In retrospect, I'm also a bit confused by the description of your project. Could you please explain it in more detail?
Step by step, what you're planning.
A file or photos might be helpful.
Thank you for your patience and understanding.
Some rows in my worksheet are partially filled with data. What I would like to do is complete one of those partially-filled rows of data by copying another row into the clipboard but only paste the copied values into the cells that do not contain any existing data, or which contain nulls.
It is similar to this pseudo-code
for cell in current row if value(cell) == null then value(cell) = clipboard(cell) endif endfor
