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 “:” or “/”. Therefore, you can create an input mask that will allow a user to enter only numbers and then automatically format them to a wanted format.

Time

For example, if only numbers for a time are inserted, Excel will automatically format them to HH:MM format. 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 Custom category, (2) enter #”:”00 in the Type box, and (3) click OK.

 

 

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

 

 

As you can see, this cell has 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 Excel will 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

Similar to Excel, 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 toolbar, 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.