Course Summary

With the continuing evolution of Excel and Microsoft 365, there have been significant feature additions over the years. If you have used Excel from the 2000s or have only encountered introductory or intermediate content, you may be missing out on a breadth of techniques and topics. Alternatively, you may be looking to move forward to be the Excel specialist in your team, to improve your productivity or make work easier.
You may also be thinking about sitting for micro credential certifications that rely on data.

Come to a face to face experience. We will explore how to use aids to improve consistency when typing data, analysis tools and ways to highlight standouts and trends. If you have not set about automating Excel with Macros and Power Query, we will have discussions and hands on practice with these.

Topics we can discuss are:

  • Data Validation aids, Conditional Formatting
  • Excel Tables with structured formulas, Pivot Tables, Slicers
  • Overview of Excel macros, addressing the Excel Object Model, getting comfortable with the VBA IDE
  • Overview of Power Query (Extract and Transformation of data)
  • Protecting the Worksheet, the Workbook, aspects of Collaboration

Who is this course for?

This course assumes some prior knowledge of Excel in the day to day workplace as well as a general understanding of personal computers and the Windows operating system environment.

What should I bring on the day?

Please have a USB on hand in order to save your work.

 

Currently, we have no dates scheduled for this course. Please contact us via email enquiries@cae.edu.au to register your interest.

During the course you will cover topics such as:

  • Quick Revision and Familiarisation
    • The Excel file – formats, extensions, compatibility
    • Behaviour and limits of Excel (Local vs Microsoft OneDrive)
    • The different cell data types
      • Text, Numbers, Boolean
      • Dates stored as Numbers, as text
      • Numbers stored as Text
    • Calculating with Excel
      • The three layers of an traditional Excel cell
        • How they affect subsequent calculation results
        • Controlling how Excel interprets them
      • Excel formula expressions
        • Absolute and Relative Addressing
        • Arithmetic and text operators in the expressions
        • Show Formulas, Trace Precedents, Dependents
        • Popular Worksheet Functions
      • Excel Array functions vs CSE array formulas
    • Understanding that working with Excel involves recognising and choosing models that are optimum for the task at hand and best use case
    • Object Names
      • Cell Addresses vs Range Names
      • Table Names
      • Other Object Names
    • Working with Excel Tables
      • Converting a cell range to a Table and vice versa
      • Table Formatting
      • Sorting
      • Hiding and Showing relevant items (Filtering)
      • Searching
      • Structured Referencing in Table Cell Formulas
      • Using Slicers with Tables
    • Basic Cell, Column, Row, Worksheet formatting
      • Excel specific techniques in printing – Print Area, Repeating Rows and Columns, Automatic
      • Fit to pages wide and tall, Gridlines, Headings
      • Conditional vs Traditional formatting
    • Excel charting and Visuals
      • Standalone Charts – approach and mindset
      • Sparklines
      • Timelines
      • Conditional Formatting
  • Pivot Tables with Pivot Charts and Slicers
  • Data Entry Aids
    • Data Validation
    • Importing and Parsing text data
    • Using Form Controls
  • Security
    • Worksheet Protection
    • Hiding Worksheets
    • Workbook Protection
  • Introduction to Power Query (Importing and transforming data into Worksheet Tables)
  • Introduction to Automating and Programming Excel with VBA
  • Figuring out what to do
    • Reading the screen for visual clues and deducing how to resolve unexpected Excel behaviour
    • Microsoft keyword sensitive Suggested Actions
    • Official Microsoft Help
    • Third Party Help on the web
Last updated: April 24, 2024 04:54pm