S No

TASK 1: STANDARDIZING THE D

1

SPORTSMEN

Col B

2

SPORTSMEN

Col K

3

SPORTSMEN

Col L

4

SPORTSMEN

Col M

5

SPORTSMEN

Col Q

S No

TASK 2: DATA FORMATTING

1

SPORTSMEN

Col A

2

SPORTSMEN

Col G

3

SPORTSMEN

Col N

4

SPORTSMEN

Col S

ATASET

Populate the FULLNAME consisting of the following fields ONLY, in the prescribed format: PREFIX FIRSTNAME LASTNAME. (Note

Get the COUNTRY NAME to which these sportsmen belong to. Make use of LOCATION sheet to get the required data

Populate the LANGUAGE spoken by the sportsmen. Make use of LOCATION sheet to get the required data

Generate the EMAIL ADDRESS for those members, who speak English, in the prescribed format : lastname.firstname@xyz.org

(Note: All lowercase) and for all other members, format should be lastname.firstname@xyz.com (Note: All lowercase)

Populate the SPORT LOCATION of the sport played by each player. Make use of SPORT sheet to get the required data

Display MEMBER ID as always 3 digit number (Note: 001, 002…, 020,… etc)

Format the BIRTHDATE as dd mmm' yyyy (Prescribed format example: 09 May' 1986)

Display the units for the WEIGHT column (Prescribed format example: 80 kg)

Format the SALARY to show the data in thousands. If SALARY is less than 100,000 then display data with 2 decimal places else

display data with one decimal place. In both cases units should be thousands (k)  e.g.

87670 --> 87.67 k and 123250 --> 123.2 k

S No

TASK 1: SUMMARIZE DATA - PI

1

ANALYSIS

Range B3

2

ANALYSIS

3

ANALYSIS

4

ANALYSIS

5

ANALYSIS

S No

TASK 2: SUMMARIZE DATA - EX

1

ANALYSIS

Range G4

2

ANALYSIS

Range H4:I4

3

ANALYSIS

Range G5:G16

4

ANALYSIS

Range H5:I16

CEL FUNCTIONS

Create a SUMMARY table in the worksheet ANALYSIS, starting at cell G4, with the following details.

Starting from range RANGE H4; get the distinct GENDER. Use remove duplicates option and transpose the data

Starting from range RANGE G5; get the distinct COUNTRY (Note: use COUNTRY NAMES)

In the cross table, get the count of candidates from each COUNTRY and GENDER type

LE

Create a PIVOT table report in the worksheet REPORT, starting at cell A3, with the following information.

dates), COUNTRY NAME, LANGUAGE and SPORT played by them

Change the report layout to TABULAR form

Remove expand and collapse buttons

Remove GRAND TOTALs

Allow user to filter the data by SPORT LOCATION

MEMBER ID

FULL NAME

PREFIX

FIRSTNAME

MIDDLENAME

LASTNAME

00001

Ms.AnnieAbbott

Ms.

Annie

Abbott

00002

Ms.AurelieLiesuchke

Ms.

Aurelie

Liesuchke

00003

Sr.TomasFerreiraFilho

Sr.

Tomas

Ferreira

Filho

00004

Ms.DarbyCruickshank

Ms.

Darby

Cruickshank

00005

Dr.JaydonBorer

Dr.

Jaydon

Borer

00006

Mr.Moriah Lynch

Mr.

Moriah

Lynch

00007

Ms.AmiyaEichmann

Ms.

Amiya

Eichmann

00008

Mr.PierceRau

Mr.

Pierce

Rau

00009

Ms.AmeliaStevens

Ms.

Amelia

Stevens

00010

Mr.TobySimpson

Mr.

Toby

Simpson

00011

SirEthanMurphy

Sir

Ethan

Murphy

00012

Mrs.AshleyWood

Mrs.

Ashley

Wood

00013

Ms.MeganScott

Ms.

Megan

Scott

00014

Hr.HelmutWeinhae

Hr.

Helmut

Weinhae

00015

Prof.MilenaSchotin

Prof.

Milena

