Excel spreadsheets can grow quite large as data is added, spanning several screenfuls.
It can sometimes take a good amount of scrolling (not only vertically but also horizontally), while wading through row after row of data looking for the required bits of information.
This can become quite frustrating when trying to compare rows that are disparately located throughout the spreadsheet.
And although not as common, scrolling horizontally left and right, can also be just as annoying.
A quick solution to the problem is to hide any intervening rows or columns, so that the required information appears in contiguous rows (or adjacent columns), ideally within a single screen.
All well and good, but then when you share the spreadsheet with someone else who is unaware of the hidden rows, or you access the spreadsheet after some period of time has passed and have forgotten about hidden content, it appears as if information is missing or has been deleted.
Obviously, it’s just the hidden rows (or columns), but how do you detect that rows and columns have been hidden, and more importantly, how do you unhide these columns and rows in an Excel spreadsheet?
In order to understand how to unhide rows and columns, it helps to know how to hide them in the first place.
There are a few different ways to to do this, which we will now take a look at.
Hiding Rows in Excel
To hide one or more rows, start by selecting the rows.
The rows can be non-contiguous (hold down the Control key while selecting each row).
Next, right mouse click on the highlighted rows to bring up the context menu.
Left mouse click the Hide option, and all selected rows will now be hidden.
In the second method, again begin by selecting the contiguous or non-contiguous rows to be hidden.
Then, from the Excel main horizontal menu, go to the Home tab and left mouse click on Format to open the drop down menu.
Select Hide & Unhide > Hide Rows to hide the selected rows.
In this final method, which is an indirect way of hiding rows by grouping them, a requirement is that the selected rows in each group must be contiguous.
You can have many groups, but the rows in each group have to be contiguous.
Select the desired rows, and from the Excel main horizontal menu, open the Data tab, and left mouse click the Group option.
There is also a keyboard shortcut to perform this action, Shift – Alt – Right arrow after selecting the rows.
The selected rows are now grouped, but as yet not hidden.
Unlike with the previous methods, where the selected rows were hidden automatically, grouping does not hide the rows, but simply marks them as grouped, and creates outline symbols (boxed 1 and 2, and boxed plus/minus) on the left sidebar, as shown in the above screenshot.
To hide the selected rows, left mouse click on the boxed 1, at the top of the sidebar (this will hide all rows in the sheet that are in groups), or left mouse click the boxed minus sign, to the left of the selected rows, to hide only this group of rows.
Hiding Columns in Excel
To hide columns in Excel, the steps are almost identical to those for hiding rows.
Begin by selecting the column or columns to hide, be they contiguous or non-contiguous.
Right mouse click anywhere inside the selected columns, and choose the Hide option from the context menu.
In the second method and just as with rows, again begin by selecting contiguous or non-contiguous columns to be hidden.
Then, from the Excel main horizontal menu, go to the Home tab and click on Format to open the drop down menu.
Select Hide & Unhide > Hide Columns to hide the selected columns.
And finally, when using Groups, the selected columns must be contiguous.
Select the desired columns, and from the Excel main horizontal menu, open the Data tab, and left mouse click the Group option.
Here too, the keyboard shortcut , Shift – Alt – Right arrow, is also available.
Unhide Rows in Excel
In order to unhide hidden rows in an Excel spreadsheet, we must first be able to find them.
Fear not, for it is quite easy to find hidden rows, if you know what to look for.
The screenshot above includes some hidden rows.
Looking at the row count in the left sidebar, we notice that the row count goes from 5 to 9, missing out rows 6 to 8.
The same goes for rows 12 to 18, and 23 to 31. These are hidden rows.
Also, notice between rows 5 and 9 (and also between rows 12 and 18, and 23 and 31), there is a very discreet double line separator, instead of the usual single line.
This too is an indicator that there are hidden rows.
Having found the hidden rows, select a range of rows that cover above and below those that are hidden.
Then, right mouse click anywhere in the selected rows to bring up the context menu, and choose Unhide.
You can also use this method to unhide more than one set of hidden rows, by selecting a range of rows, as long as the range covers all hidden rows.
And finally, instead of using the right mouse click to bring up a context menu, after selecting the rows to unhide, you can go to the Home tab in the Excel main horizontal menu, and choose Format > Hide & Unhide > Unhide Rows.
All of the above methods of unhiding rows have allowed us to selectively choose individual sets of rows to unhide.
With rows 5 to 9, 12 to 18, and 23 to 31 hidden, we could opt to unhide only rows 5 to 9, leaving the other hidden rows unaffected.
This is handy when we don’t want to unhide all rows, but only a select few.
What happens though, if we need to unhide all the hidden rows in the spreadsheet in one go?
This is achievable with just a few mouse clicks.
To unhide all hidden rows, we need to first select the entire spreadsheet.
Hover the mouse pointer over the top left corner of the sheet’s grid so that the mouse pointer turns into a thick white cross, and then left mouse click.
This will select and highlight the entire sheet.
You can now right mouse click anywhere in the selected rows to bring up the context menu and choose the Unhide option, or from the Excel main menu, open the Home tab, and then go to Format > Hide & Unhide > Unhide Rows.
This will now unhide any and all hidden rows, no matter where they are in the sheet.
What this (and the preceding procedures) won’t do however, is to unhide any grouped rows.
You can quickly tell if there are any grouped rows by looking in the top left hand corner of the sheet for the boxed 1 and 2, as well as the extra width of the left hand sidebar.
If a group is closed and therefore hiding rows, the boxed plus sign will be displayed to the left of the rows, otherwise if it is expanded, there will be a boxed minus sign to the left.
For closed groups, left mouse licking on the boxed plus sign will expand the group, revealing the hidden rows.
You can expand all groups in the entire sheet by clicking the boxed 2 at the top of the sheet.
Any grouped rows that were hidden, will now be visible.
To remove any grouping, the grouped rows must first be expanded.
Then select a range of rows that covers the group, and from the Excel main menu, open the Data tab and select Ungroup.
Unhide Columns in Excel
The process for unhiding columns is almost identical to unhiding rows.
The screenshot above includes hidden columns.
Checking the top bar, we notice that there are missing letters, A – D, and D – G.
These are the hidden columns.
As with hidden rows, so too with columns there are double lined separators between the column letters.
To unhide these columns, it is exactly the same procedure as with rows.
Start by selecting a range of columns either side of those that are hidden, then right mouse click anywhere in the selected columns to bring up the context menu, and choose Unhide.
Alternatively, from the Excel main menu’s Home tab, go to Format > Hide & Unhide > Unhide Columns.
As with rows, you can unhide more than one set of hidden columns, by highlighting a wider range of columns.
And finally, to unhide all hidden columns, select the entire spreadsheet by hovering the mouse pointer over the top left corner of the sheet’s grid so that the mouse pointer turns into a thick white cross, then left mouse click.
Next, right mouse click anywhere in the selected columns to bring up the context menu and choose the Unhide option, or from the Excel main menu, open the Home tab, and go to Format > Hide & Unhide > Unhide Columns.
This will now unhide all hidden columns, no matter where they are in the sheet.
Lastly, to unhide columns that have been grouped, check the top left corner of the sheet for the boxed 1 and 2 symbols, to determine if indeed there are groups.
You can either left mouse click on the boxed plus sign to expand the corresponding closed group, or left mouse click on the boxed number 2 to expand all column groups in the sheet. At this point, any hidden rows in the groups should now be revealed.
To remove any groups, select a range of columns covering the group, and from the Excel main menu go to the Data tab, and select the Ungroup option.
Hiding rows and columns can help make large spreadsheets more manageable, allowing the user to focus on those pieces of information of interest. But they can also be a source of confusion for those who are unaware of this capability.
As with most applications, there is always more than one way to skin a cat, and Excel is no exception.
Hopefully, this article has given some insight as to how to hide and unhide selected rows and columns (or all rows and columns), in an Excel spreadsheet.
