Sometimes a column has to stay in the sheet — a formula depends on it, or a chart reads from it — but you do not want the numbers on screen or in print. Hiding the whole column works, though it also hides the heading and makes the sheet awkward to navigate. The neater option is a custom number format that displays nothing at all.
Hide the values with a custom format
- Select the cells whose values you want to hide.
- Press
Ctrl+1to open Format Cells. - On the Number tab choose Custom.
- In the Type box enter three semicolons:
;;; - Click OK.
The cells now look empty. The values are still there: click a cell and you will see its contents in the formula bar, and any formula that refers to the cell keeps working exactly as before.
The trick works because a custom number format has four sections separated by semicolons — positive numbers, negative numbers, zeros and text. Leaving all four empty tells Excel to display nothing in every case.
What this is and is not good for
It is good for tidying up helper columns, intermediate calculations and lookup ranges that clutter a report. It is not a security measure: anyone can select the cell and read the value in the formula bar, or simply clear the format. If the data genuinely must not be seen, remove it from the workbook or protect the sheet — and remember that sheet protection in Excel is a convenience feature, not encryption.
To bring the values back, select the cells, open Format Cells again and pick General (or press Ctrl+Shift+~).
Hiding the column instead
When the heading can go too, hiding the whole column is simpler: right-click the column letter and choose Hide, or press Ctrl+0.
Two consequences worth knowing. Hidden columns are excluded from printing, which is often the point. But they are not excluded from a copy: select a block spanning a hidden column, copy, paste elsewhere, and the hidden data comes with it. To copy only what you can see, use Alt+; (Go To Special → Visible cells only) before copying.
For columns you hide and unhide repeatedly, grouping is friendlier than hiding. Select them and press Shift+Alt+→: Excel adds a small +/− control above the sheet, so the next person can see that something is collapsed rather than wondering why the letters jump from C to E.
Keeping the format when the data changes
A custom format applies to cells, not to data. Insert a row in the middle of a formatted range and the new row inherits the format from its neighbours — usually what you want. But paste data in from elsewhere with a normal Ctrl+V and the source formatting comes with it, undoing the hide.
Paste with Values only (Ctrl+Alt+V, then V) to keep the formatting in place. If a range is refreshed often, applying the format through a conditional formatting rule instead makes it stick regardless of how the values arrive.
For the other two meanings of “hiding” — concealing the formula while leaving the result on screen, and hiding rows or entire sheets — see hide cell contents and formulas.
Preparing for a Microsoft Office Specialist exam? Custom number formats are an explicit objective in the MOS Excel Expert exam. See our MO-211 exam guide for the full objective list.
