TRAINING DETAILS

Training Contents

• Cell Reference (Absolute Vs Relative):
  • Techniques To Use $ Sign In Formula
  • Different Types Of Use Of References (A4, $A4, A$4, $A$4)
  • Managing Cell Reference With Complex Formula In Easy Rule

• Use Essential Functions For Restructuring Usable Data Table
  • Describing The New Functions In Excel 2019
  • Joining Multiple Cells
  • Extracting The Required Portion Of A Cell Value
  • Removing Unwanted Extra Spaces
  • Convert A Cell Text To Your Required Case
  • Converting A Text Number To A Computable Pure Number Or Date
  • Rounding A Cell Value In Different Ways
  • Join A Text With Formatted Number Or Date.
  • If & Ifs() Function

• Lookup & References
  • General Vlookup & Hlookup Function
  • Complex Uses Of Vlookup/Hlookup
  • Match Function And Index Function
  • Combination Of Match And Index Function
    

• 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

• Convert Number In Words Automatically
  • Procedure To Convert A Number Into Words

• 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

• Powerpivot – Use The Most Powerful Feature In Excel (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

• Data Summarization & Aggregation Techniques From Large Data
  • New Summarization Functions In Excel 2019
  • Data Summarizing From A Large Dataset Using Functions
  • Countifs, Sumifs And Averageifs Functions
  • Use These Functions With Multiple Criteria And Conditions
  • Data Summarizing Using Indirect Linking

• Data Sorting & Filtering
  • Multi-Level Data Sorting & Filtering Using Multiple Customizing Conditions
  • Finding Unique Records In A Range And Paste At Another Place
  • Removing Duplicates At The Same Range
  • Customized Filtering Using If () Condition

• Date Functions:
  • Do You Know How Many Mistakes You Are Doing Every Day For Date??
  • Date Formatting Using Formula And Shortcut
  • Presenting Day Name And Month Name Of A Date
  • Combining Date With A Text (Report Date: 31/Dec/2014)
  • Calculating Future Date Adding 3 Years 8 Months And 18 Days
  • Calculating Date Difference Between Two Dates (Difference Of Days, Months And Years)
  • Preparing Age Calculator (Example: Your Age Is 25 Years 05 Months And 18 Days)

• Error Handling And Cleaning Reports
  • Type Of Errors And When It Occurs
  • Circular Reference And Carefulness For Errors!
  • Error Handling Using Iserror, Iferror And Isna Function
  • Magic Tips To Find Error Cells And Cleaning
  • Formula Tracing And Debugging Techniques

• Data Validation And File Protection
  • Protect Your Workbook So That It Can Be Used By Anyone With A Limited Use Of Excel
  • Locking And Protecting Cells
  • Restriction Incorrect Data Entry With Data Validations
  • Creating List With Static Values Or A Dynamic Range
  • Creating Error Validation Messages
  • Create Your Workbook Password Protected

• 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
  • 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
  • Carefulness Of Macros!

• Problem Solving Session
  • Share Your Excel Problem
  • The Facilitator Will Share Some Critical Real Cases

N/A