If you want to type a great many repeated data in an Excel file like the information of gender, department, etc. There’s a quick way in addition to add drop-down list, which allows you to enter just a simple number and make it shown as specific word or text. Let’s see the detailed steps.
Assuming that I need type-in the gender of all employees in Excel, should I input “Female” or “Male” in every cell constantly?
It’s not necessary in deed.
1. As the table below, you can select all the cells in Gender column and press [Ctrl+1] (or right-click the selected area and choose Format Cells… in the menu).
2. In the popping out Format Cells window, select Custom in the list of Category, then you can input or copy the formula [=1]”Female”;[=2]”Male” to the textbox below Type. It means when you type “1” this cell the text will be displayed as “Female”, type “2” and it will show as “Male”. You can of course change the formula to suit your own needs.
Note: the formula (including all the marks in it) must be typed in half-angle correctly.
3. Hit OK to confirm the custom setting. Then you can check the result in the worksheet.
Type “1” in the cell B2 and press Enter or click anywhere outside the cell, then “1” will be converted into “Female” immediately.
Type “2” and it will show as “Male”.
In this way you can type repeated content in Excel very conveniently.