Department of Technology

TECH 394 - Tech Computer Applications

Summer 2004

E-mail Me


Syllabus

Schedule

Handouts

Assignments


All My Webs

Dept Home

 

Hiding Cells, Columns, Rows, and Worksheets

In many situations, you may want some of the calculations and workings of your spreadsheets to be hidden from the user.  In some cases, the cells containing formulas clutter the sreen, and invite the user to enter a number, thereby wiping out your formula.  A poorly documented feature of Excel is its ability to format cells as "hidden" - the formula remains, but the user can't see the contents.  In the example shown, the cell containing the conversion from Fahrenheit to Celsius is hidden.  You can see what's in the cell by placing the cursor on it, and looking in the contents window (on the right, just above the cells).

wpe5.jpg (29400 bytes)

To hide the cell, select it, and click Format, Cells, then choose the Number tab.  Select a custom number, enter 3 semicolons (;;;), and select OK.   The cells selected will be hidden.

wpe6.jpg (25081 bytes)

In other situations, it may be necessary to hide entire rows or columns .   This can be done in two ways.  You may right-click on the column label (A, B, etc.), and select Hide; or you may select a cell in the column, and click on Format, Column, and select Hide. The column disappears, and the column label is removed.  In the example shown, column D is hidden:

wpe8.jpg (29060 bytes)

The column can be restored by selecting the columns on either side of the hidden column, and then clicking Format, Column, Unhide.  The drawback to hiding columns is that the user can readily see that a column has disappeared, and may choose to unhide it.

You may choose to place hidden data in a different worksheet, and hide the worksheet.  Again, you have two choices.  With the sheet selected, you can click on Format, Sheets, Hide.  The tab disappears from view.  If you remove "Sheet2" from the middle of the selection above, it would be a good idea to rename the remaining sheets to prevent the user from getting curious and selecting Unhide.

A more secure choice would be to include a line of code in a macro that runs automatically (like Auto_Open) that hides the sheet and prevents the user from unhiding it.  If the code contains the line:

     Sheets("Sheet2").Visible = False

Sheet2 will be hidden, but the user can unhide it.  If instead it read:

     Sheets("Sheet2").Visible = xlVeryHidden

The user will not be able to unhide the sheet.  The only way to unhide this sheet is to run a line of code setting the "Visible" property to "True".