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

When working with large data sets, there may be hidden cells that are not displayed on the screen, and selecting these cells along with the visible ones can lead to inaccurate analysis. In this context, learning how to select visible cells in Excel can be very helpful.
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.

how to select visible cells in excel

How to Select Visible Cells in Excel: 5 Quick Tricks

For ease of understanding, we are taking a “Sales Report of ABC Technology”. This dataset includes the Product ID, Product Name, Quantity, Unit price, and Sales amount under columns B, C, D, E, and F respectively. You can see that there are some rows hidden in this dataset e.g. rows 6, 9, 10, 12.

sales report of ABC technology with hidden rows

After unhiding the sheet, the entire dataset is visible to us. We can see that our dataset has a total of 10 rows without the header row.

sales report of ABC technology

Note: This is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

Limitations of Normal Copy-Pasting for Visible Cells in Excel

Before we start discussing the methods to select visible cells in Excel, we’ll see the limitation of normal selection for visible cells. See the steps below to understand the phenomena.

  • Select the B4:F14 range and press CTRL + C on your keyboard to copy the selected range. From this image, you obviously got an idea that Excel is copying 7 rows here.

copying visible cells in Excel

  • Then, select cell B18 where you want to paste the range, and press CTRL + V.

pasting visible cells in Excel

  • But, the result is the opposite. All the hidden rows get pasted also.

all cells including hidden cells get pasted

So, we realized that although it appears partially selected, the entire dataset was selected.
Now, we’ll utilize the above dataset to select visible cells in Excel using multiple methods. So, let’s explore them one by one.
Not to mention, here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience. Please leave a comment if any part of this article does not work in your version.

1. Use Keyboard Shortcut to Select Visible Cells Quickly in Excel

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.

  • First, drag and select the entire dataset (e.g. B4:F14).
  • Then, press the ALT +; keys together.

selecting visible cells using semicolon key with ALT key

  • You can see the range is selected. To prove that only the visible part is selected, we will copy the selected visible range. Just press CTRL + C on your keyboard.

pressing CTRL+C to to copy selected cells

  • See, we have copied the selected portion.
  • Now, we have to paste this copied data anywhere in the sheet to see the result. We selected cell B18 and pressed CTRL + V to paste the data.

pasting visible selected cells using CTRL+V

Finally, it’s pasted in the preferred location.

verification of select visible cells in excel using paste command

You can notice that only the visible cells got pasted in the sheet. Hidden rows are not present in the pasted range.

2. Select Visible Cells Using 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 and see how to select visible cells in the Excel worksheet.

  • First, select the dataset (B4:F14).
  • Then, navigate to the Home tab. Under the Editing group of commands, click the Find & Select drop-down icon. From the drop-down list, select the Go To Special option.

clicking on Go To Special command

  • After that, in the Go To Special dialog box, select Visible cells only and press OK. This will exclude any hidden cell from the selection.

working on Go To Special dialog box

  • All the visible cells get selected here.

visible cells selected in Excel

  • Like the previous method, you can use the Copy-Paste feature to verify the selection of visible cells only.

visible selected cells are pasted in sheet

Note: You can press the F5 key to replicate the process in short. Also, the CTRL + G keyboard shortcut can do the same work. You may practice those on your own.

3. Make Use of “Select Visible Cells” Command from the 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.

  • To begin with, click on the down arrow icon which resembles the Customize Quick Access Toolbar option.
  • From the options, select More Commands.

customizing quick access toolbar in Excel

  • Now, select All Commands under the section Choose commands from. Find the Select Visible Cells command and click on Add>>.

adding select visible cells command on QAT

  • After clicking on the Add button, the command is visible on the right side box. That means it’s now visible on the QAT. Click OK.

select visible cells command added in QAT of Excel

  • Clearly, you can see a square-shaped icon that wasn’t present before. It’s the icon to Select Visible Cells.

Select visible cells icon available on quick access toolbar

  • Now, highlight the entire dataset and click on this icon on the Quick Access Toolbar.

clicking on select visible cells icon on QAT

  • Simply, the visible cells in your dataset get selected.

visible cells are selected only

  • By employing the copy-paste feature, it is possible to authenticate the selection of visible cells only.

pasting selected visible cells in excel

4. Use Filter Feature to Select Visible Cells

We can filter our data by applying Filter to the dataset. In this process, some rows of data got hidden. In this method, we’ll show how we can select only the visible rows containing data.

  • Firstly, select any cell inside the dataset. In this case, we selected cell B4.
  • Then, click the Home tab >> Sort & Filter drop-down icon >> Filter.

applying filter option to dataset

  • Instantly, it’ll add a down arrowhead to all the column headers.
  • Click on the arrowhead beside the heading Product Name. Unselect some items that will be invisible and click OK.

