Excel Skills for Business: Advanced Level

Learn the most advanced formulas, functions and types of financial analysis to be an Excel power user.

This training aims to provide the simplest and effective techniques of the powerful excel features including PivotTables, PowerPivot, Sparkline, Goal Seek, Conditional Formatting, Report Automation and Subtotals! Get more things done faster with customized workbooks, the Quick Access Toolbar, and keyboard shortcuts; control and analyze data more effectively and communicate more visually with new charting tools, SmartArt and plenty more!

Outline

LOOKUP & REFERENCES

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

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
• Aging monitoring using conditional formatting

REPORTING TECHNIQUES USING PIVOTTABLES, PIVOTCHART AND SLICERS

• 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 TECHNIQUES FROM LARGE DATA

• 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

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

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 ACTION OR REPORT AND SAVE TIME (02 Hours)

• 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