Three methods quickly hide/mask cell contents with asterisk or other string in Excel
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.
- Hide and mask cell contents with asterisk with Format Cells
- Hide and mask cell contents with asterisk with VBA code
- Encrypt and mask cell contents with asterisk or other string
- Other relatived articles (operations) about worksheet
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.
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.
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.
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 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.
2. Then in the popping dialog, type and confirm the password, then in Mask section, check the option as you need.
3. Then click Ok. At present, all selected cells have been masked.
Check None, the cells display a string of gibberish.
Check Char option, you can type any character into the textbox, then the cells will display the character only.
Check String option, then type the string you want the cells display.
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.
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.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
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!
