How to Copy Rows in Excel (7 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Copying and pasting are two of the most frequent tasks that we do while working with a dataset in Excel. To better deal with the dataset and access flexibilities, we must know how to do them accordingly. Understanding this fact, in this article, we’ll demonstrate 7 easy and quick methods on how to copy rows in Excel. So, go through the entire article to understand the topic properly.

How to Copy Rows in Excel: 7 Ways

For ease of understanding, we are going to use an Employee List. This dataset includes the ID, Name, and Department in columns B, C, and D respectively.

how to copy rows in excel

Now, we’ll use this dataset to show you 7 super easy and cool methods to copy rows in Excel. All of them are super handy to use. You may pick up any of them for your task. So, let’s explore them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Using Copy Command from Ribbon

If you want to avoid keyboard shortcuts and feel comfortable working with your mouse, this method is appropriate for you. It’s just a matter of a single mouse click, and you’re ready to go. Here’s how to do it; just follow along.

📌 Steps:

Let’s say we want to copy the cells in Row 7 which is the row with ID 669.

  • At the very beginning, select cells in the B7:D7 range.
  • After that, navigate to the Home tab.
  • Then, click on the Copy icon in the Clipboard group of commands.

Using Copy Command from Ribbon

Now, your selected row has been copied to the clipboard, and you are ready to paste it anywhere you want. Assume that you want to paste it in cell B14.

  • So, go to cell B14 and press CTRL+V on your keyboard to paste it.

Immediately, the row is visible in the desired location.

Using Copy Command from Ribbon to copy rows in Excel

2. Utilizing Context Menu

You can use this method instead of the first method that we have stated above. You can just right-click on the selection area and then select the copy command from the pop-up window. Here’s the step-by-step procedure for your better understanding:

📌 Steps:

  • First of all, select cells B7:D7.
  • Secondly, right-click on the mouse keeping the cursor anywhere inside the selection.
  • From the context menu, click on Copy.

Utilizing Context Menu

  • Again, select cell B14 and press CTRL+V to paste them.

Utilizing Context Menu to copy rows in Excel

3. Applying Drag and Drop Method

This method is super useful when you want to paste your data into a different location that you might want to select randomly. You can literally copy a row and then hold on to the CTRL key and drag your data anywhere you like in Excel. Follow these simple steps below.

📌 Steps:

  • At first, select the row.
  • Then, move the pointer to the border of the selection area. So that the mouse pointer becomes a move pointer.
  • After that, press the CTRL button and drag the selection area to a new location at the same time. Later, release the CTRL button.

Applying Drag and Drop Method

Finally, it looks like this.

Applying Drag and Drop Method to copy rows in Excel

4. Adopting Keyboard Shortcut

For those who were actually looking for a keyboard shortcut to copy rows in Excel, here you go, guys. This method will allow you to work swiftly in Excel without causing any issues. Here are the steps to follow:

📌 Steps:

  • Firstly, select Row 7 (B7:D7).
  • Secondly, press the CTRL key along with the C key on your keyboard.

Adopting Keyboard Shortcut

  • Thirdly, go to cell B14 and press CTRL+V to paste it.

Magically, it gets copied, and now it is in the new location.

Adopting Keyboard Shortcut to copy rows in excel

5. Copying Without Hidden Rows

Consider that there are some hidden rows in the dataset. Look at the image below.

Copying Without Hidden Rows

There are 3 rows hidden between Row 6 and Row 10. In this case, if you copy the range and paste it into cell B14 using Method 1, the output will be like the following.

The hidden rows get copied as well. So, how can we copy just the visible rows, avoiding the hidden rows? Follow the steps below.

📌 Steps:

  • Firstly, select cells in the B4:D12 range.
  • Then, proceed to the Home tab.
  • After that, click on the Find & Select drop-down icon on the Editing group.
  • From the drop-down list, select the Go to Special… option.

Go to Special feature in Excel

Instantly, the dialog box for the Go To Special command appears before us.

  • Here, select Visible cells only.
  • As always, click OK.

working on go to special dialog box to copy rows in Excel

  • Now, follow the previous steps to copy and paste them into a preferred location.

6. Copying Every Other Row

Sometimes, we need to remove each row after each relevant row. They could be duplicates or irrelevant. So, here, we’ll copy each row after another row. Let’s see it in action.

📌 Steps:

  • Initially, select cell B14 and enter the following formula.
  • Then, press ENTER.

Copying Every Other Row

  • Then, drag the Fill Handle horizontally to cell D14.

using fill handle tool to copy rows in Excel

  • After that, select cells in the B14:D15 range and drag the Fill Handle up to cell D22.

Consequently, it will look like the following image.

It comes without any formatting, just the values.

  • Again, select the new data range.
  • Following this, advance to the Home tab.
  • Later, click on the Sort & Filter drop-down.
  • Next, choose Filter from the list.

Applying Filter to copy rows after every row in Excel

Instantly, it’ll apply filters in this range.

  • Now, click on the Filter Button beside the heading ID.
  • Afterward, uncheck the box of Blanks and click OK.

The blank rows get removed.

After a bit of formatting, the copied range looks like the following one.

Copying Every Other Rows in Excel

7. Copying Rows with Formula

Already, we have seen several ways to copy rows in Excel. Besides these, we can apply formulas to copy rows in Excel. So, without further delay, let’s dive in!

📌 Steps:

  • Primarily, go to cell B15 and insert the formula below.

Here, the VLOOKUP function returns the value of the same row from the specified column of the given table, where the value in the leftmost column matches the lookup_value.

  • Secondarily, press ENTER.

Copying Rows with Formula in Excel

How to Copy Multiple Rows in Excel

Actually, it’s nothing new, similar to Method 1. Just follow the steps carefully.

📌 Steps:

  • At first, select any row. Then, hold the CTRL key and select as many rows as you want.
  • After that, release the CTRL key when you are done selecting the rows.

How to Copy Multiple Rows in Excel

  • Following this, repeat the steps of Method 1 to copy and paste them.

How to Copy Multiple Rows to Another Sheet in Excel

Moreover, we can copy rows and paste them into another sheet in Excel. So, no need to wait. Let’s look at this method fast.

📌 Steps:

Now, we are on the Another Sheet worksheet, and our dataset is also here. But we want to copy its rows and paste them into the sheet Another Sheet Copy which is just to the right of this sheet. See the image below for a better understanding.

  • Firstly, go to the Developer tab.
  • Then, click on the Visual Basic icon.

How to Copy Multiple Rows in Excel to Another Sheet

Suddenly, the Microsoft Visual Basic for Applications window will open up.

  • Secondly, jump to the Insert tab.
  • Then, click on Module from the options.

Inserting module to copy rows in Excel

Automatically, Excel will insert a code module.

  • At this time, paste the following VBA code into the module.
Sub Multiple_Rows_Another_Sheet()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Sheets("Another Sheet").UsedRange
Set rng2 = ThisWorkbook.Sheets("Another Sheet Copy").Range(rng1.Address)
rng2.Value = rng1.Value
End Sub

VBA code to copy rows in Excel

  • After that, click on the Run button.

And the final result is on another sheet. But it will come without formatting, you have to apply formatting manually.

Applying VBA Code to Copy Multiple Rows in Excel to Another Sheet

Things to Remember

  • Make sure you always select the row first.
  • CTRL+C is the copy hotkey.

Download the Practice Workbook

You may download the following Excel workbook for better understanding and practice.


Now, hopefully, you’re able to copy rows in Excel in a simple and concise manner. Don’t forget to download the practice file. Thank you for reading this article. Please let us know in the comment section if you have any queries or suggestions.

How to Copy Rows in Excel: Knowledge Hub

<< Go Back to Copy Paste in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF