t

Module 2 Formatting Workbook Text and Data I Excel

J
ff case Problem 1
Data File needed for this Case Problem· Gre
• en.x 1sx
t Green Clean Homes Sean Patel is developin b .
professional home cleaning service in Toledo ~hiismess plan f?r Gr~en Clean Homes, a new
r predict the company's annual income and ex' · ~s par~ of his business plan, Sean needs to
workbook containing the projected income si:nses. ou will help him finalize and format the Excel
a ement. Complete the following:
1. Open the Green workbook located in the Exce1 2 >
Green Clean in the location specifi db . Casel folder, and then save the workbook as
e Yyour instructor
2. In the Documentation sheet, enter your name in cell B3· d h .
. d . . an t e date m cell B4.
3. Display the ate m cell B4 tn the Long Date format and left-aligned
4. Change the theme of the workbook to Facet. ·
5. Make the following formatting changes to the Doc umen tat1on · sheet:
a. Merge and center cells A1 and B1.
b. Apply the Accent2 cell style
. to the merged cell A1 and to th e range A3·A5 . .
c. In cell A1, set the font size to 22 points and bold th e tex t. Ita 1·1c1ze
· the word "Cl ean " ·m t he
company name.
d. Add borders around each cell in the range A3:B5 . Top-align the text in the range A3:B5.
e. Change the font color of the text in the range B3:B5 to Dark Green, Accent 2.
6. In the Income Statement worksheet, merge and center the range A1:Cl, and then apply the
Accent2 ~~II style to the merged cell. Change the font size to 24 points and the text style to
bold. Italicize the word "Clean" within the company name.
7. Make the following changes to the Income Statement worksheet:
a. Format the range A3:C3 with the Heading 1 cell style.
b. Format the range A4:C4,A9:C9 with the 40% - Accentl cell style.
c. Format cell B5 in the Accounting style with no decimal places.
d. Format cell 86 and the range Bl 0:B17 in the Comma style with no decimal places.
8. Add the followin g calculati ons to the workbook:
a. In cell C7, calculate the gross profit, which is equal to the gross sales minus the cost of sales.
b. In cell Cl 8, calculate the company's total operating expenses, which is equal to the sum of the
values in the range B10: B17. Format the value in the Accounting format with no decimal places.
c. In cell C20, calculate the company's operating profit, which is equal to its gross profit minus
its total operating expenses.
d. In cell C21, calculate the company's incomes taxes by multiplying its total operating profit by the
corporate tax rate (cell G25). Format the value in the Accounting format with no decimal places.
e. In cell C22, calculate the company's net profit, which is equal to the total operating profit
minus the income taxes.
9. Finalize the formatting of the Projected Income statement by adding the following:
a. Add a bottom border to the ranges A6:C6, A17:Cl 7, and A20:C20. Add a single top border
and a double bottom border to the range A22 :C22.
b. Indent the expenses categories in the range A10:A 17 twice.
10. Format the Financial Assumptions section as follows:
a. Add borders around all of the cells in the range E4:G25.
b. Format the range E3:G3 with the Heading 1 cell style.
c. Merge the cells in the ranges E4:E7, E9:E13, El 4:El 5, El 6:E18, and E2 0:E22 .
d. Top-align and left-align the range E4:E25.
e. Change the fill color of the range E4:F25 to Green, Accent 1, lighter 609/4,.
 Excel I Module 2 Formatting Workbook Text and Data

11 . U~e conditional formatting to highlight the net profit (cell C22 ) if its value is less than SSo,OOo
with a light red fill with dark red text. ..
12. Change the value in cell G9 from 4 to 5. Observe the impact th at hiring another cleaner ha5
0
the projected net profi t for the company in cell C22 . n
13. Format the printed version of the Income Statement worksheet as follows:
a. Add a manual page break between columns D and E.
b. For the first page, add a header that prints Prepared by followed by your name in the left
section of the header and the current date in the right section of th e header. Do not display
header text on any other page. . . .
c. For every page, add a footer that print: the workbook filename m th ~ left s~ction, ~ge followed
by the page number in the center section, and the worksheet name m the right section.
d. Set the margins to 1 inch on all four sides of the printout, and center th e contents of the work.
sheet horizontally within the printed page.
14. If you are instructed to print, print the entire contents of the workbook in portrait orientation.
15. Save and close the workbook.

