Here are all 36 classic tips in Excel spreadsheets

Here are all 36 classic tips in Excel spreadsheets

Tip 1: Force line breaks within cells

Tip 2: Lock the title line

Tip 3: Print the title line

Tip 4: Find duplicate values

Tip 5: Delete duplicate values

Tip 6: Quickly enter a check mark √

Tip 7: Display for 10000 yuan

Tip 8: Hide 0 values

Tip 9: Hide all values in cells.

Tip 10: Enter 00001 in cells

Tip 11: Fill in dates by month

Tip 12: Merge the contents of multiple cells

Tip 13: Prevent duplicate entry

Tip 14: Formula to Value Conversion

Tip 15: Convert decimals to integers

Tip 16: Quickly insert multiple lines

Tip 17: Swapping Two Columns

Tip 18: Batch Set Sum Formula

Tip 19: View two worksheets in an Excel file simultaneously.

Tip 20: Modify multiple worksheets simultaneously

Tip 21: Restore Unsaved Files

Tip 22: Add an open password to an Excel file

Tip 23: Quickly close all Excel files

Tip 24: Creating a dropdown menu

Tip 25: Secondary linkage pull-down

Tip 27: Delete blank lines

Tip 28: Tables can only be filled out and cannot be modified

Tip 29: Center text across columns

Tip 30: Annotate and add images

Tip 31: Batch Hide and Show Annotations

Tip 32: Solving the problem of numbers not being able to sum

Tip 33: Interleave blank lines

Tip 34: Quickly adjust the most suitable column width

Tip 35: Quickly copy formulas

Tip 36: Merge Cells and Filter

Tip 1: Force line breaks within cells

Pressing alt+enter after a character in a cell will force the cursor to move to the next line.

Tip 2: Lock the title line

Select the second row, view – freeze pane – freeze the first row (or select the second row – freeze pane), and when scrolling down, the title row will always be displayed at the top.

Tip 3: Print the title line

If you want to display the title on every page during printing, page layout print title header row: select the row to be displayed

Tip 4: Find duplicate values

Select data range – Start – Conditional formatting – Highlight cell rules – Repeat values.

Display effect:

Tip 5: Delete duplicate values

Select a range of cells containing duplicate values, Data – Remove duplicate values.

Tip 6: Quickly enter a check mark √

The fastest way to input symbols in Excel is to use alt+numbers, such as entering √. You can:

Press alt and hold it down, then press the numeric keys on the keypad: 41420

Tip 7: Display for 10000 yuan

Enter 10000 in an empty cell (it is recommended to set the number format and border) – Copy it – Paste Special – Operation: Division

After conversion

Tip 8: Hide 0 values

If you do not want to display the 0 value in the table, you can use: File Excel Options Advanced In Cells with Zero Values

Tip 9: Hide all values in cells.

If you want to hide the value of a cell, select the area, right-click – Format Cell – Number – Customize – Enter three semicolons in the text box on the right;;;

Tip 10: Enter 00001 in cells

If you enter a number starting with 0 in a cell, you can format it as text before entering. If you want to fix the number of digits (such as 5) and fill it in with 0, you can:

Select the area, right-click – Format Cells – Number – Customize – Enter 00000 in the text box on the right

Enter 1 to display 00001

Tip 11: Fill in dates by month

Drag down the date cell and copy it. Open the paste list and select “Fill in with months”

Tip 12: Merge the contents of multiple cells

Adjust the column width to make it easy to merge characters, then select the merged area – Start – Fill – Align Both Ends

After merging:

Tip 13: Prevent duplicate entry

Select the cell range to prevent duplicate entry, Data Validation Customization Formula:

If duplicate entries are made, an error message will be displayed and the entered content will be cleared

Tip 14: Formula to Value Conversion

Select the area where the formula is located, copy paste paste paste as numerical value

Tip 15: Convert decimals to integers

Select the number range, Ctrl+H to open the replacement window, search for. *, leave the replacement blank, and then click Replace All.

Tip 16: Quickly insert multiple lines

When you select a row and place the cursor in the bottom right corner, and press the shift key, you will find that the cursor will change to the shape shown in the following picture.

At this point, you can drag and drop

You will find that as many lines as you drag, you will insert as many empty lines. This insertion method is more flexible than selecting a certain number of rows before inserting.

Tip 17: Swapping Two Columns

In the table below, select column C, place the cursor at the edge, press shift while holding down the left mouse button, drag to the front of column B. When a dashed line appears in front of column B, release the left mouse button to complete the swap.

Place on the edge

Left click and drag to the front of column B

complete

Tip 18: Batch Set Sum Formula

Select the area containing the yellow rows and columns, and press alt and=(hold down the alt key and then press the equal sign) to complete the input of the summation formula.

Tip 19: View two worksheets in an Excel file simultaneously

View – New Window

Set Rearrange Windows

Arrangement method

Effect after rearranging

Tip 20: Modify multiple worksheets simultaneously

Press Shift or Ctrl to select multiple worksheets, and then enter content or modify formatting in one table. All selected tables will be synchronized for input or modification. This way, there is no need to modify each table individually.

Tip 21: Restore Unsaved Files

Open path: C: UsersAdministratorAppDataRoamingMicrosoft Excel. Within the folder, you will find the folder where the unsaved files are located, as shown in the following figure.

Open the folder and find the unsaved files. Open and save as soon as possible!

Why did my test not recover successfully? How do you know the path to recover files?

First, take a look at the interface and you will understand.

