Microsoft Excel – Advanced


This course is highly interactive, as participants will be challenged to step out of their comfort zones and demonstrate the techniques practically. You will learn how to find a value with VLOOKUP, create and format Pivot Tables, record Macros and more. Designed and delivered by internationally qualified and experienced trainer, the workshop can be tailored in alignment with your business objectives.



Learning Outcome

By the end of the session, participants will be able to:
• Use Excel quick access toolbar and cell styling tools
• Use key board shortcuts to improve speed and efficiency
• Understand logical functions and use them for reports
• Learn and apply various advance excel functions
• Learn to create smart charts for data presentation
• Learn to create pivot table and analyse large data base
• Use Excel formulas for increasing productivity
• Learn to record and edit macros

Course Content

Conditional Formatting
• Formatting Cells Containing Values
• Clearing Conditional Formatting
• Working with Data Bars, Color Scales and Icon Sets
• Creating and editing Sparklines

Sorting Data
• Performing an Alphabetical Sort
• Performing a Numerical Sort
• Sorting on More Than One Column
• Sorting by Vertically & Horizontally

Filtering Data
• Understanding Filters
• Applying, Using and Clearing a Filter
• Creating Compound Filters
• Creating Multiple Value and Custom Filters
• Using Wildcards

Filling Data
• Understanding Filling
• Filling a Series
• Creating, Modifying and Deleting a Custom Fill List

Pivot Tables
• Understanding Pivot Tables
• Creating a Pivot Table Shell
• Filtering a Pivot Table Report
• Clearing a Report Filter
• Formatting a Pivot Table Report
• Understanding and Creating Slicers

Dynamic Table
• Converting data into table
• Automation calculation in table
• Converting table to normal range
• Using table in Charts & Vlookup

Logical Functions
• Understanding Logical Functions
• Using IF to Display Text
• Using IF To Calculate Values
• Nesting IF Functions
• Using IFERROR, AND and OR Functions

Formula Referencing
• Absolute and Relative Referencing
• Problems with Relative Formulas
• Creating Absolute References

Charting Techniques
• Adding Chart and Axes Titles
• Positioning the Legend
• Show Data Labels and Data Table
• Modifying The Axes
• Creating a combination chart
• Creating 2 Axis chart

Creating Charts
• Choosing The Chart Type
• Creating A New Chart
• Working with an Embedded Chart
• Resizing and changing Chart type

Advance Validation
• Creating drop down in cells
• Restricting values from list only
• Creating error messages
• Creating dependent lists

Text options
• Text to Column, SubTotal and Paste Special
• Group / Ungroup
• Sharing workbook

• Recording & Running Macros
• Creating button to run the macro
• Assigning shortcut to macro
• Deleting Macros
• Relative reference in Macros

• Count, CountA, CountIF & CountBlank
• Sum, SumIF & SumIFs
• Networkdays
• Today, Now, Trim and Concatenate functions

Lookup Functions
• Normal Vlookup & Hlookup
• Vlookup with IFError
• Vlookup from Multiple Sheets using IFError
• Using IF Conditions and Multiple criteria in Vlookup

File / Data Security
• Open & Modify file password
• Protect sheet from deletion, data copy or protect few cells, rows or columns