---
title: "Distributor Management | Don't Say No One Taught You Excel Tricks – After This Clear Tutorial, You'll Never Talk About Tricks Again!"
description: "This article presents a collection of the fastest and most powerful Excel tips for common work tasks, such as extracting names and phone numbers, comparing data, converting formats, and using formulas. It also includes detailed formula examples for employee information, attendance, data analysis, and other business scenarios, along with a section on using pivot tables for data analysis."
author: "New Distribution"
publisher: "New Distribution"
email: "zhaobo258@gmail.com"
telephone: "+8615854817671"
published: "2017-07-30"
language: "en"
canonical: "https://xinjignxiao.com/en/articles/distributor-management-don-t-say-no-one-taught-you-excel-tricks-after-th-d01291f8/"
markdown: "https://xinjignxiao.com/en/articles/distributor-management-don-t-say-no-one-taught-you-excel-tricks-after-th-d01291f8.md"
original_source: "https://mp.weixin.qq.com/s/nrWcBFinMdqFmlQU0SwMoA"
translation: "https://xinjignxiao.com/zh/articles/%E7%BB%8F%E9%94%80%E5%95%86%E7%AE%A1%E7%90%86-%E5%88%AB%E8%AF%B4excel%E6%8A%80%E5%B7%A7%E6%B2%A1%E4%BA%BA%E6%95%99%E4%BD%A0-%E8%BF%99%E4%B9%88%E6%B8%85%E6%99%B0%E7%9A%84%E6%95%99%E7%A8%8B%E7%9C%8B%E5%AE%8C-%E4%BB%8E%E6%AD%A4%E5%86%8D%E4%B8%8D%E8%B0%88%E6%8A%80%E5%B7%A7-d01291f8.md"
attribution: "New Distribution — https://xinjignxiao.com/en/articles/distributor-management-don-t-say-no-one-taught-you-excel-tricks-after-th-d01291f8/"
usage_policy: "https://xinjignxiao.com/ai-policy.txt"
---

# Distributor Management | Don't Say No One Taught You Excel Tricks – After This Clear Tutorial, You'll Never Talk About Tricks Again!

> This article presents a collection of the fastest and most powerful Excel tips for common work tasks, such as extracting names and phone numbers, comparing data, converting formats, and using formulas. It also includes detailed formula examples for employee information, attendance, data analysis, and other business scenarios, along with a section on using pivot tables for data analysis.

**Massive Excel spreadsheets, employee basic info,**
**commission calculations, attendance statistics, contract management**
**....**
**Endless tasks**
**When will they ever be done?**
**Actually, today**
There are so many useful tips in Excel. Below are the quickest and most powerful Excel tips organized for your daily work – see which one is the most impressive.

**Extract names and phone numbers**
Want to split names and phone numbers? First enter the first name, select it and the empty rows below, press Ctrl+E, and the split is done in a second (same method for extracting phone numbers).

**Compare whether data in the same row is identical**
Select two columns, then press Ctrl + \ (backslash).

**Paste data skipping blank cells**
Copy – Paste Special – Skip blanks. This allows you to paste while skipping blank columns/rows.

**Restore E+ number format display**
If a number is longer than 11 digits, it displays in scientific notation. Set a custom format of 0 to make the number display normally.

**Convert formulas to values**
Press the **CTRL** key and select the range in two segments, copy, select the paste location, and press **Enter** to complete the conversion.

**Convert dates**
Use Text to Columns to quickly convert irregular numbers and strings into standard date formats.

**Quickly input checkmarks in boxes ☑**
As shown in column D below, set the font to Wingdings 2. Entering R displays ☑, and entering S displays ☒.

**Quickly merge multiple rows of data**
Widen the column – select the data – Home – Fill – Justify (in Excel 2003, it's Content Reflow).

**Quickly copy and paste across sheets**
Hold Shift to select multiple worksheets, select the table area to copy, then Home – Fill – Across Worksheets.

**Quickly set sum formulas**
Press Ctrl+G to go to special blanks – then press Alt+= to set the sum formula in one click.

**Quickly select ranges in formulas**
When setting a SUM formula, I used to drag the cursor down to select the range... Sometimes I'd just set a large row range for convenience. I never thought there was a simpler way. (Why not use =SUM(B:B)? Because if column B has other data that shouldn't be summed, it would be wrong.)
Actually, when selecting a range in a formula, just press **Ctrl** + **Shift** + **↓** and it's done.

