Many friends use Excel to process tables, and they will encounter various problems in the process of using them. Below we have compiled 25 practical Excel tips for you, hoping to help you.

1. Find the position corresponding to the same value in two columns of data

=MATCH(B1A:A,0)

2. Know the formula to get the result

Definition Name=EVALUATE(Sheet1!C1)

Formula with known result

Definition Name=GET.CELL(6Sheet1!C1)

3. Forced line break

Use Alt+Enter

4. More than 15 digits input

Too many people have asked this question, so let’s put it away. 1. The cell is set to text; 2. Enter ‘ before entering the number

5. If column B is hidden, if it is displayed

Select columns A to C, right-click, and unhide

Select columns A to C, double-click to select any column width line or change any column width

Move the mouse between columns AC and drag it when the mouse turns into a double vertical line.

6. Exchange of ranks in EXCEL

Copy, Paste Special, select Transpose, and OK

**7. How is Excel encrypted?**

(1), when saving, you can save as >> “Tools” in the upper right corner >> General >> Settings

(2), Tools >> Options >> Security

8. About COUNTIF

The COUNTIF function can only have one condition, such as greater than 90, it is =COUNTIF(A1:A10″>=90″)

Subtract between 80 and 90 is =COUNTIF(A1:A10″>80″)-COUNTIF(A1:A10″>90″)

**9. Extract date of birth based on ID number**

(1), =IF(LEN(A1)=18DATE(MID(A174)MID(A1112)MID(A1132))IF(LEN(A1)=15DATE(MID(A172)MID

(A192)MID(A1112))”Wrong ID number”))

(2), =TEXT(MID(A276+(LEN(A2)=18)*2)”#-00-00″)*1

10. Want to fully reference the data input by SHEET1 in SHEET2

Workgroup, hold down the Shift or Ctrl key, and select Sheet1, Sheet2 at the same time

11. Do not enter repeated numbers in a column

[Data]–[Validity]–[Custom]–[Formula]

input=COUNTIF(A:AA1)=1

If you want to find repeated numbers

Conditional Formatting “Formula” =COUNTIF(A:AA5)>1 “Format selection red

12. Can’t you open a spreadsheet file directly?

Find the .XLS file in “Folder Options” – “File Type”, and confirm whether there is a parameter 1% in “Advanced”. If not, please add it manually

13. Implementation of excel drop-down menu

[data]-[validity]-[sequence]

14. 10 columns of data are combined into one column

=SUM(OFFSET(\$A\$1(ROW()-2)*10+1101))

15. Find two data formulas (basic search function is VLOOKUPMATCH)

(1), find the corresponding result according to the two conditions of the row and column

=VLOOKUP(H1, A1: E7, MATCH(I1, A1: E1, 0), FALSE)

(2), find the corresponding result according to the data matching two columns (for the array formula)

=INDEX(C1:C7, MATCH(H1&I1A1:A7&B1:B70))

16. How to hide 0 in cells

Cell format custom 0;-0;;@ or Options > View > Zero Value to uncheck. Oh, if you use a formula, it depends on the situation.

17. Combined calculation of cells of multiple worksheets

=Sheet1!D4+Sheet2!D4+Sheet3!D4, better =SUM(Sheet1:Sheet3!D4)

18. Get the worksheet name

(1), define the name: Name

=GET.DOCUMENT(88)

(2), definition name: Path

=GET.DOCUMENT(2)

(3) Enter =CELL(“filename”) in A1 to get the path-level file name

Enter in the cell where you need to get the file name

=MID(A1FIND(“*”SUBSTITUTE(A1″””*”LEN(A1)-LEN(SUBSTITUTE(A1″”””))))+1LEN(A1))

(4), custom function

PublicFunctionname()

DimfilenameAsString

filename=ActiveWorkbook.name

name=filename

EndFunction

**19. How to get the maximum number of days in a month**

: “=DAY(DATE(200231)-1)” or “=DAY(B1-1)” B1 is “2001-03-01”

What formula should be used for the sum of items containing a certain character in the data area

=sumif(a:a”*”&”a character”&”*”data area)

The last line is the text:

=offset(\$b\$1MATCH(CHAR(65535)b:b)-1)

The last row is a number:

=offset(\$b\$1MATCH(9.9999E+307b:b)-1)

Or: =lookup(21/(b1:b1000<>””)b1:b1000)

In the judges’ scoring, how to remove more than two highest scores and two or more lowest scores, and find the average score of the remaining staff and display the removed scores.

See the trimmean() function for help.

Dropped scores:

Maximum two: =large(data{1;2})

Minimum two: =small(data{1;2})

How to easily judge the last digit is a letter

right(a1)*1

wrong letter

=IF(ISNUMBER(–RIGHT(A11))”number””letter”)

=IF(ISERR(RIGHT(A1)*1)”letter””number”)

How to set a cell to not accept characters containing spaces

Select column A

data – validity – custom – formula

=iserror(find(“”a1))

Data–Validity–Custom–Formula

=len(a1)=len(trim(a1))

The original function is =a1+a4+a7+a10+a13+a16+a19+a22…..

Now it is =sum(n(offset(a1(row(1:10)-1)*3)))

Reference data in other worksheets in one worksheet, but the referenced worksheet is not fixed, and the corresponding work is automatically selected according to the worksheet name I enter

How to quote the data in the table in the formula

=INDIRECT(“A1″&”!”&”E1″)A1 is the worksheet name

Sum of odd rows = SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000)2))

Summation of even rows = SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000)2)))

View the number of characters in a string

=LEN(A1)

Find the number of non-empty cells

The data calculated by the formula cannot be used by COUNTA (otherwise the null characters are also calculated)

=COUNTIF(\$E\$3:\$E\$65536″*”)

The dynamic sum formula sums from cell A1 in column A to the cells in the row before the current row.

=SUM(INDIRECT(“A1:A”&ROW()-1))

**20. A relatively easy-to-use EXCEL document repair tool**

ExcelRecovery

21. EXCEL square root operation

To raise 8 to the third power, you can use this formula, enter =8^(1/3) in the cell

22. The data in the cells is scattered and aligned

Text format “full-width input

23. Find links in worksheets

Ctrl+~ or Edit” link

24. How to automatically fill empty cells with 0

Select the area to be changed > Find > Empty > Replace > 0

25. Convert numbers in Word to Excel

There are many methods, select “Copy” set the input cell as text “Paste Special” value

Select “Convert table to text” Paste “Columns” and set the column options to text

Save as a text file, open a text file in EXCEL, and set the corresponding settings in the import text dialog box

Recommended reading: