Introduction
Take your skills to the next level with the Advanced Excel course. Become a master in Pivot Tables. Learn the basics of Power Query and see how you can transform and combine data from multiple sources using the UI tools. Automate MIS and reports. Learn to use Power Pivot and write basic measures in DAX.
The course uses Problem-Based Learning (PBL), including introductions and real-world exercises in each section for active participation. If you have mastered the basics of Excel and are looking for more, this course is for you.
Course Content
What is an Excel Table? – Formatting Tables – Column Names - Table Names – Structured References in Tables – Syntax and Usage.
Creating your first Pivot Table – Rows and Columns in a Pivot Table – Filters – Grouping Data - Field Settings – Formatting a Pivot Table – Calculated Fields – Calculated Items – Slicers – Timeline – Pivot Charts
Get data from external sources (Excel files, Text and HTML files, PDF Files, Databases) – The Power Query Editor – Basic Transformations – Data Types - Applied steps and M Code – Loading Destination – Refreshing Data – Managing Errors.
Removing Columns – Removing or keeping Rows – Splitting Columns – Grouping and Bucketing Data – Replacing Values – Filling Blank Rows – Pivot or Unpivot Columns – Format Columns (Trim, Clean, Capitalize, Prefix, Suffix) – Merge Columns – Extract text from Columns – Number and Date Transformations – Locale when importing dates – Adding Columns.
Combine data from multiple workbooks – Combine all Excel files in a folder – Consolidate data from multiple sheets. Matching and joining two tables (VLOOKUP on steroids!)
Create a Power Pivot Table – Create and Manage Relationships - Introduction to Data Analysis Expressions (DAX) – Calculated Columns – Measures – KPI – Filters – Common DAX functions.