MICROSOFT EXCEL WORKSHOP – ESSENTIAL EXCEL SKILLS
Lot 1-15A Level 1
Wangsa Walk Mall, Wangsa Avenue 9, Jalan Wangsa Perdana 1, Wangsa Maju, 53300 Kuala Lumpur
Workshop Session: 10.00am – 6.00pm
This is an instructor-led 2-days training program which puts you through important Excel formulas, Pivot Table and Excel’s new tool Power Query. You will be guided hands-on unto scenarios to apply important Excel functions where these functions work best; and where their weaknesses are. This is done so that you would know what to use in your work situation.
Learn the real-world usage and connection between Power Query and the Pivot Table; and how Power Query could replace conventional Excel formulas to extract, manage and shape BIG DATA. You will learn the proper way to create the Pivot Table and maintain the integrity and robustness of the table.
OBJECTIVES of the training
This course will help you understand essential MS-Excel functions, when to use them and when not to use them in certain scenarios. The training is rigorous on avoiding formula mistakes and increasing the value-adding the time spent when you use Excel. While doing that, also learn how format your data conventionally and in a remarkable manner.
Deep-dive into Pivot Table and get introduced to Power Query; experience the interactivity between both tools, use Power Query as a VLOOKUP replacement and edit complex data easily.
WHO should attend?
Data analysts, project managers, revenue managers, finance managers, data crunchers who use Excel regularly and wish to have an in-depth dive on critical and essential Excel functions and formulas.
The aim is learning and applying the correct tools and functions efficiently for an increased work performance.
1.Look up Functions
- VLOOKUP Function
- Match types-looking up an exact match & looking up a range
- INDEX, MATCH Functions
- IF Function
- SUMIF & SUMIFS Functions
- How these Functions work and their limitations
- Customizing the Status Bar & using Auto Calculate
4.Counting & Text Functions
- COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS Functions
- LEFT, RIGHT, MID, TEXT Functions
5.Introduction to Power Query
- What is Power Query?
- Connecting to data source & importing data
- Creating a new query
- Merging & appending queries Changing data types, data format & replacing values
- Using Power Query to replace
- the VLOOKUP
6.Grouping and Outlining
- Manual Grouping, Auto Outlining SUBTOTAL, AGGREGATE Functions
7.Database and Autofilters
- What is an Excel Database?
- Database do’s & don’ts
- Creating a Table. Manipulating Tables What is AutoFilter?
8.Excel Database Functions
- DSUM Function
- DSUM vs SUMIF & SUMIFS Function
- What is Conditional Formatting? Conditional Formatting Rules, Data Bars, ColorScales and Icon Sets
- Creating and Managing Rules Sparklines(Excel 2010 and above only). Advance Formatting
- Pivot Table basics
- Building Effective Pivot Tables
- Using Pivot Table Filters
- Sorting, Formatting Pivot Tables Calculations and Inserting Formulas
- Pivot Table charts
- Creating Pivot Table from external sources Grouping Function in Pivot Tables
- Slicers & Timelines