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

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

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

In this tutorial, I will show you different scenarios where you can copy and paste multiple cells in Excel.

If you have been using Excel for some time now, I’m quite sure you would know some of these already, but there’s a good chance you’d end up learning something new.

So let’s get started!

Copy and Paste Multiple Adjacent Cells

Let’s start with the easy scenario.

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

Below are the steps to do this:

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

2. Right-click on the selection
3. Click on Copy

4. Right-click on the destination cell (E1 in this example)
5. Click on the Paste icon

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

In case 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 to only copy and paste the values, or the formatting, or the formulas, etc.

These options are available to you when you right-click on the destination cell (the icons below the paste special option).

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

Useful Keyboard Shortcuts for Copy Paste

In case you prefer using 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-paste shortcuts (using the paste special dialog box).

To use this, first copy the cells, then select the destination cell, and then 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 case you’re using Mac, use Command instead of Control.

Also read: How to Cut a Cell Value in Excel (Keyboard Shortcuts)

Mouse Shortcut for Copy Paste

If you prefer 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

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

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

Also read: Paste into Filtered Column (Skipping Hidden Cells)

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 you copy these cells, you’ll see a prompt as shown below.

This is Excel’s way of telling you that you cannot copy multiple cells that are non-adjacent.

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

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

But there are a few scenarios where you can actually copy and paste non-adjacent cells in Excel.

Let’s have a look at these.

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

While you can not copy non-adjacent cells in different rows and columns, if you have non-adjacent cells in the same row or column, Excel allows you to copy these.

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

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 in the destination cell but make these adjacents.

Similarly, you can select multiple nonadjacent cells in one column, copy them, and then paste it 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 copy them.

Now when you paste these in the destination cell, these would be pasted as adjacent rows or columns.

Below is an example where I copied multiple non-adjacent rows from the dataset and pasted 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 you want to copy the value from the cell above.

Below I have some dates in column A, and there are some blank cells as well. I want to fill these blank cells with the date in the last filed cell above them.

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 Dataset

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. Press the F5 key on your keyboard. This will open the Go To dialog box.
3. Click the Special button. This will open the Go To Special dialog box.

4. In the Go To Special dialog box, select Blanks

5. Click OK

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

Now, we want to somehow copy the value in the above field cell in these blank cells. This 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

This part is really easy.

1. With the blank cell selected, first hit the equal to key on your keyboard

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

3. Hold the Control key and press the Enter key

The above steps would enter the same formula in all the selected blank cells – which is to refer to the cell above it.

While this is a formula, the end result is that you have the blank cells filled with the above-filled date in the data set.

Once you have the desired result, you can convert the formula into values if you want (so that the formula doesn’t update the cells in case you change any value in a cell that is being referenced in the formula).

So these are a couple of methods you can use to copy and paste multiple cells (adjacent and non-adjacent cells) in Excel. I am sure using these methods will help you save tons of time in your day-to-day work.

I hope you found this tutorial useful!

Other Excel tutorials you may also like:

• How to Copy and Paste Column in Excel? 3 Easy Ways!
• How to Copy Excel Table to MS Word (4 Easy Ways)
• How to Copy Conditional Formatting to Another Cell in Excel
• How to Copy and Paste Formulas in Excel without Changing Cell References
• How to Edit Cells in Excel?