Microsoft Excel Core & Intermediate

Module 1: Creating a Microsoft Excel Workbook
This module explains how to create and start working with workbooks and worksheets.
Lessons

• • Starting Microsoft Excel
  • Creating a Workbook
  • Saving a Workbook
  • The Status Bar
  • Adding and Deleting Worksheets
  • Copying and Moving Worksheets
  • Changing the Order of Worksheets
  • Splitting the Worksheet Window
  • Closing a Workbook

Lab : Creating a Microsoft Excel Workbook
After completing this module, students will be able to:

• Start Microsoft Excel.
• Create a Microsoft Excel workbook.
• Save a Microsoft Excel workbook.
• Understand the Status Bar.
• Add and delete Microsoft Excel worksheets.
• Copy and move worksheets.
• Split the worksheet window.8. To close a Microsoft Excel workbook.

Module 2: The Ribbon
This module explains how to work with the Ribbon.
Lessons

• Tabs
• Groups
• Commands

Lab : The Ribbon Exercises

• Open a Workbook
• Select, Open, and Save a Template Agenda

After completing this module, students will be able to:

• Work with tabs.
• Work with groups.
• Work with commands.
• Know which groups and commands are found on which tabs.

Module 3: The Backstage View (The File Menu)
This module explains how to work with the Backstage View.
Lessons

• Introduction to the Backstage View
• Opening a Workbook
• Open a Workbook
• New Workbooks and Excel Templates
• Select, Open and Save a Template Agenda
• Printing Worksheets
• Print a Worksheet
• Adding Your Name to Microsoft Excel
• Adding a Theme to Microsoft Excel

Lab : Backstage View Exercises

• Print a Worksheet
• Open a Workbook

After completing this module, students will be able to:

• Work with the Backstage view.
• Open a Microsoft Excel workbook.
• Start a new Microsoft Excel workbook.
• Use Microsoft Excel templates.
• Print a Microsoft Excel workbook.
• Personalize your copy of Microsoft Excel

Module 4: The Quick Access Toolbar
This module explains how to work with the Quick Access Toolbar.
Lessons

• Adding Common Commands
• Adding Additional Commands with the Customize Dialog Box
• Adding Ribbon Commands or Groups
• Placement
• Customize the Quick Access Toolbar

Lab : Customize the Quick Access Toolbar
After completing this module, students will be able to:

• Add common commands to the Quick Access Toolbar.
• Add additional commands to the Quick Access Toolbar.
• Move the Quick Access Toolbar.

Module 5: Entering Data in Microsoft Excel Worksheets
This module explains how to enter data in Microsoft Excel worksheets.
Lessons

• Entering Text
• Adding and Deleting Cells
• Adding a Hyperlink
• Add WordArt to a Worksheet
• Using AutoComplete
• Entering Text and Using AutoComplete
• Entering Numbers and Dates
• Using the Fill Handle
• Entering Numbers and Dates

Lab : Entering Data Exercises

• Entering Text and Using AutoComplete
• Entering Numbers and Dates

After completing this module, students will be able to:

• Enter text in Microsoft Excel worksheets.
• Add or delete cells in worksheets.
• Add an outline for your data.
• Enter a hyperlink in a worksheet.
• Use AutoComplete.
• Enter numbers and dates in Microsoft Excel worksheets.
• Use the Fill Handle to add data to cells.

Module 6: Formatting Microsoft Excel Worksheets
This module explains how to format Microsoft Excel worksheets.
Lessons

• Selecting Ranges of Cells
• Hiding Worksheets
• Adding Color to Worksheet Tabs
• Adding Themes to Workbooks
• Customize a Workbook Using Tab Colors and Themes
• Adding a Watermark
• The Font Group
• Working with Font Group Commands
• The Alignment Group
• Working with Alignment Group Commands
• The Number Group
• Working with Number Group Commands

Lab : Formatting Worksheets Exercises

• Customize a Workbook Using Tab Colors and Themes
• Working with Font Group Commands
• Working with Alignment Group Commands
• Working with Nummber Group Commands

