Excel Skills For Business: Advanced Level

CELL REFERENCE (RELATIVE VS ABSOLUTE)

• Techniques to use $ sign in a formula
• Locking a specific row or column
• Locking both row and column
• Practical examples to use $ sign in a formula

DATA CLEANING AND RESTRUCTURING

• Extracting a specific portion from a cell velue
• Removing unwanted extra spaces
• Converting case in different forms
• Joining multiple cell values with plain text
• Finding the required text using function
• Counting functions
• Rounding functions

LOGICAL FUNCTION WITH OPERATOR

• Understanding the IF() condition
• Using AND(), OR() function in IF() condition
• Nested IF with AND, OR function

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

CONFIGURING DATA VALIDATION: STOP TYPING INVALID DATA

• Restriction incorrect data entry with data validations
• Creating list with static values or a dynamic range
• Set validation rule in a range for accepting only dates, numbers or specific range of values
• Creating error validation messages

FILE SECURITY & PROTECTION: STOP UNAUTHORIZED ACCESS

• Protect workbook so that it can be used by anyone with a limited use of Excel
• Steps for locking and protecting cells
• Create your workbook or worksheet password protected

MACRO & VBA: AUTOMATE YOUR EXCEL ACTION OR REPORT AND SAVE TIME

• 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