When dealing with large data sets in Excel it can be useful to hide rows and columns. This allows you to focus on only the data that is necessary for you to complete the task at hand. Excel makes this process very easy. This quick guide will explore a few methods that can be used to hide and unhide rows and columns in Excel.
First, select the rows or columns that you would like to hide. You can select a single row or column or multiple rows or columns. Selections can be next to each other, though this is not mandatory. To select consecutive rows or columns simply hold down the Shift key and select or use the mouse to highlight the options you wish to hide. If the desired areas are not next to each other you can hold down the Control key (Command if on a Mac) and make your selection.
With your selection made, navigate to the Cells group on the Home tab. Click Format > Hide & Unhide > Hide Rows or Hide Columns. For an even quicker option, simply right click and select hide.
Once done the rows or columns that you selected will now be hidden. You will see a thin double line in the spreadsheet that indicates the hidden rows or columns.
A time will come when you will likely need to unhide the rows or columns previously hidden. When this becomes necessary you have a few options:
1) Right click on the thin double line that indicates a hidden row or column. Select the unhide option from the drop down menu and the rows or columns will become visible.
2) Navigate to the Cells group on the Home tab. Click Format > Hide & Unhide > Unhide Rows or Unhide Columns.
If you received a spreadsheet and notice some rows and columns are hidden it may be best to unhide everything so you know what you are working with. Simply select the entire worksheet, right mouse click and select unhide rows or unhide columns.
Here at ExcelTraining we are all about making your Excel life easier. We’ve dedicated an entire page to exploring the various shortcuts that make working in Excel a breeze.
Use the following keyboard shortcuts to hide a row or column:
Hide a row: Ctrl + 9
Hide a column: Ctrl + 0
The following can be used to unhide the row or column:
Unhide a row: Ctrl + Shift + 9
Unhide a column: Ctrl + Shift + 0
When working with large data sets a spreadsheet can become confusing to look at and fast. With Excel it is possible to hide the contents of a single cell without losing the data.
In the following example we will hide the contents of cell D2. Note: while the contents of the cell will be hidden the data will still remain. This can be confirmed by selecting the cell and viewing the data in the formula bar. Any formulas that reference the cell will still work as normal.
First, select the cell you want to hide. In our example we are hiding D2. Press Ctrl + 1 to open the Format Cells dialog box. You can also access the dialog box by right clicking the cell and selecting Format Cells.
With the Format Cells dialog box open navigate to the Custom section at the bottom of the left side list. Select the Type box, which likely says General at first, and enter three semi-colons (;;;). Hit Enter or select Ok.
Poof! The contents of cell D2 are now hidden. As previously mentioned, while hidden, the data is still in the cell and can be referenced by formulas. To verify, look at the formula bar with the cell selected and you will be able to view the contents of the cell.
To unhide the contents of the cell simply reverse the steps. Navigate to the Custom section of the Format Cells dialog box. Go to the Type field and select General. Select OK and the contents of the cell will now be visible.
We promised a quick, easy guide to hiding and unhiding rows and columns in Excel. We tossed in the final section on hiding a single cell for you over achievers. Hopefully this guide helps you better manage the large data sets that are inevitable when working with Excel on a daily basis.