Excel: Hide Values Using a Number Format

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

  1. 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.

  2. 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:

  3. Both axis have labels.

    After hiding the data, the axis labels are gone.

  4. Axis labels are hidden when the range is hidden.

For more resources for Microsoft Excel