From 9.30am to 12.45pm in our training room at Perth Airport or online via Teams.
COURSE OUTLINE
Ideal if you are new to Excel or need to brush up your skills.
Exploring Excel
- Creating a new workbook
- Navigating the ribbon (how the “Tabs” work, dialogue boxes, quick access toolbar etc)
- Features of a sheet (understanding rows, columns, structure, gridlines etc)
Basic Workbook Skills
- Selecting cells & ranges
- Working with rows & columns (inserting & deleting / changing width & height etc)
- Working with sheets (adding & deleting worksheets / re-naming / moving)
Basic Formulas
- Understanding formulas (how to get started using the = symbol)
- Adding, subtracting, multiplying & dividing formulas
Basic Functions (using the Autosum button to do calculations quickly)
- Autosum, average, count, max, min
Formatting data and cells (improvements to the appearance of a spreadsheet and its consistency)
- Formatting numbers and text
- Colouring data and cells
- Wrapping, shrinking & merging cells & using indents
- The format painter & cell styles / applying borders
Introduction to Conditional Formatting (colour-based formatting to highlight, emphasise & differentiate data)
- Highlight cell rules & top / bottom rules
- Data bars / colour scales / icon sets
Working with Data
- Sorting Data (how to sort one field at a time / two or more / sorting by text, values, colour etc)
- Filtering Data (adding the filter button to quickly find information)
Copying and Moving Data
- Copy & paste /cut & paste
- Working with data series (how to quickly set up a spreadsheet with days of week, months, dates etc)
- Drag & drop / undo & redo / find & replace (ways to make changes quickly)
Printing
- Accessing print options & choosing what to print
- Previewing and printing
- Page Setup / Orientation / Scaling / Margins
COURSE OUTLINE
Helpful if you already use Excel but want to be able to do more with it.
Exploring Excel
- Customising the ribbon
- Using templates
Basic Formulas
- Adding, subtracting, multiplying & dividing
Basic Functions
- Autosum, average, count, max & min
Managing Workbooks
- Formatting & worksheet techniques
- Page set-up and print options
Working with Data
- Sorting (2 or more fields) / custom sorts
- Auto & Advanced filtering
- Filling data quickly
Summarising Data
- Using subtotal (to quickly find sum, average, count etc on sorted data)
- Using 3D formulas (totalling data from identical sheets into a master sheet)
- Using the consolidate function (totalling data from non-identical sheets into a master sheet)
Tables
- Creating and editing tables (adding rows, columns & formulas and using slicers to find information quickly)
Data Validation
- Drop down menus (creating lists to help with accurate data input)
- Basic data validation & error messages (to help with accurate data input)
Absolute cell referencing & named cells
- Absolute (using $ to fix reference cell into position)
- Named cells (naming individual cells and ranges)
Introduction to the IF Function
- IF Statements (to evaluate certain conditions)
- Introduction to the IFERROR function (to stop error messages such as #Null! #N/A! etc)
Conditional Formatting
- Basic conditional formatting (colour-based formatting to highlight, emphasise & differentiate data)
Basic Pivot Tables
- Introduction to PT’s (quickly summarise large amounts of data without changing the layout of the original data)
COURSE OUTLINE
Helpful if you are an experienced user but want to know more about formulas and functions for data analysis.
Summarising Data
- Using subtotals
- Using 3D formulas
- Using the consolidate function
Absolute & Mixed Cell Referencing
- Using the $ for cell or range of cells
Formulas & Functions
Logical Functions
- IF, Nested IF, IFS
- Switch function
- IF Error
- Using AND / OR within functions
- CountIF/SumIF/AverageIF
- SumIFS/CountIFS
Lookup Functions
- Review of V & H Lookups
- XLookup
Date & Time Functions
- Workday & Networkday functions
Advanced Conditional Formatting
- New rule with AND / OR / IF functions
- Highlighting entire rows
- Using dropdown lists
Working with Pivot Tables
Analysing Data
- Creating a pivot table & pivot field
- Summary functions (using sum, count, average etc)
- Sorting data & calculated fields
- Grouping & ungrouping pivot table data
- Creating a pivot table chart
- Slicers in pivot tables
- Introduction to PowerPivot
- Introduction to Power Query