1. No category

Your Office Microsoft® Office 2013
Excel Module 1 Workshop 2
Concept Check Answers
1. Why should you format data in Excel?
Data need formatting when there is additional context to add. For example, adding a $ sign to
a number classifies the value as currency. Commas help the reader correctly grasp the
magnitude of large numbers. A % sign indicates that the number represents a proportion.
Color can indicate a classification, magnitude, or issue.
There are many reasons to format data. The best reason(s) to format data is to add
information.
How might you format data for a person who is colorblind?
Add formatting that is not color-dependent but highlights the data in a different way. Bold,
italic, parentheses, hyphens, hatched backgrounds, etc.
2. What are the different functions made available via the AutoSum button?What does
each function calculate?

Sum – The additive total of a range of cells

Average – The average of a range of cells

Count Numbers – The count of the cells in a range that contain numeric values

Max – The maximum numeric value in a range of cells

Min – The minimum numeric value in a range of cells
3. What are the advantages of calculating totals in a table total row?
The range that is included in the calculation is automatically determined by the scope of the
table. Several statistics are available via the arrow in each cell in the total row – to allow each
cell to contain a statistic unique to its column data. Table data can be filtered so the table
“totals” can be used in what-if analysis without having to modify the specified ranges in the
functions.
Your Office Microsoft Office 2013
Page 1 of 2
4. What character precedes all formulas in Excel?
= (equal sign)
What purpose do parentheses serve in Excel formulas?
They can change the order of mathematical operations.
5. What is conditional formatting?
Conditional formatting is the application of character and/or cell formatting that is
determined by data values relative to benchmarks or to other data values.
How can conditional formatting assist in decision-making?
Decision-making can be assisted by conditional formatting by calling attention to values that
meet certain decision criteria, by identifying outlier values, and in some cases actually
recommending decisions.
6. List two reasons why it may be necessary to hide rows or columns of information in a
worksheet.
To hide information, such as parameters, from users that they don’t need to see.
Another reason to hide information would be hiding detailed information used to calculate
totals until such time that the person using the worksheet would like to see it.
7. Why is the PDF file format good for saving documentation?
It stores a copy of a worksheet or workbook in a format that is easily read but difficult to
modify.
Your Office Microsoft Office 2013
Page 2 of 2
Format the column
headings in B2:F3 as
Align Center and Bold
Company
Ticker
Price
SharesFormat
Owned B7:F7 as %
2 decimal
Annualwith
Dividends
/ Shareplaces
Yield
Total Dividends Received
Apply the Title cell style and Bold to the worksheet title in A1:N1; cells
are merged and centered
Save workbook as
e01ws02Portfolio_LastFirst.xlsx
Grid lines turned off
Excellent Wealth Management
Prime
Heartland Diamond 2x4 Lumber HiTENd
Steel
Healthcare Five LLP
Co.
Security
PRIME
PULSE
FIVE
WOOD
TEN
$41.00
$60.00
$5.00
$24.00
$10.00
243.90
166.67
2000.00
416.67 1000.00
$1.48
$1.48
$0.16
$1.20
$0.48
3.61%
2.47%
3.20%
5.00%
4.80%
$360.97
$246.67
$320.00
$500.00 $480.00
Format as Currency
B4:F4, B6:F6, B8:G8,
B12:N16, and B20:N25
Average Annual Income Per Stock
$381.53
Conditional Formatting for
Above Average values in
B20:M24 - green text,
green fill
N12:N16 - columns summed
Rows 10:17 hidden (you will have to unhide to check the next metric) AVERAGE calculated G8
A19:N25 Format
as Table applied,
sorted by STOCK
B19:N25 rows
summed
STOCK
FIVE
PRIME
PULSE
TEN
WOOD
Total
Apply Cell Styles 20% Accent 3 to A20:A25,
B25:N25, and N20:N24
JAN
$740.00
$0.00
$0.00
$100.00
$0.00
$840.00
FEB
$0.00
$0.00
$6.67
$100.00
$50.00
$156.67
MAR
$0.00
$90.24
$0.00
$100.00
$0.00
$190.24
Conditional Formatting for
Top 10 Items in B20:M24
APR
$740.00
$0.00
$0.00
$100.00
$0.00
$840.00
MAY
$0.00
$0.00
$6.67
$100.00
$50.00
$156.67
Total Dividends by Month
JUN
JUL
AUG
$0.00 $740.00
$0.00
$90.24
$0.00
$0.00
$0.00
$0.00
$6.67
$100.00 $100.00 $100.00
$0.00
$0.00
$50.00
$190.24 $840.00 $156.67
SEP
$0.00
$90.24
$0.00
$100.00
$0.00
$190.24
OCT
$740.00
$0.00
$0.00
$100.00
$0.00
$840.00
NOV
$0.00
$0.00
$6.67
$100.00
$50.00
$156.67
DEC
Total
$0.00 $2,960.00
$90.24 $360.97
$0.00
$26.67
$100.00 $1,200.00
$0.00 $200.00
$190.24 $4,747.64
A25:N25 has Top Border and N19:N25 has Left Border
Conditional Formatting for
Above Average values in
N20:N24 - double underline
Formula for stock yield
(=B6/B4) entered in B7,
formula copied to C7:F7
e01ws02Portfolio_LastFirst.xlsx
Page 1 of 2
e01ws02Portfolio_LastFirst
A4, A16, B4, B16, and C4 updated
Create Date
12/15/2015
Mod. Date
mm/dd/yyyy
By Whom
Michael Money
By Whom
Lastname, Firstname
Create Date
Sheet Name
mm/dd/yyyy Dividend Portfolio
e01ws02Portfolio_LastFirst.xlsx
Description
Stock Portfolio Monthy Dividend Analysis
Mod. Description
Completed Mr. Money's Monthly Dividend Income
worksheet
Workbook Name
Creator
Michael Money
Purpose
Monthly Dividend Income from Stock
Portfolio
e01ws02Portfolio_LastFirst.xlsx
Last Version Backup Name
Page 2 of 2
e01ws02Portfolio_LastFirst
Save workbook as
e01ws02PriceChanges_LastFirst.xlsx
A1:F1 formatted Heading1
Enter date information,
formatted as Long Date
Excellent Wealth Management
Company
Ticker
Friday, October 1, 2010
Monday, January 3, 2011
Friday, April 1, 2011
Friday, July 1, 2011
Monday, October 3, 2011
Tuesday, January 3, 2012
Monday, April 2, 2012
Monday, July 2, 2012
Minimum
Maximum
Current Gross Margin
Silver Wheaton
SLW
$
26.67
$
38.58
$
42.83
$
32.41
$
29.16
$
30.60
$
33.68
$
26.93
$
26.67
$
42.83
$
0.26
A:F column widths adjusted
A theme has been applied
B2:F2 formatted Heading3
Price Performance
Microsoft Linn Energy Bank of America Verizon
Enter stock ticker and price data
MSFT
LINE
BAC
VZ
$ 24.38 $
32.18 $
13.30 $ 32.89
$ 27.98 $
37.65 $
14.19 $ 36.43
$ 25.48 $
39.47 $
13.37 $ 38.47
$ 26.02 $
39.20 $
11.09 $ 37.80
Apply Conditional Formatting for
$ 24.53 $
33.39 $
5.53 $ 36.34
each stock (i.e. B5:B12, C5:C12,
$ 26.76 $
39.15 $
5.80 $ 39.73
etc.) green gradient data bars
$ 32.29 $
38.17 $
9.68 $ 38.52
$ 30.56 $
38.50 $
8.05 $ 44.93
B13:F13 MIN( ) stock value
$ 24.38 $
32.18 $
5.53 $ 32.89
B14:F14 MAX( ) stock value
$ 32.29 $
39.47 $
14.19 $ 44.93
$
6.18 $
6.32 $
(5.25) $ 12.04
B15:F15 calculate current
gross margin
Format B15:F14 as
Accounting
Apply Conditional Formatting
smallest minimum in row 13,
largest maximum in row 14, and
smallest & largest current gross
margin in row 15
e01ws02PriceChngs_solution.xlsx
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall
1 of 3
e01ws02PriceChngs_LastFirst
Create the Auditing worksheet
Excellent Wealth Management
Price Performance
Company Silver Wheaton
Microsoft
Linn Energy
Bank of America
Ticker
SLW
MSFT
LINE
BAC
40452
26.67
24.38
32.18
13.3
40546
38.58
27.98
37.65
14.19
40634
42.83
25.48
39.47
13.37
40725
32.41
26.02
39.2
11.09
40819
29.16
24.53
33.39
5.53
40911
30.6
26.76
39.15
5.8
41001
33.68
32.29
38.17
9.68
41092
26.93
30.56
38.5
8.05
Minimum =MIN(B5:B12)
=MIN(C5:C12) =MIN(D5:D12) =MIN(E5:E12)
Maximum =MAX(B5:B12)
=MAX(C5:C12) =MAX(D5:D12) =MAX(E5:E12)
Current Gross Margin =B12-B5
=C12-C5
=D12-D5
=E12-E5
Verizon
VZ
32.89
36.43
38.47
37.8
36.34
39.73
38.52
44.93
=MIN(F5:F12)
=MAX(F5:F12)
=F12-F5
Formulas are shown in the
Auditing worksheet
e01ws02PriceChngs_solution.xlsx
Page 2 of 3
e01ws02PriceChngs_LastFirst
A4, B4, C4, A17, and C17 updated
Create Date
mm/dd/yyyy
Mod. Date
mm/dd/yyyy
By Whom
Description
Michael Money
By Whom
Mod. Description
Lastname, Firstname Added stocks, text formatting, conditional formatting,
and formulas to calculate the change in price from
quarter to quarter.
Create Date Sheet Name
mm/dd/yyyy Stocks
mm/dd/yyyy Auditing
Creator
Michael Money
Lastname, Firstname
Workbook Name
e01ws02PriceChanges_solution.xlsx
Last Version Backup Name
Purpose
To show the formulas used in
the Stocks worksheet
e01ws02PriceChngs_solution.xlsx
Page 3 of 3
e01ws02PriceChngs_LastFirst
Save workbook as
e01ws02SpaSchd_LastFirst.xlsx
Insert the Spa logo in cell D1, snapped to
grid between columns F and G.
Set the row height
of row 1 to 50
Worksheets are submitted
to fit on one page each in
landscape orientation
Format C6:C23 as hh:mm XM
Spa Manager:Irene Kai
A6:A23 formatted a mm/dd
Date
Day
12/21
12/21
12/21
12/21
Time
Monday
Monday
Monday
Monday
10:00 AM
12:00 PM
1:00 PM
4:00 PM
Apply the Organic theme
Apply Heading 3 cell style to A4:J4
Client's Last Name
Client's First Name
Treatment
Consultant
Jacobs
Robbins
Rinker
Eatton
Sharon
Kaylee
Danie
Michael
European Facial
European Facial
European Facial
Men's Facial
Istas
Istas
Mault
Niese
Price
Daily Total
$75
$75
$75
$75
$300
12/22 Tuesday
12/22 Tuesday
12:00 PM Jacobs
2:00 PM Robbins
Sharon
Kaylee
French Manicure
French Manicure
Mault
Niese
$19
$19
Sharon
Danie
Danie
Kaylee
Hot Stone Massage 1.5 hours
Foot Reflexology 1/2 hour
French Manicure
Hot Stone Massage 1.5 hours
Mault
Mault
Niese
Niese
$110
$35
$19
$110
$38
12/23
12/23
12/23
12/23
Wednesday
Wednesday
Wednesday
Wednesday
5:00 PM
3:00 PM
4:00 PM
6:00 PM
Jacobs
Rinker
Rinker
Robbins
$274
12/24 Thursday
12/24 Thursday
10:00 AM Rinker
6:00 PM Eatton
Danie
Michael
Mud Wrap
Swedish Massage 1 hour
Istas
Istas
$65
$70
12/25 Friday
12/25 Friday
10:00 AM Eatton
2:00 PM Reed
Michael
Amy
Rosacea Treatment
Swedish Massage 1 hour
Istas
Mault
$75
$70
$135
$145
$892
Total
Weekly Overview
Highest Daily Sales
$300
A4:J24 is formatted as Table Table Style Medium 2
Target Daily Sales:
$250
C29 contains Max(J6:J24) and
has the same format as J24
Column H is hidden
Conditional Formatting is
applied to J10, J13, J18,
J21, and J24
Merge and Center A2:J2,
apply Heading 4 cell style to A2:J2 and A26:A30;
A26:A30 aligned left
e01ws02SpaSchd_solution.xlsx
Format I6:I23 as Currency
with no decimals
J10, J13, J18, J21, and
J24 SUM formulas
Page 1 of 3
e01ws02SpaSchd_LastFirst
Turquoise Oasis Spa Consultant Availability
Spa Manager:Irene Kai
Therapists
Last Name First Name Days Available
Hours Available
I
s
t
a
s
C
h
r
i
s
t
y
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
N/A
10:00 am - 6:00 pm
N/A
N/A
10:00 am - 5:00 pm
1:00 pm - 8:00 pm
N/A
M
a
u
l
t
K
e
n
d
r
a
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
N/A
10:00 am - 7:00 pm
12:00 am - 8:00 pm
12:00 am - 8:00 pm
N/A
10:00 am - 7:00 pm
N/A
N
i
e
s
e
J
a
s
o
n
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday
N/A
12:00 am - 8:00 pm
1:00 am - 8:00 pm
10:00 am - 5:00 pm
N/A
10:00 am - 2:00 pm
N/A
C7:C13 contains each day
of the week
Merge & Center and
Vertical Text applied to
A7:A13, B7:B13, A15:A21,
B15:B21, A23:A29, and
B23:B29
e01ws02SpaSchd_solution.xlsx
Page 2 of 3
e01ws02SpaSchd_LastFirst
Create Date
12/5/2015
Mod. Date
mm/dd/yyyy
By Whom
Kia, Irene
By Whom
Lastname, Firstname
A8, B8, and C8:C11
updated
Create Date Sheet Name
12/5/2015 Schedule by Date
12/5/2015 Therapist Schedule
e01ws02SpaSchd_solution.xlsx
Description
Analysis of Spa sales and services
Mod. Description
Calculated daily totals
Determined maximum day's sales
Formatted daily sales totals that met target as green and
bold
Finished and formatted the Therapist Schedule
Workbook Name
Creator
Kia, Irene
Kia, Irene
Purpose
e01ws02SpaSchd_solution.xlsx
Last Version Backup Name
Page 3 of 3
e01ws02SpaSchd_LastFirst
Painted Paradise logo in cell A1;
cell A1 height set at 50
Save workbook as
e01ws02WeeklySales_LastFirst.xlsx
Theme set to Metropolitan
Dates in B4:H4 AutoFilled, formatted as
Short Date and Oriented Clockwise
Weekly Sales Analysis
12
12
12
/1
/1
2/
1/
20
20
15
15
5
5
5
5
15
01
01
01
01
20
/2
/2
/2
/2
0/
/9
/8
/7
/6
/1
12
12
12
12
Total
Commissions
Golf Equipment
Clubs $ 576.67 $ 209.60 $ 214.02 $ 249.16 $ 450.50 $ 450.95 $ 552.35 $ 2,703.25 $
Golf Balls
72.08
26.20
22.93
28.64
55.06
61.20
61.37 $
327.48
Accessories
152.18
(49.70)
50.96
54.41
105.12
111.35
136.38 $
560.70 $
$ 800.93 $ 186.10 $ 287.91 $ 332.21 $ 610.68 $ 623.50 $ 750.10 $ 3,591.43
$
Clothing
Red Bluff Logo Wear $ 123.89
Brand Name Logo Wear
213.60
Accessories
89.71
$ 427.20
Consumables
Drinks $ 40.20
Snack Food
25.70
$ 65.90
Services
Green Fees $1,650.00
Golf Cart Rentals
440.00
Deposit Returns
(110.00)
Golf Club Rentals
55.00
Deposit Returns
(11.00)
Lessons
440.00
Conditional Formatting
$2,464.00
highlights high and low
$3,758.03
sales total in daily
totals for each sales
category and in daily
overall totals
e01ws02WeekSales_solution.xlsx
405.49
88.82
494.31
Ranges B6:H6, B11:H11,
$
35.59 $ 51.93 $ (62.80) $ 114.04 $ 106.42 $ 141.64 $
510.71
B16:H16, and B20:H20
64.82
77.89
114.38
157.64
208.90
253.97 $ 1,091.20 formatted as Accounting
26.69
(32.46)
47.10
63.73
78.83
92.80 $
366.40 Number Format
$ 127.10 $ 97.36 $ 98.68 $ 335.41 $ 394.15 $ 488.41 $ 1,968.31 Weekly Sales total for
Golf Equipment and
$
$
32.78 $
22.78
55.56 $
29.16 $
19.44
48.60 $
31.18 $
22.58
53.76 $
23.48 $ 73.18 $ 109.36 $
17.71
44.85
64.23 $
41.19 $ 118.03 $ 173.59 $
339.34 Consumables displayed
217.29 in Bold Green font due
556.63 to conditional formatting
$ 660.00 $ 577.50 $ 866.25 $1,031.25 $1,402.50 $2,062.50 $ 8,250.00
176.00
154.00
231.00
275.00
374.00
550.00 $ 2,200.00
(44.00)
(38.50)
(57.75)
(68.75)
(93.50)
(137.50) $ (550.00)
22.00
19.25
28.88
34.38
46.75
68.75 $
275.01
(4.40)
(3.85)
(5.78)
(6.88)
(9.35)
(13.75) $
(55.01)
176.00
154.00
231.00
275.00
374.00
550.00 $ 2,200.00
$ 985.60 $ 862.40 $1,293.60 $1,540.00 $2,094.40 $3,080.00 $ 12,320.00
$1,354.36 $1,296.27 $1,778.25 $2,527.28 $3,230.08 $4,492.10 $ 18,436.37
for sales benchmark
satisfaction
$ 1,540.00
Conditional formatting in cells
J9 and J25 indicated
commissions benchmark status
Page 1 of 4
e01ws02WeekSales_LastFirst
Sales by Time of Day
12/6/2015 Sunday
Time
9:00 AM
9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
12:00 PM
12:30 PM
1:00 PM
1:30 PM
2:00 PM
2:30 PM
3:00 PM
3:30 PM
4:00 PM
4:30 PM
5:00 PM
5:30 PM
6:00 PM
6:30 PM
7:00 PM
7:30 PM
8:00 PM
TOTAL
AVG. SALE
# OF SALES
MAX. SALE
MIN. SALE
A5:O28 formatted as Table Style Medium 10 and converted to a range
Golf Equipment
Clothing
Consumables
Clubs
Golf Balls
Accessories
Red Bluff Logo Wear
Brand Name Logo Wear
Accessories2
Drinks
Snack Food
Green Fees
Golf Cart Rentals
34.98
75.00
20.00
24.99
14.48
100.00
25.00
29.99
49.99
6.00
75.00
19.99
3.00
100.00
25.00
23.00
75.00
75.00
14.99
8.47
100.00
10.00
50.00
50.00
19.99
15.00
3.99
75.00
333.00
78.98
7.99
100.00
25.00
5.99
50.00
24.99
100.00
25.00
39.99
75.00
6.13
100.00
25.00
27.49
100.00
25.00
105.00
26.11
75.00
25.00
19.99
75.00
25.00
7.98
9.14
75.00
50.00
36.17
100.00
50.00
63.67
6.99
100.00
50.00
36.43
6.20
6.73
50.00
20.00
14.99
7.99
57.23
10.99
576.67
72.08
152.18
123.89
213.60
89.71
40.20
25.70
1650.00
440.00
144.17
14.42
30.44
24.78
42.72
14.95
8.04
6.43
82.50
31.43
4
5
5
5
5
6
5
4
20
14
333.00
29.99
57.23
36.43
78.98
26.11
15.00
7.99
100.00
50.00
63.67
6.13
14.99
14.99
8.47
5.99
3.00
3.99
50.00
20.00
Avg., # of Sales, and Max & Min sales calculated
e01ws02WeekSales_solution.xlsx
Services
Deposit Returns
Golf Club Rentals
Deposit Returns3
13.75
(7.50)
(7.50)
(3.00)
13.75
(15.00)
(7.50)
(7.50)
(3.00)
(7.50)
(7.50)
13.75
13.75
(7.50)
(7.50)
(15.00)
(15.00)
(15.00)
(120.00)
(10.00)
12
(7.50)
(15.00)
55.00
13.75
4
13.75
13.75
(3.00)
(3.00)
(12.00)
(3.00)
4
(3.00)
(3.00)
Lessons
Total
100.00
$229.98
$164.47
$160.98
$161.74
$98.00
100.00 $298.46
$102.50
80.00 $193.98
$534.47
$69.74
80.00 $214.99
$114.99
$123.63
$144.99
80.00 $308.11
$126.24
$134.62
$199.92
$213.16
$111.86
($0.01)
($10.01)
$50.22
440.00
3747.03
88.00
162.91
5
100.00
534.47
80.00
(10.01)
Totals in row 29 and column P calculated
Page 2 of 4
e01ws02WeekSales_LastFirst
A5:P29 formatted as Table Style Medium 3 with a total row
Sales by Time of Day
12/7/2015 Monday
Time
Clubs
9:00 AM
9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
Total
0.00
TOTAL 209.60
AVG. SALE
69.87
# OF SALES
3
MAX. SALE
75.00
MIN. SALE
63.67
Golf Equipment
Golf Balls
Accessories
Clothing
Consumables
Red Bluff Logo Wear
Brand Name Logo Wear
Accessories2
Drinks
Snack Food
14.99
19.99
1.99
Green Fees
Golf Cart Rentals
Services
Deposit Returns
Golf Club Rentals
Deposit Returns3
Lessons
7.98
24.99
25.00
9.90
17.88
26.20
8.73
3
9.90
7.98
20.00
11.00
9.72
24.99
49.70
24.85
2
24.99
24.71
14.99
35.59
17.80
2
20.60
14.99
19.99
64.82
12.96
5
19.99
9.99
11.71
26.69
6.67
4
9.99
1.99
100.00
3.00
3.00
32.78
5.46
6
9.00
1.78
0.00
22.78
7.59
3
10.80
4.99
25.00
650.00
43.33
15
50.00
25.00
20.00
180.00
20.00
9
20.00
20.00
0.00
(45.00)
(5.63)
8
(5.00)
(10.00)
11.00
22.00
11.00
2
11.00
11.00
0.00
(4.40)
(2.20)
2
(2.20)
(2.20)
100.00
175.00
87.50
2
100.00
75.00
Total
36.97
7.98
24.99
56.00
119.62
3.00
248.56
1445.76
62.86
193.22
(10.00)
Filter applied on column A
to display only rows 6:11
e01ws02WeekSales_solution.xlsx
Page 3 of 4
e01ws02WeekSales_LastFirst
Create Date
5/12/2015
Mod. Date
mm/dd/yyyy
By Whom
Aleeta Herriott
By Whom
Lastname, Firstname
Data updated in A8:B8 and C8:C12
Create Date
5/12/2015
5/12/2015
5/12/2015
Sheet Name
Weekly Sales
Hourly Sales - Sunday
Hourly Sales - Monday
e01ws02WeekSales_solution.xlsx
Description
Analysis of Pro Shop weekly sales
Mod. Description
Green background - high sales for the week
Red background - low sales for the week
Bold and green week total - sales proportion benchmark met
Commission - green arrow - minimum met
Commission - red arrow - minimum not met
Workbook Name
Creator
Herriott, Aleeta
Herriott, Aleeta
Herriott, Aleeta
Purpose
Examine Pro Shop sales by day and category
Examine Sunday sales in 1/2 hour increments
Examine Monday sales in 1/2 hour increments
e01ws02WeekSales_solution.xlsx
Last Version Backup Name
Page 4 of 4
e01ws02WeekSales_LastFirst