Schotin

00016

Hr.LotharBirnbaum

Hr.

Lothar

Birnbaum

00017

Hr.PietroStolze

Hr.

Pietro

Stolze

00018

Hr.Richard Tlustek

Hr.

Richard

Tlustek

00019

Dr.EarnestineRaynor

Dr.

Earnestine

Raynor

00020

Mr.JasonGaylord

Mr.

Jason

Gaylord

00021

Mr.KendrickSauer

Mr.

Kendrick

Sauer

00022

Dr.AnnabellOlson

Dr.

Annabell

Olson

00023

Dr.JenaUpton

Dr.

Jena

Upton

00024

Dr.ShannyBins

Dr.

Shanny

Bins

00025

Dr.TiaAbshire

Dr.

Tia

Abshire

00026

Ms.IsabelRunolfsdottir

Ms.

Isabel

Runolfsdottir

00027

Hr.BarneyWesack

Hr.

Barney

Wesack

00028

Hr.BaruchKade

Hr.

Baruch

Kade

00029

Prof.LiesbethRosemann

Prof.

Liesbeth

Rosemann

00030

Mme.ValentineMoreau

Mme.

Valentine

Moreau

00031

Mme.PauletteDurand

Mme.

Paulette

Durand

00032

Mme.Laure-AlixChevalier

Mme.

Laure-Alix

Chevalier

00033

M.ClaudeToussaint

M.

Claude

Toussaint

00034

M.VictorLenoir

M.

Victor

Lenoir

00035

M.ArthurLenoir

M.

Arthur

Lenoir

00036

M.BenjaminLebrun-Brun

M.

Benjamin

Lebrun-Brun

00037

M.AntoineMaillard

M.

Antoine

Maillard

00038

M.BernardHoarau-Guyon

M.

Bernard

Hoarau-Guyon

00039

Sr.HidalgoCantuTercero

Sr.

Hidalgo

Cantu

Tercero

00040

Sr.HadalgoPolanco

Sr.

Hadalgo

Polanco

00041

Sra.LauraOliviera

Sra.

Laura

Oliviera

00042

Sra.AinhoaGarza

Sra.

Ainhoa

Garza

00043

Sra.IsabelBanda

Sra.

Isabel

Banda

00044

Sra.CarolotaMateos

Sra.

Carolota

Mateos

00045

Mw.ElizePrins

Mw.

Elize

Prins

00046

dhr.RyanPham

dhr.

Ryan

Pham

00047

MwEliseRotteveel

Mw

Elise

Rotteveel

00048

Fru.MirjamSoderberg

Fru.

Mirjam

Soderberg

00049

H.BerndtPalsson

H.

Berndt

Palsson

00050

Sr.AdrianoPontesSobrinho

Sr.

Adriano

Pontes

Sobrinho

BIRTHDATE

ZODIAC

GENDER

COUNTRYCODE

COUNTRY NAME

LANGUAGE

09 September' 1997

Libra

Female

US

USA

English

02 Februar' 1992

Aquarius

Female

US

USA

English

07 Juli' 1969

Cancer

Male

BR

BRAZIL

Portuguese

05 Mai' 1975

Taurus

Female

US

USA

English

05 Mai' 1970

Taurus

Male

US

USA

English

12 Dezember' 1992

Sagittarius

Male

US

USA

English

07 Juli' 1999

Leo

Female

US

USA

English

05 Mai' 1963

Taurus

Male

US

USA

English

02 Februar' 1971

Aquarius

Female

GB

UK

English

12 Dezember' 1964

Sagittarius

Male

GB

UK

English

11 November' 1986

Scorpio

Male

GB

UK

English

10 Oktober' 1977

Libra

Female

GB

UK

English

02 Februar' 1977

Aquarius

Female

GB

UK

English

08 August' 1959

Virgo

Male

DE

GERMANY

German

03 März' 1965

Pisces

Female

DE

GERMANY

German

07 Juli' 1969

Cancer

Male

DE

GERMANY

German

10 Oktober' 1972

Libra

Male

DE

GERMANY

German

08 August' 1959

Virgo

Male

DE

GERMANY

German

05 Mai' 1977

Taurus

Female

OZ

AUSTRALIA

English

01 Jänner' 1976

Capricorn

Male

OZ

AUSTRALIA

English

07 Juli' 1996

Cancer

Male

OZ

AUSTRALIA

English

04 April' 1964

Aries

Female

OZ

AUSTRALIA

English

12 Dezember' 1955

Sagittarius

Female

OZ

AUSTRALIA

English

08 August' 1999

Virgo

Female

OZ

AUSTRALIA

English

07 Juli' 1966

Cancer

Female

OZ

AUSTRALIA

English

03 März' 1978

Aries

Female

OZ

AUSTRALIA

English

07 Juli' 1970

Cancer

Male

AU

AUSTRIA

German

03 März' 1982

Pisces

Male

AU

AUSTRIA

German

01 Jänner' 1994

Aquarius

Female

AU

AUSTRIA

German

10 Oktober' 1979

Libra

Female

FR

FRANCE

French

12 Dezember' 1989

Capricorn

Female

FR

FRANCE

French

12 Dezember' 1970

Capricorn

Female

FR

FRANCE

French

11 November' 1980

Scorpio

Male

FR

FRANCE

French

10 Oktober' 1981

Libra

Male

FR

FRANCE

French

07 Juli' 1955

Leo

Male

FR

FRANCE

French

02 Februar' 1975

Aquarius

Male

FR

FRANCE

French

06 Juni' 1986

Cancer

Male

FR

FRANCE

French

01 Jänner' 1983

Capricorn

Male

FR

FRANCE

French

11 November' 1984

Sagittarius

Male

AG

ARGENTINA

Spanish

06 Juni' 1988

Gemini

Male

AG

ARGENTINA

Spanish

02 Februar' 1974

Aquarius

Female

AG

ARGENTINA

Spanish

03 März' 1990

Pisces

Female

ES

SPAIN

Spanish

01 Jänner' 1960

Capricorn

Female

ES

SPAIN

Spanish

07 Juli' 1965

Leo

Female

ES

SPAIN

Spanish

05 Mai' 1960

Taurus

Female

DU

NETHERLANDS

Dutch

10 Oktober' 1973

Libra

Male

DU

NETHERLANDS

Dutch

04 April' 1968

Aries

Female

DU

NETHERLANDS

Dutch

05 Mai' 1997

Taurus

Female

SV

SWEDEN

Swedish

02 Februar' 1987

Pisces

Male

SV

SWEDEN

Swedish

07 Juli' 1993

Leo

Male

PR

BRAZIL

Portuguese

EMAIL

WEIGHT

EYECOLOR

BLOODTYPE

SPORT LOCATION

94kg

Green

A−

INDOOR

84kg

Brown

O−

INDOOR

53kg

Amber

A−

OUTDOOR

49kg

Green

O−

OUTDOOR

85kg

Blue

B−

INDOOR

83kg

Blue

O−

INDOOR

61kg

Blue

B−

OUTDOOR

106kg

Amber

A+

INDOOR

65kg

Blue

A+

INDOOR

63kg

Amber

O+

OUTDOOR

104kg

Brown

O+

OUTDOOR

101kg

Brown

O+

OUTDOOR

71kg

Green

A−

OUTDOOR

68kg

Gray

A+

OUTDOOR

105kg

Gray

O+

INDOOR

49kg

Blue

O+

OUTDOOR

106kg

Blue

A−

INDOOR

71kg

Blue

A−

OUTDOOR

70kg

Blue

A+

INDOOR

55kg

Brown

O−

INDOOR

101kg

Blue

B−

OUTDOOR

84kg

Green

A+

OUTDOOR

67kg

Blue

O+

OUTDOOR

59kg

Amber

B−

OUTDOOR

78kg

Amber

A+

OUTDOOR

86kg

Blue

B+

INDOOR

93kg

Amber

B+

INDOOR

96kg

Gray

O−

OUTDOOR

52kg

Blue

O+

OUTDOOR

