Excel Tip of the Week #192 - Hidden rows and columns

Hello and welcome back to the Excel Tip of the Week!  This week, we have a Basic User post in which we are looking at how hiding rows and columns works in Excel, and how it really works.


How to hide sections of Excel

You can simply select a batch of rows or columns, then right click / hide:

As you can see, the hidden rows / columns are not shown, and the identifiers just skip them, with a slightly thicker line between to show where some data is hidden.

Generally speaking, hidden rows and columns are poor practice.  They can easily lead to errors (see below for some examples), and are easy to miss.  The better solution for tucking away unwanted helper columns etc. is usually to using grouping instead - see TOTW #62 for details.


How hidden rows and columns interact with other Excel features

I'm just going to be using the example of hidden rows here, but most things apply equally to hidden columns.

General formulas

As you can see here, ranges that include the hidden rows will still feed into the results.


If you copy a range and paste it over a section that has hidden rows, the data will be filled into the hidden rows as if they were visible.


Selecting a range with hidden rows in it and hitting Ctrl + c will copy the entire range, including the hidden rows.

Filling with the Fill Handle

Filling over the hidden rows will also fill within the hidden rows.


Formatting a range that includes some hidden rows will also format the hidden rows.


Deleting the contents of a range containing hidden rows also deletes the contents of the hidden cells.


Excluding hidden cells

As you can see, hidden rows are still affect by pretty much all the ways that normal cells are.  This is exactly what can cause issues with hidden rows - it's easy for the user to affect formulas that they don't even realise are there, copying over or deleting them inadvertently.  There's also the issue of access, as it's hard to check in on hidden cells (you have to highlight the surrounding rows / columns and then right click => Unhide).

If you do have to use hidden cells, and want to make sure you don't affect the hidden cells in the middle of your range, pressing Alt + ; will deselect any hidden cells:

This will then let you make changes without fear of affecting the hidden cells.  Don't forget that you can make a change in one cell and then press Ctrl + Enter to make the same change in all selected cells.

Previous post - Twenty Principles for Good Spreadsheet Practice: Three years on
TOTW index 
Next post - VBA case study: DICETRAY and testing VBA code

Scottish members - I am presenting Excel Masterclasses in Edinburgh on the 7th September, and Glasgow on the 8th.  If you'd like to come along, you can find additional information here.

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.