Data File needed for this Case Problem: Office.xIsx

Office Cart Robert Trenton is a shipping manager at Office Cart, an online office supply store located in Muncie, Indiana. He wants to use an Excel workbook to track shipping orders. Robert asks you to create and format a worksheet that he can use to enter information for packing slips.

Complete the following:

$1.$ Open the Office workbook located in the Excel$2-$ Case folder included with your Data Files, and then save the workbook as Office Cart Packing Slip in the location specified by your

instructor

$2.$ In the Documentation sheet, enter your name in cell B$3$ and the date in cell B$4.$

$3.$ Set the following formats in the Documentation sheet:

a$.$ Merge cells A$1$ and B$1,$ and then left$-$align the contents of the merged cell. Change the font to

$28-$point white Calibri Light on a dark green background

b$.$ Change the font of the range A$3$:$5$ to $14-$point white Calibri Light on a dark green background,

c$.$ Change the format of the date value in cell B$4$ to the Long Date style, and then left$-$align the date in the cell.

d$.$ Italicize the text "Office Cart" in cell B$5.$

e$.$ Add a border around each cell in the range A$3$:$85.4.$ Insert a new worksheet at the end of the workbook and name it Packing Slip.

$5.$ In the Packing Slip worksheet, select all of the cells in the worksheet. $($Hint: Click the Select All button at the intersection of the row and column headings, or press the Ctrl$+$A keys.$)$ Change the font to $10-$point dark green Calibri.

$6.$ Add a thick box border around the range A$1$:$40.$

$7.$ For the range A$1$:D$3,$ change the format to a white Calibri Light font on a dark green background

$8.$ Set the width of column A to $15$ characters, Set the width of column B to $20$ characters. Set the width of column C to $30$ characters. Set the width of column D to $20$ characters.

$9.$ Merge the range A$1$:$03,$ Merge the range Cl:D$3,$ and then right$-$ and top$-$align the merged cell.

Set the row height of row $1$ to $36$ points and the heights of rows $2$ and $3$ to $15$ points.

$10.$ In cell Al$,$ enter the following three lines of text, pressing the Alt$+$Enter keys to start a new line within the cell:

Office Cart

$14$ Trenke Lane

Muncie, IN $47303$

Format the first line in a $26-$point bold font.

$11.$ In cell C$1,$ enter Packing Slip, and then format the text in a $26-$point bold font using the Headings font of the current theme,

$12.$ In the range A$5$:A$7,$ enter the following three lines of text in a bold font, and then right$-$align the text and indent the text one character:

Order Date

Order Number

Purchase Order

$13.$ Formal cell B$5$ in the Long Date format and left$-$align the cell contents. Insert border lines around each of the cells in the range B$5$:B$7.$

$14.$ In the range CS:C$7,$ enter the following three lines of text, and then use the Format Painter to copy the formats from the range A$5$:B$7$ to the range C$5$:$07$:

Date

Sales Rep

Account Num

$15.$ In cell B$9,$ enter Ship To$.$ In cell D$9,$ enter Bill To$.$ Format the text in both cells in bold.

$16.$ In cell A$10,$ enter Address, format the text in bold, right$-$align the text, and then indent it one character.

$17.$ Merge the cells in the range B$10$:$15,$ left$-$ and top$-$align the cell contents, insert a border around the merged cell, and then wrap the text within this cell.

$18.$ In cell C$10,$ enter Address. Copy the format from the range ATO:B$15$ to the range C$10$:$15.$

$19.$ Enter the following data in the indicated cells in the worksheet: cell A$17$: Item cell B$17$: Product No$.$ cell C$17$: Description cell D$17$: Order Quantity

$20.$ Format the range A$17$:$17$ in bold white Calibri on a dark green background

$21.$ Format the range A$18$:$18$ with a bottom border and a light green background. Format the range A$19$:$19$ with a bottom border and a white background. Copy the format in the range A$18$:D$19$ to the range A$20$:D$27.$

$22.$ Apply a Top and Double Bottom Border to the range A$28$:$28.$ Merge the contents of the range A$28$:$28.$ Enter Total in cell A$28,$ bold the text, and right$-$align the cell contents.

$23.$ In cell D$28,$ enter a formula to calculate the sum of the values in the range D$18$:$27.$

Bold the text.

$24.$ In cell A$30,$ enter Comments and then bold the text.

$25.$ Merge the range A$31$:D$39,$ left$-$ and top$-$align the cell contents, and then add a thick box border around the merged cell.

Excel $|$ Tutorial $2$ Formatting Workbook Text and Data

$26.$ In cell D$40,$ enter Thank you for your business! in italic $16-$point Calibri, and then right$-$align the cell contents.

$27.$ Make sure the worksheet is set to portrait orientation, and then add a footer that displays your name in the left section, the filename in the center section, and the current date in the right section. Scale the printout so that it fits onto a single page.

$28.$ Enter the packing slip data shown in Figure $2-48.$ Save and close the workbook.