applied tips : february 2008
microsoft excel : playing hide and seek with your data
There are a number of reasons to hide data in Excel. You may want to hide rows or columns to make a worksheet easier to manage. Or you may want to hide some individual cell values or whole workbooks so that data isn’t viewable, but is still accessible for use in calculations.
Whatever the reason, Excel allows you to hide—and then display again—data in cells, rows or columns, as well as entire worksheets or workbooks.
Hiding a single cell, or a range of cells
Probably the most common use of Excel’s hide feature is to hide rows and columns, but you can also hide the contents of individual cells or a range of cells.
To do this, select the cell or cell range and then select Format > Cells from the menu. Click the Number tab, then click Custom in the Category list. Click and drag to highlight the contents of the Type text box, then type an apostrophe (‘) in its place. Click OK.
When you return to the worksheet, the contents of the cell or cell range are hidden, and an apostrophe appears instead.
Even though the value or formula is hidden, it is still accessible and available for use in calculations and formulas, just as if it were visible. And, if you click on a hidden cell, the value or formula contained in the cell still appears in the Formula Bar.
Another hiding option: Instead of typing an apostrophe in the Type text box you can type a space using the spacebar, causing the cell to appear completely blank. While this can be useful, it also makes it difficult to identify which cells have hidden values in them.
To display the contents of the hidden cell or cells, make sure they are selected, select Format > Cells from the menu, and click the Number tab again. Click General (or another cell format, as desired) in the Category scroll list, then click OK. The cell contents reappear in the format you selected.
Hiding a row or column
A useful way to hide excess data that is necessary, but getting in the way, is to hide rows or columns.
To do this, select a row or column header (or multiple row or column headers), then select Format > Row or Column > Hide from the menu. Alternately, you can right-click the selection and select Hide from the shortcut menu. The row or column is hidden.
Just like when you hid the cell contents, the hidden row or column data is still accessible despite being invisible.
To make the row or column reappear, click and drag to select the row header(s) above and below the hidden row(s) or select the column headers on each side of the hidden column(s). Select Format > Row or Column > Unhide. Alternately, you can right-click the selection and select Unhide from the shortcut menu.
Hiding a worksheet
Say you have sensitive information on a worksheet that you don’t want others to see, but you still want the data to be available for use in formulas in other worksheets. Or perhaps you have a workbook filled with an unruly number of worksheets. What can you do? Hide an entire worksheet.
To hide a worksheet, click on the worksheet’s tab to select it, then select Format > Sheet > Hide from the menu. The sheet is hidden.
To make a worksheet reappear, select Format > Sheet > Unhide from the menu. An Unhide dialog box appears, listing all the sheets that are hidden. Select the sheet you want to display and click OK.
Hiding a workbook
We’ve worked our way up to the biggest illusion of all – making an entire workbook disappear. To hide a whole workbook, make sure the workbook is active and select Window > Hide from the menu. The workbook disappears.
When you close Excel, you’ll be asked if you want to save the changes to the hidden workbook. Click Yes if you want it to stay hidden the next time you open the workbook.
To make a workbook reappear, select Window > Unhide from the menu, select the workbook in the Unhide dialog box, and click OK.
schedule a class on excel
Request an on-site Excel class from Applied Office. Sessions are just $125/hr and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time. Learn more here
quick reference card
Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your own printer. You might even want to laminate it.