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

In this tutorial, I am going to show you 5 quick tricks to select only the visible cells in excel. These methods are very easy and you can apply them to any excel worksheet. In the course of this tutorial, we will learn some important features and shortcuts in excel that might come in handy in other excel tasks.


Download Practice Workbook

You can download the practice workbook from here.


5 Quick Tricks to Select Visible Cells in Excel

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.

how to select visible cells in excel


1. Using ALT Key on Keyboard

This method is probably 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.

Alt to key to select visible cells in excel

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


2. Utilizing Go To Special Feature

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 to select visible cells in our worksheet.

Steps:

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

Go To Special to select visible cells in excel

  • 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. Select Visible Cells by QAT Command

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.

opening QAT to select visible cells in excel

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

adding Select Visible cells command to select visible cells in excel

  • 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


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.

Using F5 key to select visible cells in excel

  • 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 VBA Code

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.

opening VBA window to select visible cells in excel

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

inserting module to select visible cells in excel

  • 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 inserting VBA code to select visible cells in excel

 

  • 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 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

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo