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

Introduction
This application driven 1-day programme leads delegates through selected advanced Excel functions and procedures, explaining the function or procedure and their 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 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:
    Basic & Intermediate Excel skills, including:
    1. Moving around the spreadsheet & workbook
    2. Inserting & deleting rows and columns
    3. Inserting text & formulas
    4. Copying formulas
    5. Formatting
    6. Formula structure
    7. Making cell addresses absolute
    8. Naming cells and ranges of cells
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.

Amongst other things the delegates will learn:
  1. Array functions & formulas
  2. Grouping and Outlining
  3. How to create Views of your worksheet
  4. How to look up and extract data from tables
  5. How to record macros and assign them to buttons
  6. How to set up and use Excel's "controls" - Option buttons, spinners, etc.
  7. How to set up one & two input data tables
  8. How to work with dates
  9. Indirect addressing
  10. "Masks" and their uses
  11. Pivot tables and consolidating with pivot tables
  12. The many uses of the CTRL key
  13. The use of the Row, Rows, Column, Columns and Sumproduct functions
  14. Working with strings (words)
To The Top

Course Content
  1. 30 useful Excel tips
  2. Arrays - function, formulas and constants
  3. Consolidating Data from different files
  4. Controls - using Forms toolbar - drop-down boxes, option and spinner buttons
  5. Customising toolbars
  6. Data tables -creating one and two input tables
  7. Databases - extracting, filtering, searching, sorting - DB Functions
  8. Dates - formatting dates, time and date functions
  9. Drilling down and hyperlinking
  10. Formatting cells - numbers, dates, copying, custom formatting, comments
  11. Formulas- auditing, copying, displaying, locating, making absolute, naming
  12. Functions - the function wizard, locating, identifying, using Ctrl key
  13. Goalseeking
  14. Grouping and Outlining - collapsing rows and columns
  15. Identifying input cells, formula cells, value cells
  16. If statements, and multiple (nested) "if" statements, sumif and countif
  17. Importing text files - text to columns, text import wizard
  18. Indirect Addressing
  19. ISError and ISNumber functions
  20. Linking - cells, worksheets, workbooks
  21. Lookup, Hlookup,Vlookup, Match, Index
  22. Lookups - Looking up and extracting data from tables
  23. Macros - recording, running and assigning buttons and icons
  24. Masks and their uses
  25. Multi sheet workbooks - navigating, summing through sheets
  26. Naming cells, ranges and formulas
  27. Pivot Tables - single and multiple ranges
  28. Protection - files, worksheets, workbooks, hiding formulas
  29. Reviewing spreadsheets - identifying input and formulas
  30. Row(S) and Column(S) functions
  31. Scenarios - creating and managing, producing summaries
  32. Sensitivity Analysis - sensitivity factors, tables - using Goalseek
  33. Sheets - copying, deleting, moving, naming, hiding, unhiding
  34. String functions
  35. Sumproduct function
  36. Uses of the CTRL key
  37. Views - creating custom views
  38. Viewing worksheets - freezing panes, viewing two sheets/books simultaneously
  39. Watch Window
To The Top