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: