72 Excel 2013

Tools & Terms You’ll Use:

Comma Style: This option allows you to format numbers with a thousands separator.

Increase/Decrease Decimal: This option allows you to show more or fewer decimal places for a more precise value.

Accounting Number Format: This option allows you to format numbers as Dollars, Euros or other currency.

Percent Style: This option allows you to format numbers as a percent.

Lesson Overview:You have decided to watch some movies and want to only watch the top movies to date of the current year. A quick search allows you to make a list of the top 15 movies and helps you to organize your viewing schedule.

In this lesson, you will create a new worksheet containing the information about the top grossing movies as of September 1, 2013. You will then learn to format the numbers in your worksheet.

New Skills: Applying Accounting Number Format

Applying Percent Style

Applying Comma Style

Applying Increase/ Decrease Decimal

Unit 4: Working with Numbers & Formulas

Top Grossing MoviesFormatting Numbers

Lesson 4.1 N

ote:

Thi

s is a

dra

ft o

nly.

The

cont

ent h

as n

ot b

een

final

ized

for p

ress

.

DRAFT

73Excel 2013

Instructions

1 Using Microsoft Excel 2013, create a new Blank workbook.

2 Save the fi le as 4.1 Top Grossing Movies in your My Excel Projects folder.

3 At the top of the worksheet, key the data as shown in 4.1 Source Doc. A.

4 Make the following formatti ng changes:

a. Resize the column widths so that all data displays properly

b. Change the Orientati on to Landscape

c. Insert a blank row before Row 2

d. Change the font of A1 through I18 to Times New Roman

e. Right align cell A3, D3, E3, and F3

f. Change the font size of A1 to 16

g. Merge and Center cells A1 through F1

h. Change the font size of Row 3 to 14

i. Change the font color of Row 1 to Blue

j. Change the row height of Rows 1 to .5”

k. Cut Cell A21 and Paste it to Cell F21

l. Right align the text in cell F21

m. Italicize the text in cell F21

Unit 4: Working with Numbers & Formulas

Lesson 4.1Formatting Numbers

5

Not

e: T

his i

s a d

raft

onl

y. T

he co

nten

t has

not

bee

n fi n

aliz

ed fo

r pre

ss.

DRAFT

74 Excel 2013 Unit 4: Working with Numbers & Formulas

Formatting NumbersLesson 4.1

5 From the HOME tab, Number group, do the following:

a. Select Column D and format as dollars

b. Select Column E and format with a thousands separator (comma)

c. Select Column E and decrease the decimal twice

d. Select Column F and format as a percent

e. Select Column F and increase the decimal once

6. When complete, your document should look similar to 4.1 Source Doc. B.

Proof Save Print

5

Not

e: T

his i

s a d

raft

onl

y. T

he co

nten

t has

not

bee

n fi n

aliz

ed fo

r pre

ss.

DRAFT

75Excel 2013

1234567891011121314151617181920

A B C D E F2013 Domestic Grosses as of September 1, 2013Rank Movie Title Studio Total Gross # Theatres % of Total by this Date

1 Iron Man 3 Buena Vista 408834393 4253 12 Despicable Me 2 Universal 354699250 4003 0.9683 Man of Steel Warner Bros. 290650276 4207 0.9994 Monsters University Buena Vista 263568953 4004 0.9825 Fast & Furious 6 Universal 238664225 3771 16 Oz The Great and Powerful Buena Vista 234911825 3912 17 Star Trek Into Darkness Paramount 228225160 3907 0.9988 World War Z Paramount 200286551 3607 0.999 The Croods Fox 187116898 4065 1

10 The Heat Fox 157305506 3184 0.98611 The Great Gatsby (2013) Warner Bros. 144840419 3550 112 Identity Thief Universal 134506920 3230 113 The Conjuring Warner Bros. 133896232 3115 0.97414 Grown Ups 2 Sony 130019905 3491 0.97315 The Wolverine Fox 127822228 3924 0.965

Source: boxofficemojo.com

4.1 Source Doc. A

Lesson 4.1Formatting Numbers

Unit 4: Working with Numbers & Formulas

Not

e: T

his i

s a d

raft

onl

y. T

he co

nten

t has

not

bee

n fi n

aliz

ed fo

r pre

ss.

DRAFT

76 Excel 2013

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

AB

CD

EF

Ran

kM

ovie

Titl

eSt

udio

Tota

l Gro

ss#

Thea

tres

% o

f Tot

al1

Iron

Man

3B

uena

Vis

ta40

8,83

4,39

3.00

$

4,25

310

0.0%

2D

espi

cabl

e M

e 2

Uni

vers

al35

4,69

9,25

0.00

$

4,00

396

.8%

3M

an o

f Ste

elW

arne

r Bro

s.29

0,65

0,27

6.00

$

4,20

799

.9%

4M

onst

ers U

nive

rsity

Bue

na V

ista

263,

568,

953.

00$

4,

004

98.2%

5Fa

st &

Fur

ious

6U

nive

rsal

238,

664,

225.

00$

3,

771

100.0%

6O

z Th

e G

reat

and

Pow

erfu

lB

uena

Vis

ta23

4,91

1,82

5.00

$

3,91

210

0.0%

7St

ar T

rek

Into

Dar

knes

sPa

ram

ount

228,

225,

160.

00$

3,

907

99.8%

8W

orld

War

ZPa

ram

ount

200,

286,

551.

00$

3,

607

99.0%

9Th

e C

rood

sFo

x18

7,11

6,89

8.00

$

4,06

510

0.0%

10Th

e H

eat

Fox

157,

305,

506.

00$

3,

184

98.6%

11Th

e G

reat

Gat

sby

(201

3)W

arne

r Bro

s.14

4,84

0,41

9.00

$

3,55

010

0.0%

12Id

entit

y Th

ief

Uni

vers

al13

4,50

6,92

0.00

$

3,23

010

0.0%

13Th

e C

onju

ring

War

ner B

ros.

133,

896,

232.

00$

3,

115

97.4%

14G

row

n U

ps 2

Sony

130,

019,

905.

00$

3,

491

97.3%

15Th

e W

olve

rine

Fox

127,

822,

228.

00$

3,

924

96.5%

Source: b

oxofficem

ojo.com

2013

Dom

estic

Gro

sses

as o

f Sep

tem

ber 1

, 201

3

4.1 Source Doc. B

Lesson 4.1 Formatting Numbers

Unit 4: Working with Numbers & Formulas

Not

e: T

his i

s a d

raft

onl

y. T

he co

nten

t has

not

bee

n fi n

aliz

ed fo

r pre

ss.

DRAFT

77Excel 2013

Tools & Terms You’ll Use:

Lesson Overview:You want to watch all of the movies made by studio. To do this, you can sort the studio column alphabetically to find all the movies made by the same studio.

In this lesson, you will recall the Top Grossing Movies worksheet and sort a column alphabetically.

Lesson 4.2

New Skills: Sorting Text

Unit 4: Working with Numbers & Formulas

Top Grossing Movies 2Sorting Text

Sort & Filter: This pull down arrow allows you to organize your data so it’s easier to analyze. You can sort the selected data from smallest to largest, largest to smallest, or filter out specific values.

Not

e: T

his i

s a d

raft

onl

y. T

he co

nten

t has

not

bee

n fin

aliz

ed fo

r pre

ss.

DRAFT

78 Excel 2013

Lesson 4.2

Instructions

4

c.1 Open your existi ng document 4.1 Top Grossing Movies.

2 Save the fi le as 4.2 Top Grossing Movies 2 in your My Excel Projects folder.

3 Make the following formatti ng change:

a. Insert a blank row above Row 4

4 From the HOME tab, Editi ng group, do the following:

a. Select cells C5 to C19

b. Select Sort A-Z

c. Choose Expand the Selecti on and Sort

5 When complete, your document should look similar to 4.2 Source Doc. B.

Proof Save Print

Unit 4: Working with Numbers & Formulas

Sorting Text N

ote:

Thi

s is a

dra

ft o

nly.

The

cont

ent h

as n

ot b

een

fi nal

ized

for p

ress

.

DRAFT

79Excel 2013

Lesson 4.2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

AB

CD

EF

Ran

kM

ovie

Titl

eSt

udio

Tota

l Gro

ss#

Thea

tres

% o

f Tot

al b

y Se

pt. 1

1Ir

on M

an 3

Bue

na V

ista

408,

834,

393.

00$

4,

253

10

0.0%

2D

espi

cabl

e M

e 2

Uni

vers

al35

4,69

9,25

0.00

$

4,00

3

96.8

%3

Man

of S

teel

War

ner B

ros.

290,

650,

276.

00$

4,

207

99

.9%

4M

onst

ers U

nive

rsity

Bue

na V

ista

263,

568,

953.

00$

4,

004

98

.2%

5Fa

st &

Fur

ious

6U

nive

rsal

238,

664,

225.

00$

3,

771

10

0.0%

6O

z Th

e G

reat

and

Pow

erfu

lB

uena

Vis

ta23

4,91

1,82

5.00

$

3,91

2

100.

0%7

Star

Tre

k In

to D

arkn

ess

Para

mou

nt22

8,22

5,16

0.00

$

3,90

7

99.8

%8

Wor

ld W

ar Z

Para

mou

nt20

0,28

6,55

1.00

$

3,60

7

99.0

%9

The

Cro

ods

Fox

187,

116,

898.

00$

4,

065

10

0.0%

10Th

e H

eat

Fox

157,

305,

506.

00$

3,

184

98

.6%

11Th

e G

reat

Gat

sby

(201

3)W

arne

r Bro

s.14

4,84

0,41

9.00

$

3,55

0

100.

0%12

Iden

tity

Thie

fU

nive

rsal

134,

506,

920.

00$

3,

230

10

0.0%

13Th

e C

onju

ring

War

ner B

ros.

133,

896,

232.

00$

3,

115

97

.4%

14G

row

n U

ps 2

Sony

130,

019,

905.

00$

3,

491

97

.3%

15Th

e W

olve

rine

Fox

127,

822,

228.

00$

3,

924

96

.5%

Source: b

oxofficem

ojo.com

2013

Dom

estic

Mov

ie G

ross

es

4.2 Source Doc. A

Unit 4: Working with Numbers & Formulas

Sorting Text

Not

e: T

his i

s a d

raft

onl

y. T

he co

nten

t has

not

bee

n fi n

aliz

ed fo

r pre

ss.

DRAFT

80 Excel 2013

Lesson 4.2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

AB

CD

EF

Ran

kM

ovie

Titl

eSt

udio

Tota

l Gro

ss#

Thea

tres

% o

f Tot

al b

y Se

pt. 1

1Ir

on M

an 3

Bue

na V

ista

408,

834,

393.

00$

4,

253

10

0.0%

4M

onst

ers U

nive

rsity

Bue

na V

ista

263,

568,

953.

00$

4,

004

96

.8%

6O

z Th

e G

reat

and

Pow

erfu

lB

uena

Vis

ta23

4,91

1,82

5.00

$

3,91

2

99.9

%9

The

Cro

ods

Fox

187,

116,

898.

00$

4,

065

98

.2%

10Th

e H

eat

Fox

157,

305,

506.

00$

3,

184

10

0.0%

15Th

e W

olve

rine

Fox

127,

822,

228.

00$

3,

924

10

0.0%

7St

ar T

rek

Into

Dar

knes

sPa

ram

ount

228,

225,

160.

00$

3,

907

99

.8%

8W

orld

War

ZPa

ram

ount

200,

286,

551.

00$

3,

607

99

.0%

14G

row

n U

ps 2

Sony

130,

019,

905.

00$

3,

491

10

0.0%

2D

espi

cabl

e M

e 2

Uni

vers

al35

4,69

9,25

0.00

$

4,00

3

98.6

%5

Fast

& F

urio

us 6

Uni

vers

al23

8,66

4,22

5.00

$

3,77

1

100.

0%12

Iden

tity

Thie

fU

nive

rsal

134,

506,

920.

00$

3,

230

10

0.0%

3M

an o

f Ste

elW

arne

r Bro

s.29

0,65

0,27

6.00

$

4,20

7

97.4

%11

The

Gre

at G

atsb

y (2

013)

War

ner B

ros.

144,

840,

419.

00$

3,

550

97

.3%

13Th

e C

onju

ring

War

ner B

ros.

133,

896,

232.

00$

3,

115

96

.5%

Source: b

oxofficem

ojo.com

2013

Dom

estic

Mov

ie G

ross

es

4.2 Source Doc. B

Unit 4: Working with Numbers & Formulas

Sorting Text N

ote:

Thi

s is a

dra

ft o

nly.

The

cont

ent h

as n

ot b

een

fi nal

ized

for p

ress

.

DRAFT