MS Office Excel – Advanced

Location Training Mode Duration Date Price Details
Delhi/NCR Class room 2 days 18th & 19th July 6000 + GST (18%) Book Now

Course Outline – 2 Days

  • Creating Workbooks
  • Modifying Workbooks
  • Modifying Worksheets
  • Manual and Auto Formatting
  • Entering and revising data
  • Moving data within a workbook
  • Finding and replacing data
  • Correcting and expanding upon worksheet data
  • Defining Excel tables
  • Basic Excel Function Rules
  • Understanding cell Referencing
  • Naming Groups of Data
  • Using basic formulas to calculate values
    •  Mathematical Functions (SUM, PRODUCT, POWER, SQRT, ABS ETC.)
    • Text Functions (LOWER, TEXT, SEARCH, LEN, REPLACE, TRIM, LEFT, RIGHT ETC.)
    •  Statistical Functions (COUNT, MIN, MAX, AVERAGE, COUNTA, AVERAGEA ETC.)
    •  Date & Time Functions (TODAY, NOW, EOMONTH, NETWORKDAYS, YEARFRAC                            ETC.)
  •  Finding and Correcting Errors in Calculations
  • Summarizing data that meets specific conditions
  • Manual and Auto Formatting
  • Link to data in other worksheets and workbooks using Hyperlink
  • Using Name Manager
  • Advance Calculation on data with Formula and Functions:
    • Statistical Functions (SUMIF, COUNTIF, SUMIFS, COUNTIFS, AVERAGEIF, AVERAGEIFS ETC.)
    • Informative Functions (ISBLANK, ISNUMBER, ISTEXT, ISERR ETC.)
    • Database Functions (DSUM, DAVERAGE, DCOUNT, DMIN ETC.)
    • Logical functions (IF, NESTED IF, IFNA, IFERROR, AND, OR, TRUE, FALSE ETC.)
    • Lookup & Reference Functions (LOOKUP, HLOOPKUP, VLOOKUP, INDIRECT, INDEX, MATCH, OFFSET ETC.)
  • Formula Editing Tips and Tricks
  • Working with Array Functions
  • Apply Data Validation
  • Limiting data that appears on your screen
  • Filtering Excel table data by using slicers
  • Manipulating worksheet data
  • Defining valid sets of values for ranges of cells
  • Sorting worksheet data
  • Sorting data by using custom lists
  • Organizing data into levels
  • Looking up information in a worksheet
  • Managing data by using Flash Fill
  • Using workbooks as templates for other workbooks
  • Linking to data in other worksheets and workbooks
  • Consolidating multiple sets of data into a single workbook
  • Defining multiple alternative data sets
  • Analyzing data by using data tables
  • Varying your data to get a specific result by using Goal Seek
  • Working with What-If-Analysis
  • Introduction to Charts & Graphs
  • Charts basics and its elements
  • Bar Charts, Column Charts, Line Charts & Other Charts in Excel
  • Chart Formatting
  • Making titles dynamic
  • Reversing categories
  • Combining 2 chart types
  • Adding secondary axis
  • Chart Templates
  • Analyzing data dynamically by using PivotTables
  • Filtering, showing, and hiding PivotTable data
  • Creating PivotTables from external data
  • Editing PivotTables
  • Formatting PivotTables
  • Creating dynamic charts by using Pivot Charts
  • Creating Relationships in-between Tables and create Reports using PivotTables
  • Introduction to Macros & Advanced Application Building in Excel
  • Key Concepts in Macros
  • Recording macros
  • Run macros
  • Set macros securities
  • Personal Macro workbook
  • Assigning Shortcuts to Macro Programs
  • Save workbook as Macro Enable Workbook