selecting product name to be visible

  • Now, only the selected products are visible in the worksheet.

using filter feature to hide some rows

  • Select the whole dataset and press CTRL + SHIFT + * on your keyboard. It’ll select only the visible cells.

pressing CTRL+SHIFT and asterisk button to select visible cells in Excel

  • Now use the copy-paste feature to see the result.

pasting selected visible cells in Excel

5. Apply VBA Code to Select Visible Cells in Excel

We can also apply VBA code in Excel to select visible cells by specifying a cell range to select.

  • Move to the Developer tab, then click the Visual Basic button in the Code group.

navigating to developer tab

It launches the Microsoft Visual Basic for Applications window.

  • Now, click the Insert tab and choose Module from the list. We get a small Module window to insert our VBA code.

inserting new code module

  • Here’s the working code to do the task. Paste this code into the module.
Sub Select_visible_cells()
End Sub

VBA code to select visible cells in Excel

Stick to the following video to understand the remaining process.


How to Select Visible Cells to Enter Formula in Excel

In this section, we will explore how to select only the visible cells to enter formulas in Excel, even if cells, rows, or columns have been hidden by applying a filter or collapsing an outline.

  • First, select cells in the F5:F14 range.
  • Then, press the ALT +; key on your keyboard. It selects the visible cells only.

pressing ALT key with semicolon key to select visible cells in Excel

  • In the first cell of the selected range, enter the following formula.
  • Then, press CTRL + ENTER.

pressing CTRL+ENTER to insert formula to all visible cells in selected range

  • It will apply the formula to all visible cells in the selected range.

select visible cells to enter formula in excel

  • You can unhide the rows to confirm that just the visible cells get the formula in them.

hidden cells are deprived of the formula

How to Select Only Filtered Cells to Enter Formula in Excel

Here, we’ll use the AGGREGATE function in the Excel formula to apply only to filtered cells.

  • We’ve applied Filter in our dataset to filter out some data.

applied filter in dataset

  • Now, we want to calculate the average Sales of the visible cells. We’ll show this value in cell D16.
  • In cell D16, put down the AGGREGATE function. Select 1 – AVERAGE as the function_num argument.

using AGGREGATE function in Excel

  • Write a comma after 1, and select 5 – Ignore hidden rows as options argument.

giving argument to ignore hidden rows in excel

  • The final form of the formula is the following.

select only filtered visible cells to enter formula in Excel

  • To verify that the formula is showing the average of visible sales only, clear the Filter from the dataset. You can notice the change in the result also.

different result on same formula when filter is removed

Importance of Selecting Visible Cells in Excel

Following are some common advantages of selecting visible cells in Excel.

  • Prevents Errors: When working with large datasets in Excel, it is common to apply filters, hide rows or columns, or use conditional formatting to highlight specific cells. If you copy and paste the entire range without selecting only the visible cells, you may inadvertently copy hidden or filtered cells, leading to errors in your analysis or calculations.
  • Saves Time: Selecting visible cells in Excel can save you a lot of time, especially when dealing with large datasets. By copying and pasting only the visible cells, you can avoid copying unnecessary data and focus only on the information you need.
  • Improves Data Accuracy: When you select only the visible cells in Excel, you can be confident that you are working with accurate and relevant data. This is particularly important when working with datasets that contain missing or incomplete data, as it allows you to focus on the available information without being distracted by irrelevant data.
  • Provides Better Data Insights: By selecting only the visible cells in Excel, you can get a better understanding of your data and draw more accurate conclusions. This is because you are working with a subset of the data that is relevant to your analysis, rather than the entire range.

Things to Remember

  • Remember that selecting visible cells does not remove any hidden data, so be careful when copying and pasting.
  • Make sure that your data is formatted correctly to avoid any errors when selecting visible cells.
  • Make sure to spell the VBA in-build functions properly otherwise, it will not work.
  • If you are a Mac user, you can use the keys Cmd + Shift + Z instead of ALT + ;.
  • If any cells are hidden due to filtering, then you don’t have to use these methods as Excel will exclude them by default.

Frequently Asked Questions

1. Why can’t I select visible cells only in Excel?

Answer: Make sure that you have applied the filter correctly before selecting visible cells. Also, check that you are using the correct keyboard shortcuts or features to select visible cells.

2. Can I select only visible cells in a specific range in Excel?

Answer: Yes, you can. Select the range where you want to select only visible cells, and then use the Go To Special feature or keyboard shortcuts to select only the visible cells in that range.

3. Will selecting visible cells only remove hidden data in Excel?

Answer: No, selecting visible cells only will not remove hidden data. It will only select the visible cells and leave the hidden cells untouched.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


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

That’s all about today’s session. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Thanks for your patience in reading this article.

Related Articles

<< Go Back to Visible Cells Only  | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF