Copy and Paste Visible Cells Only (filtered data)

You have used one of several methods to hide some rows for filtered data, or created a table which auto applies filter icons for each column. Now you want to copy and paste just the visible data but discovered to your horror when you pasted to another location, it included the hidden rows!

By default, Excel copies hidden or filtered cells in addition to visible cells. If you want only visible rows, here’s the steps:

• Select the cell range that you want to copy.
  
• Click Home tab, Find & Select in the Editing group and choose Go To Special…
  
• Click Special… button in the dialog box.
  
• Click Visible cells only radio button and click OK.
  

• Click Copy in Clipboard group on Home tab (or press CTRL+C).
  
• Click the upper-left cell of the desired paste area and click Paste (or press CTRL+V).
  

You have now achieved Nirvana!

Tip: You can also use the F5 Function key at Step 2 to bring up the Go To… dialog box and click the Special… button to get the same results.

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

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

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

Now all you do is select the range, click the Select Visible Cells icon on the QAT, Copy and Paste – One and done!

Copy and Paste Only Subtotaled Rows

You’ve used the SUBTOTAL function to sum only filtered data and now want to copy and paste to another location. You assume the paste will include the visible subtotaled rows only – Surprise – not! You still need to use the Go To dialog box to accomplish this but if this is something you do often, apply shortcuts:

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

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

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

Now you can copy and paste only those cells or ranges YOU want.

Hope these tips have removed a little head-scratching from one of those features that we want to use on a regular basis but doesn’t always behave the way we expect!