INTERMEDIATE

MICROSOFT EXCEL2013

INTERMEDIATEMICROSOFT EXCEL2013

  1. Formatting Cells and Ranges
  2. Inserting and Deleting Cells
  3. Manually Formatting Cell Contents
  4. Copying Cell Formatting with the Format Painter
  5. Applying Conditional Formatting to Cells and Clearing Formatting
  1. Formatting Worksheets
  2. Working with Rows and Columns
  3. Insert Headers and Footers
  1. Managing Worksheets
  2. Organizing Worksheets
  1. Working with Data
  2. Ensuring Data’s Integrity
  3. Sorting Data
  4. Filtering Data
  5. Setting Up Data in a Table Format
  1. Securing and Sharing Workbooks
  2. Secure your Work Before Sharing it to Others
  3. Tracking Changes to a Workbook

SECTION I: Formatting Cells and Ranges

LESSON ITEM #1:Inserting and Deleting Cells

Insert cells into a Worksheet

Action:

  1. Open Formatting Cells practice.
  2. Click in cell G5 to make it the active cell.
  3. On the Home tab, in the Cells group, click the top part of the Insert button.
  4. All cells in column G beginning with G5 shift down one cell.
  5. Type 50 and press Enter.
  6. Select cells G4:G9.
  7. On the Home tab, in the Cells group, click the Insert drop-down arrow and then select Insert Cells.
  8. In the Insert dialog box, ensure the Shift cells right option is selected. Click OK.
  9. Enter the following numbers in cells G4 through G9:
  10. 10
  11. 15
  12. 20
  13. 10
  14. 30
  15. 30

Delete Cells from a Worksheet

Action:

  1. Click cell J4 to make it the active cell.
  2. On the Home tab, in the Cells group, click the Delete drop-down arrow, and then select Delete Cells.
  3. In the Delete dialog box, select the Shift cells up option and click OK.

LESSON ITEM #2:Manually Formatting Cell Contents

Choose Fonts and Font Sizes

Action:

  1. Click A1.
  2. On the Home tab, in the Font group, open the Font menu and select Berlin Sans FB.
  3. Notice only the text in cell A1 changes to the new font.
  4. With cell A1 still selected, open the Font Size menu. Select 18.
  5. Select A2:I2 and from the Font list, select Arial.
  6. With A2:I2 still selected, changed the Font Size to 14.

Wrap Text in a Cell

Action:

  1. Click A2, and then hold down the Ctrl key and click I2.
  2. On the Home tab, in the Alignment group, click the Wrap Text button.
  3. Notice the text in the cells wrap to a second line without affecting the column width.

Merge Cells

Action:

  1. Select cells A1:I1.
  2. On the Home tab, in the Alignment group, click the main part of the Merge & Center button.

Fill Cells with Color

Action:

  1. Select A1.
  2. In the Font group, click the Fill Color button arrow.
  3. Select a color.
  4. Notice the color background is applied to the title row.

Place Borders around Cells

Action:

  1. Select cells A2:I2.
  2. On the Home tab, in the Font group, click the Borders button arrow to open the borders menu.
  3. Select Top and Bottom Border.
  4. With A2:I2 still selected, open the Borders menu and select More Borders.
  5. In the Format Cells Dialog box, click the Borders tab.
  6. Choose a thicker weight and choose a color.
  7. Click OK.

LESSON ITEM #3:Copying Cell Formatting with the Format Painter

Use the Format Painter to Copy Formatting

Action:

  1. Click in cell A3.
  2. On the Home tab, in the Clipboard group, click the Format Painter button.
  3. Notice the mouse pointer changes to a plus sign with a paint brush symbol.
  4. Drag over B3:I10.
  5. The formatting from A3 is applied to the data selected.
  6. If the Format Painter is still active, click the Format Painter button again or press the Esc on the keyboard to turn off the Format Painter.

LESSON ITEM #4:Applying Conditional Formatting to Cells and Clearing Formatting

Apply a Specific Conditional Format

Action:

  1. Open Conditional Formatting Practice.
  2. Select B3:L23.
  3. On the Home tab, in the Styles group, click Conditional Formatting, and then select Highlight Cells Rules and then Greater Than option.
  4. The Greater Than dialog box appears.
  5. In the Format cells that are Greater Than box, type 7500.
  6. Fill with Green Fill with Dark Green Text.
  7. Click OK.

Apply Multiple Conditional Formatting Rules

