Data Analytics with Excel

·      Learning about relative and absolute referencing

·      The concept of relative formulae

·      The issues in relative formulae

·      Creating of absolute and mixed references

·      Practical Exercise

·      Creating names range

·      Using names in new formulae

·      Working with the name box

·      Selecting range

·      Names from a selection

·      Pasting names in formulae

·      Selecting names

·      Working with Name Manager

·      Practical Exercise

·      The various logical functions in Excel

·      The If function for calculating values and displaying text

·      Nested If functions

·      VLookUp and IFError functions

·      Practical Exercise

·      Learning about conditional formatting

·      The options for formatting cells

·      Various operations with icon sets

·      Data bars and color scales

·      Creating and modifying sparklines

·      Practical Exercise

·      Multi-level drop down validation

·      Restricting value from list only

·      Learning about error messages and cell drop down

·      Practical Exercise

·      Introduction to the various formulae in Excel

·      Sum, SumIF & SumIFs

·      Count, CountA, CountIF and CountBlank

·      Networkdays, Networkdays International

·      Today & Now function

·      Trim (Eliminating undesirable spaces)

·      Concatenate (Consolidating columns)

·      Practical Exercise

·      Introduction to dynamic table in Excel

·      Data conversion

·      Table conversion

·      Tables for charts

·      VLOOKUP

·      Practical Exercise

·      Sorting in Excel

·      Various types of sorting

·      Alphabetical

·      Numerical

·      Row

·      Multiple column

·      Working with paste special

·      Hyperlinking

·      Using subtotal

·      Practical Exercise

·      The concept of data filtering

·      Understanding compound filter and its creation

·      Removing of filter

·      Using custom filter and multiple value filters

·      Working with wildcards

·      Practical Exercise

·      Creation of Charts in Excel

·      Performing operations in embedded chart

·      Modifying

·      Resizing

·      Dragging of chart

·      Practical Exercise

·      Introduction to the various types of charting techniques

·      Creating titles for charts

·      Axes

·      Learning about data labels

·      Displaying data tables

·      Modifying axes

·      Displaying gridlines and inserting trendlines

·      Textbox insertion in a chart

·      Creating a 2-axis chart

·      Creating combination chart

·      Practical Exercise

·      The concept of Pivot tables in Excel

·      Report filtering

·      Shell creation

·      Working with Pivot for calculations

·      Formatting of reports

·      Dynamic range assigning

·      The slicers

·      Creating of slicers

·      Practical Exercise

·      Data and file security in Excel

·      Protecting row, column, and cell

·      Different safeguarding techniques

·      Practical Exercise

·      Learning about VBA macros in Excel

·      Executing macros in Excel

·      The macro shortcuts

·      Applications

·      The concept of relative reference in macros

·      Practical Exercise

·      In-depth understanding of Visual Basic for Applications

·      The VBA Editor

·      Module insertion and deletion

·      Performing action with Sub

·      Ending Sub if condition not met

·      Practical Exercise

·      Learning about the concepts of workbooks & worksheets in Excel Protection of macro codes

·      Range coding

·      Declaring a variable

·      The concept of Pivot Table in VBA

·      Introduction to arrays

·      User forms

·      Getting to know how to work with databases within Excel

·      Practical Exercise

·      Learning how the If condition works

·      How to apply it in various scenarios

·      Working with multiple Ifs in Macro

·      Practical Exercise

·      Understanding the concept of looping

·      Deploying looping in VBA Macros

·      Practical Exercise

·      Studying about debugging in VBA

·      The various steps of debugging

·      Understanding breakpoints and way to mark it

·      The code for debugging and code commenting

·      Practical Exercise

·      The concept of message box in VBA

·      Learning to create the message box

·      Various types of message boxes

·      The IF condition as related to message boxes

·      Practical Exercise

·      Mastering the various tasks and functions using VBA

·      Understanding data separation

·      Auto filtering

·      Formatting of report

·      Combining multiple sheets into one

·      Merging multiple files together

·      Practical Exercise

·      Introduction to powerful data visualization with Excel Dashboard

·      Loading the data

·      Managing data and linking the data to tables and charts

·      Creating Reports using dashboard features

·      Practical Exercise

·      Learning to create charts in Excel

·      The various charts available

·      The steps to successfully build a chart

·      Personalization of charts

·      Formatting and updating features

·      Various special charts for Excel dashboards

·      Understanding how to choose the right chart for the right data

·      Practical Exercise

·      Creation of Pivot Tables in Excel

·      Learning to change the Pivot Table layout

·      Generating Reports

·      The methodology of grouping and ungrouping of data

·      Practical Exercise

·      Learning to create Dashboards

·      The various rules to follow while creating Dashboards

·      Creation of dynamic dashboards

·      Knowing what is data layout

·      Introduction to thermometer chart and its creation

·      How to use alerts in the Dashboard setup

·      Practical Exercise

·      How to insert a Scroll bar to a data window

·      Concept of Option buttons in a chart

·      Use of combo box drop-down

·      List box control Usage

·      How to use Checkbox Control

·      Practical Exercise

·      Understanding data quality issues in Excel

·      Linking of data

·      Consolidating and merging data

·      Working with dashboards for Excel Pivot Tables

·      Practical Exercise