ADVANCED EXCEL COURSES – THE KEY TOOL TO MANAGE ALL REPORTS

Administrator Expert

Description

आरंभ Mastering Excel program is intended to make you Excel champion by acquiring the fundamentals of Excel spreadsheets to perform basic data analysis.

Core & Advanced Excel with Dashboard

MS Excel from beginner to Advance excel, Formulas, Chart tips and Tricks in MS Office and Office 365.

This course is designed to teach you the basics of Microsoft Excel all the way to Excel mastery. It’s perfect for you if you are a beginner or intermediate Excel user who needs to drastically increase your excel skills to impress your boss, get a raise or even land a new job. Everything in the course is taught step-by-step in an easy, stress-free way. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for your big finale, where you will learn how to build professional dashboards in Excel.

What you will learn:

  • Build a solid understanding on the Basics of Microsoft Excel
  • Master Microsoft Excel from Beginner to Advanced
  • Build financial models from scratch (shown step-by-step)
  • Format sheets and charts professionally
  • Learn new keyboard shortcuts and be even faster in Excel
  • Use Cell Styles and format spreadsheets easier and faster
  • Writing Formulas and performing Calculations
  • Create charts with Trend Lines, and Dual Axis
  • Use Excel as a Database with Sort and Filter options
  • How to avoid the most common Database Design flaws
  • Increase productivity with functions: IF, PMT, VLOOKUP, FIND, SEARCH, MATCH, INDEX and much more…
  • Updated to include the new FILTER, XLOOKUP, SORT & UNIQUE functions
  • Learn several hidden Excel tips and tricks
  • Learn to use Pivot Tables, Pivot Charts, Slicers and Time Lines for Interactive Excel Reports.
  • Learn to make dashboards.

Download Brochure

Topics for this course

128 Lessons59h 59m 59s

Training Duration Regular: 3 Months

EXCEL INTRODUCTION?

EXCEL INTRODUCTION
  • What is Excel?
  • Excel Interface
  • An overview of the screen, navigation and basic
  • spreadsheet concepts
  • Various selection techniques
  • Shortcut Keys

CUSTOMIZING EXCEL?

CUSTOMIZING EXCEL
  • Customizing the Toolbar
  • Customizing the Ribbon
  • Custom List
  • Changing Excel's Default Options

FORMATTING AND PROOFING?

FORMATTING AND PROOFING
  • Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
  • Freeze Panes, Conditional formatting

ADVANCED PASTE SPECIAL TECHNIQUES?

ADVANCED PASTE SPECIAL TECHNIQUES
  • Paste Formats, Values, Formulas, Comment, Validations
  • Operation – Sum, Multiple, Subtract, Divide
  • Transpose Tables

FUNCTIONS?

FUNCTIONS
  • Database Functions
  • Information Functions
  • Financial Functions
  • Lookup & Reference Functions

FORMULA REFRENCE?

FORMULA REFRENCE
  • Relative
  • Absolute
  • Mixed

EXCEL BUILT IN FUNCTIONS?

EXCEL BUILT IN FUNCTIONS
  • Logical Functions
  • Math Functions
  • Text Functions
  • Date and Time Functions
  • Statistical Functions
  • Database Functions
  • Information Functions
  • Financial Functions
  • Lookup & Reference Functions

WORKGROUP COLLABORATIONS?

WORKGROUP COLLABORATIONS
  • Locking /Unlocking cells in a worksheet
  • Protecting a worksheet
  • Protecting a workbook
  • Creating a shared workbook
  • Tracking changes to a workbook
  • Accepting & protecting a workbook
  • Merging workbooks

PRINTING WORKBOOKS?

PRINTING WORKBOOKS
  • Setting Up Print Area
  • Customizing Headers & Footers
  • Designing the structure of a template
  • Print Titles –Repeat Rows / Columns

FORMULA TOOLS?

FORMULA TOOLS
  • Tracing formula precedents
  • Tracing cell dependents
  • Error checking

SORTING AND FILTERING?

SORTING AND FILTERING
  • Filtering on Text, Numbers & Colors
  • Sorting Options
  • Advanced Filters on 15-20 different criteria(s)

USING RANGES?

USING RANGES
  • Ranges in Ms Excel
  • Naming Range

WORKING WITH THE WEB AND EXTERNAL DATA?

WORKING WITH THE WEB AND EXTERNAL DATA
  • Inserting a Hyperlink
  • Importing Data from an Access Database or Text File
  • mporting Data from the web and other sources
  • Working with Existing Data Connections

SUMMARIZING DATA?

