Select Only the Visible Cells in Excel

When we work with an Excel spreadsheet, sometimes some columns are becoming too wide to fit on the screen or maybe there are not important at that moment and make unnecessary clutter.

Example

In the following example, there is a list of a few people.

Graphical user interface, application, table, Excel

Description automatically generated

If you hide one column and select the entire table, use copy, and then paste, the hidden rows or columns will also be copied. It doesn’t matter if you use Ctrl + A, Ctrl + Shift + End, or select values by dragging.

What you need to do is to select values inside the range and only the visible cells. You can do it using different methods.

Go To Special

Let’s hide column C and drag values from A1 to D7.

A screenshot of a computer

Description automatically generated with medium confidence

Now, go to Home >> Editing >> Find & Select >> Go To Special. From the dialog box, select Visible cells only.

Graphical user interface, text, application

Description automatically generated

Click OK.

Now, if you look at the selection, the C column is not selected.

A screenshot of a computer

Description automatically generated with medium confidence

Copy cells and paste them to see that the column was indeed not selected.

A screenshot of a computer

Description automatically generated with medium confidence

Using Keyboard Shortcut

You can quickly select only visible cells using the Left Alt + ; keyboard shortcut for Windows and Cmd + Shift + Z for Mac on selected cells.

What about filtered rows?

If you are dealing with filtered rows that are not visible on the screen, you don’t have to worry about them as they are not selected by default.

A screenshot of a computer

Description automatically generated

Make a shortcut using Quick Access Toolbar

If you don’t want to remember these steps to access the dialog window or memorize the keyboard shortcut, you can create a shortcut on Quick Access Toolbar.

If your QAT is hidden, you must show it first. To do it, right-click any part of the ribbon and select Show Quick Access Toolbar.

Graphical user interface, application, Word

Description automatically generated

This is the part that should appear:

Graphical user interface, application

Description automatically generated

If you can’t see it, look at the top of the ribbon as you can change its position.

Right-click the QAT and choose Customize Quick Access Toolbar….

From Excel Options, select All Commands and then add Select Visible Cells.

Graphical user interface, text, application, chat or text message

Description automatically generated

If you click OK, a new icon on the Quick Access Toolbar appears.

A picture containing table

Description automatically generated

Now, you can select cells, click this button and all the visible cells inside the selected area will be selected.

Create a macro to select visible cells

The problem with adding a shortcut to QAT is that you must select cells first. This time we are going to create a Macro that will select only visible cells inside the whole current range with the data.

Click inside data on the spreadsheet and go to View >> Macros >> Macros >> Record Macro…. You can also create a keyboard shortcut here.

Graphical user interface, text, application, email

Description automatically generated

Press OK to start recording.

Click Ctrl + A to select the current region and then Left Alt + ; to select only visible cells.

Run the macro to select the desired range. The problem with this macro is that it works only for this specific example because the exact range is written into the code.

What we want to do, is to select the current region (the region with an active cell).

Let’s replace the two first lines on the procedure with the line selecting the current region. The full procedure looks like this:

You can also add this Macro as a shortcut to QAT.

This time instead of selecting All Commands, select Macros.

Graphical user interface, application

Description automatically generated

Macros added to a toolbar have long and not the best-looking name, also the icon is the standard Macro icon.

Graphical user interface, text, application

Description automatically generated

We can easily change that by clicking the Modify… button. Here you can change the name and icon.

Whiteboard

Description automatically generated with low confidence

Click OK and the shortcut will appear on the Quick Access Toolbar.

Click a range of data with hidden rows or columns and notice that the whole region is selected with only visible data.

Table

Description automatically generated

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.