How to Select Only Filtered Cells in Excel?

Copying filtered data onto a new sheet is a great way to segregate related data rows.

It allows us to have the extracted rows available anytime without re-applying the same filters.

We have to be careful, though — the hidden rows within the dataset might get included in the cells copied if we miss the crucial step of selecting only the visible cells.

In this tutorial, I’ll show you ways to select only the visible cells within a filtered dataset so that you can easily copy and paste them onto a different sheet.

1. Using a keyboard shortcut

1. Select all the cells within your dataset.

You can do this by first selecting one of the cells within the filtered records and pressing CTRL + A to select all.

Steps to select all cells in a filtered data set

Your entire filtered dataset should be highlighted (as shown in the image above).

2. Next, press the keyboard shortcut that selects only the visible cells.

Note that the keyboard shortcut varies for Mac and Windows. Please use the table below for reference.

Computer SystemKeyboard ShortcutNotes
WindowsALT + ;ALT key and semicolon (;).
MacCMD + SHIFT + ZCommand and shift keys and letter Z.

After pressing the appropriate keyboard shortcut, notice that the hidden rows are excluded from the selection. You will see a white demarcation line between rows that have filtered-out rows that follow them.

How the filtered cells look like after selecting only the visible cells

4. You can now copy the selected cells and paste them somewhere else.

Press CTRL + C to copy.

Copy visible cells on a filtered dataset

Notice that the broken lines appear in between rows that have hidden cells after them. These lines let you know that only the visible cells are selected and copied.

5. Open the worksheet (or workbook) where you would like to paste the filtered cells.

Select a cell and press CTRL + V to paste.

Filtered cells copied onto a new worksheet or workbook

And that’s it! You have your filtered cells copied onto a new worksheet or workbook.

2. Using the Go To Special menu

If you are not much of a fan of keyboard shortcuts and prefer doing tasks with your mouse, you can also select visible cells using the Go To Special menu.

1. Highlight all the cells within your filtered dataset. (Select one cell within the dataset and press CTRL + A to select all).

Highlight all cells within the filtered dataset

2. From the Home tab, go to Find & Select and click on Go To Special.

Steps to access the "Go To Special" menu

3. The Go To Special menu should appear.

From the list of options, select Visible cells only, then click OK.

Select the "Visible cells only" option from the Go To Special menu

4. That’s it! You now have only the filtered cells selected.

How the filtered cells look like after selecting only the visible cells

5. You can now proceed with copying it to a new worksheet or workbook.

3. Using the QAT command

If you regularly copy filtred cells, there is an even quicker method for you to select visible cells. All you need to do is add a Quick Access Toolbar (QAT) command that you can readily click whenever you need to select the visible cells.

To do this:

1. Click on the Customize Quick Access Toolbar button right above the Home tab.

This button has an inverted triangle with a thin line on top (as shown in the image below).

How to access the "Customize Quick Access Toolbar" button

2. From the list of options that appear, select More Commands.

Select "More Commands..." from the list of options under the "Customize Quick Access Toolbar"

3. The Excel Options menu will appear with the Quick Access Toolbar tab opened.

Select All Commands from the “Choose commands from” dropdown menu.

Select "All Commands" from the "Choose commands from" dropdown menu in the Quick Access Toolbar

4. Wait a bit until the list of commands under the dropdown menu gets updated and looks something like this:

How the "Customize Quick Access Toolbar" menu looks like after selecting "All Commands" in the list of commands to choose from

5. Scroll down until you find the Select Visible Cells from the list of commands.

Once you find it, select it, and click on the Add >> button.

From the list of commands, look for "Select Visible Cells". Once you find it, click on the Add>> button.

6. You should now see the Select Visible Cells command added to the list box on the right.

This list box contains all the commands added to your QAT.

How the Customize Quick Access Toolbar look like after adding the "Select Visible Cells" command

After adding the command, click OK.

7. That’s it! You now have the Select Visible Cells in your top left toolbar.

The "Select Visible Cells" button added in the Quick Access Toolbar (QAT)

Click on this button whenever you want to only select the visible cells in your worksheet.

To use it in your filtered dataset:

  1. Select all the cells in your dataset.
  2. Click on this button. Notice that Excel immediately selects the visible cells.
  3. You can now press CTRL + C to copy the visible cells and paste them onto a new sheet.


Excel’s data filter is a great way to sift through your records and only capture the set of data that meets the criteria that you have set. But please remember that when you copy a filtered data set, you must ensure that only the visible cells are selected. You can choose from any of the methods above to do this.

Leave a Comment