Excel VBA hide cell contents

Question

To hide/unhide a column or a row in Excel using VBA, you can use the “Hidden” property. To use this property, you need to specify the column, or the row using the range object and then specify the TRUE/FALSE.

Table of Contents Show

Get FREE Excel + Power BI Tips
Welcome to Chandoo.org
Learn Advanced Excel – 3 Month Roadmap with Resources
The Excel Mandelbrot
FREE Calendar & Planner Excel Template for 2023
Merry Christmas & Happy New Year 2023
Range Lookup in Excel – How to lookup the pricing tier? [Formulas]
Related Tips
Learn Advanced Excel – 3 Month Roadmap with Resources
FREE Calendar & Planner Excel Template for 2023
Range Lookup in Excel – How to lookup the pricing tier? [Formulas]
#### in Excel cell
How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)
FIFA 2022 World Cup Schedule & Results – Excel [FREE Download]
57 Responses to “How to Hide a Cell’s Contents in Excel? [Quick Tip]”
How do I hide the contents of a cell in Excel?
Can you hide cells in Excel based on cell value?
How do you hide text in VBA?
How do I remove text from a cell in Excel VBA?

Specify the column or the row using the range object.
After that, use the entire column/row property to refer to the entire row or column.
Next, use the hidden property.
In the end, specify the true/false.

Following is the example to consider:

2. In the Format Cells dialog, under Number tab, select Custom from Category list, and then type ;;;** into the textbox under Type.

3. Click OK, now the select cell contents have been masked with asterisks.

But the cell contents also can be viewed in the formula bar.

4. Place cursor at the cell you do not want to hide contents, then click Review > Protect Sheet, and uncheck Select unlock cells and Format cells option (other options you can check as you need), then type and confirm the password for protecting the sheet in the popping dialogs.

Then the cell contents have been hidden and masked with asterisks.

Note: If the cell contents are numeric string, this way only with display the cell contents as blank.

Hide rows with #N/A or other specific error values by VBA code

If you want to mask both numbers and texts, you can apply VBA code.

1. Select the cells you want to encrypt, and press Alt + F11 keys to enable Microsoft Visual Basic for Applications window.

2. Click Insert > Module, then copy and paste below code to the new Module.

VBA: Hide #N/A error rows

Sub E_Cells()
'UpdatebyExtendoffice
Dim xRg As Range
Dim xERg As Range
Dim xWs As Worksheet
Dim xStrRg As String
Dim xStrPw As String
xStrPw = ""
xStrPw = Application.InputBox("Enter Password", "", "", Type:=2)
If xStrPw = "" Then Exit Sub
On Error Resume Next
Set xERg = Selection
Set xWs = Application.ActiveSheet
Set xRg = xWs.Cells
xRg.Locked = False
xERg.Locked = True
xERg.NumberFormatLocal = "**;**;**;**"
xWs.Protect Password:=xStrPw, DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

3. Press F5 key to run the code. And then type password in the popping out dialog, and click the OK button. See screenshot:

Tip:

1. For decrypting the cells, you can use below macro code.

Sub D_Cells()
'UpdatebyExtendoffice
Dim xRg As Range
Dim xERg As Range
Dim xWs As Worksheet
Dim xStrRg As String
Dim xStrPw As String
xStrPw = ""
xStrPw = Application.InputBox("Type Password", "", "", Type:=2)
If xStrPw = "" Then Exit Sub
On Error Resume Next
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange
xERg.NumberFormatLocal = "**;**;**;**"
xWs.Unprotect Password:=xStrPw
For Each xERg In xRg
    If xERg.Locked Then xERg.NumberFormatLocal = "@"
Next
End Sub

2. With the VBA code, the cell contents also can be viewed in formula bar.

Encrypt and mask cell contents with asterisk or other string

If you want to encrypt and mask cell contents with the special string as you need, the Encrypt Cells feature of Kutools for Excel can do a nice favor.

2. This might be handy when you need to calculate intermediate values, but dont want to display them. (But try to use Hide rows / hide columns feature if you can)

Remember: This formatting code only blanks out the cell contents from being seen. They contents are still there and accessible for formulas, charts as such.

Also know how to display colors in chart data labels using custom cell formatting codes

What is your favorite cell formatting trick?

Facebook

Twitter

Share this tip with your colleagues

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Tagged under custom cell formatting, Excel Howtos, Learn Excel, microsoft, MS, number formatting, quick tip, spreadsheets
Category: Excel Howtos, Featured, Learn Excel

PrevPreviousConverting Excel to PDF – Everything you need to know

NextExcel Formula 1 – Really Fast way to learn Excel FormulasNext

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my story • FREE Excel tips book

