Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Do not paste into cells that already contain data

Copper Contributor

I want to copy a range of data somewhere else in my sheet, but I do not want the values to overwrite any data that may be in any of the target cells. This is effectively treating what I have copied to the clipboard as default values, which will almost certainly be different next time I copy data.

1 Reply

@BudgieJane 

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:

  1. Source Range:
    • Assume your source range is in cells A1:B10.
  2. Target Range:
    • Assume your target range is in cells D1:E10.

Steps:

  1. Copy Values from Source Range:
    • Select the source range (A1:B10).
    • Right-click and choose "Copy" or press Ctrl + C.
  2. 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.
  3. 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.