Office hacks: fast and accurate Excel data entry has a trick

We often enter a variety of data in the Excel worksheet, if you need to enter more content in the row, it is easy to look at the wrong row; need to enter more duplicate data, it will waste time. We can choose a more appropriate method of data entry according to the type of data.

○Multiple ranks of data, use the “data sheet” entry

For the problem that it is easy to look wrong when entering data due to many ranks, such as a community personnel statistics report, there are many items to be filled in, in this case, we can pull out the record sheet for data entry.

Click “File → Options” in the window that opens switch to “Quick Access Toolbar” in the right pane, select “Commands not in the ribbon”, which will be Add the “Record Sheet” to it (Figure 1).


Return to the Excel interface, select the data in A1:Q1 (that is, the need to fill in the project line), and then click the above-mentioned add “record sheet”, at this time will pop up a vertical arrangement of the record sheet, the original long line of project content will become a vertical arrangement, according to the prompts to enter the required content can be. Click “New” after completion, you can continue to fill out (Figure 2).



You can also filter the data entered by clicking the “Conditions” button in the “Record Order” dialog box. For example, in the above entry case, if the user’s age has already been entered in advance, users who are now younger than 16 do not need to enter other information, just enter “>16” in the “Age” field of the record sheet, and when you click ” Next”, users younger than 16 years old will be skipped automatically.

○Decimal data Input integer to add decimal point automatically

Many times, we need to enter data with a decimal point, such as financial statistics in the entry of each amount needs to be accurate to the minute, the conventional method is to enter the whole number and then enter the decimal point, which is prone to error. If you need to enter a lot of such data in the document, you can automatically enter the decimal point through Excel settings.

Click “File→Options”, select “Advanced” option in the left pane of the opened window, check the “Edit Options” column in the right pane “automatically enter the decimal point” check box, in the “decimal places” increment box, enter “2”, click “OK” after the settings are complete ” (Figure 3).


This returns to the Excel window, when we need to enter a number similar to ¥ 213.56, just type 21356 and enter, it will automatically add a decimal point to 213.56. If you are entering an integer, you should add two more 0s after the number, such as 100 should be entered 10000. here to note that after the data entry, please restore the original settings (Figure 4).


○Partial duplicate data Auto fill input

For duplicate data, for example, in the statistics of 4K UHD TV products in a shopping mall, the product name should be entered as “xx brand 4K UHD TV”, and the duplicate input is “4K UHD TV”, here you can use the custom formatting Here you can use the custom format method to achieve fast automatic input. For example, you need to enter the product name in the A2:A20 area contains the above words, select the above area and right-click and select “Set Cell Format”, in the window that opens switch to “Custom”, in the right pane enter “@” “”4” “K” “super” “high” “clear” “electric” “visual”” (each character is marked with double quotation marks, a half-space in front of the product name and 4K separated), click “OK” to exit (Figure 5).


Thus, when we need to enter the product name in the A2:A20 area, such as “Sharp H65 4K UHD TV”, just enter “Sharp H65” and enter, and it will automatically change to the required data. The input effect of other brand products is similar (Figure 6).



You can set different custom formats for different areas of the cell, for example, the above example needs to set more repeated product names, then you can set different custom formats in C1, D1 in turn, such as “@” 8 “K” “super” “high” “clear” electric” “vision” “,” “liquid “”crystal”” “display” “display” “device””, and then use the “format brush” to quickly apply to the specified cell area can be.

○Non-adjacent duplicate data Locate bulk input

In our daily work we often need to enter duplicate data in some non-adjacent cells. For example, in a company’s product sampling table, for the inspection results have been marked “qualified” or “unqualified”, no conclusion is to be uniformly marked as “pending” and use the yellow background color to fill (Figure 7).


For this need to enter duplicate characters in non-adjacent cells, you can use F5 positioning batch input. selected B2: B37 region, press the F5 key and select “positioning conditions → empty value”, so that all the results will be selected without entering the empty cell, click the fill tool to set its background color to yellow, select any empty cell, in the address bar, type “to be determined “, press the “Ctrl + Enter” key combination, you can quickly complete the input (Figure 8).


Leave a Comment