17 Excel skills, will be 10 will be used Excel, will be the last 2 is a master

In the actual operation and application of Excel, there are many handy skills knowledge, if you can master each one, the work will be greatly improved ...

First, quickly fill the discontinuous cells.

Method:

1. Select the target cell (including the filled cell).

2. Shortcut key Ctrl+g Open the Position dialog box-position condition-null value-OK.

3, enter the formula: =A2 (A2 positioning method, the upper left corner of the empty cell of the previous row of cell address).

4, Ctrl+enter fill.

Second, merge cell sum.

Method:

1. Select the target cell (or cells).

2, Input formula: =sum (C3:C12)-sum (D4:D12).

3, Ctrl+enter Fill.

Third, the rapid subtotal summation.

Method:

1. Select the target cell (or cells).

2. Ctrl+g Open the Position dialog box, locate condition-null value-OK.

3, shortcut key: alt+= Quick summation.

Four, multi-line multi-column quick summation.

Method:

1. Select the target cell (or cells).

2, alt+= fast summation.

Enter the circled number quickly.

Method:

1. Select the target cell (or cells).

2, Input formula: =unichar (9311+row ()-2) (minus 2 is to correct the starting sequence number, can be changed according to actual needs, of course, can also be omitted).

3, Ctrl+enter Fill.

Enter the check mark or the wrong number with the box quickly.

Method:

1. Set the font of the target cell to: Wingdings 2.

2. Enter the uppercase R or S (R is the checkmark, S is the wrong number).

Seven, quickly generate drop-down menu.

Method:

Press alt+↓ (down arrow) in the target cell.

Interpretation:

This method is only used for a quick fill of a simple drop-down menu, provided that the preceding row has content, and that it shows only what has been entered in the forward line.

Eight, quick contrast, marking skills.

Method:

1. Select the target cell (or cells).

2. Shortcut key: Ctrl + (back slash).

3. Fill or label color.

Nine, fast for the players to score.

Method:

1. Select the target cell (or cells).

2, Input formula: =trimmean (B3:K3,2/10).

3, Ctrl+enter Fill.

Interpretation:

Formula: 2 in =trimmean (B3:K3,2/10) remove a top score and a minimum score, and 10 are the number of judges. Change according to actual needs.

X. Convert Arabic numerals to uppercase numbers quickly.

Method:

1. Select the target cell (or cells).

2, the Sogou input method in the state of the first input V, followed by the input target number.

3. Select the actual format you want.

Xi. hide the cell phone number in the middle of 4 digits.

Method:

1. Select the target cell (or cells).

2, enter the formula: =replace (b3,4,4, "* * * *").

3, Ctrl+enter Fill.

12. Convert the date to a week.

Method:

1. Select the target cell (or cells).

2, Input formula: =text (B3, "AAAA"), =text (B3, "AAA").

3, Ctrl+enter Fill.

13. Calculate the number of working days in the middle of a two date.

Method:

1. Select the target cell (or cells).

2, input formula: =networkdays. INTL (B3,C3,11,D3:D6).

3, Ctrl+enter Fill.

Interpretation:

1, the formula in the parameter 11-bit statistical method, the representative only statistics Sunday. D3:d6 is a break date.

14: Fast calculation of National Day countdown.

Method:

1. In the target cell, enter: =date (2018,10,1)-today ().

2, Ctrl+enter fill.

Quickly populate the workday.

Method:

1. Enter the start date.

2. Drag the fill handle to fill.

3. Click the arrow under the Fill handle and select Fill Day.

16. Quickly display the current date and time of the system.

Method:

1. Current date: Ctrl +; (semicolon).

2, the current time: ctrl+shift+;.

17, fast rounding.

Method:

Enter the formula in the target cell: =round (b3,1).

Interpretation:

The 1 in the formula is the number of digits reserved for decimals.

17 Excel skills, will be 10 will be used Excel, will be the last 2 is a master