File – Excel Options – Save

Tip 22: Add an open password to an Excel file

Excel file – Information – Protect workbook – Encrypt with password.

Tip 23: Quickly close all Excel files

Press the shift key without releasing it, and then click the close button in the upper right corner to key all open Excel files.

Tip 24: Creating a dropdown menu

Example: As shown in the following figure, it is required to set a dropdown menu that can be selected in the salesperson column.

Analysis: There are several methods to create a dropdown menu in Excel. Here, we will introduce how to use data validity to set the dropdown menu,

Setting steps:

Step 1: Select the cell range in the salesperson column that requires setting the dropdown menu (this step cannot be omitted), open the data validity window (Excel 2003 version data menu – validity, Excel 2007 and 2010 version data tab – data validity – data validity), and select “sequence” in the “Settings” tab of the window.

Step 2: In the source input box, we need to set the content to be displayed in the drop-down menu. There are two ways to set it.

  1. Direct input method. Enter the string connected with “,” (comma) in the box after the source: Zhang Yi, Wu Hanqing, Liu Neng, Jiang Wensheng, Li Damin

The content method of referencing cells. If the salesperson is in the B4-B8 area of cells, enter or click the fold button at the end of the box after “Source” to select this area. As shown in the following figure.

After making the above settings, we can now see the dropdown menu in the salesperson column.

Tip 25: Secondary linkage pull-down

Example: As shown in the figure below, when entering or selecting Apple in the phone column, all models of Apple phones will be displayed in the model drop-down menu. If Samsung is entered in the phone column, all models of Samsung will be displayed in the model drop-down menu.

Mobile phone selection for Apple:

Select Samsung in the mobile phone column

Students should understand what a second level pull-down linked menu is and what else to wait for. Let’s start making it together.

Operation steps:

Step 1: Set the data source area. It is to organize the phone name and model into the format shown in the following picture for backup, and store them in a convenient location.

Step 2: Batch define names. After selecting the phone name and model area, open the specified name window (in Excel 2003 version, insert menu – name – specify, 07 and 10 version formula tabs – defined name group – create according to the selected content), and select the “first row” checkbox on the window. As shown in the following figure.

Step 3: Set data validity. Select the model column, open the data validity window (as shown in yesterday’s tutorial), and enter=indirect (D5) in the source field

After making the following settings, the secondary linkage menu is set.

Tip 27: Delete blank lines

Select column A area – ctrl+g to open the positioning window – null value – delete the entire row

Tip 28: Tables can only be filled out and cannot be modified

Operation steps

Step 1: Press the Ctrl key to select all yellow areas, then press Ctrl+1 (number 1) to open the “Cell Format” window. In the Lock tab, remove the check mark before “Lock”.

Step 2: Protect the worksheet. Excel 2003 version Tools menu – Protect – Protect Sheet. Excel 2010 Review tab – Protect Sheet.

After following the above steps, try to modify the cells in the yellow area: OK. If you try to modify or insert rows/columns outside the yellow area, a prompt will pop up as shown in the following figure.

Tip 29: Center text across columns

If you don’t want to merge cells and want text to be displayed across columns. You can select multiple columns, right-click to format cells, align horizontally, and center across columns.

After display effect

Tip 30: Annotate and add images

When creating product introduction sheets or employee information sheets, it is often necessary to add product images and employee photos. In this case, inserting images with annotations is the best choice.

Select annotation – right-click “Format Annotation” – Color – Fill Effect – Image – Select Image

Select Image Window

Set completion effect:

Tip 31: Batch Hide and Show Annotations

Open the review tab and click “Show all comments”

Tip 32: Solving the problem of numbers not being able to sum

After importing data into Excel, it actually exists in text form (numbers are right-aligned by default, while text is left-aligned), even if the cell format is reset to numbers, it is of no use.

The following methods can help you quickly convert into computable numbers

When selecting a range of numbers, the first cell must contain numbers, and there is a green triangle in the upper left corner of the cell. After selecting, open the green triangle and click to convert it to a number. As shown in the following figure

Tip 33: Interleave blank lines

Interleaving is an ancient but constantly questioned topic, and there are many related tutorials online. Today, Lan Se recorded an animation to demonstrate the steps of interlacing.

The following demonstration is divided into two parts:

Insert blank lines in interlaced rows

Insert blank line before 2 categories

Note: The combination key for opening the positioning window during the demonstration process is ctrl+g

Tip 34: Quickly adjust the most suitable column width

Select multiple rows or rows, double-click the edge of one of the columns, and you will find that all rows or columns are adjusted to the most suitable column width/height.

Tip 35: Quickly copy formulas

Double click on the bottom right corner of the cell and you will find that the formula has been copied to the back of the table.

Tip 36: Merge Cells and Filter

Table with merged cells

If you filter directly, the following situation will occur. (Only filter out the first item)

If you want to achieve the correct screening results, you need to perform surgery on column A of the table.

Step 1: Copy column A to column E.

Step 2: Cancel the merge of column A

Step 3: Select column A, CTRL+G locate – empty value, enter=A2 in the edit bar, and then press CTRL+Enter to complete the filling

Step 4: Copy column A and paste it as a numerical value (convert the formula to a numerical value)

Step 5: Use a format brush to move the format of column E to column A and restore the merged format of column A.

After the surgery is completed, it can be filtered normally, as shown in the following figure.

Organizing these skills took 6 hours, and if it’s useful for classmates, it’s worth the effort!!

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *