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: 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.
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.
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.
- Then, select cell B18 where you want to paste the range, and press CTRL + V.
- But, the result is the opposite. All the hidden rows get pasted also.
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.
- 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.
- 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.
Finally, it’s pasted in the preferred location.
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.
- 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.
- All the visible cells get selected here.
- Like the previous method, you can use the Copy-Paste feature to verify the selection of visible cells only.
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.
- Now, select All Commands under the section Choose commands from. Find the Select Visible Cells command and click on Add>>.
- 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.
- Clearly, you can see a square-shaped icon that wasn’t present before. It’s the icon to Select Visible Cells.
- Now, highlight the entire dataset and click on this icon on the Quick Access Toolbar.
- Simply, the visible cells in your dataset get selected.
- By employing the copy-paste feature, it is possible to authenticate the selection of visible cells only.
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.
- 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.
- Now, only the selected products are visible in the worksheet.
- Select the whole dataset and press CTRL + SHIFT + * on your keyboard. It’ll select only the visible cells.
- Now use the copy-paste feature to see the result.
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.
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.
- Here’s the working code to do the task. Paste this code into the module.
Sub Select_visible_cells()
Range("B4:E14").Select
Selection.SpecialCells(xlCellTypeVisible).Select
End Sub
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.
- In the first cell of the selected range, enter the following formula.
=D5*E5- Then, press CTRL + ENTER.
- It will apply the formula to all visible cells in the selected range.
- You can unhide the rows to confirm that just the visible cells get the formula in them.
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.
- 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.
- Write a comma after 1, and select 5 – Ignore hidden rows as options argument.
- The final form of the formula is the following.
=AGGREGATE(1,5,F5:F14)
- 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.
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.
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 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.
