Practical Excel skills sharing: examples of using shortcut keys [Ctrl+E]

There are many Excel shortcut keys, but this pair is definitely the most magical. It is a pair of shortcut keys that can "self-learn". No need to write functions or record macros. As long as you give it a sample first and manually process a piece of data, it can complete the rest according to your method. When processing forms, with it, the efficiency can definitely be improved a hundred times.

Note: This shortcut key is only suitable for Excel2013 and above versions. It cannot be applied to all versions of WPS.

1. Extract the date of birth from the form

As shown below, we have the employee’s ID number and need to extract it from the ID number The date of birth is inserted into the year and month of birth column. The common approach is to use the function MID to intercept the 7th to 15th digits of the ID number.

Use the Ctrl E shortcut key to manually fill in the birth date of the first person, and then press "Ctrl E" to automatically fill in the rest. It only takes a second to extract everyone’s date of birth.

2. Add symbols in batches

For the following table, we need to add symbols to each The title number is added to the book title, for example, the biography of Steve Jobs is changed to "The Biography of Steve Jobs".

Adding them one by one is of course a thankless job. It is very easy to miss or fill in the wrong form accidentally. Using the shortcut key "Ctrl E", we only need to manually add the title number to the first book title, and then hold down "Ctrl E" to modify all the book titles.

##3. Information splitting

In the following table, we need to split the The information in one column is split into two columns, department and name. In the same way, using ordinary methods, Excel's column function can achieve the goal, but the process is still too cumbersome, and many people have not used the column function.

Use "Ctrl E" to quickly complete the splitting of information. At this time, it must be automatically filled column by column. First fill in the first one in the department column, and then press "Ctrl E" to automatically fill in the department column; then fill in the first one in the name column, and then press "Ctrl E" to automatically fill in the name column. Note that multiple columns cannot be autofilled at the same time. That is, if you fill in the department column and the first name column at the same time, and then hold down "Ctrl E", you will not be able to automatically fill in both columns at the same time.

4. Merge cell contents

Ctrl E can not only split cells information, and can also be operated in reverse to merge the contents of multiple cells. As shown below, the information in the first and second columns needs to be merged and filled in the third column.

We only need to fill in the relevant content in the first cell, and then hold down "Ctrl E" to complete the filling in other content. "Ctrl E" not only realizes the splicing of the contents of two cells, but also allows us to output relevant content in the format we like. For example, in cell C2, we finally output "Xiao Wang's performance is 87", which is not only the splicing of cell A2 and cell B2, we inserted the three words "performance is" to connect cell A2 and cell B2. So in actual work, you can output relevant content according to your own preferences.

##5. SummaryCtrl E shortcut key is a pair with self-learning ability The shortcut keys only need to provide a sample to complete the remaining operations. Think of it as a function that automatically records a macro and applies it. For any regular operations, such as merging data, splitting data, formatting data, adding prefixes or suffixes in batches, adjusting the order of cell content, and extracting data, you may try Ctrl E. You may be able to complete all the work in one second. . Why do you need to work overtime?

Related learning recommendations:

excel tutorial

The above is the detailed content of Practical Excel skills sharing: examples of using shortcut keys [Ctrl+E]. For more information, please follow other related articles on the PHP Chinese website!