MS Excel Intermediate & Advanced Training by Kent Lau

AmazingExcelWorkshop

Intermediate & Advanced Level

The Microsoft Excel Data Analysis Class is a 2- day hands-on practical training where our Expert Reporting Expert will be teaching you some of the most powerful Excel Advanced function that will improve your productivity, your ability immediately with industrial case study related to finance, HR, Sales, manufacturing, retail.

With over 10 years’ experience in training and consulting, Kent Lau has excellent industrial case study related to HR, Finance, Account, Manufacturing, Operation and etc.

LEARNING OUTCOME:

At the end of this training, you will be able to:
• Analyze data faster and easier than you think
• Understand VLOOKUP function completely
• Understand how to use IF function to perform data analysis.
• How to combine multiple function together to perform data analysis
• Idea that can help you improve your performance in creating monthly report.
• How to protect the report you created.
• How to highlight item that meet certain requirement
• How to use Pivot Table, Pivot Chart, Slicer
• How to use Named Cell, Named Range in complex Excel formula
• How to use INDEX, HLOOKUP, MATCH
• How to create a dropdown list

You will begin to see the usefulness of Excel like never before.

WHO IS SUITABLE:
Senior Level, administrative staff including senior Sales & marketing executive, Finance staff, Senior Account Executive, Personal Assistant, senior IT staff, purchase executive, operation staff, retail staff who job description require them to not only enter data but to analyze data into Excel. Those who play a supportive role in decision making process will find this course useful.
The following is what you can expect to learn:


COURSE OUTLINE

DAY 1 – Intermediate

Topic 1: Relative vs Absolute Cell Reference
• We design easy to understand exercise to help explain what cell address, cell reference, relative reference and absolute cell reference is.
• Exercise to help participant to understand fully understand this key concept.

Topic 2: Advanced functions and formulas
• Logical functions – TRUE, FALSE
• Conditional functions – IF
• Text functions – MID, LEFT, RIGHT, SEARCH
• Date and time functions – DATE, MONTH, YEAR, NOW, TODAY, EDATE
• VLOOKUP, HLOOKUP, MATCH

Topic 3: Pivot Table
• What is Pivot Table
• How to use Pivot Table
• The Pivot Table structure
• Using Pivot Table creatively

Topic 4: Hyperlink
• Create a Table of Content with Hyperlink
• Edit Hyperlink
• Delete Hyperlink
Topic5: Data Protection
• Protecting the Report you have analysed
• Protect the Worksheet

Topic6: Time Saving Shortcut


DAY 2 – Advanced

MODULE 1: ADVANCED PIVOT TABLE
• Summarize data on quarterly or yearly basis or even weekly using Pivot Table
• Creating PivotChart
• Using Slicer
• Changing PivotChart type and Chart element
• Using GetPivotData together with Slicer

MODULE 2: NAMED CELL/RANGE TECHNIQUE
• How to name a cell or a range fast
• Deleting and amending named ranges
• Using named cells/ranges in formula

MODULE 3: ADVANCE FUNCTIONS
Quick revisit: Using the IF function
Nested IF
And, Or
Combining If, And, Or, Not
Using INDEX & MATCH together
Learn how to look up the KPI easier with INDEX.
Using OFFSET to create dynamic chart
Understand COUNTIF and SUMIF easily.
How to highlight the item(s) is going to expire.

MODULE 4: USING EXCEL TABLE
How to name a Excel Table
Modify it
How to write formula in Excel Table
Using Advance Filter

MODULE 5: HIGHLIGHTING ITEMS THAT MEET A CONDITION AUTOMATICALLY
You will learn to highlight those members that are late payment automatically.
Highlighting items that meet multiple conditions automatically

MODULE 6: DATA VALIDATION
Learn to create a dropdown list to help user select a value easier.
How to configure a dropdown list better.

MODULE 7: MACRO
Record macro, edit the macro, write a basic macro.

Note: The organizer reserves the right to alter the content and timing of the programme in the best interest of the Seminar/workshop.