How to Select Visible Cells in Excel (5 Quick Tricks)

An intended portion of cells is naturally hidden when we hide rows and columns in Excel or filter our dataset. Now, if you want to select the visible cells only, you will face 2 situations.

For filtered-out cells, you can just easily select visible cells by dragging the mouse cursor horizontally and vertically. But in the case of hidden rows and columns or both, you cannot select the visible cells so easily.

In this tutorial, I am going to show you how to select visible cells in Excel using 5 quick tricks. These methods are very easy and you can apply them to any Excel worksheet.

Download Practice Workbook

You can download the practice workbook from here.

How to Select Visible Cells in Excel (5 Quick Tricks)

We have taken a concise dataset here to explain the steps clearly. This dataset has 6 rows and 2 columns. We will apply the following methods to select only the data cells that are visible currently.

1. Select Visible Cells Using Keyboard Shortcuts

This method is the quickest to select visible cells in Excel. In the following steps, we will see how to use the Alt key to select visible cells in Excel.

Steps:

• First, drag and select the dataset.
• Then, press Alt+; keys together.
• As a result, this will select only the visible cells which you can now copy easily.

Read More: How to Select Cells in Excel Using Keyboard (9 Ways)

2. Use Go To Special Feature to Select Visible Cells

The Go To Special feature in Excel can quickly find and select a specific type of cells within the worksheet. We will use this feature and see how to select visible cells in Excel worksheet.

Steps:

• First, go to the Editing section under the Home tab, and there click on Find & Select.
• Next, select Go To Special.

• Then, in the Go To Special window, select Visible cells only.
• Finally, press OK and this will exclude any hidden cell from the selection.

Read More: How to Go to Last Non Empty Cell in Column in Excel

3. Use “Select Visible Cells” Command from Quick Access Toolbar

If we use a certain command frequently, then we can add it to the Quick Access Toolbar. By default, this toolbar contains only 4 commands: Autosave, Save, Undo, and Redo. We can add the Select Visible Cells option to this toolbar. Let us see how to do that.

Steps:

• To begin with, go to Excel Options from the File tab in the top-left corner.

• Under Excel Options, click on Quick Access Toolbar and select All Commands.
• Then, click on Select Visible Cells and select Add.
• As a result, this will add the Select Visible Cells option to the Quick Access Toolbar.
• Now, press OK.

• Next, select your dataset and click on Select Visible Cells from the Quick Access Toolbar.
• Consequently, this will only select the visible cells from your selection.

Read More: How to Select Only Filtered Cells in Excel Formula (5 Quick Ways)

Similar Readings

• How to Select Data in Excel for Graph (5 Quick Ways)
• How Do I Quickly Select Thousands of Rows in Excel (2 Ways)
• [Fixed!] Selected Cells Not Highlighted in Excel (8 Solutions)
• How to Go to the End of Excel Sheet (2 Quick Methods)
• How to Select & Delete Blank Cells in Excel (3 Quick Ways)

4. Using Shortcut F5 Key

We can access the Go To Special feature using a shortcut and then select the visible cells in Excel. Follow the steps below to do that.

Steps:

• First of all, select the dataset as previously and press the F5 key.
• Immediately, this will bring up the Go to the window in Excel.
• Here, click on Special.

• Next, in the Go To Special window, select Visible cells only and press OK.
• As a result, you can now select and copy only the visible cells.

Read More: Select All Cells with Data in a Column in Excel (5 Methods+Shortcuts)

5. Applying a VBA Code to Select Visible Cells

We can also apply VBA code in Excel to select visible cells by specifying a cell range to select. Follow the steps below to do this.

Steps:

• First, go to the Developer tab and click on Visual Basic.

• Next, in the VBA window, click on Insert and then select Module.

• Now, in the new Module1 window, type in the following code:

Sub select_visible_cells()
    Range("B4:C9").Select
    Range("B5").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
End Sub 

• After that, close the VBA window and navigate to the Developer tab again.
• Here, click on Macros.

• Immediately, this will open up the Macro window where all the macros are available to use.
• Then, select the macro we just created and click on Run.
• As a result, this macro will select all the visible cells in your specified range.

Read More: Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)

Things to Remember

• If you are a mac user, you can use the keys Cmd+Shift+Z instead of Alt+;.
• Make sure to spell the VBA in build functions properly otherwise it will not work.
• If any cells are hidden due to filtering, then you don’t have to use these methods as Excel will exclude them by default.

Conclusion

I hope that you were able to apply the above methods and know how to select visible cells in Excel. Although this task can be achieved in multiple ways, you should choose the one that best suits your need considering your dataset and complexity. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.

Related Articles

• How to Select Multiple Cells in Excel (7 Quick Ways)
• How to Select Row in Excel If Cell Contains Specific Data (4 Ways)
• [Solved!] CTRL+END Shortcut Key Goes Too Far in Excel (6 Fixes)
• How to Select a Range of Cells in Excel (9 Methods)
• Select a Range of Cells in Excel Formula (4 Methods)
• How to Select Multiple Cells in Excel without Mouse (9 Easy Methods)