Action:

  1. Select B3:L23.
  2. Choose Conditional Formatting menu and select: Highlight Cells Rules and then Less than option.
  3. In the Format cells that are Less Than box, type 2000.
  4. In the drop-down menu, click the Yellow Fill with Dark Yellow Text option. Click OK.
  • SECTION I: COMPLETE!

SECTION II: Formatting Worksheets

LESSON ITEM #5: Working with Rows and Columns

Insert and Delete Rows and Columns

Action:

  1. Click the row 14 to select the entire row.
  2. On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Rows.
  3. To insert several rows at once, click the row 10 heading, hold down the Ctrl key, and then click row headings 17 and 23.
  4. Right-click any of the selected rows and select insert from the shortcut menu.
  5. Click the Column D heading to select the entire column.
  6. On the Home tab, in the Cells group, click the Delete button arrow and select Delete Sheet Columns.

Hide or Unhide a Row or Column

Action:

  1. Select columns D and E.
  2. Right-click the column D or E heading and select Hide.
  3. Select rows C and F. On the Home tab, in the Cells section, click the Format drop-down.
  4. Hover the mouse over Hide & Unhide and choose the Unhide Columns option.

LESSON ITEM #6: Inserting Headers and Footers

Add Page Numbers to a Worksheet

Action:

  1. Open Header Footer Practice.
  2. On the Insert tab, in the Text group, click the Header & Footer button.
  3. Click the Go to Footer button in the Navigation group on the Design ribbon.
  4. In the Header & Footer Elements group, click Page Number.
  5. The code &[Page] appears in the text box.
  6. Click in a worksheet cell that’s not part of the footer, and then click the Normal view icon on the right side of the status bar.

Insert a Predefined Header or Footer

Action:

  1. Click cell A1.
  2. On the View tab, in the Workbook Views group, click the Page Layout view button to view headers and footers.
  3. Click the center header text box.
  4. Click the Header & Footer Tools DESIGN tab now that it has become active.
  5. In the Header & Footer Elements group, click Sheet Name.
  6. &[Tab] appears in the text box.
  7. In the Navigation group, click Go to Footer. Click the right footer text box.
  8. In the Header & Footer group, click the Footer button arrow, and click the last option in the list, which combines Prepared by username, Current Date, and Page Number.
  9. It takes a moment to load.

Choose a Theme for a Workbook

Action:

  1. Open Themes Practice.
  2. On the Page Layout tab, in the Themes group, click the Themes button arrow to open the Themes Gallery.
  3. Move your mouse pointer over each theme to see its effect on the underlying worksheet, this is referred as a live preview.
  4. Find and select a theme of your choice. Notice the changes have been made in the worksheet.
  5. Return to the Home tab, and click the Cell Styles button arrow in the Styles group. Notice the color schemes for the various groups have changed.

LESSON ITEM #5: Preparing a Document for Printing

Set Margins

Action:

  1. On the Page Layout tab, in the Page Setup group, click the Margins button arrow to open the Margins menu.
  2. At the bottom of the menu, click Custom Margins.
  3. In the Page Setup dialog box, change the left and right margins to 1.5.
  4. Check the Center on page horizontally check box.
  5. Click Print Preview.
  6. Click the Back button and return to the Home tab.

Set a Worksheet’s Orientation

Action:

  1. On the Page Layout tab, in the Page Setup group, click the Orientation and then click Landscape.
  2. Click the File tab, and then click Print command.
  3. Notice the difference between the Landscape Orientation.
  4. Click the Back button and return to the Home tab.

Scale a Worksheet to Fit on a Printed Page

Action:

  1. On the Page Layout tab, in the Scale to Fit group, click the Height and Width arrows and select 1page.
  2. Click the File tab, and then click Print.
  3. Notice all of these actions can be completed on the Print Preview page.
  • SECTION II: COMPLETE!

SECTION III: Managing Worksheets

LESSON ITEM #7: Organizing Worksheets

Copy a Worksheet

Action:

  1. Open Managing Worksheets Practice.
  2. With the Jan – Week One worksheet active, click the Home tab, in the Cells group, click the Format drop-down arrow.
  3. Click the Move or Copy Sheet.
  4. In the Before sheet list, select (move to end).
  5. Select the Create a Copy box and click OK.
  6. Click the Jan – Week One worksheet tab.
  7. Click and hold the Jan – Week One tab, and then press and hold Ctrl.
  8. Notice the pointer changes from an arrow to a paper with a plus sign in it.
  9. Drag the Pointer to the right until the down-arrow just above the tabs bar points to the divider to the right of Jan – Week One (2).
  10. Release the mouse button and Ctrl key.
  11. A new worksheet is created, with its tab located just to the right of where the down-arrow was pointing. It’s name is Jan – Week One (3).
  12. With Jan – Week Three (3) active, click cell C2 and type the current date.
  13. Beginning in cell A4, type the following data, skipping over the cells without an x or a number.

Sarah / 351 / Wichita / X / X / X
Elena / 352 / Wichita / X / X / X / X / X
Clarissa / 353 / Wichita / X / X / X
Gene / 354 / Butler / X / X / X / X
Abby / 201 / Butler / X / X / X / X / X
Regina / 100 / Cowley / X / X / X

Rename a Worksheet

Action:

  1. Double-click Jan – Week One (3) worksheet tab to select its name.
  2. Type Jan – Week Three and press Enter.
  3. Repeat this process for the Jan – Week One (2) worksheet tab, and rename it Jan – Week Two.
  4. Beginning in cell A4 on Jan – Week Two worksheet, enter the following data, skipping over cells without an x or a number:

Regina / 200 / Wichita / X / X / X
Andrea / 202 / Wichita / X / X / X / X
Allison / 110 / Butler / X / X / X / X
Maddy / 225 / Cowley / X / X / X / X
Julie / 119 / Wichita / X / X / X
Yancy / 203 / Wichita / X / X / X / X / X
Gwen / 305 / Wichita / X / X / X
Lizzy / 300 / Cowley / X / X / X / X / X

Reposition the Worksheets in a Workbook

Action:

  1. Click the Jan – Week One worksheet tab.
  2. Hold the Shift button and click Jan – Week Three worksheet tab.
  3. Release the Shift button after clicking the Jan – Week Three worksheet tab.
  4. Click and hold the select sheets and move them before the Summary Tab.
  5. Release the mouse button.
  6. The Summary worksheet is repositioned at the end of the sequence.
  7. Click the January Week - One worksheet tab.
  8. Beginning in cell B4, enter the following data, skipping over cells without an x or a number:

Regina / 200 / Wichita / X / X / X / X
Andrea / 202 / Wichita / X / X / X / X
Allison / 110 / Butler / X / X / X / X
Maddy / 225 / Cowley / X / X / X / X / X
Julie / 119 / Wichita / X / X / X / X
Yancy / 203 / Wichita / X / X / X / X / X

Change the Color of a Worksheet Tab

Action:

  1. Right-click the Jan – Week One worksheet tab.
  2. In the shortcut menu, click the Tab Color.
  3. Choose a color.
  4. Click the Jan – Week Two worksheet tab, repeat the color selection process for Jan – Week Three and Summary.
  5. Your tabs bar should now appear with colors.

Insert a New Worksheet into a Workbook

Action:

  1. Click on the Jan -Week Three tab.
  2. Click the + button to the right of the worksheet tabs.
  3. Rename this new worksheet Performance Reports.

LESSON ITEM #7: Using Zoom and Freeze to Change the Onscreen View

Use Zoom and Freeze to Change the Onscreen View

Action:

  1. Open Freeze Panes Practice.
  2. Select cell A2.
  3. On the View tab, in the Window group, click Freeze Panes, and then click Freeze Panes.
  4. Cells above and to the left of the selected cell (A2) are now frozen in place for scrolling.
  5. In the Windows group, click Freeze Panes, and then click Unfreeze Panes.
  • SECTION III: COMPLETE!

SECTION IV: Working with Data

LESSON ITEM #9:Importing Data

Open a Non-Native File Directly in Excel

Action:

  1. Open a Blank workbook.
  2. On the Data tab, in the Get external Data group, click From Text.
  3. Import the Text File dialog box, locate and click Data Export Practice. Click Import.
  4. In Step 1 of the Text Import Wizard, notice the preview at the bottom.
  5. This is Excel’s best guess, for the moment, on how the data should be organized.
  6. Under Choose the file type that best describes your data, choose Delimited.
  7. Notice the My Data has Headers check box. Check the box.
  8. The Preview shows text starting on row 1, for the Set import at row option, choose 1.
  9. Click Next.
  10. In Step 2, uncheck Tab and notice what your information looks like in the preview. Check the Tab again.
  11. Scroll down the Data preview pane, notice now that Excel has found the column separations.
  12. Click Next. Click Finish.
  13. In the Import Data dialog box, set to Existing Worksheet.
  14. Click Ok.
  15. Notice Excel could not make sense of the data in Columns C:F, so it left some data type set to General and some Currency.
  16. To correct it, begin by selecting cells C2:F278.
  17. On the Home tab in the Number group, choose Currency in the drop down box.

LESSON ITEM #8: Ensuring Your Data’s Integrity

Remove Duplicate Rows from a Worksheet

Action:

  1. Open the Data Security Workbook.
  2. Click the Source Data worksheet tab.
  3. Click cell A279 and type the following data in the appropriate columns:
  4. Teatime Chocolate BiscutsTOMSPN166.44
  5. Select the cell range A1:G279.
  6. On the Data tab, in the Data Tools group, click Remove Duplicates.
  7. In the Remove Duplicates dialog box, click Unselect All.
  8. Click on the Customer check box.
  9. Leave the My data has headers box checked.
  10. Click OK.
  11. Excel responds with a dialog box stating the duplicates found and removed.
  12. Click OK.

LESSON ITEM #9: Sorting Data

Sort Data

Action:

  1. In the Source Data 2 tab, click cell A1.
  2. Hold the Shift key down while clicking cell F278.
  3. This selects the entire range for sorting.
  4. On the Data tab, in the Sort & Filter group, Sort by Product and click the Sort A to Z.
  5. Click OK.
  6. Notice the list is now sorted in ascending numerical order.
  7. With the range still selected, click the sorting button again.
  8. Hit the Add Level button and choose Customer.
  9. Click OK.

LESSON ITEM #10: Filtering Data

Use AutoFilter

Action:

  1. Click the Source Data tab.
  2. Select A1:G79.
  3. Click the drop-down button next to New Customer.
  4. To sort the table from A to Z.

LESSON ITEM #11: Setting Up Data in a Table Format

Format a Table with a Quick Style

Action:

  1. Open the Table Formatting Practice.
  2. Select the data range A2:M23.
  3. On the Home tab, in the Styles group, click Format as Table.
  4. Click the sample in row 4, column 7 (Table Style Medium 7).
  5. The Format As Table dialog box appears.
  6. Click OK.
  7. The data is now a table. Click into the table to acess the Table Tools Toolbar.
  8. Review the options available to you in the Table Tools Toolbar.

Filter Records in a Table

Action:

  1. Click the Total Column drop-down arrow.
  2. In the menu, click Number Filters, and then click Top 10.
  3. In the dialog box, leave the choices set at Top 10 items, and then click OK. The table is filtered.
  • SECTION IV: COMPLETE!

SECTION V: Securing and Sharing Workbooks

LESSON ITEM #12: Securing Your Work before Sharing It with Others

Protect a Worksheet

Action:

  1. Open Securing Workbook Practice.
  2. Select cell B2-B46 and Delete the information.
  3. Select cell B2.
  4. Click the Functions icon just below your ribbon, search for and select RANDBETWEEN.
  5. This formula will create a random number for each plant that can be used for identification purposes.
  6. In the Function Arguments dialog box, in the Bottom box, type 10000 and in the top box, type 99999.
  7. Click OK.
  8. Double-click the fill handle in cell B2 to copy the range to B3:B46.
  9. Each plant is now assigned a random five-digit Inventory number.
  10. With the range B2:B46 selected, on the Home tab, click Format and then select Format Cells.
  11. Click the Protection tab and verify that Locked is checked.
  12. This prevents Inventory numbers from being changed when the worksheet has been protected.
  13. Click OK.
  14. Click on the Review tab, and in the changes group, click Protect Sheet.
  15. If your Protect Sheet is grayed out, this is because it is currently a Shared Workbook. To unshare, click Share Workbook, and uncheckmark “Allow changes by more than one user….”
  16. In the Password to unprotect sheet box, type Password1!.
  17. Click Ok.
  18. You are asked to confirm the password. Type Password1! Again and click OK.
  19. Try to make changes in the sheet.
  20. Click Unprotect Sheet on the Review tab.
  21. Type Password1! And click OK.

Protect a Workbook

Action:

  1. On the Review tab, in the Changes group, click Protect Workbook.
  2. Select Protect workbook for Structure.
  3. In the Password box, type Password2! And then click OK.
  4. Confirm password and click OK.
  5. To verify that you cannot change worksheet options, right-click the Sales by Category worksheet tab and notice the dimmed commands.
  6. Press the Esc key on the keyboard.
  7. In the Review tab, click Protect Workbook.
  8. Enter Password2! And click OK.

LESSON ITEM #13:Tracking Changes to a Workbook