Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

Quickly copy data and paste into only visible cells / filtered list in Excel

In Excel, when going to copy and paste some data into a range which has been filtered or contains hidden rows / columns, it will paste to those filtered or hidden cells too. Can we just paste data into only visible cells in Excel? Kutools for Excel’s Paste to Visible Range will help you get it done quickly.

Copy data and paste into filtered list only

Copy data and paste into visible cells only when there are hidden rows

Paste To Visible Range Demo


Click Kutools > Range > Paste to Visible Range > All / Only Paste Values. See screenshot:


Copy data and paste into filtered list only

If a range with enabling filtering feature as shown in following screenshot, and you need to copy some data and paste it into a range with enabling filtering feature but only the visible cells, you can solve this task as follows:

shot-paste-to-visible-cells-2

1. Select the data range that you want to paste to the filtered list.

Pasting all values with cell formatting into visible cells:

2. If you want to paste all values with cell formatting into visible cells, please click Kutools > Range > Paste to Visible Range > All. See screenshot:

Pasting only values into visible cells:

For pasting only values of selected cells into visible cells, please click Kutools > Range > Paste to Visible Range > Only Paste Values. See screenshot:

3. In the Paste to Visible Range prompt box, select a cell or a range that you want to paste the selected data with all cell formatting, and then click the OK button. See screenshot:

Then you can see the selected data has been pasted into the filtered rows only. See screenshots:

Paste all with cell formatting into visible cells:

Paste only values into visible cells:


Copy data and paste into visible cells only when there are hidden rows

Supposing, there are multiple hidden rows in your worksheet, to paste some data into the visible rows only, you can also use this Paste to Visible Range utility.

shot-paste-to-visible-cells-6

1. Select the data range that you want to paste into the visible rows only.

Pasting all values with cell formatting into visible rows:

2. If you want to paste all values with cell formatting into visible rows, please apply this utility by clicking Kutools > Range > Paste to Visible Range > All. See screenshot:

Pasting only values into visible rows:

For pasting only values of selected cells into visible rows, please click Kutools > Range > Paste to Visible Range > Only Paste Values. See screenshot:

3. In the Paste to Visible Range prompt box, select a cell or a range that you want to paste the selected data, and then click the OK button. See screenshot

shot paste to visible cells 4

Then you can see the selected data has been pasted into the visible rows only as below screenshots shown:

Paste all with cell formatting into visible rows:

Paste only values into visible rows:


Notes:

1. This utility supports undo (Ctrl + Z).

2. If there are both filtered rows and hidden rows in your worksheet, this feature also can help you to paste the selected data into the visible cells only successfully.


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2019, 2016, 2013, 2010, 2007 and Office 365.
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013, 2016, 2019 and 365, as if it were Office 2000 and 2003.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.