Microsoft Advanced Excel

Become a power user of Excel, specialize Microsoft Excel at a professional level.

20
20
Hours

Book Free Demo; Get the Best Price for Advanced Excel Course

Fill in the form below to receive a callback

Register Now

Advanced Excel training at Quadra Plus will coach the students on advanced formula techniques, preparing data for analysis, work with logical and financial functions, sophisticated lookups, audit and analyze worksheet data, create pivot table, utilize data, collaborate with others, create and manage macros. etc.,

Course Objectives

At the completion of this course, learners will be able to

  • Learn to work with Pivot tables.
  • Learn to use conditional formatting and styles.
  • Use advanced formulae and their application scenarios
  • Apply advanced techniques for report visualizations
  • Employ various methodologies of summarizing data
  • Learn how to build professional dashboards in Excel
  • Understand basic principles of laying out Excel models for decision making

I. Overview of the Basic Excel

  • Customizing common options in Excel
  • Absolute and relative cells
  • Protecting and un-protecting worksheet and cells

II. Working with the Logical Functions

  • Writing conditional expression  (using IF)
  • Nested If and complex if
  • Using logical functions (AND, OR, NOT)

III. Data Validation

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

IV. Working with Templates

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

V. Sorting and Filtering Data

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

VI. Working with Reports

  • Creating Subtotals
  • Multiple-Level subtotals

VII. 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
  • Calculated fields and calculated items
  • Array with look up functions

VIII. Charts and Slicers

  • Bar Chart, Pie Chart, Line chart
  • Sharing Charts with PowerPoint I MS Word, Dynamically
  • Filtering data using slicers
  • Manage Primary and Secondary Axis

IX. Dashboards

  • Excel Dashboards
  • Planning a Dashboard
  • Adding Tables & Charts to Dashboards
  • Adding Dynamic Content to Dashboards

X. What If Analysis

  • Goal Seek
  • Data Table
  • Solver Tool

XI. Look Up Functions

  • 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

XII. More Functions

  • Date and time functions
  • Text functions
  • Database functions
  • Power Functions (Countif, CountlFS, SumlF, SumlFS)

XIII. Formatting

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

XIV.Macros

  • Relative & Absolute Macros
  • Editing Macro’s

XV.New Features of Excel

  • Overview of all the new features
  • Sparking,
  • lnline Charts,
  • Data Charts

Certified Trainer

Instructor’s Manuals

   Course Completion Certificate

Certification Assistance

  Hands on Practice

  Post Training Support

Related Courses

Open chat