ADVANCE EXCEL

INTRODUCTION TO EXCEL

  • An Overview of Excel Screen
  • Basic Concept of Spreadsheet and Workbook
  • Creating, Editing Saving and printing)
  • Excel Terminology: - Rows, Column and Cell
  • Working with functions & Formulas
  • Graphically representing data: Charts and Graphs
  • Analyzing Data: Data Menu Subtotal, Filtering Data
  • Difference between Excel 2007, 2010, 2013, 2016 and Office 365, its Boundaries & Features
  • Formatting Worksheets, Securing & Protecting Spreadsheets
  • All Tabs of Microsoft Excel 2013, 2016, Office 365
  • Simple Data Entry Number, Text, Date, Selecting Range
  • Cut, Copy, Paste, Format Printer and Move Data.
  • Saving Excel Format

LOOKUP FUNCTIONS:

  • The LOOKUP function, Date and time functions
  • Annotating formulas, Lookup, V-Lookup,
  • H-Lookup, Match, Index, Usage Of Match Function
  • Hlookup And Index, 2D & 3D Lookup, Mixed Lookup
  • Vlookup with Iferror, Multiple Vlookup, Vlookup with Choose
  • Reverse VLookup, Index with Double Match

DEVELOP THE WORKSHEET:

  • Plan a Worksheet, Row and Column labels
  • Split Worksheet/box/bar, copy data and formulas
  • Display/Move Toolbars, Enhance worksheet appearance
  • Use multiple windows: Copy/Paste between worksheets
  • Link Worksheets, Consolidate worksheets
  • Important link from other Applications
  • Use Auto Format: Create, use and modify styles and templates
  • Print Features: Create/edit an outline

CHART & DESIGNS & Dashboard

  • Preparing Charts Using Excel - Column Chart
  • Pie Chart, Line Chart, Bar Chart
  • Bubble Chart, Area of Chart, Editing Chart Contents
  • Formatting and Modifying Chart, Adding Data to a Chart

BASIC FUNCTIONS:

  • Add, Subtract, Multiplication, Divide, Sum and Count
  • Auto Sum, Formatting a Cell- Number, Text, %
  • Date and Custom Format. Font , Borders, Fill Colors
  • Patterns, Conditional Formatting, Format A Table, Cell Style
  • Use the Function wizard, Common functions
  • AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, INT
  • Text functions, Statistical function
  • Mathematical Function, Financial Function
  • Logical Functions Using IF, AND, OR, NOT
  • Nested functions, Name cells/ranges/constants
  • Relative Absolute, Mixed cell references:><,=operators

LOGICAL & MATHEMATICAL FUNCTIONS:

  • If, Sumif, Sumifs, Countifs, Nested If
  • Usage of More Than 1 If Condition Within 1 Formula
  • Iferror, Iserror, Isna, And, Or, False, Not, True
  • Ceiling, Even, Int, Lcm, Power
  • Odd, Sumif, Sumproduct, Seriessum

USING PIVOT TABLE

  • Preparing Pivot Table
  • Using Pivot Table Wizard
  • Arranging Data
  • Various Options in Pivot Table
  • Effective and Attractive Summarized
  • Presentation of Data Using Pivot Tables
  • Modifying a Pivot Table
  • Pivot Table Option

GRAPHIC OPERATIONS

  • Create Charts, Enhance Charts, Drawing toolbar features
  • Print Preview, Print Setup, Page Layout, Page-break
  • Normal View, Headers & Footer, Print Title

ADVANCE EXCEL FUNCTIONS/FORMULAS:

  • Sum, Count, Counta, Countifs, Subtotal, Average
  • Ceiling, Floor, Round, Roundup, Rounddown,
  • Chart, Text, Clean, Trim, Concatenate, Substitute
  • Hour, Date, Value, Day, Month, Year, Day,
  • Today. Exact, Forecast, Getpivotdata, Sumproduct
  • Frequency, Rank, Rand, Randbetween. Transpose
  • Match, Upper, Lower, Proper
  • Left, Right, Mid, Row, Column, Combine

DESCRIPTION OF EXCEL MENU

  • Paste Special, Paste Formula, Paste Format
  • Skip Blank and Other Paste Special Option
  • Find, Replace and Merge & Center Option, Copying
  • Moving/ Renaming, Inserting/ Deleting/ Grouping
  • Hiding & Un-hiding Worksheets, Hiding and
  • Displaying Data, Rows, Columns, Worksheet & Workbooks

DATA ANALYSIS, ADVANCE OPTIONS:

  • Advance Filter and Sorting Option in Excel
  • Filtering and Sorting By Color
  • Various Option of Text to Column
  • Remove Duplicate Record, Data Validation
  • Protecting and Sharing a Worksheet/Workbook
  • Freezing Pane, Hyperlink
  • Sub Total Reports, Auto Filter, Sheet Referencing
  • What-IF-analysis, GOAL SEEK, Reporting, Consolidation of Data, Data Validation

EXCEL MACRO:

  • Introduction and use
  • Assign a Macro, Run a Macro
  • Store a Micro in Different Worksheets
  • Introducing to VBA Program.
Download Syllabus Here