Excel Help

 

You just moved to Florida with your family and are searching for doctors who can meet your family’s medical needs. You obtained a list of more than 150 board-certified physicians from the state licensing agency. Open e04b1doctors and save it as 04b1doctors_LastnameFirstname. Freeze the panes to keep the column labels and IDs from scrolling offscreen. Convert the data to a table, and apply an appropriate table style. Name the table Doctors. Filter the table to show only those who practice Cardiology, Pediatrics, and Internal Medicine and who are accepting new patients in Fort Lauderdale. Sort the filtered records by specialty, last name, and then first name. Set a print area to include all columns except the ID and New Patient columns. Apply a conditional format for dates after 1/1/2008 with a custom format of an Orange fill and a White font. Create a PivotTable based on the Doctors table. Add the fields Specialty, City, and New Patients. Display how many doctors are accepting new patients for each Specialty and City. (Hint: There will be 3 Cardiology doctors in Coral Gables that are accepting new patients.) Further filter the Pivot Table to show only doctors in Fort Lauderdale. The individual cities should not be visible in the final PivotTable. There will be 26 doctors that fit your criteria. Create a PivotChart of your choice based on your results. Modify the chart appropriately. Name all worksheets. On each worksheet, create a footer with your name, the sheet name code, and the file name code. Save and close the workbook, and submit to Blackboard.

 

2

>

F

lorida Doctors

iami

F

/1

/

2

F

/

/

3

Cardiology M

TRUE

4

Internal Medicine M

/

8

FALSE

5

George Hollywood Cardiology M

/

/02

FALSE

6

Coral Gables

M

/

/

TRUE

7

Miami Cardiology F

/

TRUE

8

F

/

TRUE

9

Fort Lauderdale Cardiology F

/

TRUE

10

Miami

F

TRUE

Miami Cardiology F

FALSE

12

Coral Gables Cardiology M

TRUE

13

Miami Dermatology M

TRUE

14

Adam Fort Lauderdale Pediatrics M

/03

FALSE

15

Fort Lauderdale Dermatology M

TRUE

Miami Dermatology M

TRUE

Miami Internal Medicine F

TRUE

18

George Fort Lauderdale Dermatology M

TRUE

19

Miami Dermatology M

FALSE

Fort Lauderdale Cardiology F

TRUE

Miami Pediatrics F

FALSE

22

Fort Lauderdale Dermatology F

FALSE

Fort Lauderdale Dermatology M

TRUE

Coral Gables Dermatology M

FALSE

Jones

Fort Lauderdale Dermatology F

TRUE

Hollywood Pediatrics M

TRUE

Neil Fort Lauderdale Dermatology M

FALSE

Jones

Coral Gables Dermatology M

FALSE

Fort Lauderdale Dermatology M

TRUE

30

Robert Miami Cardiology M

FALSE

Miami Dermatology F

TRUE

Fort Lauderdale Dermatology F

TRUE

Adam Hollywood Internal Medicine M

FALSE

Miami Internal Medicine M

TRUE

Silva Alexander Miami Cardiology M

TRUE

Fort Lauderdale Internal Medicine F

FALSE

Fort Lauderdale Internal Medicine F

TRUE

Miami Internal Medicine F

FALSE

Fort Lauderdale Internal Medicine F

FALSE

Miami Cardiology F

FALSE

George

Hollywood Internal Medicine F

FALSE

Fort Lauderdale Internal Medicine M

TRUE

a

Miami Obstetrics F

TRUE

George Miami Internal Medicine M

FALSE

Simmons George Hollywood Internal Medicine M

TRUE

Miami Pediatrics F

FALSE

Hollywood Internal Medicine F

FALSE

Jennifer Miami Internal Medicine F

FALSE

Jessica Fort Lauderdale Internal Medicine F

FALSE

Jessica Miami Pediatrics F

TRUE

Coral Gables Internal Medicine F

TRUE

Coral Gables Internal Medicine F

TRUE

Coral Gables Internal Medicine F

FALSE

Miami Dermatology M

TRUE

Miami Dermatology F

TRUE

Fort Lauderdale Internal Medicine F

FALSE

Miami Internal Medicine M

FALSE

Fort Lauderdale Pediatrics M

TRUE

Miami Internal Medicine F

TRUE

Alexander Fort Lauderdale Obstetrics M

FALSE

Hollywood Obstetrics M

FALSE

62

Hollywood Dermatology M

TRUE

Katherine Fort Lauderdale Obstetrics F

FALSE

Hollywood Obstetrics M

FALSE

Fort Lauderdale Cardiology M

TRUE

Fort Lauderdale Obstetrics F

FALSE

67

Hollywood Pediatrics M

TRUE

68

Miami Pediatrics F

FALSE

Fort Lauderdale Pediatrics F

FALSE

Miami Pediatrics F

TRUE

Anne Miami Pediatrics F

FALSE

Christin Miami Pediatrics F

FALSE

Ledon Francisco Coral Gables Pediatrics M

TRUE

Miami Pediatrics F

FALSE

75

Miami Pediatrics M

FALSE

Louis Coral Gables Pediatrics M

FALSE

77

Matthew Coral Gables Pediatrics M

FALSE

78

Megan Miami Pediatrics F

FALSE

79

Coral Gables Pediatrics F

TRUE

80

Hollywood Pediatrics F 12/15/85 TRUE

81

Tom Miami Pediatrics M

TRUE

82

Fort Lauderdale Pediatrics F

FALSE

83 Taylor

Fort Lauderdale Internal Medicine M

TRUE

Taylor

Miami Internal Medicine F

TRUE

85 Counts

Fort Lauderdale Pediatrics F 5/15/09 TRUE

86 Baker

Fort Lauderdale Cardiology F

TRUE

Honey

Miami Cardiology F

FALSE

88 Gold Jeffrey Coral Gables Obstetrics M

TRUE

89

Coral Gables Pediatrics M

FALSE

90

Coral Gables Pediatrics F

FALSE

91

Melissa Fort Lauderdale Internal Medicine F

FALSE

92

Adam Fort Lauderdale Pediatrics M

TRUE

93

Miami Internal Medicine M

FALSE

94

Miami Internal Medicine M 11/1/08 FALSE

95

Fort Lauderdale Cardiology M 11/25/00 TRUE

96 McConnell

Fort Lauderdale Obstetrics F 5/22/98 FALSE

97

othy

Miami Obstetrics M 8/1/82 FALSE

98 Tucker

Coral Gables Cardiology M 12/15/85 TRUE

99 Miller Tim Coral Gables Pediatrics M 8/22/77 FALSE

Miller

Hollywood Cardiology M 1/16/81 TRUE

Beverley

Fort Lauderdale Internal Medicine F 5/18/70 TRUE

Kaufman

Fort Lauderdale Cardiology F 5/15/08 FALSE

Kaufman

Miami Internal Medicine M 10/1/00 FALSE

Fort Lauderdale Cardiology F 11/15/07 TRUE

Miami Pediatrics M

FALSE

Scott Coral Gables Pediatrics M 5/15/09 FALSE

Coral Gables Cardiology F 6/15/09 FALSE

Coral Gables Cardiology M

FALSE

Fort Lauderdale Internal Medicine M

TRUE

Taggart

Fort Lauderdale Cardiology M

FALSE

Meghan Miami Pediatrics M

FALSE

Baker

Fort Lauderdale Pediatrics M 5/15/09 FALSE

Miller

Miami Pediatrics F 6/15/09 TRUE

Anderson

Coral Gables Pediatrics M 3/4/03 TRUE

Samuel Coral Gables Pediatrics M 3/4/03 FALSE

Fort Lauderdale Cardiology F 11/24/79 FALSE

Miami Cardiology F

FALSE

Rodriguez

Fort Lauderdale Cardiology F 8/1/04 FALSE

Coral Gables Obstetrics F 11/1/90 FALSE

Hollywood Obstetrics M 3/15/90 FALSE

Nelson

Hollywood Obstetrics M 6/8/00 TRUE

Coral Gables Obstetrics F 5/2/79 TRUE

Hollywood Cardiology M 3/4/03 TRUE

Coral Gables Internal Medicine M 3/4/03 FALSE

Hollywood Internal Medicine M 11/24/79 TRUE

Miami Cardiology M 5/15/09 FALSE

Anderson Paul Fort Lauderdale Internal Medicine M 6/15/09 TRUE

Miami Cardiology F 3/4/03 TRUE

Andrews

Fort Lauderdale Cardiology F 11/1/90 TRUE

Baker

Miami Obstetrics F 6/8/00 FALSE

Coral Gables Pediatrics F 5/2/79 TRUE

Hollywood Pediatrics M 5/15/09 FALSE

William Hollywood Internal Medicine M 6/15/09 FALSE

Coral Gables Internal Medicine M 3/4/03 FALSE

Brandy Fort Lauderdale Pediatrics F 11/15/07 TRUE

Beau Miami Cardiology M 12/14/00 FALSE

Verna Fort Lauderdale Obstetrics F 12/1/07 TRUE

Fletcher

Fort Lauderdale Obstetrics M 11/1/08 FALSE

Vincent

Miami Cardiology F 11/1/08 FALSE

Alexander Miami Cardiology M 8/1/82 FALSE

Mark Coral Gables Cardiology M 12/15/85 FALSE

Coral Gables Pediatrics F 8/22/77 TRUE

Jensen William Miami Pediatrics M 1/16/81 TRUE

Phillips

Fort Lauderdale Pediatrics M

FALSE

Kaufman

Fort Lauderdale Cardiology M

TRUE

Beverley Keith Miami Internal Medicine M

FALSE

Kaufman Anita Coral Gables Internal Medicine F

TRUE

Hannan

Coral Gables Cardiology M

FALSE

Baker Barbara Miami Pediatrics F

FALSE

Wiklendt Brady Fort Lauderdale Cardiology M 6/15/09 FALSE

ID

Last Name

First Name

City

Specialty

Gender

