2. Lock the header row: click the View tab above Excel, then click the freeze window and select Freeze the first row to lock the header row.
3. Find duplicate values: first select the range to be duplicated, then find the conditional format in the Start tab, click the highlight cell rule and select duplicate values.
4. Delete duplicate values: click Delete Duplicate Items in the Data tab to delete duplicate values within 1 sec.
5. Hide the value of 0: Click the option in the file tab, select the advanced column, drop down and find "Show zero in cells with zero value" and check the "check mark".
6. Hide all cell values: select the cell to hide, click Cell Format in the right-click pop-up window, and enter three ";". In the user-defined type column. No. I will.
7. Fill in the date by month: enter the date XXXX/XX/XX in the cell, and then press and hold the fill handle to fill in the date. After filling, you can change the filling format in the pop-up tab in the lower right corner.
8. Enter "0000 1" in the cell: after selecting the area, click "Cell Format" in the right-click pop-up window and enter "0000" in the custom type column (you can add several zeros before the number by entering several zeros).
9. Merge the contents of multiple cells: Enter the following formula "= an &；; BN&CN "(N stands for the row to be merged and ABC stands for the column), so that the previous contents can be merged into the new cell.
10. To prevent duplicate entry: select data validity in the data tab, then set it as user-defined, and then enter "=COUNTIF($ 1:$65535, A 1)= 1" in the formula. 1 1. Convert a formula into a numeric value: first copy the cell with the formula, then right-click the cell to open the pop-up window, click Paste Special, and select Numeric Value and Numeric Format from the paste options.
12, ten thousand yuan display: enter "= a110000 &" ten thousand yuan ",and then press" Ctrl+ ENTER "to fill in other cells.
13, decimal to integer: take column A as decimal data and column B as data to be adjusted to integer. Round and round:b 1 enter =int(A 1), round up:b 1 enter =roundup(A 1, 0), round down:b 1 enter = rounddown.
14. Quickly insert multiple lines: circle any multiple lines to be inserted, then right-click to open the pop-up window, select "Insert" in the pop-up window and insert blank lines.
15, fastest sum: circle the area to be summed, and press the shortcut key "Ctrl+=" to sum.
16. Quickly enter today's date: press the shortcut key to quickly insert the current date.
17. Quickly enter the current time: press the shortcut key to quickly insert the current time.
18. Hide the area without data: press the shortcut key "Ctrl+Shift+ Right Arrow" to select all the columns on the right, and then click "Hide". Press the shortcut key "Ctrl+Shift+ Down Arrow", select all the columns below, and then click "Hide".
19. Add file password: click "Protect Workbook" in the file tab, and then select "Encrypt with password".
20. Hide the worksheet interface: click the View tab above and select Hide.
2 1. Two columns are interchanged: hold down the shift key on the keyboard and put the mouse in the border of the first column of numbers, and the pointer will become a small icon of "moving". Then click the left mouse button and drag the contents of the first column to the cell list on the right side of the second column. When dragging, keep the cell formats of the two columns consistent.
22. Filter of merged cells: after copying the merged cells to another blank cell, cancel the merging of the original merged cells, press the shortcut key F5 to open the positioning condition, with null value =A2, Ctrl+ ENTER, and finally copy the format of the original merged cells back, and then perform "Filter" to display all the data.
23. Quickly adjust the most appropriate column width: select all the entire columns to be adjusted (columns with letters at the beginning), place the mouse at the end of the last column, and double-click with the left key.
24. Make a drop-down menu: click Validity in the Data tab, select the order in the Allow tab, check Ignore Null Value and provide a drop-down arrow, and set the drop-down contents in the Source tab to be separated by English commas.
25. Customize Quick Access Toolbar: Click Options in the File tab to open Excel options, and click Quick Access Toolbar to set them.
26. Customize the ribbon tab: customize the quick access toolbar: click Options in the File tab, open the Excel options, and then click Customize Ribbon to make settings.
27. Add "Development Tool" tab: Select "Custom Ribbon" in the right-click window of the blank area of the toolbar, then select "Main Tab" in the custom ribbon and check "Development Tool".