8368722963 info@nxaeon.com Admin Login

Advanced Excel is the tool that maximizes the data of companies that will be able to store, compare and analyze information effectively. Excel gives you the ease of using graphics and empower you to use of formulas, tables that are used in reports presentations, sales and others. Among the tools Excel includes ways to identify trends by means and averages that will be useful when seeking explanations of the behavior of certain figures. Advance Excel is especially for students and working professional who wants to gain necessary to use pivot tables, audit worksheet data, vlookup, utilize data tools. Create, Edit and Format all applications will be easily used in excel application. If you want to learn Advanced Excel Training then you can come to nx-aeon at Mayur Vihar Phase-III, New Delhi.

Introduction and Basic MS Excel

  • Customizing common options in Excel
  • Absolute and relative cells
  • Protecting and unprotecting worksheets and cells

Working with Functions

  • Writing conditional expressions (using IF)
  • Using logical functions (AND, OR, NOT)
  • Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
  • VlookUP with Exact Match, Approximate Match
  • Nested VlookUP with Exact Match
  • VlookUP with Tables, Dynamic Ranges
  • Nested VlookUP with Exact Match
  • Using VLookUP to consolidate Data from Multiple Sheets

Data Validations

  • Specifying a valid range of values for a cell
  • Specifying a list of valid values for a cell
  • Specifying custom validations based on formula for a cell

Working with Templates

  • Designing the structure of a template
  • Using templates for standardization of worksheets

Sorting &  Filtering Data

  • Sorting tables
  • Using multiple-level sorting
  • Using custom sorting
  • Filtering data for selected view (AutoFilter)
  • Using advanced filter options

Working with Reports

  • Creating subtotals
  • Multiple-level subtotals
  • Creating Pivot tables
  • Formatting and customizing Pivot tables
  • Using advanced options of Pivot tables
  • Pivot charts
  • Consolidating data from multiple sheets and files using Pivot tables
  • Using external data sources
  • Using data consolidation feature to consolidate data
  • Show Value As (% of Row, % of Column, Running Total, Compare with Specific Field)
  • Viewing Subtotal under Pivot

Advanced Excel More Functions

  • Date and time functions
  • Text functions
  • Database functions
  • Power Functions (CountIf, CountIFS, SumIF, SumIfS)

Formatting 

  • Using auto formatting option for worksheets
  • Using conditional formatting option for rows, columns and cells

whatIf Analysis

  • Goal Seek
  • Data Tables
  • Scenario Manager

Advanced Excel More Functions

  • Date and time functions
  • Text functions
  • Database functions
  • Power Functions (CountIf, CountIFS, SumIF, SumIfS)

Charts

  • Using Charts
  • Formatting Charts
  • Using 3D Graphs
  • Using Bar and Line Chart together
  • Using Secondary Axis in Graphs
  • Sharing Charts with PowerPoint / MS Word, Dynamically

New Features of Excel

  • Sparklines, Inline Charts, data Charts
  • Overview of all the new features

 

Creating Macro

  • Type of VBA Macro.
  • Display of Developers Tab and VB Editor.
  • Macro Security.
  • How to record a VBA Macro
  • Execution of recorded Macro.
  • Editing the recorded Macro
  • Various Keyboard Shortcuts related to VBA Macros.
  • Working on Various Examples of Macro recorder
  • Introduction to the writing of basic VBA codes
  • Saving Off Your Macro.
  • Introduction to the Personal Macros

Programming Writing Concepts

  • Defining Variables to Macro.
  • Discussion on different Types of Variables.
  • Use of Local Window.
  • Working on Programming, Understanding on VBA Language.
  • Introduction of Message Box in Macro.
  • Introduction of Input Box In Macro.
  • Use of decision making statements in Macro by using: If and Else conditions, And /OR conditions.

Working with Loops and Worksheet Ranges

  • Introduction of the loops in Macro
  • Use of Different type of loops like, For & Next LOOP, For & Each Loop, Do & Until LOOP While and While and Wend Loop
  • Working with ranges like selecting, copying and pasting the ranges and extract the data on same Worksheet level
  • Selecting, copying and pasting the ranges and extract the data on different Worksheet level in the same Workbook
  • Selecting, copying and Pasting the Ranges and extract the data on different Workbook Level.

Writing of Formulae in VBA an Error handling

  • Introduction of programming concepts for writing the Excel Formulas
  • Difference between the A1 and R1C1 style of writing formula in VBA
  • Look at the style A1 and R1C1 and decide which is better
  • Find out the better solution to write the formulas in VBA
  • Writing of the basic formulas in to VBA
  • Writing of the complex formulas in to the VBA
  • Writing of Logical formulas and Lookup formulas in VBA
  • Freezing of the formula in VBA
  • Other useful formulas to be write in Class
  • Error handling

Advanced VBA Functions

  • What is User Defined Function
  • Use of User Defined Function
  • How to create any Function OR Formula which is not available in Excel
  • What is ADD-IN
  • Use of ADD-IN and How to create ADD-IN.
  • What are Events
  • How and when to use the VBA Events.
  • VBA User Forms
  • What are User Forms,
  • Why to create User Forms and How to create User Forms

Project