About this course
This half-day intermediate course is the second-step in becoming proficient in Excel. It focuses on teaching the smart ways to quickly update and analyse Excel data. Follow along with the trainer as you become proficient at creating effective worksheets.
If you use Excel often but find you seem to be spending way too much time getting the results you require, this is the course for you.
Who should attend?
This course is suitable for anyone who is already knowledgeable in the topics covered in our “Excel Essential Skills” course and is the next step to becoming a proficient user of Excel.
This course is definitely for you if …
- You want to save time sorting, filtering and analysing data
- You want to be confident in creating calculations
- You to be able to read other people’s calculations
- You want to learn a few of the most popular Excel functions
- You want to reduce time spent cross-referencing and updating your data
As this is an intermediate level course the prerequisite is proficiency in creating, formatting, editing, saving and printing an Excel workbook. This includes the ability to create basic formulas and use the SUM function.
By the end of this course you should be able to confidently:
- Organise, extract and subtotal lists of data using Sort, Filter and Subtotals
- Deal with unexpected results when creating and copying calculations
- Save time and reduce errors using Excel functions
- Change the formatting of data that meets a set criteria using conditional formatting
- Learn shortcuts to move, copy and link information between worksheets and workbooks
Need proof of your Continuing Professional Development (CPD)? On completion of the session, by request, we will send you a “Certificate of Attendance” confirming your participation and the duration of the training session.
Workshop delivery consists of 3.5 hours hands-on tuition. Please note the duration is set for a maximum of 12 people.
Price and Booking details
We bring the training to you. That’s right! We don’t run scheduled courses. We come onsite to your place of work on a day and time that works for you. Avoid traffic and parking hassles and learn at work. And if you don’t have a laptop, we can lend you one. Pricing is adjusted depending on the number of attendees. Minimum of 3, maximum of 12.
3 to 4 people – $2850+GST per person
5 to 7 people – $265+GST per person
8 to 12 people – $245+GST per person
Working with large tables of data
- Freeze headings so that they are still seen while scrolling
- Prepare the data for sorting and filtering
- Sort lists of data, including applying sorting levels (a sort within a sort)
- Instantly insert sub-total and total rows to a table of sorted data
- AutoFilter -using criteria to filter lists, create custom filters and filtering tricks to save you time
- Filtering tricks to save you time!
Dealing with unexpected results
- Understanding why calculations can be displayed incorrectly
- Learn quick tips for copying calculations; absolute addressing using absolute addressing
- Creating and using Named ranges in formulas
- Tips for working with multiple worksheets to save time formatting, updating, copying, moving and printing
- Link information between worksheets and workbooks
- Dealing with linked workbooks – refreshing, opening the source data, unlinking
Formulas and Functions
- Quick refresher on creating calculations in Excel
- Understand why calculations can be displayed incorrectly.
- Learn quick tips for copying calculations; absolute addressing
- Learn how to insert, write and get help on functions in Excel
- Find averages, highest value and lowest value using AVERAGE, MAX and MIN
- Count how many items are in a range using the COUNT family of functions
- Perform calculations only if set criteria are met using the IF function
- Total a range, but only include in the total values that met set criteria using SUMIF
- Work with ranges that contain errors using the IFERROR function
Your training style is wonderful
Thank you so much once again for the training yesterday. Feedback from the team is all very positive. Your training style is wonderful, and even the members of the team who probably considered themselves as capable Excel users had their minds blown a number of times.. which is awesome!
Craig Williams, National Sales Manager, Royal Canin