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 & Counting Functions

  • Tips and Tricks
  • Shortcuts for working on spreadsheets
  • Using String 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
  • Introduction to Arrays to give access to more complex formulas, and to create protected formulas
  • Advanced Counting Functions
  • Counting Cells with Multiple Criteria

Module 8 – Advanced Conditional Logic & Lookups

  • Advanced Conditional Logic – Creating formulas using Nested IF statements
  • Advanced If statements, using more than one argument
  • Creating formulas using AND & OR statements
  • Advanced If statements, where multiple conditions are required for the argument to be true.
  • Creation and Use of Advanced lookup Tables
  • VLookup tables using ISNA and Lookup formulas
  • Using an Exact Match Lookup
  • Combining Lookup with other functions

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