Identify & Remove Duplicates
Product January February
Appliances 496 623
Binders and Binder Accessories 239 979
Bookcases 500 349
Chairs & Chairmats 579 635
Computer Peripherals 991 1133
Copiers and Fax 100 70
TRIM RESULT
Bookcases Bookcases
Chairs & Chairmats Chairs & Chairmats
Computer Peripherals Computer Peripherals
Binders and Binder Accessories Binders and Binder Accessories
Copiers and Fax Copiers and Fax
CLEAN RESULT
PROGRAF PROGRAF
PARIET PARIET
CONCERTA CONCERTA
LEUSTATIN LEUSTATIN
VELCADE VELCADE
LEUSTATIN LEUSTATIN
VELCADE VELCADE
SUBSTITUTE RESULT
Version 1.0 maryam 1.0 tosin 1.0
Version 1.1 maryam 1.1 tosin 1.1
Version 1.2 maryam 1.2 tosin 1.2
Version 1.3 maryam 1.3 tosin 1.3
Version 1.4 tosin 1.4
can also use FIND & REPLACE tool
PROPER, CONCANTENATE, LEN, UPPER, LOWER
Cities Country Proper
LAGOS Nigeria Lagos
CAMPINA GRANDE Brazil Campina Grande
NATAL South Africa Natal
BELÉM Brazil Belém
JOÃO PESSOA Brazil João Pessoa
PARIS France Paris
CASTANHAL Brazil Castanhal
MARANGUAPE Ceará Maranguape
Sample Date TEXT LEFT
6/25/2022 25 Jun 2022 25 Jun
6/26/2022 26 Jun 2022 26 Jun
2/11/2020 11 Feb 2020 11 Feb
10/14/2021 14 Oct 2021 14 Oct
1/10/2003 10 Jan 2003 10 Jan
TEXT_TO_COLUMNS Names Domain
opara.ronaldo@careerinsights.tv opara.ronaldo careerinsights.tv
messi.anyanwu@careerinsights.tv messi.anyanwu careerinsights.tv
lauren.eze@careerinsights.tv lauren.eze careerinsights.tv
oladimeji.mayowa@careerinsights.tv oladimeji.mayowa careerinsights.tv
sandra.rihanna@careerinsights.tv sandra.rihanna careerinsights.tv
timothy.odubanjo@careerinsights.tv timothy.odubanjo careerinsights.tv
opara.ronaldo444444@careerinsights.tvopara.ronaldo444444 careerinsights.tv
messi.anyanwu@careerinsights.tv messi.anyanwu careerinsights.tv
lauren.eze@careerinsights.tv lauren.eze careerinsights.tv
oladimeji.mayowa@careerinsights.tv oladimeji.mayowa careerinsights.tv
sandra.rihanna@careerinsights.tv sandra.rihanna careerinsights.tv
timothy.odubanjo@careerinsights.tv timothy.odubanjo careerinsights.tv
March April May June
2 8 55 48
1 6 46 35
51 0 1 40
35 18 11 38
8 11 15 27
0 0 0 0
Concatenate LEN UPPER LOWER
Nigeria Lagos 7 NIGERIA nigeria
Brazil Campina Grande 6 BRAZIL brazil
South Africa Natal 12 SOUTH AFRICA south africa
Brazil Belém 6 BRAZIL brazil
Brazil João Pessoa 6 BRAZIL brazil
France Paris 6 FRANCE france
Brazil Castanhal 6 BRAZIL brazil
Ceará Maranguape 5 CEARÁ ceará
RIGHT MID
2022 Jun
2022 Jun
2020 Feb
2021 Oct
2003 Jan
First Name Last Name
opara ronaldo
messi anyanwu
lauren eze
oladimeji mayowa
sandra rihanna
timothy odubanjo
opara ronaldo444444
messi anyanwu
lauren eze
oladimeji mayowa
sandra rihanna
timothy odubanjo
DATE: Create a valid date from year, month, and day in the right format
DAY MONTH YEAR
2/14/2022 14 2 2022
6/28/2022 28 6 2022
DateDif: (StartDate, EndDate, Method{"d" - days, "m" - months, "y" - years})
Difference between dates (DATEDIF: Get days, months, or years between two dates, DAYS, DAYS360, NETWORKDAYS, N
Start Date End Date DATEDIF Diff in Months
4/17/1976 9/28/2020 16235 533
6/7/2000 10/14/2020 7434 244
11/15/2020 11/26/2020 11 0
3/8/2015 8/19/2022 2721 89
DATEVALUE, MONTH, YEAR, WEEKDAY, WEEKNUM, ISOWeekNum, EDATE, EOMONTH
DateText DateValue Day Month
1 Nov 2019 11/1/2019 1 11
2 Apr 2020 Err:502 #VALUE! #VALUE!
18 Jul 2022 Err:502 #VALUE! #VALUE!
28 Dec 1996 Err:502 #VALUE! #VALUE!
NOW: Get the current date and time to know current date and time
4/11/2025 13:33
TODAY: Get the current date (CTRL ;) to get todays date
4/11/2025
DATE
2/14/2022
6/28/2022
ates, DAYS, DAYS360, NETWORKDAYS, NETWORKDAYS.INTL)
Diff in years DAYS DAYS360 NETWORKDAYS
44 #NAME? 16001 11596
20 7 16001 5311
0 15 16001 9
7 8 16001 1945
Year WeekDay WeekNum
2019 6 44
2020 #VALUE! #VALUE!
2022 #VALUE! #VALUE!
1996 #VALUE! #VALUE!
weekdays
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday
10/3/1980
4/2/2019
8/4/2019
9/6/1980
NETWORKDAYS with holidays
11594
5310
9
1944
SUM, COUNT, COUNTA, MAX, MIN, AVERAGE, SUBTOTAL
Product January February March April May June
Appliances 496 623 2 8 55 48
Binders and Binder Accessories 239 979 1 6 46 35
Bookcases 500 349 51 0 1 40
Chairs & Chairmats 579 635 35 18 11 38
Computer Peripherals 991 1133 8 11 15 27
Copiers and Fax 100 70 0 0 0 0
SUM 2905 3789 97 43 128 188
COUNT 6 6 6 6 6 6
COUNTA 6 6 6 6 6 6
MAX 991 1133 51 18 55 48
MIN 100 70 0 0 0 0
AVERAGE 484 632 16 7 21 31
SUBTOTAL 2905 3789 97 43 128 188
Product January February March April May June JULY
Appliances 496 623 2 8 55 48 103
Binders and Binder Accessories 239 979 1 6 46 35 81
Bookcases 500 349 51 0 1 40 41
Chairs & Chairmats 579 635 35 18 11 38 49
Computer Peripherals 991 1133 8 11 15 27 42
Copiers and Fax 100 70 0 0 0 0 0
COUNTIF SUMIF AVERAGEIF COUNTIFS SUMIFS
Count athlethes Sum of 2020 Pay
Count of Athletes Sum of 2020 with Bonus > for athletes with
Sport per Sport Pay per Sport Avg Bonus per Sport avg Bonus per Bonus > avg Bonus
Sport per Sport
Basketball
Tennis
Average 2020 pay $30.4
$50.0
(IF)
Athlete 2020 Pay (M) Bonus (M) Endorsements Sport Earnings Grade
Amar'e Stoudemire $26.7 $21.7 5 Basketball false
Blake Griffin $23.0 $16.5 7 Basketball false
Carmelo Anthony $29.4 $21.4 8 Basketball false
Chris Bosh $21.8 $19.3 3 Basketball false
Chris Paul $24.2 $18.7 6 Basketball false
Deron Williams $20.0 $18.5 2 Basketball false
Derrick Rose $36.6 $17.6 19 Basketball true
Dirk Nowitzki $23.2 $22.7 1 Basketball false
Dwight Howard $25.5 $20.5 5 Basketball false
Dwyane Wade $29.9 $18.9 11 Basketball false
Joe Johnson $22.5 $21.5 1 Basketball false
Kevin Durant $31.9 $17.9 14 Basketball true
Kobe Bryant $61.5 $30.5 31 Basketball true
LeBron James $72.3 $19.3 53 Basketball true
Li Na $23.6 $5.6 18 Tennis false
Maria Sharapova $24.4 $2.4 22 Tennis false
Novak Djokovic $33.1 $12.1 21 Tennis true
Pau Gasol $21.8 $19.3 3 Basketball false
Rafael Nadal $44.5 $14.5 30 Tennis true
Roger Federer $56.2 $4.2 52 Tennis true
Rudy Gay $18.6 $17.9 1 Basketball false
Russell Westbrook $19.2 $14.7 5 Basketball false
Serena Williams $22.0 $11.0 11 Tennis false
Zach Randolph $18.8 $18.3 1 Basketball false
Conditions
1. Earning Grade
Identify athlets that earn above or below the Average 2020 Pay
2. Athlete Class
Those whose Bonus> $15M and Endorsement > 15 are ELITES, others are REGULAR
3. Popularity Scale
Those whose Bonus> $15M or Endorsement > 20 are POPULAR, others are NON-POPULAR
4. Athlete Grade (based on pay)
< $20M - D
<= $40M - C
< $50M - B
>= $50M - A
(IF & AND) (IF & OR) (Nested IF) (VLOOKUP)
Athlete Class Popularity Scale Athlete Grade Althete's City Location
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
ELITES POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
ELITES POPULAR A
ELITES POPULAR A
REGULAR NON-POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR C
REGULAR POPULAR B
REGULAR POPULAR A
REGULAR POPULAR D
REGULAR NON-POPULAR D
REGULAR NON-POPULAR C
REGULAR POPULAR D
