Mrinmoy Roy
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.
Download the Practice Workbook
You may download the following Excel workbook for better understanding and practice.
7 Ways to Copy Rows in Excel
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.
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.
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.
Read more: How to Copy and Paste Thousands of Rows in Excel (3 Ways)‌
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.
- Again, select cell B14 and press CTRL+V to paste them.
Similar Readings
- Paste Options in Excel with Shortcuts: A Complete Guide
- How to Copy a Cell in Excel Using Formula(7 Methods)
- Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- How to Copy the Same Value in Multiple Cells in Excel (4 Methods)
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.
Finally, it looks like this.
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.
- Thirdly, go to cell B14 and press CTRL+V to paste it.
Magically, it gets copied, and now it is in the new location.
5. Copying Without Hidden Rows
Consider that there are some hidden rows in the dataset. Look at the image below.
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.
Instantly, the dialog box for the Go To Special command appears before us.
- Here, select Visible cells only.
- As always, click OK.
- 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.
=B4- Then, press ENTER.
- Then, drag the Fill Handle horizontally to cell D14.
- 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.
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.
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.
=VLOOKUP(B5:B12,B$5:$D$12,1,FALSE)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.
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.
- 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.
Suddenly, the Microsoft Visual Basic for Applications window will open up.
- Secondly, jump to the Insert tab.
- Then, click on Module from the options.
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
- 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.
Things to Remember
- Make sure you always select the row first.
- CTRL+C is the copy hotkey.
Conclusion
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. Please visit our website, ExcelDemy, a one-stop Excel solution provider, to explore more.