After completing this module, students will be able to:

• Understand various ways you can format Microsoft Excel worksheets.
• Select a range of cells within a worksheet.
• Hide worksheets.
• Add color to workbook tabs.
• Add themes to worksheets.
• Bold, italicize and underline text.
• Add borders to cells.
• Change text and cell colors.
• Change the font and font size.
• Align text.
• Wrap text.
• Indent text.
• Merge and center text.
• Change number formats.
• Change date formats.
• Show decimals.

Module 7: Using Formulas in Microsoft Excel
This module explains how to work with formulas in Microsoft Excel.
Lessons

• Math Operators and the Order of Operations
• Entering Formulas
• AutoSum (and Other Common Auto-Formulas)
• Copying Formulas and Functions
• Relative, Absolute, and Mixed Cell References
• Working with Formulas

Lab : Working with Formulas
After completing this module, students will be able to:

• Understand math operators and the order of operations in Excel.
• Enter basic formulas.
• Use AutoSum to sum data with one click.
• Understand absolute, relative, and mixed cell references.
• Copy formulas and functions.

Module 8: Working with Rows and Columns
This module explains how to work with rows and columns in Microsoft Excel.
Lessons

• Inserting Rows and Columns
• Deleting Rows and Columns
• Transposing Rows and Columns
• Setting Row Height and Column Width
• Hiding and Unhiding Rows and Columns
• Freezing Panes
• Working with Rows and Columns

Lab : Working with Rows and Columns
After completing this module, students will be able to:

• Insert rows and columns in Microsoft Excel worksheets.
• Delete rows and columns in Microsoft Excel worksheets.
• Transpose rows and columns.
• Change row heights.
• Change column widths.
• Hide and unhide rows and columns.
• Freeze panes.

Module 9: Editing Worksheets
This module explains how to edit worksheets in Microsoft Excel.
Lessons

• Find
• Find and Replace
• Using Find and Replace
• Using the Clipboard
• Using Format Painter
• Managing Comments

Lab : Editing Worksheets Exercises

• Using Find and Replace
• Using the Clipboard
• Using Format Painter

After completing this module, students will be able to:

• Locate information in your worksheet using Find.
• Use Find and Replace to quickly replace data with other data.
• Use the Cut, Copy, Paste and Format Painter commands to edit worksheets.
• Manage comments

Module 10: Finalizing Microsoft Excel Worksheets
This module explains how to finalize Microsoft Excel worksheets.
Lessons

• Setting Margins
• Setting Page Orientation
• Setting the Print Area
• Print Scaling (Fit Sheet on One Page)
• Printing Headings on Each Page/Repeating Headers and Footers
• Headers and Footers
• Preparing to Print

Lab : Preparing to Print
After completing this module, students will be able to:

• Set margins in Microsoft Excel worksheets.
• Set the page orientation to portrait or landscape.
• Set the print area in a worksheet.
• Scale a worksheet so that all data prints on one page. Add headers and footers to a worksheet.

Module 11: Advanced Formulas
This module explains how to work with advanced formulas in Microsoft Excel.
Lessons

• Using Named Ranges in Formulas
• Using Formulas That Span Multiple Worksheets
• Entering a Formula Using Data in Multiple Worksheets
• Using the IF Function
• Using the PMT Function
• Using the LOOKUP Function
• Using the VLOOKUP Function
• Using the HLOOKUP Function
• Using the CONCAT Function
• Using the TRANSPOSE Function
• Using the PROPER, UPPER, and LOWER Functions
• Using the PROPER Function
• Using the LEFT, RIGHT, and MID Functions
• Using the LEFT and RIGHT Functions
• Using Date Functions
• Using the YEAR, MONTH, and DAY Functions
• Creating Scenarios

Lab : Advanced Formulas Exercises

• Using Named Ranges in Formulas
• Entering a Formula Using Data in Multiple Worksheets
• Using the IF Function
• Using the PMT Function
• Using the VLOOKUP Function
• Using the CONCAT Function
• Using the PROPER Function
• Using the LEFT and RIGHT Functions
• Using the YEAR, MONTH, and DAY Functions

