2 Day Specialised Excel Techniques Click to print this pagePrint
You are here: / Home / Courses / Course Categories / Excel Courses / 2 Day Specialised Excel Techniques

Introduction
This application driven 2-day programme leads delegates through advanced 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 advanced functions

To The Top

Who Should Attend
  • All persons using Excel spreadsheets
  • All persons involved in the construction and use of 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:
  • 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
  • Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12
  • Formatting
To The Top

Outcomes
By the end of the course delegates will recognize numerous areas where they could improve their own efficiency, speed and spreadsheet skills. They will be in a position to immediately apply the lessons learnt to their own spreadsheets or models in their own environment.
What the delegates will learn (from an Excel viewpoint):
  1. Advanced functions such as Sumif, Vlookup, Hlookup, Index, Rows, Columns, Indirect, etc.
  2. Conditional formatting
  3. Consolidations
  4. Creating and using range names
  5. Creating views
  6. Creating your own formats – the reasons and uses thereof
  7. Data tables
  8. Data validation (drop-down boxes and validating data at time of entry)
  9. Excel’s database facilities:
    1. Auto filter
    2. Advanced filter
    3. Subtotalling
    4. The “D” functions and their uses
    5. Pivot table reports & charts
  10. Formulas across files – how best to handle this and the pitfalls
  11. Formulas across sheets
  12. Logical tests (IF statements)
  13. Protecting & unprotecting sheets
  14. Recording macros
To The Top

Course Content
Day 1
 
Some Useful Tips to speed up your spreadsheet skills
Identifying and Protecting data in spreadsheets
Naming Cells, Ranges & Formulas
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
Drilling Down
  • Using the Ctrl key
  • Using hyperlinks
Logical Tests – IF, AND, OR, Nested Ifs
  • Hands-on exercises – IF and Nested IFs
SUMIF and COUNTIF
  • Examples of the two forms
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
Pivot Table Reports
Hands-on exercise – Pivot table reports
 
Day 2
 
Lookups
  • Vlookup
  • Hlookup
  • Lookup
  • Match
  • Index
Practical exercises incorporating all of the above
Row(S) and Column(S) functions
Using “Masks”
The SUMPRODUCT function
String functions
Data Tables
  • Hands-on exercise – Creating a data table
Outlining – Collapsing rows & columns
Creating Views
Indirect Addressing
  • Hands-on exercise – Indirect addressing
Working with Arrays, Array Formulas, Array Functions, and Array Constants
  • Hands-on exercises – Array formulas
Date Functions
Consolidating data from different files
Recording macros
Assigning macros to buttons & icons
Setting up “Controls” (Drop-Down Boxes, Option buttons, Spinners)
Appendix
  • List of uses of the Ctrl key
Course summary and conclusion


To The Top