Hide Values Using a Number Format

September 28, 2023 - by Bill Jelen

Problem: Why did you use the white font to hide those cells? Isn’t there a custom number format to hide values?

Strategy: Yes. You could select the cells, press Ctrl+1, then choose Custom and type three semi-colons to hide the text in the cell.

Format Cells, Number, Custom. Type three semi-colons as the type in order to hide data. ;;; — Figure 1337. Using ;;; will hide the cell contents.

Gotcha: While nothing will appear in the cells, if someone selects the cell, they can see the cell formula in the formula bar. If the cell contains a constant (i.e. anything other than a formula), then they will see the cell value as well.

With the ;;; custom format, you can still see the value if you select the cell and look in the formula bar. — Figure 1338. The cells are hidden, except in the formula bar.

The other problem with this method: if the data is being used in a chart, then the labels along both axis of the chart will be hidden as well. Here is the chart before hiding the data:

A chart has labels along the X-axis:
Line 1
Line 2 — Figure 1339. Both axis have labels.

After hiding the data, the axis labels are gone.

When you hide the data in the grid using ;;; the labels are also hidden in the chart. — Figure 1340. Axis labels are hidden when the range is hidden.

This article is an excerpt from Power Excel With MrExcel

Title photo by imso gabriel on Unsplash