Sharing practical Excel skills: Do you know how to use these 5 shortcut keys?

In the previous article " Practical Excel skills sharing: Make a dynamic inquiry form for employee information! 》, we learned about the practice of dynamic employee information query table, and today we talk about Excel shortcut keys. In fact, many operations in Excel can be completed with a shortcut key, but when I was young, I scratched my head and wrote complex function formulas to complete it. The following article will take you through 5 celebrity shortcut keys so that you will no longer take detours!

1.C position debut——Ctrl E

Ctrl E is the shortcut key for quick filling. It intelligently determines the relationship between the output result and the reference system (adjacent or non-adjacent columns) based on several output examples that have been input, and fills the cells below the output examples with the same rules based on this relationship.

For example, we want to convert a series of continuously arranged text (such as "Civic-Dongfeng Honda") into a model and manufacturer that can be filled in in separate cells. form.

# Such operation requirements, before Ctrl E, we may need to use complex nested formulas of multiple text functions to barely complete it.

PS: The formula in cell B2 in the picture is =LEFT(A2,FIND("-",A2)-1)&CHAR(10)&MID(A2 ,FIND("-",A2) 1,50), This is a nested formula with LEFT function, FIND function, CHAR function and MID function. It is not the focus of this article. Interested petals can study it by themselves.

But now, let Crrl E help you do it easily.

Operation Instructions:

Step 1 Enter "Output Example": In the first cell B2 of the output column area, fill in the content in cell A2 in separate rows ;

Step 2 Quick filling: Select the single-column area B2:B7 that needs to be filled, and click Ctrl E to complete the filling. The final effect is as follows:

Of course, Ctrl E's ability is not just as simple as arranging text, but also its special ability to extract numbers from irregular strings.

Operation Instructions:

#Step 1 Enter "Output Example": Manually input in cell B2 to enter from cell A2 The proposed number is "25000";

Step 2 Quick filling: Select the single-column area B2:B7 that needs to be filled, and click Ctrl E to complete the filling.

2. Sharp eyes——Ctrl G

Whether it is constants, formulas, or empty values, it is visible Ctrl+G can identify cells or row and column differences at a glance. Many little petals probably still don’t understand. Precise positioning is to quickly find and select all cells that contain specific types of data (such as formulas). What’s the use of it? Hehe, I don’t know. The excitement has just begun. Let me show you a batch of blank lines inserted!

Operation Instructions:

Step 1 Insert the auxiliary column, and paste the segmented standard column area as the inserted row one row apart, as shown in the picture of the province , paste A2:A14 into B3:B15;

#Step 2 Select the two columns of the same area A3:B14 before and after pasting, click Ctrl G, the [Position] dialog box will pop up box;

Step 3 Click the [Positioning Conditions] button to pop up the [Positioning Conditions] dialog box;

Step 4 Check [Row Content Difference Cells (W)] and click [OK] to complete the positioning of the difference content.

Step 5 Click [Start]-[Insert]-[Insert Worksheet Row] to complete batch insertion of empty rows;

Step 7 Then delete the auxiliary column inserted in step 1.

Why did Xiaohua choose the example of batch inserting rows to demonstrate the use of Ctrl G? This is because of its reverse operation, batch deleting blank rows is also a common Excel problem. What's more, this problem can also be solved using Ctrl G. We do this by locating the null value and then deleting the row.

Operation Instructions

Step 1 Select the column containing empty rows. It should be noted here that the column contains no empty rows except the empty rows. All valid cells are not empty;

Step 2 Press Ctrl G at the same time, select the positioning condition to be a null value, and complete the positioning of empty cells;

Step 3 Click [Start]-[Delete]-[Delete worksheet rows] to complete batch deletion of rows.

3. Bomber——Ctrl Enter

The road to growth is very bumpy, and I fight every day until I collapse. . I can't warm up my bed after get off work because I don't know Ctrl Enter. How to sum the merged cells? This is a problem we often encounter in our daily work. It is obviously a waste of time to enter formulas one by one, but we cannot drag and fill the formulas. What should we do? Let Ctrl Enter come to your rescue.

Operation instructions:

#Step 1 Select the merged cell range D2:D14 where you want to enter the sum formula, and enter the following formula

=SUM(C2:$C$14)-SUM(D3:$D$14)

##Step 2 Press Ctrl at the same time Enter to batch-fill the formula into each merged cell to complete the sum.

Formula description:

This is a back-extrusion formula. To understand it, you need to start from the last merged cell

D11 :D14Start watching.

Since C14 and D14 in the formula are both locked, the formula for D11 is

=SUM(C11:$C$14)-SUM(D12:$D$14). Since D12, D13 and D14 are all merged invalid cells, Ctrl Enter will not fill the formula into these cells, so they are all empty cells. So the formula of D11 is equivalent to SUM(C11:$C$14), which is the real summation formula of the merged cells.

For the penultimate merged cells D9:D10, the valid formula for cell D9 is

=SUM(C9:$C$14)-SUM(D10:$D$14). Since only D11 among D10:D14 is not empty, the formula of D9 is equivalent to SUM(C9:$C$14)-D11. Since D11=SUM(C11:$C$14), the formula can be further derived as SUM(C9:$C$14)-SUM(C11:$C$14), also It is equal to SUM(C9:C10), which is the real summation formula of the merged cells.

By analogy, the summation result of each merged cell can be obtained.

4. Paste across lines - Ctrl R

The golden combination of Ctrl C and Ctrl V can handle almost all copy and paste tasks. But once they encounter discontinuous areas, this combination becomes unable to achieve their goals. A common situation is to paste horizontally across rows after filtering. The result of Ctrl V is to paste discontinuous area values ​​​​in continuous areas, destroying the original correspondence between data, leading to errors.

At this point we need Ctrl R to complete batch cross-line pasting to maintain efficiency.

Operation Instructions

Step1 Select the drop-down menu in column B to filter out the lender.

Step 2 Press Ctrl to select columns C and E, copy the data source in column C, and paste the target cell in column E.

#Step 3 Press Ctrl R at the same time to complete pasting across columns. At this time, the data is pasted horizontally one by one.

#Step4 Then filter column B as debit, select the data in column C and the blank cells in column D, and press ctrl R to complete the paste.

#Step5 Finally, display all the contents of column B and you can see the following results.

5. Old trees bloom new flowers - Ctrl H

You will use find and replace ? Do you use wildcards to perform fuzzy search and replace? Would you use cell matching to accomplish exact replacement? Will you change the find and replace scope to workbook to replace all symbol conditional contents in the workbook at one go? Do you find and replace based on format? ...

If you don’t know the above functions, you may need to learn more about this familiar stranger. But if you are already proficient in these common usages, don’t be complacent, because below I will share the technique of batch format brushing of cells that meet the conditions. Are you sure you can use it?

Operation Instructions

Step 1 Select the starting cell F2, double-click the [Start] tab-Format Painter button

Step 2 Press Ctrl H, enter the search conditions, and click the [Search All] button

Step 3 In Press Ctrl A in the search results list to complete the batch format brushing.

The final effect is as follows:

Related learning recommendations: excel tutorial

The above is the detailed content of Sharing practical Excel skills: Do you know how to use these 5 shortcut keys?. For more information, please follow other related articles on the PHP Chinese website!