Microsoft Excel

m-excel1

Module 1 – Getting Started

  • Starting up Excel & Basic uses of Worksheets
  • The Excel Window, use of the Tabs & Ribbon
  • The Microsoft Office button and Quick Access Toolbar
  • Creating A New Workbook
  • Entering, Editing and deleting data
  • Using the Undo Feature
  • Saving and Password Protecting Workbooks, using Save As and Save
  • Sharing Spreadsheets with Previous versions of Microsoft Office
  • Opening a Workbook
  • Closing a Workbook
  • Exiting Excel
  • Using the different Workbook views
  • Selecting Cells and Ranges of data
  • Formatting the Workbook, including Changing Font/Font Size, Changing the Number Format, Alignment, Colours and Shading

Module 2 – Basic Skills

  • Copy, Cut and Paste using Text between one or more Workbooks
  • Entering Values as Text
  • Moving Around the Worksheet
  • Automatically inserting lists used on a regular basis, i.e. Jan, Feb, Mar using the AutoFill feature
  • Sorting Data into Alphabetical or Numerical order
  • Formatting Rows and Columns, including Sizing, Adding, Deleting and Hiding
  • Inserting Cell Comments
  • Inserting Symbols
  • Naming Ranges to make worksheets easier to manipulate
  • Using Find & Replace
  • Printing and Print Preview
  • Page Set-up including Headers and Footers, Page Orientation, Margins and Fit to Page
  • Splitting and Freezing Cells
  • Working with Multiple Workbooks
  • Using the AutoSum feature to automatically add values

m-excel2

Module 3 – Creating Formulas

  • Using the AutoSum feature to automatically add values
  • Understanding Formulas and their rules
  • Creating Basic Formulas
  • Copying and Pasting Formulas
  • Using and Creating Absolute Formulas to enable a specific value to be used many times, i.e. VAT rate
  • Finding Minimum, Maximum and Average values of a list of data
  • Using named ranges within a formula
  • Using the Function Library
  • Inserting and Formatting Dates and Times
  • Using Dates within Formulas

Module 4 – Links, Graphs & Basic Conditional Logic

  • Linking Workbooks
  • Working with Hyperlinks
  • Protecting Formulas and Worksheets
  • Understanding Conditional Logic, i.e. IF statements
  • Working with Multiple Worksheets within a Single Workbook, Renaming, Inserting, Copying Information and Creating Summary Worksheets
  • Creating Graphs

m-excel3

Module 5 – Advanced Formulas

  • Using the Audit Feature to display cells used within Formulas
  • Use of the Transpose Feature to switch the display of data
  • Understanding Conditional Logic, i.e. IF statements
  • Creating formulas using Basic Conditional Logic
  • Creating formulas using Nested IF statements
  • Creating formulas using Nested AND & OR statements
  • Using the =Countif feature to count data meeting specified criteria
  • Using the =Sumif feature to total data meeting specified criteria
  • Using the =Averageif feature to find the average of data meeting specified criteria

Module 6 – Using the Database Functions

  • Apply Conditional Formatting
  • Applying Data Validation to ensure the correct information is entered
  • Remove Duplicate Values for line of data
  • Understanding the Database Functions
  • Using AutoFilter to view a specific set of data
  • Inserting Automatic Subtotals
  • Converting Text Strings to separate columns
  • Creation of Pivot Tables to display data in varying formats
  • Creation and use of Vlook-up table

m-excel4

Module 7 – Manipulating Text & Using Arrays

  • Tips and Tricks
  • Shortcuts for working on spreadsheets
  • Introduction to Arrays to give access to more complex formulas, and to create protected formulas
  • Using the SUMPRODUCT function
  • How to RANK results
  • Using String Functions
    • Change case functions
    • Determining whether a cell is text or a number
    • Comparing cell contents
    • Removing excess spaces and nonprinting characters
    • Replacing text with other text
    • Extracting words from a text string

Module 8 – Counting Functions & Lookups

  • Advanced counting functions using COUNTIF, SUMIF, AVERAGEIF
  • Advanced counting functions with multiple criteria, COUNTIFS, SUMIFS, AVERAGEIFS
  • Creation and use of advanced lookup tables
    • Review of exact and non-exact match VLOOKUP
    • Combining VLOOKUP with the IF function
    • Preventing error messages in VLOOKUP using IFERROR and ISERROR
    • Using VLOOKUP with multiple lookup tables
    • Using the LOOKUP function
    • Two way Lookups
    • Using INDEX and MATCH to perform a lookup

m-excel5

Module 9 – Automating Spreadsheets and Using Advanced Formulas

  • Using IS functions – Useful for preventing error messages in complex spreadsheets
  • Rounding
  • Advanced Data Validation- Identifying Incorrect entries by using Formulas for Data Validation Rules
  • Creating Custom Formats
  • Date Functions – Creating formulas to display the number of days elapsed between two dates without weekends, holidays etc.
  • Time Calculations – Adding up times
  • Recording Macros and attaching to Buttons
  • Creating Custom Views to allow specific areas to be printed quickly
  • Using Match Function – To find whether information in two cells is exactly the same
  • Outlining – Useful for summarising spreadsheets and simply hiding detail

Module 10 – Advanced Filtering & Use of Analysis Tools

  • Use the consolidation feature to bring information together
  • Advanced Database Filtering – Inserting Subtotal Calculations with Filters display filtered information on a separate sheet
  • Entering Database Statistics – Calculations to summarise information with DSum, Daverage etc where is returns data dependent upon the criteria specified
  • Using Goal Seek to automatically arrive at a required result by changing the variables used within a formula
  • Use of Solver (similar to Goal Seek with the additional option of setting specific criteria)
  • Creating Solver reports
  • Using the What If feature to build cross-reference tables
  • Overview of the Scenario Manager
  • Creating Multiple Scenarios within a worksheet
  • Displaying various Scenarios
  • Editing and Deleting Scenarios