Course Summary

Have you been “doing” Excel for a while and wonder whether there is more that you can do to take your work to the next level? Many people use only use basic facets of Excel and miss out on productivity, on seeing trends and improving data integrity.  

  • Improve your productivity and reliability by using Power Query to manage incoming data into structured Tables.  
  • Use Pivot Tables to better analyse the data you bring in.  
  • Present your data better with Slicers, Conditional Formatting, Smart Filtering. Use Data Validation aids.  
  • Find out how to automate your work and reduce human error with Excel VBA Macros.

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.

 

Course Sessions

Wednesday, 23rd June - 2 classes


Session ID SRAX3702C
23 Jun - 30 Jun 9:30am - 4:30pm
Location CAE
Session Fees
$345 / $328 (conc)
Teacher
Ananda Sim

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: May 13, 2021 12:55pm