SUMMARIZING DATA
  • Adding subtotals to a list
  • Nesting subtotals
  • Text to column
  • DATA VALIDATION
    • Number, Date & Time Validation
    • Text and List Validation
    • Custom validations based on formula for a cell
    • Dynamic Dropdown List Creation using Data Validation
    • USING MACROS
      • Macro options
      • Creating Macros
      • Editing and Deleting Macros
      • Use Relative References
      • DATA ANYLYSIS TOOL
        • Goal Seek
        • Scenario Manager
        • Data Tables (PMT Function)
        • Solver Tool
        • PIVOT TABLES
          • Introduction to PivotTables
          • Classic Pivot Table
          • How to automatically update PivotTables
          • Text Filters-how text filters are used to filter a PivotTable based on text
          • .
          • Grouping and Pivot Charts
          • Value Field Settings and Show Values As
          • Creating Calculated Fields and Calculated Items
          • Using Slicers, Filters, and Timelines to quickly analyse data GETPIVOTDATA Function
          • Creating Pivot Tables from Multiple Ranges
          • Multiple Pivot Tables -how to create many PivotTables based on a Filter
          • .
          • Data Model
          • Using Multiple Slicers with Pivot Tables
          • Conditional Formatting and PivotTables
          • POWER PIVOT, DATA ANALYSIS & VISUALIZATION
            • Power Pivot
            • Data Analysis Using Statistics, Analysis Tool pack
            • CHARTS AND SLICERS
              • Creating a chart with the 2D or 3D
              • Create column chart, pie chart
              • Various Charts i.e. Bar Charts / Pie Charts / Line Charts
              • Create speed – o- meter chart
              • Create Progress Chart
              • Moving a chart one to another sheet
              • Formatting category & value Axis data
              • Formatting a data series
              • Changing a charts source data
              • Using SLICERS, Filter data with Slicers
              • ARRAY FUNCTIONS
                • What are the Array Formulas, Use of the Array Formulas?
                • Basic Examples of Arrays (Using ctrl+shift+enter)
                • .
                • Array with if, len and mid functions formulas
                • .
                • Array with Lookup functions
                • .
                • Advanced Use of formulas with Array

DATA VALIDATION?

DATA VALIDATION
  • Number, Date & Time Validation
  • Text and List Validation
  • Custom validations based on formula for a cell
  • Dynamic Dropdown List Creation using Data Validation

USING MACROS?

USING MACROS
  • Macro options
  • Creating Macros
  • Editing and Deleting Macros
  • Use Relative References

DATA ANYLYSIS TOOL?

DATA ANYLYSIS TOOL
  • Goal Seek
  • Scenario Manager
  • Data Tables (PMT Function)
  • Solver Tool

PIVOT TABLES?

PIVOT TABLES
  • Introduction to PivotTables
  • Classic Pivot Table
  • How to automatically update PivotTables
  • Text Filters-how text filters are used to filter a PivotTable based on text
  • .
  • Grouping and Pivot Charts
  • Value Field Settings and Show Values As
  • Creating Calculated Fields and Calculated Items
  • Using Slicers, Filters, and Timelines to quickly analyse data GETPIVOTDATA Function
  • Creating Pivot Tables from Multiple Ranges
  • Multiple Pivot Tables -how to create many PivotTables based on a Filter
  • .
  • Data Model
  • Using Multiple Slicers with Pivot Tables
  • Conditional Formatting and PivotTables

POWER PIVOT, DATA ANALYSIS & VISUALIZATION?

POWER PIVOT, DATA ANALYSIS & VISUALIZATION
  • Power Pivot
  • Data Analysis Using Statistics, Analysis Tool pack

CHARTS AND SLICERS?

CHARTS AND SLICERS
  • Creating a chart with the 2D or 3D
  • Create column chart, pie chart
  • Various Charts i.e. Bar Charts / Pie Charts / Line Charts
  • Create speed – o- meter chart
  • Create Progress Chart
  • Moving a chart one to another sheet
  • Formatting category & value Axis data
  • Formatting a data series
  • Changing a charts source data
  • Using SLICERS, Filter data with Slicers

ARRAY FUNCTIONS?

ARRAY FUNCTIONS
  • What are the Array Formulas, Use of the Array Formulas?
  • Basic Examples of Arrays (Using ctrl+shift+enter)
  • .
  • Array with if, len and mid functions formulas
  • .
  • Array with Lookup functions
  • .
  • Advanced Use of formulas with Array

LOOKUP FUNCTIONS?

LOOKUP FUNCTIONS
  • Vlookup
  • Nested VLookup
  • HLookup
  • Lookup
  • Row, Rows, Column, Columns
  • Index and Match
  • Reverse Lookup using Choose Function
  • Worksheet linking using Indirect
  • Vlookup with Helper Column
  • 3D Lookups
  • Duplicate Vlookup
  • Partial & Duplicate Vlookups
  • Dynamic Lists
  • Address
  • Offset
  • Smart Lookup
  • Mastering Vlookup – 50+ Examples Rehearsal

EXCEL DASHBOARD?

EXCEL DASHBOARD
  • Planning a Dashboard
  • Adding Tables and Charts to Dashboard
  • Link with one sheet to another worksheets and workbook
  • Interactive Dashboard with Form Controls
  • Form Controls for reports automation

NEW FUNCTION IN EXCEL 2013, 2016 & OFFICE 365?

NEW FUNCTION IN EXCEL 2013, 2016 & OFFICE 365
  • Flash Fill – Auto complete a data range and List
  • New Charts-Tree Map, Waterfall, Sunburst, Forecast Sheet
  • Sparklines- - Line, Column & Win/Loss
  • Using 3-D Map
  • Various Time Lines and New controls in Pivot Tables
  • Quick Analysis Tool
  • SWITCH, XOR, XLOOKUP, XMATCH, SORT, SORT BY, SEQUENCE, FILTER, RANDARRAY, UNIQUE, LET etc

ADVANCED GOOGLE SHEETS

4,999.00
  • Course level: Expert
  • Duration 59h 59m 59s