Become a power user of Excel, specialize Microsoft Excel at a professional level.
Microsoft Advanced Excel
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
