Microsoft Excel Advanced Data Processing and Reporting Techniques

Contents of the Training:

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 Excel’s built-in commands for data cleaning
• Data organizing using IF() and IFS() conditions
• Various auto-filling techniques on different data types

DATA IMPORTING FROM VARIOUS SOURCES USING POWER QUERY:

• 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

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

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
• Data summarizing using INDIRECT linking

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 CHART AND VISUAL PRESENTATION

• 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:

• After completing each lesson, you have to click on the “COMPLETE” button then go to the next lesson.
• All participants will get a digital certificate from Thriving Skills Limited.
• Must fill up your first name and last name then display name 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, you will unable to complete the next lessons or parts of this webinar and a Certificate will be generated.

Webinar Instructor:

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