36 Excel tips

36 excel tips

1. Divide a column of data by 10000 at the same time

Copy 10000 In the cell where it is located, select the data area - select sticky paste - except

2. Freeze row 1 and column 1 at the same time

Select the intersection of the first column and the first row Corner position B2, window-freeze pane

#3. Quickly convert formulas into values

Select the formula area-press the right button and drag to the right and then drag Back - Select to keep only numerical values.

4. Display all formulas in the specified area

Find = and replace it with "=" (space = sign) to display it in the worksheet All formulas

5. Edit all worksheets simultaneously

Select all worksheets and edit directly, all worksheets will be updated.

6. Delete duplicate values

Select data area-data-Delete duplicate values

7.Display duplicate values

Select the data area-Start-Conditional Formatting-Display Rules-Duplicate Values

8. Convert text numbers into numerical values

Select the text number area, open the green triangle of the upper left corner cell, and select Convert to Numeric

9. Hide cell content

Select the area to be hidden - Set cell format - Number - Customize - Enter three semicolons;;;

10. Add password to excel file

File-Information - Protect the workbook - Encrypt with password

11. Add a password to the cell range

Review - Allow users to edit the range - Add a range and set the password

12. Paste the contents of multiple cells into one cell

Copy the area-open the clipboard-select a cell-in Click the copied content in the clipboard in the edit bar

13. View two worksheets of an excel file at the same time

View-New Window-Reset All Rank

#14. Enter the fraction

Input 0 first, then enter a space, then enter the fraction

15. Force a line break

Press alt and Enter after the text to change to the next line

16. Delete the empty line

Select column A-Ctrl g to open the positioning window-position Condition: Null value - Delete the entire row

#17. Insert empty rows in alternate rows

Drag and copy 1~N next to the data table, and then Then copy the serial number below and sort by the serial number column.

#18. Quickly find the worksheet

Select the worksheet you are looking for in the right-click menu of the progress bar.

19. Quick filter

In the right-click menu - Filter - Filter by the selected cell value

20. Let the PPT chart update synchronously with excel

Copy the chart in excel-in the PPT interface-paste selective-paste link

21. Hide formula

Select the area where the formula is located - set the cell format - Protect: select Hide - protect the worksheet

22. Set the row height in centimeters

points "Page Layout" button in the lower right corner, the row height unit can be centimeters

23. Keep the row height and column width unchanged when copying

Select the entire row to copy , select "Keep column widths" after pasting.

24. Enter a number starting with 0 or a long number exceeding 15 digits

Enter single quotes first, and then enter the number. Or format it as text first and then enter it.

25. Display all long numbers exceeding 11

Select the number area - set the cell format - customize - enter 0

26. Quickly adjust the column width

Select multiple columns and double-click the edge to automatically adjust the appropriate column width

27. Quickly add new charts Series

Copy and paste to add a new series to the chart

28. Set a font greater than 72 points

in excel The largest font size is not 72 points, but 409 points. You just need to enter the number.

29. Set title row printing

Page Settings-Worksheet-Top Title Row

30. Do not print error values

Page Settings-Worksheet-Error values ​​are printed as: empty

31.Hide 0 values

File-Options-Advanced-Remove " Display zero in cells with zero values"

32. Set the font and font size of the new file

File-Options-General-New Job When writing...

#33. Quickly view function help

Click the function name shown below in the formula to open the help for the function page.

34. Speed ​​up the opening of excel files

If there are too many formulas in the file, set it to manual when closing, and it will open faster.

35. Sort by row

In the sorting interface, click Options and select Sort by row

36.Set a printable background Picture

To insert a picture in the header, you need to

Recommended tutorial: excel tutorial

The above is the detailed content of 36 Excel tips. For more information, please follow other related articles on the PHP Chinese website!