Want an AWESOME
Excel Class?

Excel School made me great at work.

 5/5

– Brenda

FREE Goodies for you...

100 Excel Formulas List

From simple to complex, there is a formula for every occasion. Check out the list now.

20 Excel Templates

Calendars, invoices, trackers and much more. All free, fun and fantastic.

13 Advanced Pivot Table Skills

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Get started with Power BI

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Learn Advanced Excel – 3 Month Roadmap with Resources

Advanced Excel Skills are highly sought after. Use this FREE Excel roadmap to learn and grow you Excel skills in 3 months.

The Excel Mandelbrot

FREE Calendar & Planner Excel Template for 2023

Merry Christmas & Happy New Year 2023

Range Lookup in Excel – How to lookup the pricing tier? [Formulas]

Best of the lot

Excel for beginners
Advanced Excel Skills
Excel Dashboards
Complete guide to Pivot Tables
Top 10 Excel Formulas
Excel Shortcuts
#Awesome Budget vs. Actual Chart
40+ VBA Examples

Learn Excel

Learn Advanced Excel – 3 Month Roadmap with Resources

Learn Excel

FREE Calendar & Planner Excel Template for 2023

Learn Excel

Range Lookup in Excel – How to lookup the pricing tier? [Formulas]

Learn Excel

#### in Excel cell

Learn Excel

How to connect to a SINGLE file on SharePoint from Power Query (Fix Unable to Connect Error)

Learn Excel

FIFA 2022 World Cup Schedule & Results – Excel [FREE Download]

57 Responses to “How to Hide a Cell’s Contents in Excel? [Quick Tip]”

moatasem says:
Great tip PHD
- sekhar says:
  thank you somuch great tip..
Radu says:
Hi, Chandoo. I've been reading your posts a fairly long time now, and they're all fantastic. I see you're making a habit of saving my (professional) life. :)) But seriously, this is a great tip. Thanks for sharing it, and many thanks for all your hard work!
Tony Rose says:
Chandoo - this is a neat little trick. I would typically use the color white so the text/numbers do not show. The downside is that someone can highlight the boxes and see the white text. With this trick that issue is solved.
One use that I can think of and do use is for the values in a drop down box (data validation - list). Now you can set the values, but hide them.
hwsris says:
thanks Chandoo.
Michael Nickey says:
Any idea on how this may work in StarOffice?
- Chandoo says:
  @Michael: Unfortunately I haven't used staroffice in a really long time.
  @all.. anyone, any sun fans out there who are using star office?
  - jan says:
    Hi this a great trick but i want the hidden content look (-) this to people not empty is it possible
Michael Pierce says:
Great tip, I always forget about this and it's good to have the reminder.
One favorite formatting trick that I have is to right-align numbers when some of them are percentages. Typically, if you had the numbers 6.23% and 7.31 in a column, the decimals won't align because of the percent sign. The percentages get the standard "0.00%" formatting, but for the other numbers, I use: "0.00_%" and everything aligns at the decimal point nicely.
Bob Gannon says:
Great tip. But there doesn't seem to be any way to do this with conditional formatting. Too bad. You can do white text.
Chandoo says:
@Bob.. in 2007 you can do this, but in earlier versions of excel this is not allowed in conditional formatting.
@Michael Pierce: That is a good one.. keeping the alignment right..
@Tony: that is a fun way to use this...
Gerald Higgins says:
You can't do it within normal conditional formating.
But you can use some of the conditional functions within custom formats.
For example
[<500]0;;
will display numbers smaller than 500. Anything more than 500 will not display. I worked this out from some custom format instructions on John Peltier's site.
Chandoo says:
@Gerald.. you are right... the three sections of the custom format code can be used to define some minimal conditional formatting. For eg. a code like [<500][red]0;[<1000][blue]0;[green]0; would display red, blue and green text based on the value. I have also described an example of it here: http://chandoo.org/wp/2009/01/29/colors-in-excel-chart-labels-trick/
- Gb4821 says:
  I am trying to build an invoice and would like to hide the pricing until I have a quantity in it so as to not show all pricing all the time. Is there a way to hide the price cells until you enter a value in the formula?
  Thanks for your help,
  Greg Brown
  - Hui... says:
    @Greg
    Yes, use Conditional Formatting
