Hard core dry goods! 20 Excel Tips for Workplace People to Hate Meeting Later

Hard core dry goods! 20 Excel Tips for Workplace People to Hate Meeting Later

In Excel, it is necessary to master and learn function formulas and pivot tables, which are relatively difficult. In addition, there are some common skills that must be mastered!

1、 Excel tip: Automatically add prefixes and suffixes.

Method:

  1. Select the target cell and press Ctrl+1 to open the Format Cell dialog box.
  2. Select 【 Custom 】 in 【 Classification 】, enter XXX Limited Company @ or @ Limited Company in 【 Type 】, and 【 OK 】.

Interpretation:

Add a prefix after the symbol “@” and a suffix before it.

2、 Excel tip: Character alignment.

Method:

  1. Select the target cell and press Ctrl+1 to open the Format Cell dialog box.
  2. Click the [Align] tab, select [Scatter Align (Indent)] from the [Horizontal Align], and click [OK].

3、 Excel tip: Date alignment.

Method:

  1. Select the target cell and press Ctrl+1 to open the Format Cell dialog box.
  2. Select “Custom” in “Classification”, enter “e/mm/dd” in “Type”, and click “OK”.

Interpretation: e/mm/dd is the format code for standard dates.

4、 Excel tip: Force line breaks within cells.

Method:

  1. Insert the cursor after the target character.
  2. Shortcut keys Alt+Enter.

Interpretation:

If the content needs to be displayed in multiple lines, simply repeat the above steps.

5、 Excel tip: Batch input content in blank cells.

Method:

  1. Select the target cell, use the shortcut Ctrl+G to open the 【 Positioning 】 dialog box, 【 Positioning Conditions 】 – 【 Blank 】 – 【 OK 】.
  2. Edit the desired input, such as “blank”, and press the shortcut Ctrl+Enter to fill it in.

6、 Excel tip: Quickly delete rows or columns.

Method:

  1. Select the target row or column.
  2. Shortcut Ctrl+- (minus sign).

7、 Excel tip: Convert dates to corresponding weeks.

Method:

  1. Select the target cell and press Ctrl+1 to open the Format Cell dialog box.
  2. Select 【 Custom 】 in 【 Classification 】, enter: aaaa in 【 Type 】, and 【 OK 】.

Interpretation:

The code aaaa represents long weeks, and aaa represents short weeks.

8、 Excel tip: Quickly round and round.

Method:

  1. Select the target cell.
  2. Shortcut keys Ctrl+Shift+1.

9、 Excel tip: Quickly scale the selected area.

Method:

  1. Select the target cell.
  2. [View] – [Zoom to Selected Area].

Interpretation:

Select [100%] to return to the original view mode.

10、 Excel tip: Quickly generate dropdown menus.

Method:

  1. Select the target cell.
  2. Shortcut keys Alt+↓ (down arrow).

Interpretation:

The content of the quickly generated dropdown menu is based on the content of the first row in the same column.

11、 Excel tip: Convert textual numerical values to numerical types.

Method:

  1. Enter 1 in any cell and copy.
  2. Select the target cell, right-click – 【 Paste Special 】, and open the 【 Paste Special 】 dialog box.
  3. Select [Multiply] in [Operation] and [OK].

Interpretation:

When the copied value is “1”, the operation can be either “multiply” or “divide”. If you want to choose “add” or “subtract”, the value must be “0”.

12、 Excel tip: Quickly sum rows and columns simultaneously.

Method:

  1. Select the target cell, including the sum range.
  2. Shortcut key Alt+=.

Interpretation:

From the calculation results, it can be seen that Alt+=is actually a shortcut key for the Sum function.

13、 Excel tip: Merge cells from multiple columns and sum them.

Method:

Enter the formula=SUM (D3: G9) – SUM (H4: H9) in the target cell.

Interpretation:

  1. The value of the merged cell is stored in the upper left corner cell of the current merged area.
  2. The sum value of the current region minus the sum value outside the first cell in the current region yields the value of the merged cell range.

14、 Excel tip: Display phone numbers in segments.

Method:

  1. Select the target cell and press Ctrl+1 to open the Format Cell dialog box.
  2. Select [Custom] in [Classification], enter: 000 0000 0000 in [Type], and [OK].

15、 Excel skill: display the ID card number in segments.

Method:

  1. Add auxiliary columns.
  2. Enter the ID number in the first cell in segments.
  3. Select all target cells and use the shortcut Ctrl+E.

16、 Excel tip: Quick data formatting.

Method:

  1. Select the target cell.
  2. Shortcut key Ctrl+Q or select the icon in the bottom right corner and select the corresponding formatting command.

17、 Excel Tip: Quickly copy the content of the previous row or cell.

Method:

  1. Select the row or cell that needs to be filled.
  2. Shortcut key Ctrl+D.

Interpretation:

Ctrl+D cannot copy content interlaced.

18、 Excel tip: Automatically add table borders.

Method:

  1. Select the target cell.
  2. 【 Conditional Format 】 – 【 New Rule 】, open the 【 New Format Rule 】 dialog box.
  3. Select “Use Formula to Determine Cells to Format” in “Select Rule Type”, and enter “=A2<>” in the “Format Values that Match This Formula” dialog box. Click “Format” in the bottom right corner, select “Border” – “Outer Border”, and click “OK”.

19、 Excel tip: Merge the contents of multiple cells into one cell.

Method:

  1. Select the target cell.
  2. 【 Start 】 – 【 Edit 】 tab, select 【 Fill 】 and select 【 Content Reorder 】.

Interpretation:

If the width of the cell is not enough, it will automatically fill in the second cell, third cell.

20、 Excel tip: Copy visible cells.

Method:

  1. Select the area that needs to be copied.
  2. Shortcut key Alt+; And copy with Ctrl+C.
  3. Paste in the target area.

Conclusion:

Starting from reality, the article interprets the 20 commonly used skills in work, which is of great help in improving work efficiency!

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 *