Exploring Microsoft Excel 2021, Chapter 1

This set of exercises mirrors the hands-on exercises throughout this chapter, following the
same steps as the textbook exercises but based on a different scenario. It may be useful for in-
class demonstration.
Author Book Swag
You have been hired by Jose Guerrero, an independent author, to create an Excel spreadsheet to
track sales of book swag for his series, The Infinity Clause. He has contracted with Zebulon
Promotional Merchandise to create the book branded merchandise to be sold at a writer’s
convention. The author has decided on six products. The spreadsheet will determine the markup
amount, the retail price, the sale price, and the author’s profit margin.

Hands-On Exercise 1: Introduction to Spreadsheets

Before you work with the data, you will add product labels and check spelling. Using Auto Fill,
you will provide product codes for each item to ensure they are unique, and then add data, clear
unneeded contents, and find and replace the percentage off for the book swag.
Steps:
1. Enter Text and Check the Spelling in a Worksheet
a. Open the file named e01_script_data.xlsx and save it as
e01_script_solution1_LastFirst.xlsx
b. Click Find & Select in the Editing group and select Go To. Type A8 in the Reference box
and click OK.
c. Type Playing Cards in cell A8. Press Enter.
d. Click cell A9, type Magnet, and then press Enter.
e. Click cell A1. Click the Review tab and click Spelling in the Proofing group.
f. Click Change to change the misspelled word to Retail. Click OK.
g. Click Save on the left side of the title bar.
2. Use Auto Fill to Complete a Sequence
a. Click Cell B5, type 101, and then press Enter.
b. Position the pointer on the fill handle in the bottom-right corner of cell B5. Double-click
cell B5 fill handle.
c. Click Auto Fill Options and select Fill Series. Save the workbook.
3. Enter Values, Enter Dates, and Clear Cell Contents
a. Add the following values to the worksheet:
Type 0.20 into cell G6 and press Enter.
Type 0.15 into cell G7 and press Enter.
Type 0.08 into cell G8 and press Enter.
Type 0.25 into cell G9 and press Enter.

Copyright © 2022 Pearson

 b. Click cell A2 and type 9/3 to add the date.
c. Click cell A2, click the Home tab, click Clear in the Editing group, and then select Clear
Formats.
d. Type 9/3/2024 in cell A2 and press Enter. Save the workbook.
4. Find and Replace Data
a. Go to cell A1, click Find & Select on the Home tab, and then select Replace.
b. Type 0.15 in the Find what box.
c. Press Tab and type 0.20 in the Replace with box.
d. Click Replace All and click OK. Close the Find and Replace dialog box. (2 instances are
replaced)
e. Save the workbook.

Hands-On Exercise 2: Mathematical Operations and Formulas

In this exercise, you will insert formulas to calculate the missing values. You will use cell
references in the formulas, so when you change a referenced value, the formula results will
update automatically.
Steps:
1.Use Cell References in a Formula, Copy a Formula, and Use Semi-Selection to Create a Formula
a. Open the file named e01_script_solution1_LastFirst.xlsx, if you closed it at the end of
Hands-On Exercise 1. Save it as e01_script_solution2_LastFirst.xlsx.
b. Click cell E5 and type =C5*D5, but do not press Enter.
Point out the cell references in the formula match colored borders around the cells.
c. Click Enter.
d. Position the pointer on the cell E5 fill handle and double-click the fill handle.
e. Click cell F5. Type =, click cell C5, type +, click cell E5, and then click Enter.
f. Double-click the cell F5 fill handle and save the workbook.
2. Apply the Order of Operations
a. Click cell H5.
b. Type =F5*(1-G5) and press Enter.
c. Double click the cell H5 fill handle to copy the formula down column H.
d. Click cell H6 and point out how the formula changed on the Formula Bar.
e. Click cell I5.
f. Type =(H5-C5)/H5 and press Enter.
g. Double-click the cell I5 fill handle. Save the workbook.
3. Display Cell Formulas
a. Click the Formulas tab and click Show Formulas.

Copyright © 2022 Pearson

 b. Click cell C6, type 5.95, and then press Enter.
c. Click cell D8 and press F2.
d. Type 5 and press Enter.
e. Click cell G7, type 0.25, and then press Enter.
f. Save the workbook.

Hands-On Exercise 3: Worksheet Structure and Clipboard Tasks

The author has asked you to add a profit column to the spreadsheet before the profit margin
column and to add rows for product information and category names. The author also decided
not to sell one of the items in the Swag Shop, so you will delete the row for the item.
Steps:
1. Insert a Column
a. Open the file named e01_script_solution2_LastFirst.xlsx, if you closed it at the end of
Hands-On Exercise 2. Save it as e01_script_solution3_LastFirst.xlsx.
b. Click any cell in column I.
c. Click the Insert arrow in the Cells group on the Home tab and select Insert Sheet
Columns.
d. Click cell I4, type Profit Amount, and then press Enter.
e. Type =H5-C5 in cell I5 and press Enter. Double-click the cell I5 fill handle. Save the
workbook.
2. Insert and Delete Rows
a. Right-click the row 5 heading and select Insert from the shortcut menu.
b. Click cell A5. Type Apparel and press Ctrl+Enter. Click Bold in the Font group.
c. Right-click the row 8 heading and select Insert from the shortcut menu.
d. Click cell A8. Type Souvenirs and press Ctrl+Enter. Click Bold in the font group.
e. Click cell A11. Click the Delete arrow in the Cells group and select Delete Sheet Rows.
Save the workbook.
3. Adjust Column Width, Adjust Row Height, and Hide and Unhide a Column
a. Point to the right border of the column A header. Double-click the border.
b. Point to the right border of the column A header and drag the border to the right until
the ScreenTip displays Width: 23.00. Release the mouse button.
c. Click cell A1. Click Format in the Cells group and select Row Height.
d. Type 30 in the Row height box and click OK.
e. Click the column B heading. Press Ctrl and select column D heading.
f. Click Format in the Cells group, point to Hide and Unhide, and then select Hide
Columns.
g. Select the columns C and E headings. Click format in the Cells group and point to Hide &
Unhide and then select Unhide Columns. Save the workbook.

Copyright © 2022 Pearson

4. Select a Range, Move a Range, and Insert Cut Cells
a. Right-click the row 9 heading and select Insert from the menu to insert a blank line.
b. Select range A7:J7.
c. Click Cut in the clipboard group.
d. Click cell A9 and click Paste in the Clipboard group.
e. Click Undo twice.
f. Select range A7:J7 and click Cut in the clipboard group.
g. Right-click cell A9 and select Insert Cut Cells. Save the workbook.
5. Copy and Paste a Range
a. Select range A9:J9 and click Copy in the Clipboard group.
b. Click cell A11. Click Paste in the Clipboard group and press Esc.
c. Click cell A12, press F2, press Home, type Travel and press Enter.
d. Change the value in cell D11 to 0.5. Save the workbook.
6. Use Paste Options
a. Click cell A1 and click Bold. Click the Font Size arrow in the Font group and select 14.
Click the Font Color arrow, and then select Purple in the Standard Colors section.
b. Select range A1:J11 and click Copy in the Clipboard group.
c. Click Cell A14.
d. Click the Paste arrow in the Clipboard group and point to Formulas.
e. Click Values & Source Formatting and press Esc. Click cell H6 to show the formula. Click
cell H21 to show that the formula in cell H20 in the pasted version has converted into an
actual value.
f. Save the workbook.

Hands-On Exercise 4: Worksheet Formatting

Applying a cell style, merging and centering a title, aligning text, formatting values, and applying
other formatting enhance the readability of the worksheet.
Steps:
1. Apply a Cell Style and Merge Cells
a. Open e01_script_solution3_LastFirst.xlsx and save it as
e01_script_solution4_LastFirst.xlsx.
b. Select range A1:J1, click Cell Styles in the Styles group on the Home tab, and then click
Heading 1 from the gallery.
c. Click Merge & Center in the Alignment group.
d. Select range A2:J2. Click Merge & Center. Save the workbook.
2. Change Cell Alignment and Wrap Text

Copyright © 2022 Pearson

 a. Click cell A1 and click Middle Align in the Alignment group.
b. Select range A4:J4.
c. Click Center in the Alignment group and click Bold in the Font group.
d. Click Wrap Text in the Alignment group. Save the workbook.
3. Increase Indent
a. Select cell A6.
b. Click Increase Indent in the Alignment group twice.
c. Select range A8:11 and click Increase Indent twice. Save the workbook.
4. Apply a Border and Fill Color
a. Select range A4:J4 and click the Fill Color arrow in the Font group.
b. Click Blue, Accent 5, Lighter 80% (second row, column nine).
c. Select range G4:H11, click the Border arrow in the Font group, and then select Thick
Outside Borders. Click cell A4. Save the workbook.
5. Apply Number Formats and Increase and Decrease Decimal Places
a. Select ranges C6:C11, E6:F11, and H6:I11.
b. Click Accounting Number Format in the Number group.
c. Select ranges D6:D11 and J6:J11. Click Percent Style in the Number group. Click Increase
Decimal in the Number group.
d. Select range G6:G11, click Percent Style, and then click Center.
e. Select range J6:J11, click Align Right, and then click Increase Indent.
f. Save the workbook.

Hands-On Exercise 5: Worksheet Management, Page Setup, and Printing

To further enhance the readability of the worksheet, you will apply a cell style, merge and
center a title, align text, format values, and apply other formatting.
Steps:
1. Copy, Move, and Rename a Worksheet
a. Open e01_script_solution4_LastFirst.xlsx and save it as
e01_script_solution5_LastFirst.xlsx.
b. Right-click the Sheet1 tab. Select Move or Copy.
c. Click the Create a copy check box and click OK.
d. Drag the Sheet1 (2) worksheet tab to the right of the Sheet1 worksheet tab.
e. Double-click the Sheet1 sheet tab, type September, and then press Enter. Rename
Sheet1 (2) as Formulas.
f. Press Ctrl+` to display formulas in the Formula worksheet.
g. Change these column widths in the Formulas sheet:

Copyright © 2022 Pearson

  Column A: 12.00
 Columns C and D: 6.00
 Columns E, F, H, I, and G: 7.00
 Column G: 6.00
2. Specify Page Options and Set Margin Options
a. Click the September sheet tab, press and hold Ctrl, and then click the Formulas sheet
tab.
b. Click the Page Layout tab, click Orientation in the Page Setup group, and then select
Landscape.
c. Click Margins. Select Custom Margins.
d. Click the Top spin arrow to display 1.
e. Click the Horizontally check box and click OK.
f. Right-click the Formulas sheet tab and select Ungroup Sheets. With the Formulas sheet
active, click the Page Setup Dialog Box Launcher in the Scale to Fit group. Click Fit to
and click OK. Save the workbook.
3. Create a Header
a. Click the September sheet tab. Press and hold Ctrl and click the Formulas sheet tab.
b. Click the Insert tab, click Text, and then click Header & Footer in the Text group.
c. Click in the left section of the header and type Your Name.
d. Click in the center section of the header and click Sheet Name.
e. Click in the right section of the header and click File Name.
f. Click in any cell in the worksheet and click Normal in the status bar.
g. Click cell A1, click the Review tab, and then click Spelling. Correct all errors and click OK,
when the spelling check completes. Leave the worksheets grouped. Save the workbook.
4. View in Print Preview and Print
a. Click the File tab and click Print.
b. Verify that the Printer box displays the printer you want to use to print the workbook.
Verify that the first Setting option displays Print Entire Workbook. Verify that the last
Settings option displays Fit Sheet on One Page.
c. Click Next Page to see the second page.
d. Click the Back arrow and save the workbook.
e. Save and close the file. Exit Excel.

Copyright © 2022 Pearson