Excel document edit

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.

 Open the WearEverShoes-01.xlsx start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. 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.

Remove a split and freeze panes.

 Click the Split button [View tab, Window group]. The split is removed.

 Click cell A4 to freeze the column titles at this position.

 Click the Freeze Panes button [View tab, Window group].

 Choose Freeze Panes. A solid border appears between rows 3 and 4 to mark the pane.

 Scroll to row 31 and click cell E31. The column titles are frozen.

 Type 3 and press Enter (Figure 1-91).There is a thin border below the cells in row 3

Figure 1-91 Panes frozen at cell A4

 Press Ctrl+Home. The insertion point returns to cell A4.

Change zoom size and unfreeze the pane.

 Click the 100% button [View tab, Zoom group].

 Click the Freeze Panes button [View tab, Window group].

 Choose Unfreeze Panes.

 Click cell A1.

Check spelling in the worksheet.

 Click the Spelling button [Review tab, Proofing group]. The first occurrence of “Hikin” is found as misspelled.

 Select Hiking in the Suggestions list.

 Click Change All to correct all occurrences of the misspelled word (Figure 1-92).

The correct spelling is H I K I N G in Suggestions list

Figure 1-92 Change all occurrences of a misspelled word

 Click OK.

Use the Fill Handle to copy a formula.

 Click H4. The formula displays in the Formula bar. The formula multiples the quantity by the cost to calculate the value of the current stock (Figure 1-93).The formula is E4 times G4

Figure 1-93 Formula to be copied

 Point to the Fill Handle for cell H4.

 Double-click the Fill pointer. The formula is copied down the entire column.

 Press Ctrl+Home.

 Click the Themes button [Page Layout tab, Themes group] and choose Ion from the gallery.

Merge and center the titles.

 Select cells A1:J1 and click the Merge & Center button [Home tab, Alignment group].

 Merge and center cells A2:J2.

Apply cell styles and adjust row height.

 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.

 Select cell A2 and apply the Heading 4 style.

 Click the Font Size arrow [Home tab, Font group] and select 14 pt. The font size change overwrites the font size of the cell style.

 Point to the row 1 heading and drag to select row headings 1:2.

 Click the Format button [Home tab, Cells group] and choose Row Height.

 Type 22 as the new row height and press Enter.

 Select cells A3:J3.

 From the Cell Styles gallery [Home tab], select Light Yellow, 40% - Accent3.

 Apply bold to the selected cells.

 Click the row 3 heading.

 Point to the bottom border of the row heading to display the resize arrow.

 Drag the resize arrow to 21.00 (28 pixels) as the new row height and release the pointer (Figure 1-94).

The resize pointer is between the row headings for rows 3 and 4

Figure 1-94 Adjust row height from the row heading

 Click the Zoom Out button in the Status bar if necessary so that you can see all rows (1:39).

Change alignment, format values, apply borders, and increase the indent.

 Select cells D4:F39.

 Click the Center button [Home tab, Alignment group].

 Select cells G4:H39.

 Click the Accounting Number Format button [Home tab, Number group].

 Select cells A3:J39.

 Click the Borders drop-down arrow [Home tab, Font group] and select All Borders.

 Click the Font size drop-down arrow [Home tab, Font group] and choose 12 while the cells are selected.

 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.

 Click and drag to select columns A:J.

 Double-click the border between columns J and K to AutoFit the selected columns (Figure 1-95).

Columns A:J are selected and shaded gray

Figure 1-95 Select column headings to AutoFit

 Select and center align the labels in row 3.

Insert a column and create a pattern to fill data.

 Right-click column heading B.

 Choose Insert from the context menu.

 In cell B3, type Disc? and press Enter.

 In cell B4, type No and press Enter.

 In cell B5, type No and press Enter.

 In cell B6, type Yes and press Enter. The pattern is two occurrences of No and one occurrence of Yes.

 Select cells B4:B6 and increase the indent twice.

 Select cells B4:B6 and double-click the Fill pointer (Figure 1-96).The Fill pointer displays at cell B6

Figure 1-96 Double-click the Fill pointer to complete a series

 Select cells A4:A39 and increase the indent one time.

 Press Ctrl+Home.

Rename the sheet tab.

 Double-click the Sheet1 tab.

 Type Inventory and press Enter.

Use Page Layout view to insert a footer.

 Click the Page Layout button in the Status bar.

 Click the center header section. The Header & Footer Tools tab is active.

 If necessary, click the Header & Footer Tools Design tab to display it.

 Click the Go to Footer button [Header & Footer Tools Design tab, Navigation group].

 Click the middle section if necessary.

 Click the File Name button in the Header & Footer Elements group. The code is &[File].

 Click a worksheet cell to see the file name.

 Switch to Normal view and press Ctrl+Home.

Change page setup options.

 Click the Page Layout tab and click the Page Setup launcher.

 Display the Page tab and select the Landscape radio button under Orientation.

 Select the Fit to radio button and enter 1 page wide by 1 tall.

 Click OK.

 Switch to Normal view and press Ctrl+Home.

Set a print area.

 Select cells A1:K11.

 Click the Page Layout tab.

 Click the Print Area button [Page Setup group] and select Set Print Area.

 Press Ctrl+Home.

 Click the File tab and select Print to preview the worksheet print area.

 Return to the worksheet.

 Click the Save button in the Quick Access toolbar to save the workbook changes. The print area is saved.

 Close the workbook (Figure 1-97).