Board Certified

New Patients

1

Feld

Alana

M

Internal Medicine

5

7

8

4

FALSE

Polanco

Crystal

Coral Gables

Cardiology

3

30

81

TRUE

Nunez

Francisco

Miami

2/3/01

Campion

George

Hollywood

4/

12

6

Rivera

9

13

Jones

Jeffrey

Pediatrics

10

14

82

Ledon

Jenna

3/

19

80

Fernandez

Lauren

Fort Lauderdale

Obstetrics

4/

18

93

Kaufman

Marjorie

3/

15

90

Brown

Megan

Dermatology

8/9/

79

11

Richman

Tara

1/15/

88

Spencer

Tom

9/30/

94

Dunn

Adam

9/7/

96

Naab

8/

22

Webb

Benjamin

10/3/

97

16

Walsh

Carl

3/3/88

17

Sheppard

Eleanor

12/12/93

O’Sullivan

10/30/01

Jackson

Jason

7/1/

92

20

Silva

Jessica

6/2/

62

21

Bethune

Katrina

6/7/04

Shock

Krystle

6/15/92

23

Simmons

Lawrence

2/3/

99

24

Ripley

Louis

2/2/

89

25

Marion

11/25/97

26

Sywolski

Neil

4/22/

78

27

Watson

7/26/94

28

Ramsie

3/22/00

29

Ostrow

Robert

7/4/78

Villagomez

11/26/04

31

Vogel

Roselande

9/20/

86

32

Serna

Stacy

5/10/

98

33

Barnes

6/2/89

34

Felsher

Alexander

5/18/03

35

7/26/04

36

Chandler

Allison

11/28/90

37

Wiklendt

Ann

6/26/97

38

Donnelly

Anne

3/1/04

39

Young

Becky

10/30/

91

40

Patalano

Bridget

6/26/89

41

Brittany

9/4/

85

42

Beverley

Chris

2/3/82

43

Miller

Christin

4/3/

68

44

Tucker

7/3/93

45

10/5/89

46

Diamond

Jacqueline

2/11/04

47

Gomez

Jennifer

5/31/

95

48

Weisman

10/15/88

49

Irani

2/27/92

50

Wall

12/15/85

51

Diaz

Katherine

12/25/

83

52

Pena

Kimberly

6/9/02

53

Larson

Martamaria

9/20/91

54

Goodman

Max

10/4/04

55

Kantor

Rachel

12/18/90

56

Stack

Renee

4/1/01

57

Gold

Samuel

10/1/97

58

Staab

Steven

11/24/79

59

Mazzillo

Tiffany

8/22/

67

60

Huff

8/1/04

61

Labossiere

Craig

11/1/90

Ershowsky

Elvis

3/15/90

63

Malberg

6/8/00

64

Sessler

Lindsay

5/2/79

65

Counts

Matthew

7/27/

75

66

Geisler

Meghan

7/1/

77

Pierre

Allberto

6/8/89

Weiner

Amie

7/8/79

69

Burgmeier

Amy

8/21/00

70

Honey

Angela

2/1/04

71

Skinner

5/6/82

72

Deedy

6/27/00

73

7/4/86

74

Taylor

Kayci

11/23/94

Collier

Kelsey

9/30/86

76

Thibodeau

5/2/81

Martinez

11/25/00

McClary

5/22/98

Baker

Reeva

8/1/82

Camar

Theresa

McElroy

8/22/77

Rumpke

Yvonne

1/16/81

Mark

5/18/70

84

Vicki

5/15/09

Annie

Ida

6/1/09

87

Lisa

6/15/09

5/15/08

Anderson

Jerry

10/1/00

Antonio

Melissa

11/15/07

Antonelli

12/14/00

Grotegut

12/1/07

Gover

Nick

11/1/08

Mangini

Scott

McConnell

Thomas

Barbara

Helquiest

Tim

Travis

100

Ted

101

Tina

102

Beverly

103

Micah

104

Pierce

Erica

105

Piereder

Brian

1/1/09

106

Willes

107

Wilshaw

Cynthia

108

Talbot

Ian

3/4/03

109

Taggart

Keith

4/1/99

110

Michael

6/1/07

111

Tahbo

4/3/08

112

Jed

113

Olena

114

Paul

115

Rondas

116

Rogers

Latisha

117

Rodriguez

Hilary

8/22/67

118

Raimee

119

Lorscheider

Verna

120

Nelson

William

121

Beau

122

Olson

Brandy

123

Olsen

Calvin

124

Ollivier

Clinton

125

Jensen

Dennis

126

Hannan

Dave

127

128

Andrews

Anita

129

Julie

130

Denise

131

Pederson

Angie

132

Perry

Lyle

133

Vincent

134

Zumiez

Brad

135

Fletcher

136

Eagleston

137

Durfey

138

Carlos

139

Alesha

140

Riley

141

Roberts

142

Phillips

Mary

143

144

Brady

6/1/05

145

Kevin

4/25/06

146

10/1/05

147

11/1/06

148

Barry

12/15/08

149

8/4/06

150