Click OK (or just hit ENTER key as OK is already selected).

Click Visible Cells Only to select only the visible cells in the selected range.

Click the SpecialРІР‚В¦ button at the bottom of the dialog box.

Press F5 function key to display Go To dialog box.

(Excel is actually selecting the hidden rows as well but this will get taken care of in the next steps). 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: YouРІР‚в„ўve used the SUBTOTAL function to sum only filtered data and now want to copy and paste to another location. Now all you do is select the range, click the Select Visible Cells icon on the QAT, Copy and Paste РІР‚вЂњ One and done!

If desired, use the arrow boxes to change the iconРІР‚в„ўs position on the toolbar.

Click Add and then click OK to add to end of the QAT.

Scroll down and click Select Visible Cells.

From the Choose Commands From dropdown, choose All Commands.

Right click anywhere in the Ribbon and choose Customize Quick Access ToolbarРІР‚В¦ OR click the QAT dropdown arrow, and choose More Commands.Make this great solution even easier and faster by utilizing the Quick Access Toolbar (QAT): 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.Р”Р„dd the Icon for Select Visible Cells to the Quick Access Toolbar Click the upper-left cell of the desired paste area and click PasteР’В (or press CTRL+V).Click Copy in Clipboard group on Home tabР’В (or press CTRL+C).Click Visible cells onlyР’В radio button and click OK.Click SpecialРІР‚В¦ button in the dialog box.Click HomeР’В tab, Find & Select in the Editing group and choose Go To SpecialРІР‚В¦.Select the cell range that you want to copy.If you want only visible rows, hereРІР‚в„ўs the steps:

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!Р”В«y default, Excel copies hidden or filtered cells in addition to visible cells. 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. Copy and Paste Visible Cells Only (filtered data)

Excel copy on visible cells