After completing this module, students will be able to:

• Name and label cells and ranges of cells.
• Use names and labels in formulas.
• Create formulas that span multiple worksheets.
• Use the conditional IF function and its variants in formulas.
• Use the PMT function to calculate payments for loans.
• Use the LOOKUP function.
• Use the VLOOKUP function.
• Use the HLOOKUP function.
• Use the CONCAT function to join the contents of numerous cells.
• Use the TRANSPOSE function.
• Use the PROPER, UPPER, and LOWER functions to alter the casing of text.
• Use the LEFT, RIGHT, and MID functions to return characters from the start or end of a string, or a specific number of text characters.
• Use various date functions.

Module 12: Working with Lists
This module explains how to work with lists.
Lessons

• Converting a List to a Table
• Removing Duplicates from a List
• Sorting Data in a List
• Filtering Data in a List
• Adding Subtotals to a List

Lab : Work with Lists Exercises

• Converting a List to a Table
• Removing Duplicates from a List
• Sorting Data in a List
• Filtering Data in a List
• Adding Subtotals to a List

After completing this module, students will be able to:

• Convert data into tables.
• Remove duplicates from tables.
• Sort data in Excel.
• Filter data in Excel.
• Use subtotals to automatically total related data.
• Group and ungroup data.

Module 13: Working with Illustrations
This module explains how to work with Illustrations in Microsoft Excel.
Lessons

• Working with Clip Art
• Using Shapes
• Adding Shapes
• Working with Icons
• Working with SmartArt
• Using Office Ink

Lab : Working with Illustrations Exercises

• Working with Clip Art
• Adding Shapes

After completing this module, students will be able to:

• Add pictures to your worksheets.
• Use Clip Art to illustrate your worksheets.
• Insert shapes into Microsoft Excel worksheets. Use SmartArt to illustrate your worksheets.

Module 14: Visualizing Your Data
This module explains how to work with various types of charts in Microsoft Excel.
Lessons

• Inserting Charts
• Using the Chart Recommendation Feature
• Editing Charts
• Using Chart Tools
• Using the Quick Analysis Tool
• Add and Format Objects
• Create a Custom Chart Template

Lab : Visualizing Your Data Exercises

• Inserting Charts
• Editing Charts

After completing this module, students will be able to:

• Create charts that enable you to visualize your data.
• Choose what data is displayed in your charts.
• Show and hide data labels.
• Show and hide the legend.
• Show and hide the chart title.
• Add a picture or shape to a chart.
• Change the way text displays in a chart.
• Change the fill color of a chart.
• Add and format objects in a worksheet.
• Create a custom chart template.

Module 15: Working with Tables
This module explains how to work with tables in Microsoft Excel.
Lessons

• Format Data as a Table
• Move between Tables and Ranges
• Modify Tables
• Define Titles
• Creating and Modifying a Table in Excel

Lab : Creating and Modifying a Table in Excel

• Creating and Modifying a Table in Excel

After completing this module, students will be able to:

• Format data in Excel as a table.
• Modify Excel tables

Module 16: Advanced Formatting
This module explains how to work with advanced formatting features in Microsoft Excel.
Lessons

• Applying Conditional Formatting
• Using Conditional Formatting
• Working with Styles
• Creating and Modifying Templates

Lab : Advanced Formatting Exercises

• Using Conditional Formatting
• Working with Styles

After completing this module, students will be able to:

• • • Use conditional formatting to display cells differently based on their values.
    • Quickly format tables using styles.
    • Format cells using styles

•   Create basic worksheets using Microsoft Excel.
•   Perform calculations in an Excel worksheet.
•   Modify an Excel worksheet.
•   Modify the appearance of data within a worksheet.
•   Manage Excel workbooks.
•  Print the content of an Excel worksheet.
•  Learn how to use handy features.
• Use formulas and functions.
• Create and modify charts.
• Convert, sort, filter, and manage lists.
• Insert and modify illustrations in a worksheet.
• Work with tables.

• Students who have little or no familiarity with Microsoft Excel.