Case Problem 2
...
Data File needed for this Case Problem: Peak.xlsx
a.
a., Peak Bytes Peter Taylor is an engineer at Peak Bytes, an Internet service provider located in Great
c( Falls, Montana. Part of Peter's job is to track the over-the-air connection speeds from the company's
transmitters. Data from an automated program recording Internet access times has been entered
into a workbook, but the data is difficult to interpret. He wants you to edit the workbook so that the
data is easier to read and the fast and slow connection times are quickly visible. He also wants the
workbook to provide summary statistics on the connection speeds. Complete the following:
1. Open the Peak workbook located in the Excel2 > Case2 folder, and then save the workbook as
Peak Bytes in the location specified by your instructor.
2. In the Documentation sheet, enter your name in cell 83 and the date in cell 84.
3. Apply the Banded theme to the workbook.
4. Format the Documentation sheet as follows:
a. Apply the Title cell style to cell A 1. Change the font style to bold and the font size to 24 points.
b. Add borders around the range A3 :B5.
c. Apply the Accent4 cell style to the range A3:A5 .
d. Top-align the contents in the range A3:B5.
5. In the Speed Test worksheet, move the data from the range A 1:D97 to the range A12:0108.
6. Copy cell A 1 from the Documentation sheet, and paste it into cell A 1 of the Speed Test worksheet.
7. In cell A2, enter Internet Speed Test Results. Apply the Heading 1 cell style to the range A2:D2.
8. In cell A4, enter Date and format it using the Accent4 cell style. In cell B4 enter 4/8/2017 and
1

format it using the Long Date format. Add a border around the cells in the range A4:84.
9. Format the data in the Speed Test worksheet as follows:
a. In !he range A 13:A 108, format the numeric date and time values with the Time format.
(Hmt: The Time format is in the Number Format box in the Number group on the Home tJb.l
b. In the range Cl 3:Dl 08, show the numbers with three decimal pl aces.
c. In the range A12:D12, apply the Accent4 cell style and center the text.
d. In the range A 12:D108, add borders around all of the cells.
10. Create a table of summary statistics for the Internet Speed Test as follows:
a. Copy the headings in the range Bl 2: Dl 2, and paste them into the ra nge 86:D6.
b. In cell A7, ente_r Average. In cell AB, enter Minimum. In cell A9 enter Maximum. Fonn,11thl'
range A7:A9 with the Accent4 cell style. ' ·
 Module 2 Fonnattlng Wo,l<hook Te,rt and Data I heel

c. In cell 87,_use the AVERAGE function to calculate the average ping value of the values in the
range 13.8108. In cell 88, use the MIN function to calculate the minimum ping value of the
values in the range 813:8108. In cell B9, use the MAX function to calculate the maximum
ping value of the values in the range 813:B108.
d. Copy the formulas from the range B7:89 to the range C7:D9 to calculate summary statistics
for the download and upload speeds from the Internet test.
e. Format the values in the range B7,C7:D9 to show two decimal places.
f. Add borders around all of the cells in the range A6:D9.
11 . ~se cond!tion~I formatting to high!igh~ ping values greater than 70 in the range B13:B 108 with a
light red fill with dark red text to highlight times when the Internet usually appears to be slow.
12. Use conditional formatting to highlight upload values less than 3.5 in the range Cl 3:Cl 08 with
a light red fill with dark red text.
13. Use conditional formatting to highlight download values less than 2 in the range D13:D108 with
a light red fill with dark red text.
14. In cell D11, enter the text Slow Connection. Use conditional formatting to display this text string
with a light red fill with dark red text. Center the text, and add a border around cell D11 .
15. Set the print titles to repeat the first 12 rows at the top of every page of the printout.
16. For the first page of the printout, add a header that prints Prepared by followed by your name
in the left section of the header and the current date in the right section of the header. Do not
display header text on any other page.
17. For every page, add a footer that prints the workbook filename in the left section, Page followed
by the page number followed by of followed by the number of pages in the center section, and
then the worksheet name in the right section.
18. If you are instructed to print, print the entire contents of the workbook in portrait orientation.
19. Save and close the workbook.

:! ,Lt µa;y < • .. 9 :l.. P :tK1'tBUNIR

1 f ff :tSU
,a;w ~.;

Case Pro bf em 3
w Data File needed for thi~ Case Probl(•m: V\'ail.x bx
C)
YuriTech Kayla Schwartz is the customer service. manager at YuriTech, an electronics and computer
z firm located in Scottsdale, Arizona. Kayla is analyzing the calling records for technical support calls
w
..J to YuriTech to determine which times are understaffed, resulting in unacceptable wait times. She has
..J
compiled several months of data and calculated the average wait times in one-hour intervals for each
<
:r: day of the week. You will format Kayla's workbook to make it easier to determine w~en YuriTech
u should hire more staff to assist with customer support requests. Complete the following:
1. Open the Wait workbook located in the Excel2 > Case3 folder, and then save the workbook as
WaitTimes in the location specified by your instructor.
2. In the Documentation sheet, enter your name in cell B3 and the date in cell B4.
3. Apply the Ion theme to the workbook.
4. Format the Documentation sheet as follows:
a. Format the title in cell A1 using a 36-point Impact font with the Purple, Accent 6 font color.
b. Format the range A3:A5 with the Accent6 cell style. . .
c. Add a border around the cells in the range A3:B5. Wrap the text within each cell, and
top-align the cell text. .
5. Copy the format you used in cell A1 of the Documentation sheet, and paste 1t to cell A1 of the
Wait Times worksheet.
, , Excel IModule 2 Fo=tti"& Wo,kbook ,,., and O<ta

6. Format the text in cell Al with 14-paint bold fo nt and the Purple, Accent6 font color.
7. In the range A14:H39, format the average customer wait times for each hour and day of the
week data as follows: st th
a. Merge and center the range A14:Hl 4, and apply th e Title cell yle to e mer~ed contents_
b. Change the number format of the data in the range Bl &:H39 to show_one decimal place.
39 th th
c. Format the column and row labels in the range Al S:Hl S,Al &:A wi e Accent6 cell
style. Center the column headings in the range Bl 5:Hl S. 34
8. In cell BS, enter the value 22 as an excellent wait time. In cell B&,
60 enter as a ~ood wait tirn
In cell 87, enter 45 as an acceptable wait time. In cell BS, enter as a poor wait !ime. In ce{
89, eflter 78 as a very poor wait time. In cell Bl 0, enter 90_as_an unacceptable wait time.
9. In the range A4:Cl O, apply the following formats to the wait time goals:
a. Merge and center the range A4:C4, and apply the Accent6 cell Slyle to the merged cells.
b. Add borders around the cells in the range A4:C10.
7
10. In cell E4, enter the label Average Wait lime (All Days) . I~ cell E ~:nt:r Ihde label Average Wait
Tnne (Weekdays). In cell ElO, enter the label Average Watt 11,me , ... ee en s).
11 . Merge and center the range E4:F6, wrap the text in the merged cell, center the cell content both
horizontally and vertically, and then apply the Accent6 cell style to the merged cell.
12. Copy the format from the merged cell E4:F6 to cells E7 and El 0.
13. In cell G4, enter a formula to calculate the average of the wait times in the range Bl 6:H39.
In cell G7, enter a formula to calculate the average weekday wait times in the range Cl 6:G39.
In cell Gl 0, calculate the average weekend rate times in the range 816:839,Hl 6:H39.
14. Merge and center the ranges G4:G6, G7:G9, and Gl0:G12, and then center the calculated
averages vertically within each merged cell.
15. Add borders around the cells in the range E4:G12.
16. Change the fill color of the range A5:C5 to a medium green, the fill color of the range A6:C6 to a
light green, the fill color of the range A7:C7 to a light gold, the fill color of the range A8:C8 to a
light red, and the fill color of the range A9:C9 to a medium red. Format the range A1O:Cl Owith
white text on a black background.
@ Explore 17. Use conditional formatting to highlight cells with custom formats as follows:
a. Select the range G4:G12,816:H39. Use conditional formatting to highlight cells with values
less than 22 with a custom format that matches the fill color used in the range A5:C5.
b. Use conditional formatting to highlight cells with values greater than 90 in the range
G4:G12,816:H39 with a custom format of a white font on a black fill.
c. Use conditional formatting to highlight cells with values between 22 and 34 in the range
G4:G12,816:H39 with a custom format that matches the fill color used in the range A6:C6.
d. Use conditional formatting to highlight cells with values between 34 and 60 in the range
G4:G12,816:H39 with a light gold fill color that matches the cells in the range A7:C7.
e. Use conditional formatting to highlight cells with values between 60 and 78 in the range
G4:G12,816:H39 with light red, matching the fill color of the cells in the range A8:C8.
f. Use conditional formatting to highlight cells with values between 78 and 90 in the range
G4:G12,816:H39 with medium red, matching the fill color of the cells in the range A9:C9.
18. In cell A41, enter the label Notes and then format it with the Title cell style.
19. M~r~e the range A42:H50. Top- and left-align the contents of the cell. Turn on text wrapping
w1~h•~ the merged cell. Add a thick outside border to the merged cell.
20. '-:4"1thm the merged cell in the range A42:HSO, summarize your conclusions about the wait
times. Answer whether the wait times are within acceptable limits on average for the entire
week, on weekdays, and on weekends. Also indicate whether there are times duri ng the week
that customer~ are exP<:rience very poor to unacceptable delays.
21. Format the printed version of the Wait limes worksheet as follows:
a. Scale the sheet so that it fi ts on a single page in portrait orientation.
b. Center the sheet on the page horizontally and vertica lly.
 Module 2 Formatting Workbook Text and Data I Excel EX 129

c. Add the header Prepared by followed by your name in the right section.
d. Add a footer that prints the filename in the left section, the worksheet name in the center
section, and the date in the right section .
22. If you are instructed to print, print the entire contents of the workbook.
23. Save and close the workbook.

w Case Problem 4
w Data File needed for this Case Problem: Pandaisia.xlsx

w panc/aisia Chocolates Anne Ambrose is the owner and head chocolatier of Pandaisia Chocolates,
a:: a chocolate shop located in Essex, Vermont. Anne has asked you to create an Excel workbook in
u which she can enter customer orders. She wants the workbook to be easy to use and read. The final
design of the order form is up to you. One possible solution is shown in Figure 2-47.

- 111~ - - Pandaisia ~ ates order for_m_______ _______ _ __ _

I p G

company title 1----+.

and address 2
13
4

( order data )1---- s-•~,---,•~-{

1
r-r.u
Order D ate
Order ID 'OR-541283
Iri<½i·, ~ 17 Tax Rate 5.20¼

· 17 Purcbue Order 'i>o.3iss14 -_____ _ Items Ordered 6 '

! SJ Total Charge
,9 Date !_~·. Al'_ril_21, ~ 17 i
salesperson I10
1 - - - - , -1.,-i
l_,
{SalesAccount
Repttsentatwe
Number
Anne Ambrose
'At63 - -
taking the order
1 total charge is
1 2-1 highlighted when
Bill To Kenn Dikkcr
billing information ~--...__,._ _ _ _"i 18 Osage Acenu, greater than $100
13 Hunt.,-iJle, Al 35811
1
/14 I L_______ ---- ---
1s7
16 Pamela Nielsen
( shipping information )f----,-!--.;,. ---•~ {Ship To 573 Km,berlv \'\'a,·
'Red Spnogs: NC 28377
I111
1~ J
lq I oduct ID o.. oon Qn· Pru:e. TotaI
20 1 GA2381 Chocolate GeneBl Assortment I $ 29.9J_ S 29.9J
211 F110043 Fruit Marzipan Assortment 3 18.95 56.85
22 1 WW1881 Wrapped O\ocolote Wafus 2 8.95 17.90
( items being ordered )i----n 1
1 2s-)
26 :
1 21
( order subtotal )f---------,,
l2s - Subtotal I04.10
I Tu 5+1
,29 ,
TOTAL S 110.H
30
' J
131 Comment• _ total charge f.."-••- - , ""'.'
"--c -. ..-.,,,: - - - - -
32 , 11 cndt"-UtW :Ui'.'•H" Vl~'~ Sll)(' h •J;n P~·1., .,•. 1 (.,,., • ., • •,. ' - - - - - - -
0

133 f'NJO}'.'!
' '
,34 I sales tax
35
136
'37
!Js
I
,39
 ·,
,·,,
'f
.
... EX 130 Excel I Module 2 Formatting Workbook Text and Data
1

Complete the following:

1 Open the Pandaisia workbook located in the Excel2 > Case3 folder, and then sav h
· as Pandaisia Order in the location specified by your instructor. et e Workbook
2. In the Documentation sheet, enter your name in cell B3 and the date in cell 84 _
3. Insert a worksheet named Order Form after the Documentation worksheet.
4. Enter the following information in the order form:
• The title and address of Pandaisia Chocolates
• The order date, order ID, and purchase order ID
• The date, sales representative, and account number for the order
• The billing address of the order
• The shipping address of the order
• A table listing every item ordered inclu~ing the item's product ID, description, quanti
ordered, price, and total charge for the 1tem(s) ty
• A comment box where Anne can insert additional information about the order
5. Include formulas in the order form to do the following:
a. For each item ordered, calculate the cost of the item(s), which is equal to the quantity
multiplied by the price.
b. Calculate the subtotal of the costs for every item ordered by the customer.
c. Calculate the sales tax for the order, which is equal to 5.2 percent times the subtotal value.
d. Calculate the total cost of the order, which is equal to the subtotal plus the sale tax.
6. Format the order form by doing the following:
a. Apply a different built-in Excel theme.
b. Change the font colors and fill colors.
c. Format a text string within a cell.
d. Align content within cells.
e. Format dates with the Long Date format.
f. Apply the Percent, Accounting, and Currency formats as appropriate.
g. Add borders around cells and ranges.
h. Merge a range into a single cell.
7. Pandaisia Chocolates includes a free complimentary truffle sample for every order over $100.
Use conditional formatting to highlight the total charge in bold colored font when it is greater
than $100.
8. Test your order form by entering the data shown in Figure 2-47. Confirm that the charge on your
order matches that shown in the figure .
9 - Set up the print version of the order form so that it prints in portrait orientation on a single sheet
Add a header and/or footer that includes your name, the date, and the name of the workbook.
10. If you are instructed to print, print the entire contents of the workbook.
l 1 • Save and close the workbook.