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.

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.

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.

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

Press CTRL + C to copy.

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.

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).

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

3. The Go To Special menu should appear.

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

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

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).

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

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

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

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

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.

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.

After adding the command, click OK.

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

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.

Conclusion

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.