Activity Name / Demonstration Notes
10.2 Navigating Among Worksheets, Renaming Worksheets, and Changing the Tab Color of a Worksheet / ·  Right-click the Sheet3 tab and rename it Township Office
·  Double-click the Sheet1 tab and enter Youth Center
·  Double-click the Sheet2 tab and enter Senior Center
·  Right-click the Youth Center sheet tab, and select Tab Color Purple, Accent 4, Lighter 40%.
·  Click the Senior Center sheet tab. On the Home tab, in the Cells group, click Format and format the tab Aqua, Accent 5, Lighter 40%.
·  Right-click the Township Office sheet tab, and select Tab Color Orange, Accent 6, Lighter 40%.
10.4 Clearing Cell Contents and Formats / ·  On the Youth Center worksheet, select cells A1:A2 and press the Delete key.
·  In cell A1, enter Area #1
·  Select cells A1:A2. On the Home tab, in the Editing group, click the Clear arrow, and then click Clear Formats.
·  Repeat this for cell A5. In the Number group, notice that General displays as the number format of the cell. Discuss the way Excel stores dates. Click Undo, and save the workbook.
10.6 Wrapping Text in a Cell in Several Worksheets at the Same Time / ·  With the Youth Center sheet selected, press Ctrl + Home to go to cell A1. Right-click the sheet tab and click Select All Sheets. Mention that [Group] displays at the top of the screen.
·  Select columns A:F and set the width to 83 pixels.
·  Open the data file e10A_EDD_Data2, and from the Youth Center sheet, copy the range D4:F4 to cells D4:F4 in this workbook.
·  Select the range A4:F4, right-click, open the Format Cells dialog box, and format as follows:
o  Font: Bold, Italic, Orange, Accent 6, Darker 25%.
o  Horizontal Center, Vertical Center, and Wrap text.
·  Mention Alt + Enter as a way to manually start a new line in a cell.
·  Select the Senior Center worksheet. Mention that the sheets are now ungrouped. Select each sheet to ensure the changes just made affected all sheets. Save the workbook.
10.8 Constructing Formulas that Refer to Cells in Another Worksheet / ·  In cell B4, type = and on the Youth Center worksheet, click cell D12 and press Enter. Discuss that the formulas on this sheet are based on cells in another sheet.
·  In cell C4 type = and on the Youth Center worksheet, click cell E12 and press Enter.
·  Repeat these two formulas for the next two rows, using the appropriate sheet.
·  From e10A_EDD_Data2, Summary sheet, copy cells A1:A2 and paste to cells A1:A2 in this workbook.
·  Select cells A1:C1 and Merge and Center. Select cells A2:C2 and Merge and Center.
·  Select the two worksheet titles and format the font as Cambria 14 pt. Bold, Orange, Accent 6, and Darker 25%.
·  Select rows 1:2, increase the row height to 35 pixels, and Middle Align the text. Select columns B:C and set the column width to 80 pixels.
·  Enter Total Sales in cell A7 and mention that the formatting from the cell above is carried down to the new cell.
·  Select the range B4:C7, and use Sum to total the two columns.
·  Demonstrate how the totals on this sheet instantly change by changing a number in cell B11 or C11 on one of the previous sheets. Undo any changes.
·  Select the range B7:C7 and apply a Top and Double Bottom border. Save the workbook.
10.12 Formatting as You Type / ·  From e10B_EDD_Data, copy the range A6:A11 and paste to cells A6:A11 in this workbook.
·  Use the Format Painter to copy the format from cell A1 and paste to cell A6.
·  AutoFit column A.
·  Enter 2008 in cell B10 and in cell C10, enter 2018
·  Select the range B10:C10 and drag the fill handle to cell F10.
·  Click cell B11 and mention that the format is General. Enter 179,746 (include the comma), click Enter on the Formula Bar, and mention that the format changed to Number. Press the Delete key; mention that the cell is still formatted as Number.
·  In cell B11, enter 179746 (no comma).
·  Discuss the difference between the Comma format in cell B3 and the Number format in cell B11.
·  In cell B8, enter 26% (include the % sign) and click Enter on the Formula Bar. Press the Delete key and mention that the percentage format for cell B8 remains. Enter 26 (without the % sign).
11.1 Using the SUM, AVERAGE, and MEDIAN Functions / ·  Open e11A_EDD_Data and save as 11A_Omaha_Inventory_Firstname_Lastname in a folder named Excel Chapter 11.
·  Merge and Center cell A1 across columns A:J. Repeat this for cell A2.
·  Format both titles as Bold Cambria 16 pt. with Fill Color of Blue, Accent 1, Lighter 60%.
·  Click cell B4. On the Formulas tab, in the Function Library group, click AutoSum. Select the range A17:A46 and press Enter. Widen the column if necessary to display the cell contents properly. Scroll up to view the top of the worksheet, mention that the formula’s results are now in cell B4, and discuss the formula and its arguments.
·  Click cell B5. In the Function Library group, click More Functions, click Statistical, and click AVERAGE. Drag the dialog box out of the way as necessary.
·  In the Number1 box type B17:B46 and click OK.
·  Click cell B6. Click More Functions, click Statistical, and click MEDIAN. Discuss how MEDIAN differs from AVERAGE.
·  To the right of the Number1 box, click Collapse Dialog. Select the range B17:B46 and press Enter.
·  Click OK and save the workbook.
·  Copy the range A4:A8 to F4. Press Esc.
·  In cell G4, enter the SUM function using the range F17:F39.
·  In cells G5 and G6, enter the functions AVERAGE and MEDIAN using the range G17:G39.
·  Format cells B4 and G4 as Comma Style, zero decimal places, and save the file.
11.2 Using the MIN and MAX Functions / ·  Click cell B7. On the Formulas tab, click More Functions, click Statistical, and click MIN. Select the range B17:B46 and click OK.
·  Click cell B8. Use the same guidelines and range to insert the function MAX.
·  In cells G7 and G8, enter the MIN and MAX functions using the range G17:G39, and save the file.
11.3 Moving Data, Adding Borders, and Rotating Text / ·  Select cells A4:B8, drag to cell B4, and AutoFit columns B:C.
·  Select cells F4:G8, drag to cell G4, and AutoFit columns G:H.
·  Select the nonadjacent ranges B4:C8 and G4:H8, and format as Bold with a Thick Box Border.
·  In cell A6 type Women
·  Select cells A5:A7, right-click, and click Format Cells. Click the Alignment tab and select Merge cells. Under Orientation, drag the diamond upward to 30 Degrees and click OK.
·  Format as Bold 12 pt. Italic and Center and Middle Align.
·  In cell F5 type Men
·  Copy the format from cells A5:A7 to cell F5 using the Format Painter. Save the workbook.
11.4 Using the COUNTIF Function / ·  Right-click row 10 and click Delete. Press F4. Click cell C4 and mention that the formula was adjusted as a result of deleting the two rows.
·  Click cell A10 and type Dance Styles Available
·  Copy cell A10, paste it to cell F10, and press Esc.
·  Click cell A11. On the Formulas tab, in the Function Library group, click More Functions, click Statistical, and click COUNTIF. Select the range C15:C44. In the Criteria box, type Dance and click OK. Discuss the COUNTIF function.
·  Click cell F11 and enter the COUNTIF function using the range H15:H37.
·  Select cells A11 and F11, format as Align Text Left, and save the file.
11.5 Using the IF Function and Applying Conditional Formatting / ·  Click cell E15. On the Formulas tab, in the Function Library group, click Logical and click IF. Place the insertion point in the Logical_test box, click cell A15, and type <30
·  Press Tab, and in the Value_if_true box, type Order
·  Press Tab, and in the Value_if_false box, type OK and click OK. Discuss the formula and comparison operators.
·  Drag the fill handle of cell E15 down to cell E44.
·  Copy cell E15 and paste to cell J15, and mention that the cell references adjust accordingly. Drag the fill handle of cell J15 down to cell J37 and save the workbook.
11.6 Applying Conditional Formatting Using Custom Formats and Data Bars / ·  Select the range E15:E44. Click Conditional Formatting. Select Highlight Cells Rules, and click Text That Contains. In the box on the left, type Order
·  In the box on the right, click the arrow and click Custom Format. Select the format Bold Italic with Theme Colors of Blue, Accent 1, Darker 25%. Click OK twice.
·  Use the Format Painter to copy this conditional format from cell E15 to cells J15:J37.
·  Select the range A15:A44. Click Conditional Formatting. Select Data Bars and click Orange Data Bar.
·  Use the Format Painter to copy this conditional format from cell A15 to cells F15:F37.
·  Press Ctrl + Home and save the file.
11.9 Freezing and Unfreezing Panes / ·  Press Ctrl + Home. Scroll down to show that the column titles disappear.
·  Press Ctrl + Home again, and select row 15. On the View tab, in the Window group, click Freeze Panes and click Freeze Panes. Scroll down to show how the column titles stay in view. Mention also that columns and rows can be frozen simultaneously using this command.
·  Unfreeze the panes and save the file.