Hide Cell Contents and Formulas in Excel

“Hide the cell contents” can mean three different things in Excel, and they protect very different amounts. Choosing the wrong one is how people end up believing a workbook is confidential when it is not.

1. Hide what is displayed, keep the value

Select the cells, press Ctrl+1, choose Custom on the Number tab and enter three semicolons: ;;;

The cells appear blank on screen and in print, but the values remain and formulas that reference them keep working. A custom format has four sections — positive, negative, zero, text — and leaving all four empty displays nothing in every case. Click any of the cells and the value is still visible in the formula bar. Use this for tidiness, never for secrecy. There is a worked example in hiding values in a column.

2. Hide the formula from the formula bar

This is the one most people are actually looking for: the result stays visible, but nobody can see how it was calculated.

1. Select the cells containing the formulas.
2. Press Ctrl+1 and open the Protection tab.
3. Tick Hidden (leave Locked ticked as well).
4. Go to Review → Protect Sheet and confirm.

The Hidden checkbox does nothing on its own — it only takes effect once the sheet is protected. That catches almost everyone the first time.

3. Hide the rows, columns or sheet

Right-click a row or column header and choose Hide, or right-click a sheet tab and hide the sheet. For sheets there is a stronger option: in the VBA editor (Alt+F11) set a sheet’s Visible property to xlSheetVeryHidden, which removes it from the normal Unhide dialog.

What none of this is

None of these is security. Worksheet protection in Excel is designed to prevent accidental edits, not determined access, and passwords on sheet protection are widely known to be trivially removable. If data must genuinely be kept from someone, do not send them the workbook containing it — remove the data, or protect the whole file with an open password, which does use real encryption.

Hide only the zeros

A common variation: keep every real number visible but suppress the zeros that formulas return for empty inputs. Two ways, and they differ in scope.

For the whole sheet: File → Options → Advanced, then untick Show a zero in cells that have zero value. This affects the active worksheet only, and it applies to every cell on it.

For selected cells: use a custom format with an empty third section:

0.00;-0.00;;@

Positives and negatives display normally, the zero section is empty so zeros vanish, and @ in the fourth section passes text through unchanged. Omit that @ and any text in those cells disappears too — which is rarely what you intended.

Why white text is not the answer

The instinctive fix is to set the font to white on a white background. It looks identical on screen and it is worse in every other respect.

• Select the cells and the values reappear as highlighted text.
• Change the fill colour, or apply a banded table style, and everything is visible again.
• Copying to another application carries the values across in plain sight.
• Printing in greyscale often renders white as a light grey that is perfectly readable.

The three-semicolon format has none of these failure modes, because the cell genuinely displays nothing rather than displaying something invisible.

Finding what someone else hid

Inheriting a workbook and suspecting there is more in it than you can see is a common situation. Three checks, in order:

1. Select all cells with Ctrl+A and set the number format to General. Any three-semicolon formatting is undone at once.
2. Look for gaps in the row numbers or column letters — a jump from C to E means column D is hidden. Select the columns either side and choose Unhide.
3. Right-click any sheet tab and choose Unhide. If the list is empty but you still suspect a sheet is missing, open the VBA editor with Alt+F11 and check the Properties pane for a sheet set to xlSheetVeryHidden.

Worksheet protection and custom number formats are both named objectives of the MOS Excel Expert exam — see the MO-211 guide.