75kg

Blue

B+

OUTDOOR

82kg

Amber

O−

INDOOR

78kg

Blue

O+

OUTDOOR

57kg

Green

O+

INDOOR

56kg

Blue

B+

OUTDOOR

89kg

Amber

O+

OUTDOOR

78kg

Brown

O−

OUTDOOR

96kg

Blue

B−

OUTDOOR

60kg

Gray

O−

INDOOR

78kg

Gray

B−

OUTDOOR

98kg

Blue

A−

OUTDOOR

52kg

Amber

O−

OUTDOOR

56kg

Brown

O+

INDOOR

102kg

Amber

O+

OUTDOOR

59kg

Gray

O−

OUTDOOR

64kg

Blue

O+

INDOOR

99kg

Amber

B+

OUTDOOR

62kg

Gray

O−

OUTDOOR

50kg

Amber

O+

OUTDOOR

46kg

Blue

A−

OUTDOOR

93kg

Green

A+

INDOOR

SPORTS

SALARY

Cycling Track

80.73K

Boxing

87.47K

Football

64.72K

Alpine Skiing

110.8K

Water Polo

56.92K

Fencing

51.13K

Cycling Road

65.47K

Curling

109.9K

Shooting

60.06K

Cycling Road

32.76K

Freestyle Skiing

99.61K

Archery

56.60K

Rugby

117.4K

Canoe Sprint

64.86K

Cycling BMX

10.24K

Alpine Skiing

88.76K

Handball

80.76K

Cycling Mountain Bike

88.79K

Short Track Speed Skating

63.53K

Basketball

46.35K

Triathlon

106.8K

Equestrian / Dressage

96.47K

Beach Volleyball

16.53K

Canoe Slalom

21.89K

Cycling Road

62.04K

Cycling Track

89.74K

Volleyball

41.04K

Rugby

28.46K

Cycling Road

55.01K

Golf

69.04K

Volleyball

86.26K

Beach Volleyball

19.23K

Diving

95.12K

Triathlon

62.76K

Hockey

108.4K

Triathlon

66.27K

Sailing

33.97K

Cycling Track

71.35K

Canoe Slalom

116.4K

Beach Volleyball

114.1K

Athletics

79.87K

Gymnastics Artistic

102.0K

Canoe Slalom

50.66K

Athletics

58.22K

Judo

39.94K

Beach Volleyball

44.87K

Beach Volleyball

90.48K

Football

38.97K

Biathlon

35.39K

Swimming

20.53K

SPORTS LOCATION

SPORTS

INDOOR

Cycling Track

INDOOR

Boxing

OUTDOOR

Football

OUTDOOR

Alpine Skiing

INDOOR

Water Polo

INDOOR

Fencing

OUTDOOR

Cycling Road

INDOOR

Curling

INDOOR

Shooting

OUTDOOR

Freestyle Skiing

OUTDOOR

Archery

OUTDOOR

Rugby

OUTDOOR

Canoe Sprint

INDOOR

Cycling BMX

INDOOR

Handball

OUTDOOR

Cycling Mountain Bike

INDOOR

Short Track Speed Skating

INDOOR

Basketball

OUTDOOR

Triathlon

OUTDOOR

Equestrian / Dressage

OUTDOOR

Beach Volleyball

OUTDOOR

Canoe Slalom

INDOOR

Volleyball

OUTDOOR

Golf

INDOOR

Diving

OUTDOOR

Hockey

OUTDOOR

Sailing

OUTDOOR

Athletics

INDOOR

Gymnastics Artistic

INDOOR

Judo

OUTDOOR

Biathlon

INDOOR

Swimming

COUNTRYCODE

US

BR

GB

DE

OZ

COUNTRY NAME

USA

BRAZIL

UK

GERMANY

AUSTRALIA

LANGUAGE

English

Portuguese

English

German

English

AU

FR

AG

ES

DU

SV

AUSTRIA

FRANCE

ARGENTINA

SPAIN

NETHERLANDS

SWEDEN

German

French

Spanish

Spanish

Dutch

Swedish