It’s often what appears to be the simplest things that are frustrating and can waste us loads of time. Copying data that contains hidden rows and/or columns is just one of these. You might expect anything that is hidden to not be copied but that is not the case.
At first thought you might assume that each block of information will need to be coped and pasted separately. Although this would work it could be very time consuming. Luckily Microsoft Excel has a special trick up its sleeve.
When can you use this?
This problem could occur when rows and columns have been manually hidden but also when using outlining tools or subtotals where the opportunity to quickly show a certain level of detail in your data using outlining tools makes this hugely useful.
Copy and paste visible cells only
So how does it work?
- Select the cells you want to copy. With this method it will not copy hidden rows and columns so don’t worry that these are selected too at this point.
- THE CLEVER PART!!! From the Home tab, click on the Find and Select icon.
- Choose Go To Special
- Choose Visible cells only
5. Now only the visible cells you originally selected will selected. You will see a feint double line between rows and columns where data is hidden and therefore not selected.
6. Continue to copy and paste as normal. Click on the Copy icon from the Home tab (or any other option you prefer). Move to where the data will be copied to and Click on the Paste icon (or other method)
Adding the Select Visible Cells icon to the quick access toolbar
For a one off the method already mentioned is great but if you are doing it regularly this is still a bit fiddly. By adding the Select Visible Cells icon to the quick access toolbar (QAT) the steps in the process can be greatly reduced.
Depending on the version of Microsoft Excel you are using the QAT will appear slightly differently. When using Excel 2019 or earlier the QAT sits at the top left of the screen. If using Microsoft 365 it may initially be hidden. If that is the case, then right click where it says AutoSave and choose Show Quick access toolbar. It may appear above or below the ribbon.
Once you have found the QAT complete the following steps:
- Click on the drop down at the end of the QAT
- Select More Commands
- Where it says Popular Commands, change this to Commands Not in the Ribbon
- Scroll to find Select Visible Cells
5. Click on Add, then OK
The Select Visible Cells icon will now appear on the QAT. Clicking on this icon replaces steps 2 to 4 in the list of steps to Copy and Paste Visible Cells only.
NOTE: If you haven’t customised the quick access toolbar before then you won’t be aware of its functionality. You can add any icons that you use on a regular basis, and they will be immediately available whichever tab on the ribbon you are selected on. For options that are tucked away in the depths of the options this is particularly useful.
If you’ve enjoyed reading about how to avoid copying hidden rows and columns in Excel, there’s some other blogs below that you might find useful: