1 Day Intermediate Excel Click to print this pagePrint
You are here: / Home / Courses / Course Categories / Excel Courses / 1 Day Intermediate Excel

Introduction

This application driven 1-day programme leads delegates through selected Excel functions, explaining the function and its uses.

This is a very hands-on, non-intimidating, course - delegates work through various exercises under the supervision, and with the help, of the facilitator to get to grips with the nuances of Excel's most-used functions

To The Top

Who Should Attend
  • All persons using Excel spreadsheets
  • All persons involved in the construction and use of spreadsheets and financial models, including budgets, projections, evaluations, cash flows, projects, etc.
To The Top

Pre Requisite(s)
  • Delegates should be familiar with Microsoft Excel (though not necessarily expert in the use thereof, as any relevant Excel function pertinent to an exercise will be discussed prior to tackling the exercise).

    Excel knowledge required:
    1. Basic Excel skills
      1. Opening & closing files
      2. Moving around the spreadsheet
      3. Inserting & deleting rows and columns
      4. Inserting text & formulas
      5. Deleting cells
      6. Copying formulas
      7. Printing
    2. Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12
    3. Formatting

To The Top

Outcomes
By the end of the course delegates will recognize areas where they could improve their own spreadsheeting. They will be in a position to immediately apply the lessons learnt to their own spreadsheets in their own environment.

What the delegates will learn:
  1. Conditional formatting
  2. Creating and naming formulas
  3. Creating and using range names
  4. Creating your own formats - the reasons and uses thereof
  5. Data validation (drop-down boxes and validating data at time of entry)
  6. Excel's database facilities:
    1. Auto filter
    2. Advanced filter
    3. Extracting one of each (unique)
    4. Subtotalling
    5. The "D" functions and their uses
  7. Formulas across files - how best to handle this and the pitfalls
  8. Formulas across sheets
  9. Hyperlinks and drilling down
  10. Logical tests (IF statements)
  11. Protecting & unprotecting sheets
  12. Subtotal
  13. Sumif & Countif
  14. Summing through the sheets
  15. Useful tips & tricks

To The Top

Course Content

  • Some Useful Tips to speed up your spreadsheeting
      Hands-on exercise
  • Identifying and Protecting data in spreadsheets
  • Naming Cells, Ranges & Formulas
      Hands-on exercise
  • Creating your own Formats (and the uses thereof)
  • Conditional Formats
    • Hands-on exercise - Creating conditional formats
  • Validating data and producing drop-down lists
  • Multiple sheet models - Working across sheets
    • Hands-on exercise - Working across sheets
  • Drilling Down
    • Using the Ctrl key
    • Using hyperlinks
  • Logical Tests - IF, AND, OR, Nested Ifs
    • Hands-on exercises - IF and Nested Ifs
  • Lookups - Vlookup and Hlookup
    • Hands-on exercise - Vlookup
    • Hands-on exercise - Hlookup
  • SUMIF and COUNTIF
  • Excel's Database
    • Auto filter & advanced filter
    • Extracting one of each (unique)
    • Sorting
    • Creating subtotals
    • The effect of the SUBTOTAL function
    • Using Dfunctions - DSUM, DCOUNT, DAVERAGE
    • Hands-on exercise - D Functions


Detailed Course Contents

Entering Data Quickly with AutoFill
  • Entering Numerical Data with AutoFill
  • Adding Your Own Lists Into the AutoFill

Formulas
  • The 4 Golden Rules
  • Entering Formulas
  • Understanding the Order of Operation

Functions
  • Entering Functions
  • Using the AutoSum Button
  • Entering Functions Using the Formula Composer

Copying Formulas And Functions
  • Copying Using the Copy and Paste Buttons
  • Copying Using AutoFill
  • Copy a Formula and Paste the Value

Using Relative and Absolute Cell Address
  • Relative Cell Address
  • Absolute Cell Address
  • Mixed Reference
  • Changing a Cells Reference Type

Formatting Cells
  • Adding Comments to Cells
  • Formatting with AutoFormat
  • Copying Formats with the Format Painter
  • Conditional Formatting

Printing
  • Selecting a Paper Size and Orientation
  • Scaling your Spreadsheet
  • Setting Margins
  • Centering your Worksheet on a Page
  • Adding a Header and Footer
  • Printing Column and Row Headings
  • Controlling Page Breaks
  • Using Page Break Preview to Break Pages

Views
  • Freezing Columns And Row Titles
  • Splitting Worksheets
  • Grouping and Outlining
  • Creating Views

Creating a Database
  • Entering Data into a Database

Sorting a Database
  • Sorting By a Single Field
  • Sorting By Multiple Fields

Extracting Records From a List
  • Filtering a Database
  • Using Custom Filter

Creating Charts
  • Using the Chart Wizard
  • Modifying a Chart
  • Printing a Chart

Adding Graphic Objects
  • Formatting an Object
  • Inserting a Clipart Image
  • Module Objectives

Working with Toolbars
  • Displaying or Hiding Toolbars
  • Moving a Toolbar
  • Customising a Toolbar
  • Change a Button Image
  • to Reset a Toolbar
  • Creating a New Toolbar

Templates
  • Creating a Template for new Workbooks
  • Creating a New Workbook Based on a Template
  • Modifying a Template

Working with Multiple Worksheets
  • Inserting Worksheets
  • Selecting Multiple Worksheets
  • Entering and Editing Data in a Group
  • Renaming Worksheets
  • Deleting Worksheets
  • Moving Worksheets
  • Copying Worksheets
  • Arranging Multiple Worksheets
  • Hiding Worksheets

Linking Worksheets
  • Writing a Formula to Link Worksheets
To The Top

Learning Method
 Our training approach is lecture based, highly individualised and practical. Each learner must have access to a modern laptop or notebook. Comprehensive course material is supplied for future reference.

All learners will complete a Pre and Final Evaluation.

To The Top