Excel Practical Skill: Quick Entry Sex

When you use Excel to count information about personnel, you often need to enter the gender of everyone. If the step-by-step input, data, if more, then whether the use of wubi input or pinyin input, the "male", "female" input will appear to be more trouble. Is there any way to make us steal a little bit lazy, so that the gender input problem is not so boring? The following few small tricks, perhaps can let you get rid of the trouble, better try?

First, custom format

Select the range of cells you want to enter, right-click, and click Format Cells in the pop-up menu to open the Format Cells dialog box. Click the Number tab of the dialog box, select Custom in the Category list on the left, and then enter "[= 1]" In the Type input box on the right; [= 2] "female", as shown in Figure 1. When you are sure, just enter the number "1" in these cells, and it will appear as "male", enter the number "2" and show as "female." In this way, it is much simpler to enter a number than to enter a Chinese character.

Figure 1 (click to see larger image)

This way, the screen shows the "Male", "female", however, in the formula bar can be seen, is still "1", "2".

Second, find replacement

This effect can also be achieved by using a find-and-replace method. In the input, "male" is entered "1", and "female" is entered "2". When all of the input is complete, select the range of cells, and then press the "ctrl+f" shortcut to open the Find and Replace dialog box. Click the "Replace" tab, enter "1" in the Find What entry box, enter "male" in the "Replace with" input box, and click the Replace All button to replace all number "1" in the selected area with "male". In the same way, replace "2" with "female" and you are done.

Figure 2 (click to see larger image)

This method input only needs to enter the number, convenient and quick. The replacement can be replaced with the corresponding text, the screen display and the actual content is also consistent, compared to the first method can avoid some due to screen display and the actual content of the problem caused by inconsistencies.

Third, the formula set

This method requires the addition of a secondary column. For example, the gender input should be in the D2:d100 cell range, and we use column C as the secondary column. When you enter, the corresponding input process is completed in column C. Still is "male" is "1", "female" is "2". You can also enter "1" only when you should be "male", but you do not need to enter it when you are "female". When all is done, enter the formula "=if" (c2=1, male, female) in cell D2, and then drag the fill handle of the D2 cell to the D100 cell. Release the mouse to get what you need, as shown in Figure 3. We can select the C column cell range, right-click, and then click "Hide" in the pop-up menu to hide the C column.

Figure 3

This method seems to be troublesome, but in the input we can only input a number, it adds up, from the whole process, it will save a lot of time and effort.

All right, here are three ways. Which one do you fancy?