How to Mask Data Input in Excel & Google Sheets

This tutorial demonstrates how to mask data input in Excel and Google Sheets.

 

 

What does it mean to “mask” data in Excel?

• Do you want to hide sensitive information?
• Or, do you want to apply a number format that can simplify data entry?

Click here to jump to the Google Sheets walkthrough.

Mask Data for Privacy

If you wish to mask data for privacy reasons, you can represent the data by a series of hashes or other symbols. Consider the following list of names and phone numbers. Say you want to mask the phone numbers so passersby can’t see them.

 

 

1. Highlight the phone number data cells, and then in the Ribbon, go to Home > Number. Click the Number Format drop down.

 

 

2. Scroll down to the bottom and click More Number Formats…

 

 

3. Click Custom, and then type in the input mask inside double quotes. In this example, for phone numbers represented by hashes, that’s “(###) ### ####”. Click OK to apply the data mask.

 

 

4. Your data is now formatted with a privacy mask.

 

 

Mask Data Input

When entering data such as time or date, it can be time-consuming to enter preformatted values including symbols like “:” or “/“. Therefore, you can create an input mask that allows a user to enter numbers only and then automatically format them.

Time

For example, if only numbers for a time are inserted, you want Excel to automatically format them as HH:MM. Say you want to create an input mask for a time in cell B2.

1. Right-click the cell where you want to create a mask input (here, cell B2), and choose Format Cells…

 

 

2. 2. In the Format Cells window, choose Number > Custom, enter #”:”00 in the Type box, and click OK.

 

 

Now, you can enter numbers only for the time (here, 450), and Excel formats it as 4:50.

 

 

As you can see, this cell has a custom format. Therefore, it won’t be recognized as a time format by Excel.

Date

In a similar way, you can also mask data input for dates. In this case, each entry should be only numbers (for example, 02282021), and you want Excel to display it as a date (02/28/2021). To do this, repeat the steps above, but for Step 2, type in the following: 00″/”00″/”0000.

 

 

Now, you can enter 02282021 in cell B2, and it is displayed as 02/28/2021.

 

 

Mask Data Input in Google Sheets

You can also mask data input for times in Google Sheets.

1. Select the cell where you want to create a mask input (here, cell B2), and in the Menu, go to Format > Number > More Formats > Custom number format.

 

 

2. In the number format box, enter #”:”00 and click Apply.

 

 

The result is the same as in Excel. If you enter 450 in cell B2, it is formatted as 4:50.

 

 

Using the steps above, you can also mask data input for dates by entering 00″/”00″/”0000 as a custom number format. Or mask for privacy with, for example, the format “(###) ### ####”.