Advanced Microsoft Excel Training
2 Day Accredited Training
Unit standards: 116943, NQF Level 4, 3 Credits
Dates: 21 – 22 November 2024
Locations: Grayston Ridge Office Park, Sandton
Platform: Available In-Class / Online
Price: Available on Request
Course Introduction
It’s time to save your time and gain control over your financial, accounting, auditing, presentations, and day to day data calculations, sorting, and storing work, by one of the most intelligent software application Ms Excel. The masterclass might cover advanced Excel functions, pivot tables, data analysis tools, automation using macros, complex formulas, scenario analysis, and maybe even programming within Excel using VBA (Visual Basic for Applications). It’s designed to take users beyond the basics, helping them leverage Excel’s full potential for complex data manipulation, analysis, and visualization.
Discover methods to dramatically reduce the time you spend on spreadsheets.
Develop practical solutions to your business problems through superior spreadsheet design.
Upgrade your spreadsheet skills with advanced data management techniques.
Consolidate worksheets from different sources into one workbook.
Develop efficient techniques for using charts.
Course Objectives
People credited with this unit standard can:
Use spreadsheet skills for advanced financial applications.
Create, format, and troubleshoot pivot tables.
Analyse data accurately using pivot tables and slicers.
Adopt logical methods to solve complex problems by analysing data accurately using pivot tables Controls over Financial Statements
Efficient working with related worksheets and workbooks by benefitting from new reporting and presentation techniques.
Who should attend?
Accountants
Auditors
Creditors Managers
Accounts Receivable Depts.
Accounts Payable Depts.
Finance
Managers
Operation Managers
Project Managers
HR Managers
Business Executives
Consultants
Head of Departments
Finance people and anyone who uses Excel to analyse and manipulate data and extensively for business reporting.
Training Methodology
Our diverse instructional approaches ensure effective learning:
– Lectures & Presentations: Engage with expert-driven, stimulating content.
– Course Material: Access well-crafted supporting resources.
– Group Work: Collaborate on discussions and case studies for practical insights.
– Workshops & Role-Play: Participate in immersive, scenario-based activities.
– Practical Application: Focus on applying theoretical knowledge in real situations.
– Post-Training Support: Receive extensive support after training for skill implementation.
Training Outline
Unit 1 Filling Data
Understanding Filling
Filling A Series Creating a Custom Fill List
Modifying A Custom Fill List
Deleting A Custom Fill List
Unit 2 Formula Referencing
Absolute and Relative Referencing
Relative Formulas
Problems With Relative Formulas
Creating Absolute References
Creating Mixed References
Other basic formulas
Unit 3 Creating Name Range
Creating Names for the range
Using Names in New Formulas
Creating Names Using the Name Box
Using Names to Select Ranges
Pasting Names into Formulas
Creating Names from A Selection
Using The Name Manager
Unit 4 Logical Functions
Understanding Logical Function
Using IF To Display Text
Using IF To Calculate Values
Nesting IF Functions
Using IFERROR
Using AND
Using OR
Vlookup function
Multiple Vlookup
Unit 5 Conditional Formatting
Formatting Cells Containing Values
Clearing Conditional Formatting
More Cell Formatting Options
Top & Bottom Ten Items
Working With Data Bars
Working With Colour Scales
Working With Icon Sets
Sparkline (New feature)
Creating Sparklines
Editing Sparklines
Unit 6 Advanced Validation
Creating drop down in cells
Restriction values from list only
Creating error message
Creating dependent list
Unit 7 Formulas
Count, CountA, CountIF & CountBlank
Sum, SumIF & SumIFs
Network days
Network days International (For ver 2010)
Today & Now function
Trim (Removing unwanted spaces)
Concatenate (Combining columns)
Unit 8 Dynamic table (new features)
Converting data into table
Automation calculation in table
Converting table to normal range
Using table in Charts & VLOOKUP
Unit 9 Sorting Data
Understanding Lists
Performing An Alphabetical Sort
Performing A Numerical Sort
Sorting On More Than One Column
Sorting By Rows
Working with subtotal
Paste special.
Hyperlink
Unit 10 Filtering Data
Understanding Filtering
Applying And Using a Filter
Clearing A Filter
Creating Compound Filters
Multiple Value Filters
Creating Custom Filters
Using Wildcards
Unit 11 Creating Charts
Choosing The Chart Type
Creating A New Chart
Working With an Embedded Chart
Resizing A Chart
Dragging A Chart
Changing The Chart Type
Unit 12 Charting Techniques
Adding A Chart Title
Adding Axes Titles
Positioning The Legend
Showing Data Labels
Showing A Data Table
Modifying The Axes
Showing Gridlines
Adding a Trendline
Adding a Textbox to a Chart
Creating combination chart
Creating 2 Axis chart
Unit 13 Pivot Tables
Understanding Pivot Tables
Creating A PivotTable Shell
Dropping Fields into A PivotTable
Filtering A PivotTable Report
Clearing A Report Filter
Calculation in pivot
Formatting A PivotTable Report
Dynamic range for pivot
Understanding Slicers (New Feature)
Creating Slicers (New Feature)
Unit 14 Macros
Recording & Running Macros
Creating button to run the macro.
Assigning shortcut to macro
Deleting Macros
Relative reference in Macros
Unit 15 File/Data Security
Open & Modify file password.
Sheet protection
Protecting your sheet from deletion
Protecting few cells, rows, or cols
Protecting your data from copying
Related Courses
A Complete Introduction To Power BI Desktop
View Course
