TRAINING CONTENT:

LOOKUP & REFERENCES

• Practical uses of VLOOKUP/HLOOKUP
• MATCH function and INDEX function
• Combination of MATCH and INDEX function
• OFFSET function
• The XLOOKUP Function – The most cool function in Excel

ADVANCED CONDITIONAL FORMATTING

• Using multiple conditional formatting in a range
• Using Data Bars, color scales and icon set for great presentation
• Advanced formula based conditional formatting
• Different aging monitoring reporting techniques

AUTOMATED REPORTING USING PIVOTTABLES, PIVOTCHART, SLICERS AND TIMELINES

• Describing the New features in Excel 2019 PivotTable
• Data mapping to prepare a PivotTable
• Customizing subtotal
• Adding calculated fields and calculated items
• Drill-down to the Pivot data
• Grouping/ungrouping data
• Slicer – creating, changing and formatting in PivotTable
• PivotChart – creating, changing and formatting in PivotTable
• Timeline – creating changing and formatting in PivotTable
• Automated dashboard reporting techniques using PivotTable

DATA SUMMARIZATION & AGGREGATION TECHNIQUES FROM LARGE DATA SET

• Data summarizing setting condition on columns
• Data summarizing setting condition on rows
• Data summarizing setting condition on both rows and columns
• Different advanced tricks for summarizing reports

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

• Activation PowerPivot in MS Excel-2010, 2013, 2016, 2019 and Microsoft 365
• Building relationships among the tables
• Developing Data Model from different data sources
• Creating report using PowerPivot linking with Excel
• Data linking with other external sources (from Access/SQL Server..)
• Using Data Analysis Expression (DAX) in PowerPivot

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 the chart elements professionally
• Chart for target-achievement analysis
• Chart for Main group-sub group analysis
• Waterfall chart
• KPI presentation techniques

MACRO & VBA: LET EXCEL DO YOUR TASK BY AUTOMATION

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

POWERQUERY: KNOW THE CAPABILITY OF EXCEL TO MANIPULATE DATA

• Data consolidation from multiple files to one file
• Data consolidation from multiple sheets to one sheet