Excel 2016 In Practice Guided Project 1-3 Instructions

1 E31 )< ,I f. A B C D F G H 1 Wear-Ever Shoes 2 Current Stock 3 Product ID Product Co lo r Size Quantity Men's or\ Cost Value 16 WE013 Comfy Walking Shoes Taupe 2W 17 WE014 Comfy Walking Shoes Brown 8 3W 18 WE015 La2y Flip-Flops Pink 6 4W 19 WE016 Lazy Flip-Flops Pink 7 ow 20 WE017 La2y Flip-Flops Pink 8 2W 21 WE018 La2y Flip-Flops Whit e 6 3W 22 WE019 La,y Flip-Flops White 7 lW 7 .5 23 WE020 La2y Flip-Flops White 8 2W 24 WE021 La2y Flip-Flops Brown 8 2M 25 WE022 La2y Flip-Flops Brown 9 4M 26 WE023 Lazy Flip-Flops Brow n 10 2M 7 .5 27 WE024 La2y Flip-Flops Brown 11 2M 28 WE025 Seriously Tall Boots Black 6 ow 29 WE026 Seriously Tall Boots Black ow 30 WE027 Seriously Tall Boots Black 7 1W .. KjwE028 Seriously Tall Boots Black 1 3j w 1- 91 Panes frozen at cell A4 USING MICROSOFT Excel 2016 Guided Project 1-3 Guided Project 1 -3 In this Project , you edit a worksheet for Wear-Ever Shoes that tracks their product inventory.

2 You change the zoom size, reset the panes, and format data. You copy an existing formula to calculate the value of the current stock and insert a column for new data. After adjusting print options, you set a print area for a specific product line. Skills Covered in This Project Open and save a workbook. Change zoom size and remove a split. Freeze panes. Format labels and values. Use the Fill Handle to copy a formula and fill a series. Check spelling. Apply a workbook theme, cell styles, and font attributes. Merge and center labels. Insert a column and adjust column widths and row heights. Adjust alignment, indents, and borders. Rename the sheet tab. Use Page Layout view to insert a footer. Step 1: Download start file 1. Open the start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it.

3 The file will be renamed automatically to include your name. Change the Project file name if directed to do so by your instructor, and save it. 2. Remove a split and freeze panes. a. Click the Split button [View tab, Window group]. The split is removed. b. Click cell A4 to freeze the column titles at this position. c. Click the Freeze Panes button [View tab, Window group]. d. Choose Freeze Panes. There is a solid border between rows 3 and 4 to mark the pane. e. Scroll to row 31 and click cell E31. The column titles are frozen. f. Type 3 and press Enter (Figure 1-91). g. Press Ctrl+Home. The insertion point returns to cell A4. 3. Change zoom size and unfreeze the pane. a. Click the 100% button [View tab, Zoom group]. b. Click the Freeze Panes button [View tab, Window group]. c. Choose Unfreeze Panes. d. Click cell A1. Excel 2016 Chapter 1 Creating and Editing Workbooks Last Updated: 11/27/17 Page 1 l!

