Excel Intermediate Course
(4.83 out of 5) 12727 Student Reviews
About the Course
This one-day Excel Intermediate course takes learners through the techniques associated with building better, faster and more reliable workbooks. It covers the core function families used in day-to-day business work, including conditional aggregation, logical, date and time, and lookup functions.
The course teaches XLOOKUP as the primary lookup method alongside VLOOKUP and HLOOKUP, introduces dynamic array functions such as UNIQUE, SORT and FILTER, and includes a full unit on Excel Tables and structured references. Participants then apply these skills to PivotTables, PivotCharts, conditional formatting, custom number formats and chart presentation. Training is delivered through business scenarios, with hands-on practice throughout.
Who should do this course?
Designed for current Microsoft Excel users, this course is for those wanting to build on existing fundamental skills to go beyond building simple workbooks.
Upon successful completion of this course, students will be able to:
- ▪ Use fill operations, custom lists and Flash Fill to speed up data entry
- ▪ Understand conditional formatting in Excel
- ▪Understand dynamic arrays and use UNIQUE, SORT, SORTBY, FILTER and SEQUENCE
- ▪Create and manage Excel Tables and write formulas with structured references
- ▪ Pull data from another list automatically (XLOOKUP and VLOOKUP)
- ▪Work with logical functions to build decision-making formulas
- ▪ Use date and time functions to perform calculations
- ▪ Summarise multiple rows into a clear report in minutes (PivotTables)
Prerequisites
Participants should have already completed the Excel Beginner Course before attending this course, or have equivalent skills in creating and printing formatted spreadsheets containing simple formulas and functions.
Excel Intermediate Course Detail
$385 incl GST
- Duration:1 Day
- Max. Class Size:10
- Avg. Class Size:5
-
Study Mode:
Classroom Online Live
- Level:Intermediate
- CPD Hours:6 hours
- Course Times: Classroom: 9.00am - 5.00pm approx(Local Time) Online Live: 9.00am - 5.00pm approx(AEST or AEDT)
- Download Course PDF
Excel Intermediate Course Dates
Looking for Excel Intermediate dates?
There are currently no scheduled dates matching your selected filter, but we'd love to assist. Get in touch with our team to discuss your individual training needs or corporate group requirements, and we'll explore available options with you.
Excel Intermediate Course Content
⊕ Expand full topic list
Unit 1: Filling Data and Flash Fill
- Understanding Filling
- Filling a Series
- Filling a Growth Series
- Filling Using Options
- Creating and Modifying a Custom Fill List
- Extracting with Flash Fill
- More Complex Flash Fill Extractions
⊕ See more
Unit 2: Defined Names
- Understanding Defined Names
- Creating Names Using the Name Box
- Creating Names from a Selection
- Using Names in Typed Formulas
- Applying Names to Existing Formulas
- Defining Names for Constant Values
- Scoping Names to a Worksheet
- Working with Name Manager
⊕ See more
Unit 3: Essential Worksheet Functions
- Reviewing Important Worksheet Functions
- Using COUNTIF and COUNTIFS
- Using SUMIF and SUMIFS
- Using AVERAGEIF and AVERAGEIFS
- Using MAXIFS and MINIFS
- Using the ROUND Function
- Rounding Up and Rounding Down
- Using the PMT Function
⊕ See more
Unit 4: Logical Functions
- Working with Logical Functions
- Using the IF Function with Text
- Using the IF Function with Numbers
- Nesting IF Functions
- Using the IFS Function
- Using the AND Function
- Using the OR Function
- Using the NOT Function
- Using the IFERROR Function
- Using the SWITCH Function
⊕ See more
Unit 5: Date and Time Functions
- Understanding Date and Time Functions
- Using the TODAY and NOW Functions
- Calculating Future Dates
- Calculating the Difference Between Dates
- Using the DATE Function
- Using the WEEKDAY Function
- Using the WORKDAY Function
- Using the EOMONTH Function
- Working with Hours and Minutes
⊕ See more
Unit 6: Lookup Functions
- Understanding Data Lookup Functions
- Using VLOOKUP for Exact Matches
- Using VLOOKUP for Approximate Matches
- Using HLOOKUP
- Understanding the Limitations of VLOOKUP and HLOOKUP
- Introducing XLOOKUP
- Using XLOOKUP for Exact Matches
- Returning a Message When XLOOKUP Finds Nothing
- Using XLOOKUP for Approximate Matches
- Performing Reverse Lookups with XLOOKUP
- Returning Multiple Columns with XLOOKUP
- Using the XMATCH Function
- Trapping Lookup Errors with IFNA
⊕ See more
Unit 7: Working with Dynamic Arrays
- Understanding Dynamic Arrays and Spilling
- Recognising the Spill Range and Spill Errors
- Using the UNIQUE Function
- Using the SORT Function
- Using the SORTBY Function
- Using the FILTER Function
- Filtering on More Than One Condition
- Handling Empty Results Returned by FILTER
- Using the SEQUENCE Function
- Referencing a Spill Range with the Hash Operator
- Building a Simple Dynamic Summary Report
⊕ See more
Unit 8: Working with Excel Tables
- Understanding Excel Tables
- Converting a Range to a Table
- Understanding Table Elements and the Table Design Tab
- Naming a Table
- Applying and Modifying Table Styles
- Adding Rows and Columns to a Table
- Understanding Structured References
- Writing Formulas with Structured References
- Working with Calculated Columns
- Displaying and Using the Total Row
- Sorting and Filtering a Table
- Removing Duplicate Values from a Table
- Inserting and Formatting Slicers
- Using a Table as the Source for a Chart
- Converting a Table Back to a Range
⊕ See more
Unit 9: Advanced Filtering Techniques
- Creating Custom Filters
- Using Wildcards in a Filter
- Using Date Filters
- Filtering by Colour
- Understanding Advanced Filter
- Building a Criteria Range
- Copying Advanced Filter Results to Another Location
- Extracting Unique Records
⊕ See more
Unit 10: PivotTables
- Understanding PivotTables
- Creating Your Own PivotTable
- Creating a PivotTable from an Excel Table
- Defining the PivotTable Structure
- Filtering a PivotTable
- Switching PivotTable Fields
- Counting in a PivotTable
- Formatting a PivotTable
- Formatting PivotTable Values
- Refreshing PivotTable Data
- Adding Slicers to a PivotTable
- Inserting a Timeline Filter
⊕ See more
Unit 11: PivotCharts
- Inserting a PivotChart
- Defining the PivotChart Structure
- Changing the PivotChart Type
- Using the PivotChart Filter Field Buttons
- Connecting Slicers to a PivotTable and PivotChart
⊕ See more
Unit 12: Conditional Formatting
- Understanding Conditional Formatting
- Formatting Cells Containing Values
- Clearing Conditional Formatting
- Formatting Top and Bottom Items
- Working with Data Bars
- Working with Colour Scales
- Working with Icon Sets
- Creating and Editing Sparklines
- Creating Custom Rules with a Formula
- Managing and Prioritising Rules
⊕ See more
Unit 13: Custom Number Formats
- Working with Number Formats
- Understanding Format Codes
- Creating Descriptive Custom Formats
- Custom Formatting Large Numbers
- Customising the Display of Negative Values
- Formatting Calculated Time
- Applying Colour Within a Custom Format
⊕ See more
Unit 14: Chart Elements and Formatting
- Understanding Chart Elements
- Adding a Chart Title and Axes Titles
- Repositioning the Legend
- Showing Data Labels and Gridlines
- Adding a Trend Line
- Adding a Data Table
- Creating a Combination Chart
- Adding a Secondary Axis
- Selecting Chart Objects
- Using Shape Styles
- Changing the Colour of a Series
- Formatting the Chart Area and Background
- Working with the Format Pane
- Creating an Exploded View for Pie Slices
⊕ See more
Unit 15: Finding and Replacing Data
- Understanding Find and Replace Operations
- Finding Text
- Replacing Values
- Using Replace to Change Formulas
- Replacing Within a Range
- Finding Formats
- Using Go To Special
- Finding the Last Cell
⊕ See more
Training Packages
Microsoft Training Package
Book any two 1-day Microsoft Office courses and receive a third 1-day Microsoft office course for FREE!
Related Courses
Student Reviews
I thought that I would need to be in person to get anything out of the course, I also thought because it was online that I would fall behind. I didn't Paul was great he never left anyone behind.
this is my second time taking this course with my free resit, and it is just as useful the second time as it was the first! clear information presented in understandable pieces with related worksheets to practice what we are learning.
This was a great course. It was useful to learn the IF and AND functions as well as the deep dive into chart functionality and how to manipulate and present data in meaningful ways. Thank you!
Oakley is a great trainer and he is able to explain things clearly and at a pace that works for everyone
I found it very useful however I needed more time spent on the formulas. eg commas, brackets etc I think I just need to start using them more then I will be a wiz. Thank you again Oaks : )
This was an excellent course! Our trainer was very knowledgeable and explained the use of various functions in excel in simple terms. The examples used were easy to follow and relevant to our work. Thank you Oakley.
The trainer was clear, knowledgeable, and engaging. They explained everything well and made the session easy to follow and understand.
Penn was very interactive and explained the concepts of Excel very clearly and was funny with a good sense of humour !
I probably should have gone for the level3 course, but I did learn a few things, and the refresher on some things I haven't used for a while was beneficial.... Instructor was great!
Wayne displayed amazing knowledge and was a fantastic instructor for this course. I have learnt many valuable skills and am looking forward to attending the next course!
Wayne is a solid trainer who is extremely knowledgeable and very familiar with how to teach people.
Martin was a great instructor that kept a good pace, looked for examples that would specifically help class members, and made us all feel at ease. Was a really great learning environment!
A very well run course Martin delivered a lot of information in an easy to grasp manner, keeping it interesting and relevant.
Thank you very much Martin for an extremely informative day. I did find it a bit difficult sitting in the back seat, so my hearing or vision did not help me, but I appreciated the individual help that you provided so generously. I learnt a huge amount during the day.
Excellent course, very valuable information that reflected the work that I do and filled in the gaps for me. also, the right amount of humour/banter from Adam the trainer.
Enquire Now
Fill in your details to have a training consultant contact you to discuss your training needs.
Note: Form fields marked with * are required.
Save $385
Book any two 1-day Microsoft Office courses and receive a third 1-day Microsoft Office course for
FREE!
For more info please
Call 1300 888 724