We all know how to copy and paste columns and rows in Excel by dragging and dropping. But what if you have thousands of rows in your worksheet which you want to copy and paste into another section in Excel?
To select thousands of rows while keeping on dragging the data is a really tiring, time-consuming and naive way to do it right? In Excel, there are some tricks to achieve that in a lot easier ways.
This article will show you how to copy and paste thousands of rows in Excel in three the easiest ways.
Download Practice Workbook
You can download the practice workbook from here,
3 Easy Ways to Copy and Paste Thousands of Rows in Excel
Here you will learn how to copy and paste thousands of data just by utilizing the Address Bar, and how to copy and paste rows while keep the formula without changing the cell references and copy and paste rows with the same width.
Below are three of the most effective and simple methods to copy and paste thousands of rows in Excel,
1. Copy and Paste Thousands of Rows from Address Bar
Copy and paste rows from the address bar is the easiest and the most convenient method to relocate thousands of rows. Instead of dragging thousands of rows manually, all you have to do is just simply give the address bar some address of your data and it will automatically copy all those.
The steps of copy and pasting thousands of rows from the address bar in Excel are as described below,
i. Select the first cell of the first row that you want to shift.
ii. Then go to the Address Bar (shown below in the picture).
iii. In the address bar, after the selected cell reference number, put a colon (:) and then write the reference number of the last cell of the rows.
Here in the picture, the reference number of my first cell is B4 and the last cell is D1004. So in the address bar, we write the range as B4:D1004.
iv. Then hit Enter. It will select the entire set of rows.
Now Copy the selected set of rows by right-clicking the mouse or pressing Ctrl+C on your keyboard.
vi. Now select another empty cell that you want as the first cell of your new copied set of rows.
vii. Paste the set of rows by right-clicking the mouse or pressing Ctrl+V on your keyboard.
This will copy and paste all of your rows on the worksheet in Excel.
Read more: How to Copy Rows in Excel (4 Easy Ways)
2. Copy and Paste Thousands of Rows while Keeping the Formula without Changing Cell References
What if you want to copy and paste a row while keeping the formula inside? You can’t do that in Excel straightway because Excel automatically updates all the referenced cells relative to where they are being pasted. So to do that, you need to do some tricks.
Let’s find out how to do that.
i. Select the row that shows the result of your data, meaning the row that has some calculations and formulas in it.
ii. Now go to the menu bar, from there select Find & Select and select Replace.
iii. In the Find and Replace pop-up box, write the equal (=) sign in the Find what label box and fill the Replace with label box with a hash (#) sign. Then click Replace All.
This will convert all formulas to text with the hash (#) sign at the front.
iv. Now select this row and paste it into another empty cell that you want.
v. Now select both of the original and the pasted rows by holding the Ctrl key of your keyboard and again select Replace option from the Find & Select menu.
vi. In the Find and Replace pop-up box, write the hash (#) sign in the Find what label box and fill the Replace with label box with an equal (=) sign. Then click Replace All.
Once these steps are done, both rows will contain the same formulas without shifting references.
- Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- How to Copy and Paste in Excel Without Changing the Format
- Copy the Same Value in Multiple Cells in Excel (4 Methods)
- How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)
3. Copy and Paste Thousands of Rows while Keeping the Same Width
You have a large Excel worksheet with lots of rows, you modified the width of all the columns so that the data of your rows will be more visible and easier to work with. Now you need to copy and paste those rows to another sheet. After doing so, you notice that all of your previous works have gone in vain because now the width of the columns is not the same as you modified in the original worksheet. Such a disaster right?
Okay, let’s see how we can copy and paste rows while keeping the same width.
i. First, select and copy the range of rows that you want to paste with the same width as the original.
ii. In another worksheet or another place of the same worksheet, select an empty cell to be the first landing spot of your set of data.
iii. Right-click the mouse, from the appeared menu list, select Paste Special.
iv. A pop-up box will appear. Select the Columns widths option from the box. Next press OK.
This will automatically adjust the new column’s widths to match the original widths as well.
v. Again Paste the selected data by right-clicking the mouse or pressing Ctrl+V on your keyboard.
It will paste your new set of rows exactly with the width of the original set of rows.
Handling a lot of data is an obligatory part of our daily life, no matter whether it is in our academic life or in our work-life. So it is really common to relocate some of the data from here to there. This article was made by keeping all of those in mind so that your day-to-day activities would become less stressful. In this article you have learned how to copy thousands of rows in the simplest way possible and also you have learned how to do that while keeping the formula with unchanged cell references and unchanged width. Hope this article has been very beneficial to you.