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.
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.
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.