Phone: +91-9639146350
E-mail: info@opaelearning.com Website: www.opaelearning.com
Lookup & Reference Functions Data Sorting
• Using Lookup and reference Functions • Sorting by Multiple Columns
(VLOOKUP HLOOKUP, MATCH, INDEX) • Sorting Tables
• VLOOKUP with Exact Match, Approximate • Using Multiple-level Sorting
Match • Using Custom Sorting
• Nested VLOOKUP with Exact Match
• VLOOKUP with Tables, Dynamic Ranges Data Filtering
• Using VLOOKUP to Consolidate Data from
Multiple Sheets • What are Filters?
• HLOOKUP with Exact Match, Approximate • Preparing to Filter
Match • Advanced Filter
• Nested HLOOKUP with Exact Match • One Condition in Two or More Columns
• HLOOKUP with Tables, Dynamic Ranges One Condition in one Column or
• Using HLOOKUP to Consolidate Data from Another
Multiple Sheets • One of Two Sets of Conditions for Two
Columns
Power Functions • To Turn Off the Advanced Filter
• COUNTIF Function
Pivot Table
• COUNTIFS Function
• SUMIF Function • Formatting and Customizing Pivot Tables
• SUMIFS Function • Using Advanced Options of Pivot Tables
• Pivot Charts
• Consolidating Data from Multiple Sheets
Logical Function
and Files Using pivot Tables
• Using External Data Sources
• IF Function
• Using Data Consolidation Feature to
• Description of the IF Function
Consolidate Data
• Examples of the IF Function
• Show Value As (% of Row, % of Column,
• Using AND and IF Functions Together
Running Total, Compare with Specific Field)
• Using OR and IF Functions Together
• Viewing Subtotal Under Pivot
Creating Slicers (Version 2010 & above)
Some More Functions
Charts
• Data and Time Functions
• Text Functions • Using Charts
• Database Functions • Formatting Charts
• Using 3D Graphs
Data Validation • Using Bar and Line Chart Together
• Using Secondary Axis in Graphs
• Specifying a Valid Range of Values for a Cell • Sharing Charts with PowerPoint / MS Word,
• Specifying a List of Valid Values for a Cell Dynamically
• Specifying Custom Validations based on • (Data Modified in Excel, Chart would
Formula for a Cell Automatically get Updated)
Phone: +91-9639146350
E-mail: info@opaelearning.com Website: www.opaelearning.com
WhatIf Analysis Subtotal
• Goal Seek • Formatting and Sorting Your Excel Data
• Data Tables • Applying Subtotal to Your Table
• Scenario Manager
Working with Tables in Excel
Working with Templates
• Creating Tables in Excel
• Designing the Structure of a Template • Properties of Excel Tables
• Using Templates for Standardization of • Deleting an Excel Table
Worksheets
Excel Macros
Working with Reports
Previewing and Printing
• Creating Subtotals
• Multiple-level Subtotals • Printing the Worksheet
• Printing of Worksheet in Multiple Pages
References • Repeating Rows and Columns for Multiple
Pages
Naming Cells and Ranges • Previewing Worksheet
• How do You Define a Named Range? New Features of Excel
• Sparklines, Inline Charts, Data Charts
Using Excel Auditing Tools • Overview of all the New Features
• Viewing Formulas
Conditional Formatting
• Tracing Cell Relationships
• Identifying Precedents
• Highlight Orders from Texas
• Tracing Error Values
• Highlight Dates in the Next 30 Days
• Fixing Circular Reference Errors
• Highlight Column Differences
• Using the Excel Formula Evaluator
• Excel Error Values
Comments in Excel
• What a Comment can do in Excel?
• How to Add Comments to Cell in Excel
• How to Show/Hide Comments to Cell in
Excel
