Advanced Excel with Practical Business Case Studies

Contents of the Training:

USE $ SIGN IN FORMULA

• Concept of a formula in Excel
• Lock rows in a formula
• Lock columns in a formula
• Lock both rows and columns in a formula

LOOKUP & REFERENCES

• General VLOOKUP & HLOOKUP function
• Complex uses of VLOOKUP/HLOOKUP
• MATCH function and INDEX function
• Combination of MATCH and INDEX function
• OFFSET function
• Various practical business case study for data matching

DATA CLEANING AND PROCESSING:

• Functions for data extraction from a cell value
• Functions for data re-shaping from the cell value
• Use the Excel’s built-in commands for data cleaning
• Data organizing using IF() and IFS() conditions
• Various auto-filling techniques on different data types

REPORTING TECHNIQUES USING PIVOTTABLE, PIVOTCHART, SLICER AND TIMELINE

• Describing the New features in Excel 2019 PivotTable
• Data mapping to prepare a PivotTable
• Various types of report layout-report in compact form, report in tabular form
• Customizing subtotal at any row label data and subtotal category
• Changing PivotTable data source
• Inserting a calculated field and calculated item
• Drill-down to the Pivot data
• Grouping/ungrouping data by year, quarter, month, day
• Sorting and filtering techniques in PivotTable data
• Slicer in PivotTable-creating, changing and formatting
• Make your PivotTable dynamic using slicer
• Creating PivotChart from existing workbook data
• Automated dashboard reporting techniques using PivotTable

POWERPIVOT – USE THE MOST POWERFUL FEATURE IN EXCEL (BUILT-IN BI TOOL)

• What is PowerPivot?
• Activation PowerPivot in MS Excel-2010, 2013, 2016 and 2019
• Clear concept about Excel table
• Building relationships among the tables
• Developing Data Model from different data sources
• Creating report using PowerPivot linking with Excel
• Creating report using PowerPivot linking with other external sources (from Access/SQL Server)
• Inserting a calculated column using Data Analysis Expression (DAX)
• Techniques to use fields and slicers from PowerPivot Field List

POWERQUERY – DATA IMPORTING AND PROCESSING FROM VARIOUS SOURCES

• Combine data from multiple files to one file using PowerQuery
• Combine data from multiple sheets to one sheet using PowerQuery
• Download data from website
• Import data from another database software
• Converting text data to Excel data

DATA SUMMARIZATION & AGGREGATION

• Data summarizing from a large dataset using functions
• COUNTIFS, SUMIFS and AVERAGEIFS functions
• SUMPRODUCT function
• Use these functions with multiple criteria and conditions

ERRORS & ERROR HANDLING

• Concepts of Circular Reference error
• Recognizing various types of Errors
• Error handling techniques
• Use of calculation options for managing big data set
• Formula tracing and debugging

CREATING ADVANCED CHARTS AND VISUAL PRESENTATIONS

• New charts in Excel 2019
• Data visualization using Sparklines
• Choosing the perfect chart for your data
• Creating various types of Charts-Column, Bar, Pie, Line etc.
• Customize your chart’s labels, axes and background
• Chart for target-achievement analysis
• Chart for Main group-sub group analysis
• Waterfall chart and funnel chart
• Geographical Map chart

MACRO & VBA: AUTOMATE YOUR EXCEL

• Overview of macro in Excel step by step
• Building macro without having any programming knowledge
• Macro settings and security
• Trust center and trust center settings
• Automation techniques using macro
• Creating macro to generate report using criteria
• Running the recorded macro using shortcut key
• Running the recorded macro using button or objects
• VBA script editing techniques
• Macro Project-1
• Macro Project-2

Note:

• All participants will get a digital certificate from Thriving Skills Limited.
• Must set up your first name, last name, and display name from settings of your profile for your certificate.
• Don’t click on the “FINISH COURSE” button.
• If you click on the “FINISH COURSE” button then the webinar will be finished and a Certificate will be generated.

Instructor of this Training:

Nazmul Muneer

Courses of this Instructor:

1. Microsoft Excel – Advanced Level
2. Most Common Features of Excel for Professionals
3. Microsoft Excel – VBA & Macro
4. Microsoft Excel VBA & Macro Basics
5. Lookup Functions with Practical Business Case Study
6. Excel VBA & Macro
7. Microsoft Excel-Professional Tips & Tricks
8. Most Out of Excel
9. Advanced Automated Dashboard Reporting System Using Excel
10. Microsoft Excel Advanced Data Processing and Reporting Techniques