derek says:
This method highlights the big weakness of Excel's custom number formatting for me: its failure to properly handle logicals and errors. Try the following format on a range of types:
"positive";"negative";"zero";"text"
You'll find that numbers are shown using the "positive etc." labels, (right aligned); text uses the "text" label, left aligned, naturally; errors do not respond to formatting; and logicals are treated with the "text" label, only center aligned. So I'm stuck with using conditional formatting to paint FALSE values white in my spreadsheets (conditional formatting does not allow you to format numbers, only some text and cell attributes)
However, at least I can create an expression that lets me switch FALSE values from invisible to bright red over the whole sheet, for diagnostic purposes, by typing "Y" in a sngle cell 🙂
Best month ever [blogging updates] | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org says:

[...] How to hide a cell’s contents in Excel [...]
savithri.v says:
“This formatting code only blanks out the cell contents from being seen. They contents are still there and accessible for formulas, charts as such”
Felt like sharing with you this…
Recently, I was witness to a presentation which just had lines all over in the graph, with no clue as to whether the numbers represented %s or values. It seems that the presenter had inadvertently missed to format the value axis in the graph to display the labels (which had source data formatted “;;;”)
savithri
San says:
Hi Chandoo... I tried this, but i am getting a error mesage saying "Microsoft Office Excel cannot use the number format you typed. Try using one of the built-in formats. For more information about custom number formats, click Help".
Could you please tell me what is wrong that i have done???
Chandoo says:
@San, are you sure you have typed three ;;; as shown in the pic above? It should accept the format as is. Let me know if you have typed something else.
Ashish Patel says:
Hi,
Thanks for the great tip.. I have one problem though.. you can still see the value if when you enter in the cell in the formula bar.
This is also the case when you protect a worksheet and do not allow selection of locked cells. You can still navigate to the locked cell via the formula bar. Anyway around this ?
Ashish
Saeed says:
Thanks for the tip Chandoo.
Initially, I simply changed the font color to white on a white background to make the cell content quasi invisible; then I came across your tip.
Somehow, without knowing why!, I think your tip is more elegant and technically better.
Would you please comment on that? Thanks.
Saeed
Rose says:
@ashish, if you lock the specific cells that you do not want to be seen and then protect the worksheet, it will not allow another user to select the cell whose value you want to not be seen. This still allows you to allow others to select the cells which are unlocked while keeping the sensitive values unseen in the cell and formula bar.
Goh Hock Siew says:
Dear Sir,
I was so keen to learn to hide the cell conmtents but when I use it on my Excel 2003 it does not work. Appreciate if you can help.
Thank you.
Saif says:
Dear Sir,
Is there anyway with which I can hide data from both cells and formula bar???
Regards,
Saif
kumaran P says:
pls help:
if 47 days working for sales man 5000 salary ..if above 47 days working per day 750 rs every day how to calculate pls help
vaishali says:
I want to colour entire row based on the change of value of one cell. i.e suppose i want to apply condition that the value in the cell is greater than 0 the entire row should be green, if the value is greater than 5 the entire row should be yellow and so on...
i have tried conditional formatting -- new rule --Excel: Change entire row colors based on value of one of it's cells? but it is not working... suggest me
- Hui... says:
  You have to add a CF for each Color/Value you want
  Lets say it is Row 5 based on the value of Cell D1
  Select Row 5
  Goto CF
  Delete All CF for this area
  ---
  Goto CF
  New Rule
  Use a Formula
  =$D$1=1
  Set Format
  Apply
  Repeat from the --- and add as many rules/formats as there are values / colors
Carlos Magno says:
Thanks for the tip Chandoo. It helps me in some unusual ways.
Unfortunately I found that the contents are not accessible for filtering.
The ideia is using, in a large table, a tiny column, to put hidden text, in order to tagging rows and using colors to distinguish the different tags. But all fails because when I try to grouping the rows by some tag, the text doesn't appear in the filter options drop box. Any ideia? Thanks for all.
Kate Grosmaire says:
Thanks, Chandoo. I like this, but what I am really trying to do is display a portion of the cell data because there are so many characters, e.g. "The Division has requested sampling of the data so that..." and then if you hover or click on the cell, you can see all the characters. I can use yours with the instructions that cell data can be viewed in the formula bar, but I'd rather have a "hover" solution.
Stephen says:
Chandoo, This tool doesn’t seem to work if it involves a cell that has a VLOOKUP command in it. I have CF for cells that are equal to 0, but when they are not equal to 0, the text still doesn’t show. Is there another way to hide text with cells that have a background pattern?
- Hui says:
  @Stephen
  Can you post a small sample file with an example?
  Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Stephen says:
Update: It appears the cell isn't refreshing. If I go into the formula bar and hit enter, the text appears, and it updates automatically everytime after that, unless I delete the text in Column A. I've checked the Excel Options to make sure it is calculating automatically. Can you think of another issue that would cause this?
- Hui says:
  @Stephen
  Have you setup your table as an Excel Table (Insert Table)?
  Once again can you post a sample file with an example?