**Think that's the end?**
**Digest the above first,**
**Even more awesome skills are coming!!!!**

1
**Employee Information Table Formulas**
**1. Calculate gender (Column F)**
=IF(MOD(MID(E3,17,1),2),"Male","Female")
**2. Date of birth (Column G)**
=TEXT(MID(E3,7,8),"0-00-00")
**3. Age formula (Column H)**
=DATEDIF(G3,TODAY(),"y")
**4. Retirement date (Column I)**
=TEXT(EDATE(G3,12*(5*(F3="Male")+55)),"yyyy/mm/dd aaaa")
**5. Place of origin (Column M)**
=VLOOKUP(LEFT(E3,6)*1,AddressLibrary!E:F,2,)
Note: The attached example includes the address library code table.
**6. Social work years (Column T)**
=DATEDIF(S3,NOW(),"y")
**7. Company work years (Column W)**
=DATEDIF(V3,NOW(),"y")&" years "&DATEDIF(V3,NOW(),"ym")&" months "&DATEDIF(V3,NOW(),"md")&" days"
**8. Contract renewal date (Column Y)**
=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1
**9. Contract expiration date (Column Z)**
=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]Expired 0 days;[<30]Expiring soon 0 days;Still far")
**10. Seniority pay (Column AA)**
=MIN(700,DATEDIF($V3,NOW(),"y")*50)
**11. Chinese zodiac (Column AB)**
=MID("Monkey Rooster Dog Pig Rat Ox Tiger Rabbit Dragon Snake Horse Goat",MOD(MID(E3,7,4),12)+1,1)

2
**Employee Attendance Table Formulas**
**1. Working days in current month (Column AG)**
=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)
**2. Compensatory leave days formula (Column AI)**
=COUNTIF(B9:AE9,"Comp")
**3. Deduction formula (Column AO)**
Marriage/funeral leave deducts 10 yuan, sick leave deducts 20 yuan, personal leave deducts 30 yuan, absenteeism deducts 50 yuan.
=SUM((B9:AE9={"Personal";"Absent";"Sick";"Funeral";"Marriage"})*{30;50;20;10;10})

3
**Employee Data Analysis Formulas**
**1. Number of employees with bachelor's degree**
=COUNTIF(D:D,"Bachelor")
**2. Number of bachelor's degree holders in the office**
=COUNTIFS(A:A,"Office",D:D,"Bachelor")
**3. Total number of people aged 30-40**
=COUNTIFS(F:F,">=30",F:F,"<40")

4
**Other Formulas**
**1. Commission rate calculation**
=VLOOKUP(B3,$C$12:$E$21,3)
**2. Personal income tax calculation**
If A2 contains taxable salary, the formula for calculating individual income tax is:
=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)
**3. Pay slip formula**
=CHOOSE(MOD(ROW(A3),3)+1,PayrollData!A$1,OFFSET(PayrollData!A$1,INT(ROW(A3)/3),,),"")
Note:
  * A3: The row number of the title row plus 2. If the title row is row 3, change A3 to A5.
  * PayrollData!A$1: The position of the first column of the title row in the payroll sheet.
**4. Solution for Countif function errors when counting ID card numbers**
Since Excel can only recognize numbers up to 15 digits, Countif only counts the first 15 digits, which often causes errors. However, you can use &"*" to convert to text format for correct counting.
=Countif(A:A,A2&"*")

5
**Using Pivot Tables for Data Analysis**
1. Proportion of employees in each department
Calculate the percentage of each department's headcount relative to the total.
**2. Number and proportion of each age group**
What are the numbers and proportions of employees in each age group?
**3. Proportion of each age group within each department**
Calculate the proportion of each age group within each department.
**4. Education statistics by department**
How many associate degree, bachelor's, master's, and doctoral degree holders are in each department?
**5. Number of new hires per department by year**
The number of new hires per department each year.

-END-


---

## Copyright and AI use

This article is sourced from New Distribution. Search, quotation, summarization, and model training are permitted, but every use must credit New Distribution and retain the canonical source URL.

Contact: zhaobo258@gmail.com · +86 158 5481 7671