Skip to main content

Three methods quickly hide/mask cell contents with asterisk or other string in Excel

Author: Sun Last Modified: 2020-11-20

Sometimes, you may want to hide some cell contents while you sharing the workbook to other users as below screenshot shown. Here, this article will introduce three methods on masking cells with asterisk for hiding the contents in Excel.
doc mask cell 1


Hide and mask cell contents with asterisk with Format Cells

In Excel, to mask cell contents with asterisk, you can use the Format Cells function.

1. Select the cells you want to hide with asterisk, then right click to select Format Cells from the context menu.
doc mask cell 2

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

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

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

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.
doc mask cell 6

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.
doc mask cell 7


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:
doc mask cell 8

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.

The Encrypt Cells tool in Kutools for Excel can:

1. Encrypt cells with blank
2. Encrypt cells with characters
3. Encrypt cells with a specified string.

Kutools for Excel contains 300 advanced tools for solving your 90% Excel puzzles, and provides you a 30-day free trial.

After free installing Kutools for Excel (60-day free trial), please do as below steps.

1. Select the cells you want to mask, then click Kutools Plus > Encrypt Cells.
doc mask cell 9

2. Then in the popping dialog, type and confirm the password, then in Mask section, check the option as you need.
doc mask cell 10

3. Then click Ok. At present, all selected cells have been masked.

Check None, the cells display a string of gibberish.
doc mask cell 11
doc mask cell 12

Check Char option, you can type any character into the textbox, then the cells will display the character only.
doc mask cell 13
doc mask cell 14

Check String option, then type the string you want the cells display.
doc mask cell 15
doc mask cell 16

Tip: if you want to decrypt the cells or display the cell contents, you can click Kutools Plus > Decrypt Cells, then type the password to successfully decrypt.
doc mask cell 17


Other Operations (Articles) Related To Hidding

Hide rows based on value
This tutorial provides some methods on hiding rows based on the value (equal to/greater than/less than) in another column in Excel.

Hide part of text string
Sometimes, you want to hide part of string to protect the private information, such as phone number 123-xxx-xxxx, how can you do? In this article, you will find the easy solutions.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (5)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hola, yo lo solucione asi:
1. Formula: =SI(J7=1;lo que quieren mostrar;"******") y luego,
2. Macro: una macro que descargue el 1 en la celda J7.
3. Boton: un botón que ejecute la macro, puede llamarse mostrar cifrado o algo asi.

De esta manera, se logra solucionar las mascaras u ocultamiento de celdas.
This comment was minimized by the moderator on the site
Is it possible to create a mask email without the email account's password?
This comment was minimized by the moderator on the site
I suggest this:
A1 content               - Expected Resultfoo.bar@gmail.com - foo***********com

Formula:=LEFT(A1,3)&REPT("*",LEN(A1)-6)&RIGHT(A1,3)
Remove left or/and right and change the length to control what to replace with * and what to keep at the beginning or at the end.
This comment was minimized by the moderator on the site
If you want to hide only some parts of the string, here is another way:

=LEFT(A1,3)&REPT("*",LEN(A1)-6)&RIGHT(A1,3)
Explanation:
1. LEFT - Will add some letters from the left of the phrase.2. REPT - Will repeat *, n times. where n can be LEN(A1) or LEN(A1)-X where X is the number fo letters that you want to keep showing3. RIGHT - Will add some letters from the end of the phrase.
If A1 content is foo.bar@gmail.comThis formula will result in:
foo***********com
This comment was minimized by the moderator on the site
How do Mask baseon cell value :Sub tra5()Dim selrange As Range
Dim dgstring1, dgstring2, dgstring3, dgstring4 As String
dgstring1 = Sheet1.Range("F26")
dgstring2 = Sheet1.Range("F26")
dgstring3 = Sheet1.Range("F26")
dgstring4 = Sheet1.Range("F26")
dgstring = "dgstring1;dgstring2;dgstring3;dgstring4"
selrange.NumberFormatLocal = dgstring
End Sub

Please help.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations