Course Objective
Participants will be able to understand the advanced features of Excel to maintain and present data in an effective way. Excel has got many hidden and new features, and this course is designed with an intention to work around most of them and use them in day-to-day work. Participants attending this session will be able to manage reports and work with data extraction and analysis effectively.
Pre-requisite:
Participants should be having basic knowledge of Excel and at least have 1-2 years experience in working with Excel.
Upon successful completion of this course, the participants will be able to
Present report in many ways
Extract data using various options
Prevent and highlight errors
Analyse and extract data from a vast database/excel list
Efficiently use Pivots and Charts to present data
Working with Data (4 Hrs)
Creating Custom Lists & Sorting Data using Advanced Techniques
Text to Columns
Extracting the Data using Filters & Advanced Filters
Data Consolidation
Importing the Data from External Sources
Applying Subtotals & nested Subtotals
Custom Data Validation
Smart Working (15 Min)
Using Hyperlinks
Shortcut Keys
Using Advanced Functions (3 Hrs)
VLookup and HLookup
Looking up with Text
Looking up static and dynamic ways
IF and Nested If
Date and Workday Functions
Calculating Age
Date Differences regarding days, months, years
Arithmetic Functions
Text Functions
Others (if required)
Working with Range and Naming Convention (15 min)
Apply a Name to a cell or range of cells with scope
Modify and delete range names
Create Name from Selection
Apply in Formula
Day-2
Performing Spread Sheet What-If Analysis (2 Hrs)
Creating Data Tables
Using Scenarios Manager
Single Cell Goal Seeking
Using Solver
Pivot Table and Pivot Chart Reports (3 Hrs)
Create a Pivot Table Report
Modify a Pivot Table Field Layout
Creating Groups and Inserting a Calculated Field
Create a Pivot Chart
Create Pivot from another Pivot
Create Pivot from multiple consolidation ranges
Working with Charts (3 Hrs, Advanced Settings and Options Only)
Chart Terminology (When to use what charts)
Types of Charts
Pie, Exploded Pie, Pie on a Pie, Bar on a Pie
Line
Column, Stacked Column, 100% Stacked, Bar, Stacked Bar, 100% Stacked
XY Scatter, Bubble and Radar
Doughnut
Area and Surface
Stock
Defining Major Chart types and plotting them on 2 Axis
Trendlines, SeriesLines, DropLines, High-low Lines
Plotting Target Lines
Break-in-axis Charts
Waterfall Charts/Gantt Charts (floating bar)
View this Course
Certified
You have 
