Advanced Excel (Online)

Advanced Excel (Online)

About

Data Analysis & MIS Reporting , Statistical Analysis, Data Science for Business, Predictive Analysis, Modelling in Financial through Excel, Forecasting, Decision Analysis, Human Resource Management, Dashboard and Reporting, Data Visualization, Report Automation. Basic Quiz, Practice Set Exercise & Assignments, Hand-on Experience, Assessment Test, Industry Specific Projects

Faculty Profile

• 15+ Years of Industry Experience in BPO, KPO, Data Analysis and Research Field
• Specialized in Report Automation, Business Dashboard, MIS Reports, Visual Analytics, Trend Analysis and Forecasting
• Conducted Over 150+ Training Sessions at Various Colleges and Corporate

Curriculum

Basic Overview

  • Navigating & Exploring Excel Interface
  • Data Types, Number Formatting, Operators, Basic Calculation
  • Editing and Formatting Data, Creating Hyperlink, Basic Keyboard Shortcuts
  • Page Setup, Page Break Preview
  • Data Sorting & Data Filter
  • Freezing Panes, Hiding Gridlines
  • Auto Fill, Flash Fill, Paste Special, Cell Reference

Formula & Functions

  • NAME MANAGER, EXCEL TABLE
  • TEXT FUNCTION : LEN, LEFT, RIGHT, SEARCH, FIND, SUBSTITUTE.
  • DATE AND TIME FUNCTION : NOW, TODAY, DAY, MONTH, YEAR, DATE, HOUR, MINUTE, WEEDAY, EDATE, NETWORKDAYS, EOMONTH, WORKDAY
  • MATHEMATICAL FUNCTION : ABS, EXP, LN, LOG, RANBETWEEN, ROUND, SQRT, SUMIF, SUMSQ
  • STATISTICAL FUNCTIONS: COUNTIF, DEVSQ, FREQUENCY, INTERCEPT, SLOPE, KURT, SKEW, STDEV
  • LOGICAL FUNCTIONS: AND, OR, NOT (COMBINING IF WITH AND, OR, NOT)
  • CONDITIONAL FUNCTIONS: IF (ADVANCED IF, NESTED IFS)
  • FINANCIAL FUNCTIONS: DB, SLN, PMT, PPMT, IPMT, CUMIPMT, CUMPRINC
  • LOOKUP FUNCTION : VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET
  • Hand-On Experience (Loan Repayment Worksheets, Bank Reconciliation Statements, Frequency Distribution, Creating Depreciation Schedules)

Data Managing & Analysis

  • Worksheet and Workbook Custom Protection
  • Data Parsing, Data Consolidation, Removing Duplicates and Data Outline
  • Advance Filter
  • Pivot Table (Grouping Items, Calculated Field, Pivot Chart)
  • Hand-On Experience (Attendance Report, Gantt Chart, Movie Database)

Data Validation & Visualization

  • Chart and Graphs, Sparklines
  • Conditional Formatting (Data Bars, Color Scale, Icons)
  • Data Validation (Drop Down List, Custom Formula)
  • Find and Select Tool
  • DASHBOARDS - Introduction to Dashboards, Introduction to Slicers, Interactive Dashboards with slicers and Timeline, Data Visualization Techniques.
  • Hand-On Experience (Attendance Report, Performance Report, Gantt Chart, Movie Database)
  • Hands-On Experience: Creating – Call Center Dashboard & Interactive Dashboards

Advance Analytics tools and Decision Analysis

  • What If Analysis (Scenario Manager, Goal Seek, Data Table)
  • Excel Addin : Data Analytics ToolPak (Descriptive Statistics, Sampling, Histogram , Rank and Percentile, Correlation, Moving Average, Exponential Smoothing, Regression)
  • Excel Addin – Solver ( Linear Programming Problems, Decision Analysis)
  • Case Studies : Forecast, Diet Cost Optimization, Investment Decision, Production Decision

Task Automation through VBA & Macro

  • Overview VB, VBA, VBE and Macro
  • Macro Security
  • Macro Recorder
  • VBA Events
  • Task Automation
  • Creating Basic Calculator
  • Creating Custom Function
  • VBA Coding Tips
  • Dealing with Error