Advanced Microsoft Excel
Skill Level
Advanced
Duration
18 Hours
Language
English
Overview
This course is tailored for those faced with the task of analyzing large, and usually unstructured data sets to provide useful insights for decision-making. It is for experienced Excel users tired of VLOOKUP shortcomings while consolidating data and looking for flexible methodologies to analyze, visualize, and present reports. Managers and Executives who manage, analyze, and prepare reports for managerial discussions will find this course most beneficial.
Completing this course will help you with:
- Data cleansing techniques.
- Prepping your data for analysis.
- Data mining and analysis techniques.
- Visual presentation of data (charts and pre built conditional formatting).
Course Prerequisites
The learner should be an experienced Excel user and MUST be well conversant with our Intermediate Microsoft Excel concepts covered in the previous module.
Learning Path
- Avoid typing a column index number in VLOOKUP by combining VLOOKUP + MATCH functions.
- Say goodbye to VLOOKUP and hello to INDEX + MATCH for two-way (matrix) lookups, as well as a right-to-left lookup.
- Applying logical functions (IF…AND/OR) to make logical business decisions and segment data.
- Using Text functions (LEFT, RIGHT, TRIM, CONCATENATE, LEN, FIND, PROPER, UPPER, LOWER) to return the number of specified characters from a given text or join two/more text strings; Text to column feature to parse data.
- Nested IF function for data segmentation.
- Retrieving and reconciling data made easier by use of VLOOKUP.
- 3D Consolidation or Data Consolidation by position.
- Working with INDIRECT to consolidate data from multiple
worksheets. - Conditional aggregation with multiple criteria using SUMIFS, COUNTIFS & AVERAGEIFS.
- Summarizing data using PivotTables.
- Interactive filters using Slicers and Timelines:
ᵒ Change the calculation & display options e.g., show as %, running totals, YoY%, etc.
ᵒ Group dates into months and quarters with a single mouse click.
ᵒ Show top views, i.e. top/bottom performing products/sales agents/customers.
ᵒ Build a simple but interactive dashboard using pivots. - Sensitivity (“What if”) analysis on models using Data Tables, Goal
Seek.
- Customized Actual versus Budgets charts.
- Special charts focus on waterfall chart.
- Make reports more insightful with formula driven conditional formatting:
- Track performance towards a goal (green for good, amber for
average and red for poor). - Highlight entire row based on a single value.
- Highlight upcoming due dates or overdue bills/premiums.
- Track performance towards a goal (green for good, amber for
- Sparklines to show trends.
What people are saying
“I was looking for a Social Media Course to expand my career horizon. A friend of mine suggested to learn from Steve’s online courses and it really worked for me.”
“A fantastic and very engaging course. All of the information offered by Steve was completely practical, relevant and presented in an easy, digestible and interesting way.”
“This course was a breakthrough in my knowledge of digital marketing. The content is comprehensive and of great quality, engaging and interactive.”
Previous
Next