Intermediate Excel Masterclass.
Course overview.
INTERMEDIATE EXCEL MASTERCLASS TRAINING MALAYSIAThe comprehensive workshop is designed for individuals with basic Excel knowledge, aiming to unlock its full potential for advanced data analysis and insightful reporting. This workshop delves into Excel’s extensive capabilities, transforming it from a simple data entry tool to a powerful instrument for decision-making and analysis. The curriculum covers a wide range of topics, from advanced formulae to complex data manipulation and visualization techniques, ensuring a thorough understanding and practical application of Excel’s features.
This workshop stands out for its interactive and hands-on approach, with sessions structured to promote active engagement and practical learning. As participants progress through the sessions, they gain both the technical know-how and practical proficiency in Excel, providing them with the confidence to apply these in their professional tasks
Key takeaways.
INTERMEDIATE EXCEL MASTERCLASS TRAINING MALAYSIABy the end of the course, participants will:
- Understand PivotTables, PivotCharts, slicers and timelines
- Learn various advanced features in Excel to save time
- Learn essential functions like VLOOKUP, XLOOKUP, ROUND and PMT
- Understand conditional formatting and creating data bars, colour scales, icon sets
- Using defined names in formulas and select ranges
- Validating data, creating an input & error message as well as creating a drop down list
- Understanding data linking; linking between worksheets & workbooks
- Understanding and creating tables, working with table styles, removing duplicates and sorting tables
- Learn number formatting techniques, applying alternate currencies & applying alternate date formats
- Learn useful Excel tips & tricks
Who should attend.
Analysts, managers, reporting professionals, executives and business owners who prepare Excel dashboards, scorecards or KPI reports, including directors, general managers, CFOs, finance managers, accountants, marketing, sales and HR managers.
Prerequisites.
Participants should be comfortable with basic Excel tools and functions, navigate confidently, create simple formulas and link between workbooks.
The programme.
INTERMEDIATE EXCEL MASTERCLASS TRAINING MALAYSIASESSION 1Introduction
- Overview of training aim, objectives and agenda
SESSION 2Useful Excel Tips & Tricks
- Centre across selection
- Keyboard shortcuts for selecting cells and ranges
- View multiple worksheets at the same time
- Protect cells, sheets and workbooks
SESSION 3Setting Excel Options
- Understanding Excel options
- Personalising Excel
- Setting the default font
- Setting formula options
- Understanding and setting save options
- Setting the default file location
- Setting advanced options
SESSION 4Formula Referencing
- Absolute versus relative
- Referencing relative formulas
- Problems with relative formulas
- Creating absolute references & mixed references
SESSION 5Filling Data
- Understanding filling
- Extracting With flash fill
- More complex flash fill extractions
- Extracting dates and numbers
SESSION 6Defined Names
- Understanding defined names
- Defining names from worksheet labels
- Using names in typed formulas
- Applying names to existing formulas
- Creating names using name box
- Using names to select ranges
- Pasting defined names into formulas
- Defining names for constant values
- Creating names from a selection
- Scoping names to a worksheet
- Using the name manager
- Documenting defined names
SESSION 7Essential Functions
- Worksheet functions
- Using IF with text and numbers
- Nesting IF functions
- The VLOOKUP function
- Using counting functions
- The ROUND function, rounding up and rounding down
- Manipulative functions
- The TODAY, NOW & DATE Function
- The PMT Function
SESSION 8Number Formatting Techniques
- Applying alternate currencies
- Applying alternate date formats
- Understanding format codes
- Creating descriptive custom formats
- Custom formatting large numbers
- Custom formatting for fractions
- Padding numbers using custom formatting
- Aligning numbers using custom formats
- Customising the display of negative values
SESSION 9Conditional Formatting
- Understanding conditional formatting
- Formatting cells containing values
- Clearing conditional formatting
- Top ten items, top and bottom formatting options
- Working with data bars, colour scales, icon sets
- Creating & editing sparklines
- Creating custom rules
- The conditional formatting rules manager
- Managing & clearing rules
SESSION 10Validating Data
- Understanding data validation
- Creating a number range validation
- Testing a validation
- Creating an input & error message
- Creating a drop down list
- Using formulas as validation criteria
- Circling invalid data
- Removing invalid circles
- Copying validation settings
SESSION 11Data Linking
- Understanding data linking
- Linking between worksheets & workbooks
- Updating links between workbooks
- Using names to link between workbooks
SESSION 12Worksheet Tables
- Understanding and creating tables
- Working with table styles
- Inserting table columns
- Inserting or deleting table records
- Removing duplicates
- Sorting tables
- Filtering tables
- Renaming a table
- Splitting a table
- Deleting a table
SESSION 13Pivottables
- Creating PivotTable
- Defining the PivotTable
- Structure
- Filtering a PivotTable & clearing a report filter
- Switching PivotTable fields
- Formatting a PivotTable
- Creating slicers
- Inserting a timeline filter
- Performing calculation
Throughout the workshop, participants will work through practical exercises to enhance their Excel knowledge
Your trainer.
INTERMEDIATE EXCEL MASTERCLASS TRAINING MALAYSIAAlvin Yong is a UK-registered Fellow Chartered and Certified Accountant (FCCA). He obtained his Master of Science in Professional Accountancy (Merit) in 2017 from the University of London.
To bring.
INTERMEDIATE EXCEL MASTERCLASS TRAINING MALAYSIAReviews from our Malaysian clients.
INTERMEDIATE EXCEL MASTERCLASS TRAINING MALAYSIA“Content is very good, notes are sufficiently detailed, presentation is clear and pace is good. Alvin entertained questions calmly, clearly, and with sufficient depth. Very knowledgeable and qualified for the training subject.”
“Alvin is an excellent trainer. He led interesting, interactive and focused discussions during the workshop.”
“Alvin was well prepared, knowledgeable and easy to follow. Level of detail was where it needed to be and the documentation was very thorough. The course went quickly which was always a good sign.”
“Fantastic trainer in terms of knowledge and skills. Extremely friendly, helpful and patient.”
“Good understanding and knowledge of the subject. Attentive and responsive to participants' queries. Presented the subject very clearly.”
“The trainer is knowledgeable. He was helpful. He handled the class material well.”
“Very informative. Gave an excellent count of how-to's.”
“Alvin is a very people person. Sound knowledge in his area of expertise. He carried out the programme well.”
Related courses.
EXCELReady when your team is.
Public runs across the region and live online — or we scope an internal cohort around your actual reports.
Enquire about in-house training