Being able to copy only visible cells in excel is particularly useful in a situation where you have a large data set filtered and you need to copy only the information currently filtered in. In today’s post, we would take you through all you need to know to be able to copy visible cells only.
To practice, you’ll need a worksheet that can be filtered based on some user defined categories. Not to worry if you don’t have this, as we would be sharing the workbook we use as an example in this post. Also note that this also works for hidden cells. This means that for data with hidden cells, following this guide would allow you copy only cells that are visible. So let’s begin….
Step 1: Apply filter to your data by using the Keyboard combination: Shift +Ctrl + L, or better still add the filter cone through the home tab as shown in this guide.
Step 2: Filter for the category you are interested in. For our sample data, that would be 2020 data only in Column G.
Step 3: Highlight already filtered data from top to bottom and on the home tab click Find & Select.
Step 4: A selection box pops out, in the selection box, click visible cells only and click Ok. Please not that you can bypass step 3 above by pressing the key F5 to pop out the selection box.
Step 5: Press Ctrl + C to copy the range
Final Step: Go to the new sheet or new cell you want to paste the copied data to and press Ctrl + V to paste the range.
As you can see, the data pasted excludes 2019 and 2021 data. So any time you need to copy data from a range with filtered or hidden rows, remember to select visible cells only before copying and pasting. See you in the next post, bye.
