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: =5MAX(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
- 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-