How to Mask Data Input in Excel & Google Sheets

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

 

 

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 will allow 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. In the Format Cells window, (1) choose Number > Custom, (2) enter #”:”00 in the Type box, and (3) click OK.

 

 

Now, you can enter numbers only for the time (here, 450), and Excel will format 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, and in Step 2, type in the following: 00″/”00″/”0000.

 

 

As a result, 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.