Firoz says:
Hello All,
Can anyone help me to hide in between text...?
for example:
Once again can you post a sample file with an example?
    here i want to hide just "sample file"
so please help me out...
Thanks
JL says:

This is great but if I need to send to a client, they can still click on the cell with the missing info and find out what the value is.
micah says:
What about PARTIAL text within a cell, i only want a small section of data hidden, can this do it? When i select the text the "format cells" box only gives me the Font formatting, not the custom and one of the options under font formatting is NOT Hidden.
Comprende?
Venkatesh Kolluru says:
Hi I want to know how to hide the formulas in cell,but i want to see the result in the particular cell. if i edit the data relating to that formula in another cell it should be updated in the formula cell also.
Thank you
Venki.K
David Jeffery says:
I is a unayversity students. THANKING OF YOU! life solved, grate help x
Candace says:
Just curious if anyone knows.. when you put in a formula with the sum of multiple other cells how do you make the cell with the formula appear blank until there is a sum to fill it in (if its dollars rather than having $0.00 to just be blank)???
Thanks!
- Hui... says:
  @Candace
  Use a Custom Number Format
  Select the cell/s
  Ctrl 1 or Right Click, Format cells
  Number
  Custom
  enter #,##0.00;-#,##0.00;;
  in the Custom Type box
  Apply
  The gap between the consecutive ;'s causes Excel to display nothing for a 0 value
  Refer: http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/
6 Best charts to show % progress against goal | Chandoo.org - Learn Microsoft Excel Online says:
[…] By default color scales show the values too. To hide them use ;;; custom cell formatting code (how to). […]
Venkatesh says:
Very Nice....Working
hiso says:
"" does the same as ;;;
by way of excel trivia:-
typing in 'hide' or 'show' (without the quotes) gives an unexpected result if there is a number in the cell
YasserKhalil says:
Very good trick. Thanks for sharing
geetanjali says:
What do we do in a case where, with change in value of a cell, enables or disables the content in other different cells OR hide the contents of different cells. I neede a formula for that
Chetan says:
Thnks chandu You are superb!!!
Vibin says:
Based on changes in one particular cell, I need to hide or display another cell.
ie; if cell 'A4' is less than one or greater than 5, the contents of cell 'B4' need to hide.
Pls help
- Hui... says:
  @Viban
  The formula in cell B4 should be
  =If(or(A4 < 1,A4 > 5),"", Your existing B4 Formula goes here)
Rev. Christopher Seward says:
Using this method causes the data in the cell to be ignored by a filter. The cells are not included in the filter dropdown and when filter is applied.
Ankit Kumar saini says:
Hi
How Can I partially Hide A Email On Ms Excel
Example Like [email protected] to AB***f2gmail.com
www.yahoo.co.uk says:
Thanks for sharing your thoughts about excel howtos. Regards
ATUL GAMIT says:
hii...
i want to hide data/text in the cell if another cell is empty in the same raw
example: if cell "A1 is empty than data should be hide in cell C1
tapeka says:
GREAT TIP! EXTREMELY HELPFUL!
Dale says:
What causes the formula line to only show the last 2 characters when you select any cell?
nigel cowburn says:
Thanks Chandoo, I used this to control whether prices showed or not in a contract document, very, very useful.
Landscape Architect
Dunedin, NZ
Elisa says:
Hi Chandoo,
this is great, no VBA required, and with conditional formating it is very easy to show/hide the text!

How do I hide the contents of a cell in Excel?

Hiding Cell Contents.

Select the cell(s) to be hidden..

From the Home command tab, in the Cells group, click Format » select Format Cells... The Format Cells dialog box appears..

Select the Number tab..

Under Category, select Custom..

In the Type text box, type three semicolons ( ;;; )..

Click OK. The cells are now hidden..

Can you hide cells in Excel based on cell value?

One way is to use the built-in filter feature. To do this, first select the data that you want to filter. Then, click the Data tab on the ribbon and click the Filter button. In the drop-down menu that appears, click the column that you want to filter by and then uncheck the box next to the value that you want to hide.

How do you hide text in VBA?

In the last step, we will run the VBA code and hide our textbox..

A new dialog box called Macros will appear..

Now, select the Hide_Textbox option..

Afterward, click on the Run button to run this code..

How do I remove text from a cell in Excel VBA?

How to remove specific character in Excel.

Select a range of cells where you want to remove a specific character..

Press Ctrl + H to open the Find and Replace dialog..

In the Find what box, type the character..

Leave the Replace with box empty..

Click Replace all..