You’ve got your Excel spreadsheet looking all nice, you’ve hidden all the columns you don’t want people to see, you’ve filtered out thousands of rows that are unnecessary at that moment and you want to select a whole bunch of that data and do something with it.
You may want to delete the rows you’ve filtered down to as the data in them is bogus.
You may want to just hit delete to make the data blank in those cells.
You may want to over type the selected range with another value, hitting CTRL+Enter to repeat the value in that selection.
HOWEVER, if you did any of these things on a selection with hidden rows or columns, you’ll be doing it to the hidden cells as well. It’s well annoying innit!
There is a neat little trick that we use a lot to get around this. Check out the simple example below where I’ve put some content into cells B3 to J3. I’ve coloured E3 to G3 red as they’re the columns I’m going to hide.
If you selected B3 to J3 whilst the column E,f and G are hidden and hit delete to clear the contents you get the following once you unhide those columns..
If you want to leave the content in those hidden cells alone you need to do one of the following once you’ve selected B3 to J3
The easiest option is to hit Alt+; on the keyboard
On the ‘Home’ tab in the ‘Editing’ section, click ‘Find & Select’ and select the ‘Go To Special’ option.
The ‘Go To Special’ box will pop up and you need to select the ‘Visible cells only’ option and click OK.
You’ll see that your selection now breaks around your hidden columns.
And if you hit delete and unhide your hidden columns you will find that you have only affected the cells that were visible at the time.
This little beauty has many uses indeed and we find ourselves using it more and more. A lot of the data we use is in an Excel table format and we auto filter the contents to view only relevant data. Should we need to change the data, delete rows, apply formatting or run macros on just the visible data: WE DO THE ABOVE FIRST