MS Excel VBA

Copy and Paste Multiples Cells in Excel (Adjacent & Non-Adjacent)

Copy and Paste Multiples Cells in Excel (Adjacent & Non-Adjacent)

Copying and Paste a cell or a range of cells is one of the numerous common tasks users do in Excel.

A correct understanding of how to copy-paste multiple cells (that are adjacent or non-adjacent) would help you be a lot more efficient while working with Microsoft Excel.

 

This tutorial will show you distinct scenarios where you can copy and paste multiple cells in Excel.

Let’s start with the easy technique.

Suppose you have a range of cells (that are adjacent) as shown below, and you like to copy it to some other location in the same worksheet or some different worksheet/workbook.

Dataset to copy and paste

Below are the steps to do this:

  1. Select the range of cells that you like to copy

Select the cells you want to copy

  1. Right-click on the selection
  2. Click on Copy
Right click and copy the cells
  1. Right-click on the destination cell (E & 1 in this example)
  2. Click on the Paste icon

Right click on destination cell and click on paste icon

The above actions would copy all the cells in the selected range and paste them into the destination range.

Copied data has been pasted

If you already have something in the destination range, it would be overwritten.

Excel also gives you the flexibility to choose what you want to paste. For example, you can choose only to copy and paste the values, the formatting, the formulas, etc.

When you right-click on the destination cell (the icons below the special paste option).

 

Or you can click on the Paste Special option and then choose what you want to paste using the options in the dialogue box.

Useful Keyboard Shortcuts for Copy Paste

In case you prefer utilizing the keyboard while working with Excel, you can use the below shortcut:

  • Control + C (Windows) or Command + C (Mac) – to copy range of cells
  • Control + V (Windows) or Command + V (Mac) – to paste in the destination cells

And below are some advanced copy-pasting shortcuts (using the paste special dialog box).

To use this, first copy the cells, select the destination cell and use the below keyboard shortcuts.

  • To paste only the Values – Control + E + S + V + Enter
  • To paste only the Formulas – Control + E + S + F + Enter
  • To paste only the Formatting – Control + E + S + T + Enter
  • To paste only the Column Width – Control + E + S + W + Enter
  • To paste only the Comments and notes – Control + E + S + C + Enter

In point you’re using Mac, use Command instead of Control.

Mouse Shortcut for Copy Paste

If you like using the mouse instead of the keyboard shortcuts, here is another way you can quickly copy and paste multiple cells in Excel.

  1. Select the cells that you want to copy
  2. Hold the Control key
  3. Place the mouse cursor at the edge of the selection (you will notice that the cursor changes into an arrow with a plus sign)
  4. Left-click and then drag the selection where you want the cells to be pasted

Copy and Paste using Mouse

This method is also quite fast but is only useful if you want to copy and paste the range of cells in the same worksheet somewhere nearby.

If the destination cell is a smallish far off, you’re better off using the keyboard shortcuts.

Copy and Paste Multiple Non-Adjacent Cells

Copy-pasting multiple cells that are nonadjacent is a bit tricky.

If you select multiple cells that are not adjacent to each other and copy these cells, you’ll see a prompt as shown below.

Excel doesnt allow copying non-adjacent cells

Is Excel telling you that you cannot copy multiple non-adjacent cells?

 

Unfortunately, there’s nothing that you can do about it.

There’s no hack or a workaround, and if you like to copy and paste these nonadjacent cells, you will have to do this one by one.

Copy and Paste Multiple Non-Adjacent Cells (that are in the same row/column)

You can copy cells in the same row (even if these are non-adjacent). Select the cells and then use Control + C (or Command + C for Mac). You will see the outline (the dancing ants outline).

Excel allows copying non-adjacent cells in the same row

Once you have copied these cells, go to the destination cell and paste these (Control + V or Command + V)

Excel will paste all the copied cells into the destination cell but make these adjacents.

Similarly, you can select multiple nonadjacent cells in one column, copy them, and paste them into the destination cells.

Copy and Paste Multiple Non-Adjacent Rows/Columns (but adjacent cells)

Another thing Excel allows is to select non-adjacent rows or non-adjacent columns and then duplicate them.

Excel allows copying non-adjacent rows

When you paste these into the destination cell, these will be pasted as adjacent rows or columns.

Non adjacent rows copied and pasted

Below is an example where I copied multiple non-adjacent rows from the datasets and Pasting these in a different location.

Copy Value From Above in Non-Adjacent Cells

One practical scenario where you may have to copy and paste multiple cells would be when you have gaps in a data set and want to copy the value from the cell above.

Below I have some dates in column A, and some blank cells exist. I want to serve these blank cells with the date in the last filed cell above them.

Data with blank cells in it

To do this, I would need to do two things:

  • Select all the blank cells
  • Copy the date from the above-filled cell and paste it into these blank cells

Let me show you how to do this.

Select All Blank Cells in the Datasets

Below are the steps to select all the blank cells in column A:

  1. Select the dates in column A, including the blank ones that you want to fill
  2. Select the column that has empty cells
  1. Press the F5 key on your keyboard. This will open the Go To dialog box.
  2. Click the Special button. This will open the Go To Special dialog box.

Click the Special button

  1. In the Go To Special dialog box, select Blanks
Select Blanks in the Go To Special dialog box

The above steps would select all the blank cells in column A.

Blank Cells are selected
Now, we want to somehow copy the value in the above field cell in these blank cells. It cannot be done using any copy-paste method, so we will have to use a formula (a very simple one).

Fill Blank Cells with Value Above

Enter equal to sign in the active cell

  1. Now hit the Up arrow key. will automatically enter the cell reference of the cell that is above the active cell.
  2. Refer to the cell above the active cell

Hold the Control key and press the Enter key.

Value from cell above is copied to the blank cells

Leave a Comment

Your email address will not be published. Required fields are marked *


Scroll to Top