HOME > LTCL > Training > Online Course: Microsoft Excel – Advanced Functions & Features

Online Course: Microsoft Excel – Advanced Functions & Features

Last Date of Registration: 2 June, 2022
Start Date: 3 June, 2022
Time: Friday &  Saturday (07:30 PM – 10:30 PM)
Duration: 06 Hours.
Course Fee: 1,500 TK
Contact: 01811448063, 01811449483, 01847179477

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


Start Course