Busy? READ LATER! DOWNLOAD THIS Mail As PDF >> CLICK HERE <<

Copy and Paste Visible Cells Only (filtered data)

Y'all have used one of several methods to hide some rows for filtered information, or created a table which automobile applies filter icons for each column. At present y'all want to re-create and paste just the visible information but discovered to your horror when you pasted to another location, information technology included the subconscious rows!

Excel, Paste only Filtered Data

By default, Excel copies hidden or filtered cells in improver to visible cells. If you want only visible rows, hither's the steps:

  • Select the cell range that you desire to copy.
  • ClickHome tab, Detect & Select in the Editing group and cullGo To Special…
  • Click Special… button in the dialog box.
  • ClickVisible cells simply radio push and clickOK.

Excel, Copy and Paste Filtered Cells Only

  • ClickCopy in Clipboard grouping on Dwelling tab (or press CTRL+C).
  • Click the upper-left cell of the desired paste area and clickPaste (or press CTRL+V).

You lot have now achieved Nirvana!

Tip: You can also use the F5 Function key at Footstep two to bring upward the Go To… dialog box and click the Special… button to go the same results.

Add the Icon for Select Visible Cells to the Quick Admission Toolbar

Brand this great solution even easier and faster by utilizing the Quick Access Toolbar (QAT):

  • Right click anywhere in the Ribbon and cull Customize Quick Access Toolbar… OR click the QAT dropdown arrow, and cull More Commands.
  • From the Cull Commands From dropdown, choose All Commands .
  • Scroll downward and click Select Visible Cells.
  • Click Add together and then click OK to add to terminate of the QAT.
  • If desired, use the pointer boxes to change the icon's position on the toolbar.

At present all y'all practice is select the range, click the Select Visible Cells icon on the QAT, Re-create and Paste – One and done!

Copy and Paste Only Subtotaled Rows

Y'all've used the SUBTOTAL function to sum just filtered information and at present want to re-create and paste to another location. You assume the paste will include the visible subtotaled rows only – Surprise – non! You still need to apply the Get To dialog box to accomplish this but if this is something yous exercise often, apply shortcuts:

  • Select the range you want to copy. (Excel is actually selecting the hidden rows besides but this will get taken intendance of in the next steps).
  • Press F5 function primal to display Go To dialog box.

Excel, Copy and Paste only Subtotaled, filtered Rows with Special...

  • Click the Special… button at the bottom of the dialog box.
  • Click Visible Cells But to select merely the visible cells in the selected range.

  • Click OK (or just hit ENTER key as OK is already selected).
  • Press CTRL C to re-create the selected visible cells to the Clipboard.
  • Select a destination jail cell (tin be on the same sail, a different sheet, or on a new workbook).
  • Paste the range by pressing CTRL V. Excel copies just the subtotaled rows.

Now yous can copy and paste only those cells or ranges You lot desire.

Hope these tips have removed a picayune head-scratching from one of those features that nosotros desire to utilize on a regular footing merely doesn't always carry the way we expect!

BUSY? READ LATER! DOWNLOAD THIS POST AS PDF >> CLICK HERE <<