4 El v -,pt! ,g l~dUI~ P1iiJcirin1,1 84 A 1 Weer-Evers 2 Cur rent Stock 3 Product ID ,! WE001 5 WE002 0 WE003 7 Wi:004 8 WEOOS g WE006 10 WEOO? 11 Wi:008 JI) Lookup , ii;;i~ a, ~',,id< P es Cornmc::nl C D ramml"'f'lt ~ Shaw, A I ,ts a, N""l Shc,w Ink Dom ~nb E G Spelling: En~lish (Unit~(! Slates) =-------,Co ~lot 12/dlonar:r. Product Hi kl n Boots.._ _____ 11Bn 1-!ikln Rugged Hl kl n Boots llrc Rugged H1k1 n Boots Ill, Rugged Hiki n Soots Rup,p,ed Hi kl n Boots. Rugged Hlkl n Boots Rugged H1k1 n Soots Prolul Prol~ct Sh,,t W i<Mok [Q Ej: Sh~,e '""-Vo, kbook ,;,, T H Cl~, u, 7 X ! gn rir-Qn rt Md t~ Dldlo;;;;;i I ~ange ] Lc11~e_J W{009 13 WEDlO ._ _____ "_, Auto(OD<!d l 14 WEOll omfy Sh oes Di,tionorr I guage: English n~ d St<rtosl El 15 ComfyWiilkingS oes Hi WE013 Comfy W~lkin 0 5 17 WEIJl14 comfy Walkln;p; sh oes 18 WE015 Laz fl1 - Fl o s H4 ~.)}

5 A B C 1 Wear- Ever Shoes 2 Current Stock 3 Product ID Product Color 4 WE001 Ru88ed Hikins Boots Brown 5 WE002 Rugged Hiking Boots Brown 5 WE003 Ru88ed Hikins Boots Black 7 WE004 Ru ed Hikin Boots Black (Formula) D E F G r ace, 7 65 14 y N Size Quantity Men' s o r \ Cost Value Retail Reorder 8 5M 46. 51 ! 90 N 10 3M 90 y 5M 46,5 90 N 4M 90 N 1-93 Formula to be copied USING MICROSOFT Excel 2016 Guided Project 1-3 4. Check spelling in the worksheet. a. Select B4:B11. Click the Spelling button [Review tab, Proofing group]. The first occurrence of Hikin is found as misspelled. b. Select Hiking in the Suggestions list. c. Click Change All to correct all occurrences of the misspelled word (Figure 1-92). d. Click OK. 1-92 Change all occurences of the misspelled word 5. Use the Fill Handle to copy a formula. a. Click H4. The formula is visible in the Formula bar.

6 The formula multiples the quantity by the cost to calculate the value of the current stock (Figure 1-93). b. Point to the Fill Handle for cell H4. c. Double-click the Fill pointer. The formula is copied down the entire column. (For Excel 2013, drag the Fill pointer down to cell H39 to copy the formula.) d. Press Ctrl+Home. 6. Click the Themes button [Page Layout tab, Themes group] and choose Ion from the gallery. 7. Merge and center the titles. a. Select cells A1:J1 and click the Merge & Center button [Home tab, Alignment group]. b. Merge and center cells A2:J2. Excel 2016 Chapter 1 Creating and Editing Workbooks Last Updated: 11/27/17 Page 2 ,. Product ID A B C D E F G H K 1 Wear-Ever Shoes 2 Current Stock Size JO 3M 46.

7 5 90 y 6 WE003 6 M 279 90 N 1-94 Adjust row height from the row heading USING MICROSOFT Excel 2016 Guided Project 1-3 8. Apply cell styles and adjust row height. a. Select cell A1, click the Cell Styles button or the More button [Home tab, Styles group], and select Title in the Titles and Headings category. b. Select cell A2 and apply the Heading 4 style. c. Click the Font Size arrow [Home tab, Font group] and select 14 pt. Your font size change overwrites the font size of the cell style. d. Select row headings 1:2. e. Click the Format button [Home tab, Cells group] and choose Row Height. f. Type 22 as the new row height and press Enter. g. Select cells A3:J3. h. From the Cell Styles gallery [Home tab], select 40% - Accent 3. i. Apply bold to the selected cells. j. Click the row 3 heading. k. Point to the bottom border of the row heading to display the resize arrow.

8 L. Drag the resize arrow to (28 pixels) as the new row height and release the pointer button (Figure 1-94). m. Click the Zoom Out button in the Status bar if necessary so that you can see all rows (1:39). 9. Change alignment, format values, apply borders, and increase the indent. a. Select cells D4:F39. b. Click the Center button [Home tab, Alignment group]. c. Select cells G4:H39. d. Click the Accounting Number Format button [Home tab, Number group]. e. Click cells A3:J39. f. Click the Borders drop-down arrow [Home tab, Font group] and select All Borders. g. While the cells are selected, click the Font size drop-down arrow [Home tab, Font group] and choose 12. h. Select cells B4:C39 and click the Increase Indent button [Home tab, Alignment group] one time. This moves the label away from the border for easier reading. i. Click and drag to select columns A:J.

9 J. Double-click the border between columns J and K to AutoFit the selected columns (Figure 1-95). k. Select and center align the labels in row 3. Excel 2016 Chapter 1 Creating and Editing Workbooks Last Updated: 11/27/17 Page 3 A3 . /r Wear-Ever Shoes I'. B C D E F -G H I J ~ l 1 Wear-Ever Shoes 2 Current Stock 3 Product I D Product Color Size Quantify Men's or Cost Value Retail Reorder 4 WEOOl Rugged Hiking Bo, Brown 8 5 M $ $ 90 N 5 WE002 Rugged Hiking Bo, Brown 10 3 M $ $ 90 y 6 WE003 Rugged Hiking Bo, Black 6 M $ $ 90 N 7 WE004 Rugged Hiking Bo, Black 10 .5 4 M $ $ 90 N 8 WE005 Rugged Hiking Bo, Black 4 w $ $ 98 N 9 WE006 Rugged Hiking Bo, Black 8 2 w $ $ 98 y 10 WE007 Rugge d Hiking Bo, Brown 9 3 w $ $ 98 N 11 WE008 Rugged Hiking B0< Brown 2 w $ $ 98 y 1 2 WE009 Comfy Walking Sh Brown 4 M $ $ 65 N 1 3 WEOl O Comfy Walking Sh Black 9 1 M $ $ 65 y 1 4 WEOl l Comfy Walkinq Sh Navy 4 w $ $ 65 N 1-95 Select column headings to AutoF/t B4 _:J X ~ Ix No -A B C 1 2 3 Product ID Disc?

10 Product I : WEOOl No Rugged Hiking Boots WE002 No I Rugged Hiking Boots 6 WE003 Ye s +.: Rugged Hiking Boo ts 7 WE004 ~ lgged Hiking Boots 8 WE005 ~gged Hiking Boots 9 WE006 Rugged Hiking Boots 10 WE007 Rugged Hiking Boots 11 WE008 Ru g ged Hiking Boots 12 WE009 Comfy Walking Shoes 1 3 WE010 Comfy W a lking Shoes 1-96 Double-click the FIii pointer to complete a series USING MICROSOFT Excel 2016 Guided Project 1-3 10. Insert a column and fill data. a. Right-click column heading B. b. Choose Insert from the context menu. c. In cell B3, type Disc? and press Enter. d. In cell B4, type No and press Enter. e. In cell B5, type No and press Enter. f. In cell B6, type Yes and press Enter. g. Select cells B4:B6 and increase the indent twice. h. Select cells B4:B6 and double-click the Fill pointer (Figure 1-96). (For Excel 2013, drag the Fill pointer down to cell B39.)