About this course
This full-day intermediate course introduces Excel features that save you time. It is designed primarily for those who spend many hours formatting and updating data in 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 aimed at a person who is already proficient in the fundamentals of using Excel and is now ready to learn the features that are related to an intermediate understanding of Excel.
This course is definitely for you if …
- You want to learn the Excel functions and features that will reduce time spent formatting, analysing and updating your data
- You’re ready to step up to an intermediate understanding of Excel and want to learn how to use Excel functions to save time calculating and analysing data
- Grouping and analysing data are key Excel features that you want to be confident in
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.
This hands-on course takes learners through the time-saving features of Excel. Exercises and practice files are supplied for use during and after the training, allowing learners the opportunity to repeat the exercises again in their own time.
By the end of this course you should be able to confidently:
- Organise, extract and subtotal lists of data using Sort, Filter, Subtotals and Pivot Tables
- Deal with unexpected results when creating and copying calculations
- Save time and reduce errors using math, logical and date 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
- Record and use macros to cut down on processing time
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 6 hours hands-on tuition. Please note the duration is set for a maximum of 12 people. Individual coaching can be customised to suit. Please contact us to discuss.
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.
1 person only – $650+GST (5 hours)
2 to 3 people – $350+GST per person
4 to 6 people – $325+GST per person
7 to 12 people – $295+GST per person
Working with large tables of data
- Learn basic layout standards before using the data management and analysis tools in Excel
- Freeze headings to that they are visible as you scroll down or across
- Sort lists of data, including apply 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
- Inserting the SUBTOTAL function to display only a total for filtered data
- Introduction to Pivot Tables – creating a Pivot Table, changing the layout of the report, sorting and filtering the data, refreshing and resetting the source the data
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
Time Saving functions
- Perform calculations only if a set criteria is met using the IF, AND, OR SUMIF, COUNTIF , SUMPRODUCT and IFERROR functions
- Use Date functions to save time
- Identify data that meets a set criteria using Conditional Formatting
- Highlight duplicate and unique data
- Create Conditional Formatting based on a formula
- Modify Conditional Formatting Rules
- 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
An introduction to macros
Please note: macro programming and VBA are not covered
- Recording and running a macro
- Benefits of storing the macro in the workbook vs in your Personal macro workbook
- Create buttons in your workbook to run the macro
- Assign your macro to a button on the Quick Access Toolbar
- Deleting macros
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