Master Of Excel

Course Overview

In this course, we will equip you with the essential skills needed to effectively manage and analyze data. You’ll learn how to leverage Excel’s versatility to organize complex datasets, perform advanced calculations, and create impactful visualizations. This course covers powerful features such as pivot tables, advanced formulas, and data analysis tools, enabling you to uncover trends, make informed decisions, and optimize workflows. By mastering these skills, you’ll boost your productivity and accuracy, and empowering you to tackle a wide range of analytical tasks in today’s data-driven world

Rating :

4.75

Reviews :28

Students :3,029

Available In :English | Hindi

Updated On :January, 2024

Trainer :Sagar Thorat

Fee :

₹ 8,000

Find A Batch

Key Features

Zero to Hero
Learn with fun

Who should join?

Anyone and Everyone: Anyone and Everyone working in non-technical fields looking to transition into roles that require Microsoft Excel

MIS executives: Anyone who work or want to work as MIS executives role

Work Efficiently: Anyone who wants to work efficiently in data and Microsoft Excel

Students and Graduates: Students and graduates aiming to enhance their data management skills for their careers

Pre-requisites

Curious to Learn

Willingness to Practice

Course content

IMPORTANT: Currently we are re-desiging all our courses and latest syllabus will be available as soon as it is ready.

1.Excel & Function Basics
- History Of Excel
- Know Excel Interface
- Formula Vs Function
- Function Syntax
- Types Of Parameters
2.Cell Referencing
- What is Cell Referencing
- Types Of Cell Referencing
- → Relative Reference
- → Absolute Reference
- → Row Fixed Reference
- → Column Fixed Reference
- Importance Of Referencing
3.Quick Access Toolbar
- Adding Commands In QAT
- Removing Commands
- Import/Export Customization
4.Ribbon Customization
- Arranging Tabs
- Hiding Unwanted Tabs
- Creating Custom Tab
- Creating Custom Group
- Adding Commands In Group
- Adding Icons To Commands
- Removing Commands
- Removing Group
- Removing Custom Tab
- Resetting Specific Ribbon Tab
- Resetting All Default Setting
- Import/Export Customization
5.Text To Columns
- Split Data With Delimiters
- Split Fixed Width Data
- Using Multiple Delimiters
- Consecutive Delimiters
- Text Qualifiers
- Setting Data Format
- Excluding Specific Columns
- Changing Destination
- Advanced Text Setting
- Changing Number Setting
6.Creating Outlines
- Grouping Rows & Columns
- Un-Grouping Rows & Columns
- Adding Auto Outlines
- Remove / Clear Outlines
7.Using Flash Fill
- Get First, Last & Middle Name
- Get Required Data
- Swap Names
- Remove Extra Space
- Get Initials
- Reverse Numbers / Text
- Using Multiple Column Data
- Rules Of Flash Fill
- Specifying More Rules
8.Using Form Controls
- Combo Box / List Box
- Option / Radio Button
- Scroll Bar / Spin Button
- Check Box / Group Box
- Form Vs ActiveX
9.Autofilter
- Using Autofilter
- Adding / Removing Filter
- Expanding Filter
- Removing Filtered Criteria
- Adding / Removing Filter
- Various Autofilter Criteria
- Getting Top Records
- Getting Only Required Data
10.Data Sorting
- Sorting Data
- Adding More Rules
- Sorting Limitations
- Sorting Data By
- Sort By Cell Color / Font Color
- Sort By Icons
- Sort By Values
- Sort By Custom Order
- Top To Bottom/Left To Right
- Multiple Level Sorting
- Excluding Headers
- Managing Sorting Rules
11.Subtotal
- Subtotal Basics
- Adding & Removing Subtotal
- Multiple Level Subtotal
- Removing All Subtotals
- Replace Current Subtotals
- Adding Page Breaks
- Summary Below / Above Data
- Outlines In Subtotals
- Show / Hide Outlines
12.Financial Functions
- PMT
- PPMT
- IPMT
13.Database Functions
- DAVERAGE
- DCOUNT
- DCOUNTA
- DGET
- DMAX
- DMIN
- DPRODUCT
- DSUM
14.Math & Trig Functions
- ABS
- AGGREGATE
- EVEN
- INT
- MOD
- MROUND
- ODD
- PI
- POWER
- PRODUCT
- ROUND
- ROUNDDOWN / ROUNDUP
- SQRT
- SUBTOTAL
- SUM
- TRUNC
15.Date & Time Functions
- DATE
- DATEVALUE
- DAY
- DAYS360
- EDATE
- EOMONTH
- HOUR
- MINUTE
- MONTH
- NETWORKDAYS
- NETWORKDAYS.INTL
- NOW
- SECOND
- TIME
- TIMEVALUE
- TODAY
- WEEKDAY
- WEEKNUM
- WORKDAY
- WORKDAY.INTL
- YEAR
- YEARFRAC
- DATEDIF
16.Logical Functions
- AND
- FALSE
- IF, IFS
- NESTED IF
- IFERROR
- NOT
- OR
- TRUE
17.Text Functions
- CHAR
- CLEAN
- CODE
- CONCATENATE
- DOLLAR
- EXACT
- FIND
- FIXED
- LEFT
- LEN
- LOWER
- MID
- PROPER
- REPLACE
- REPT
- RIGHT
- SEARCH
- SUBSTITUTE
- T
- TEXT
- TRIM
- UPPER
- VALUE
18.Error Handling
- ISERR
- ISERROR
- ISNA
- IFERROR
- NA
19.What If Analyis?
- Goal Seek
- Scenario Manager
- Data Table
20.Statistical Functions
- AVERAGE / AVERAGEA
- AVERAGEIF / AVERAGEIFS
- COUNT / COUNTA
- COUNTBLANK
- COUNTIF / COUNTIFS
- FREQUENCY
- LARGE
- MAX / MAXA
- MEDIAN
- MIN / MINA
- PERMUT
- SMALL
21.Array Functions
- Single Dimensional Array
- Multi Dimensional Array
- Array Compatible Functions
- Array Parameters
- Array Return Value
- Using Named Array
- Advantages Of An Array
- Disadvantages Of An Array
- Using SUMPRODUCT
- And Condition
- Or Condition
- And With Or Condition
22.Information Functions
- CELL
- ERROR.TYPE
- INFO
- ISBLANK
- ISERR
- ISERROR
- ISEVEN
- ISLOGICAL
- ISNA
- ISNONTEXT
- ISNUMBER
- ISODD
- ISREF
- ISTEXT
- N
- NA
23.Reference Functions
- ADDRESS
- AREAS
- CHOOSE
- COLUMN
- COLUMNS
- GETPIVOTDATA
- HYPERLINK
- INDEX
- INDIRECT
- MATCH
- OFFSET
- ROW
- ROWS
- TRANSPOSE
24.Data Summarization
- Using SUMPRODUCT
- SUMIF / SUMIFS
- COUNTIF / COUNTIFS
- AVERAGEIF, AVERAGEIFS
- MAX, MAX WITH IF
- MIN, MIN WITH IF
- Using Multiple Criteria’s
- All Version Calculations
25.Generate Random Data
- Whole Numbers
- Decimal Numbers
- Characters
- Text / String
- Time / Date
- Percentage
- Number In Multiply
26.Find & Replace
- Find Values
- Match Case
- Match Entire Cell Value
- Find In Worksheet
- Find In Workbook
- Find In Specific Range
- Find By Rows And Columns
- Find In Formulas And Values
- Find Formats
- Replace Formats
- Using Wild Card Characters
- Find Wild Card Characters
- Get Required Data
- Get First & Last Name
27.Lookup Functions
- CHOOSE
- VLOOKUP
- HLOOKUP
- LOOKUP
- INDEX
- MATCH
- INDEX-MATCH
- INDEX With
- → Row MATCH
- → Column MATCH
- → Row & Column MATCH
- Two Dimensional Lookup
- → VLOOKUP
- → HLOOKUP
- → INDEX-MATCH
- → OFFSET
- Three Dimensional Lookup
- → VLOOKUP
- → HLOOKUP
- → INDEX-MATCH
- → OFFSET
- Lookup By Position
- Reverse Lookup
- VLOOKUP In Horizontal Data
- HLOOKUP In Vertical Data
- Names In Lookup
- Table In Lookup
- Array As A Lookup Table
- Exact Matching
- Approximate Matching
- Approximate Match Types
- Default Match Type
28.Excel Names
- Creating Basic Name
- Using Names In Formulas
- Pasting Names In Formulas
- Benefit Of Using Names
- Naming Convention
- Scope Of A Name
- Advantages Of Using Name
- Limitations Of Using Name
- Simplifying Complex Formula
- Solving Formula Limitations
- Creating Multiple Names
- Deleting Un-wanted Names
- Editing Existing Names
- Dynamic Name Creation
- Name For Array Calculation
- → In Condition Formatting
- → In Data Validation
- Using Dynamic Name In
- → Calculation
- → Pivot Table
- → Data Validation
- → Conditional Formatting
- → Pictures
- → Charts
29.Number Formatting
- Date & Time Number Formats
- Format Number As
- → Text / String
- → Thousands, Millions, Billions
- → Inches, Feet etc
- Colors In Number Formats
- Conditions In Number Formats
- Other Number Formats
- Hiding Values Using Formats
30.Go To
- Navigation With Name Box
- Range Selection Techniques
- Smart Navigation Techniques
- Using History Ranges
- Using Last Cell
- Using Special Cells
- → Comments
- → Constants & Formulas
- → Numbers
- → Text/string
- → Logical Values
- → Errors
- Working With Blank Cells
- → Fill Above
- → Fill Below
- → Required Data
- → Deleting Blank Cells
- Using Current Region
- Using Current Array
- Working With Objects
- Row Differences
- Column Differences
- Precedents & Dependents
- → Direct Level
- → All Levels
- Using Last Cell
- Using Visible Cells
- Importance Of Visible Cells
- Copy Pasting Only Visible Cells
- Copy Data Over Visible Cells
- Quick Selection
- → Conditional Formats
- → Data Validation
31.Protection
- Protecting Sheet
- All Cells Locked
- Some Cells Locked
- Some Cells Unlocked
- Some Cells Hidden
- Some Cells Locked / Hidden
- Allow Slicer
- Allow Time Line
- Allow Form Controls
- Allow Few Shapes & Objects
- Advanced Protection Settings
- Disabling Selection
- Allow Formatting
- Allowing Required Actions
- Allow Insert & Delete
- Disabling Copy Paste
- Allowing Autofilter
- Pivot Table
- Shapes & Objects
- Allow Users To Edit Ranges
- Protecting Structure
- Protecting Window
- Protection Workbook
- Password To Open
- Password To Modify
- Password To Open & Modify
- Backup File Creation
- Removing Password
32.Editing & Managing Links
- Updating Values
- Changing Source File
- Opening Source File
- Breaking Links
- Checking For Status
33.Sharing & Track Changes
- Sharing Workbook
- Benefits Of Sharing
- Limitations In Shared Book
- Tracking Changes
- Highlighting Changes
- Generating History Report
- Reviewing Change History
- Accepting / Rejecting Changes
- Changing History Setting
34.Macro Recording
- Macro Security Settings
- Macro Enabled File Formats
- Naming Convention
- Recording First Macro
- Assigning Macro To A Button
- Recording Complex Macros
35.Formula Auditing
- Tracing Dependents
- Tracing Precedents
- Remove Tracing Arrows
- Showing Formulas
- Evaluating Single Formula
- Evaluating Complex Formula
- Step In / Out
- Finding Errors
- Evaluating Expression
- Calculating All Formulas
- Adding Watch Window
- Benefits Of Formula Auditing
- Checking & Tracing Errors
36.Paste Special
- Various Paste Special Options
- → All
- → Formulas
- → Values
- → Formats
- → Comments
- → Validation
- → All Using Source Theme
- → All Except Borders
- → Column Width
- → Formulas & Number Formats
- → Values & Number Formats
- Merging Conditional Formats
- Skip Blanks
- Transpose
- Paste Link
- Operation In Paste Special
- → Add
- → Subtract
- → Multiply
- → Division
- → Add Two Data Set Values
- → Time Saving Technique
- → Converting
- → Number To Percentage
- → Percentage To Number
37.Creating Excel Table
- Create a table in excel
- Benefits of using a table
- Using Table In
- Formulas, Data Validation
- Pivot Table & Charts
- Conditional formatting
38.Conditional Formatting
- Conditional Formatting Basics
- Creating Basic Format Rules
- Edit / Delete / Manage Rules
- Understanding Limitations
- Merging Rules
- Understanding Order Of Rules
- Using Stop If True
- Coloring Cells Based On Values
- → 2 & 3 Color Scale
- → Data Bars
- → Icon Sets
- Highlighting Cells Contains
- → Text / String
- → Number
- → Date
- → Errors
- → Blanks / Non-Blanks
- Top Or Bottom Ranked Values
- Above Or Below Average
- Unique Or Duplicate Values
- Using Formulas
- → Basic Formula Rules
- → Concept Of Logical Formulas
- → Or Condition
- → And Condition
- → And With Or Condition
- → Cell Referencing
- → Alternate Row Or Column
- → Chess Board
- → Using Array Formulas
- → Using Names
- → Using Form Controls
39.Data Validation
- What is Data Validation?
- Allowing Any Value
- Only Whole Numbers
- Only Decimal Numbers
- Only Date
- Only Time
- Specific Text Length
- List
- → From Values
- → From Range
- → From Another Sheet
- → Dynamic List
- → Dependent List
- Clearing All Rules
- Applying Rules To Other Cells
- Setting Input Message
- Setting Error Alert
- Various Error Alert Styles
- → Stop
- → Warning
- → Information
- Basic Formula Rules
- → Or Condition
- → And Condition
- → And With Or Condition
- → Mobile No
- → Email ID
- → Upper, Lower, Proper
- → Specific Criteria Values
- Using Array Formulas
- Circle Invalid Data
- Limitations Of Data Validation
40.Advanced Filter
- Single Column Unique List
- Multiple Columns Unique List
- Filtering Data
- → In Place
- → To Another Location
- → To Another Sheet
- → To Another Workbook
- Using Dynamic Range
- Fetching Required Columns
- Adding Extra Columns
- Fixed Conditions
- → Single Condition
- → Or Condition
- → And Condition
- → And With Or Condition
- → Using Wild Card Characters
- → Using Equal & Not Equal
- → Using Multiple Conditions
- → Using Dates
- → Using Numbers
- Formula Based Conditions
- → Simple Logical Formula
- → Rules To Use Formula
- → Or Formula
- → Using And Formula
- → And With Or Formula
- → Relational Formula
- → Multiple Relation Formulas
- → VLOOKUP To Filter Data
- → Reconciling Two Tables
41.Get External Data
- From Excel
- From Access
- From Text
42.Pivot Table & Charts
- Types Of Fields
- → Report / Page Fields
- → Data / Value Fields
- → Row / Vertical Fields
- → Column / Horizontal Fields
- Pivot Cache Memory Concept
- Using Existing Pivot Cache
- Benefits Of Using Cache
- Refreshing Pivot Table
- Refreshing All Pivot Tables
- Grouping / Un-Grouping
- → Date / Time / Numbers
- Changing Value Field Settings
- Advanced Value Field Settings
- Calculated Items / Fields
- Creating Calculated Columns
- Changing Pivot Table Range
- Dynamic Pivot Table
- Move / Delete Pivot Table
- Advanced Pivot Table Setting
- Sorting Data In Pivot Table
- Manual Sorting Pivot Data
- Creating Pivot Chart
- Creating Multiple Charts
- Removing Filter Icons
- Add & Remove Subtotals
- Add & Remove Grand Totals
- Changing Report Layout
- Insert Slicers
- Linking Multiple Slicers
- Horizontal Slicer
- Adding Time Line
- Linking Multiple Time Line
43.Charts & Dashboard
- Creating First Chart
- Switching Rows / Columns
- Hiding Empty Cells
- Showing Hidden Data
- Copy Paste Series
- Adding Individual Series
- Dynamic Chart Using Series
- Dynamic Chart Using Tables
- Different Chart Elements
- Speedo Meter Chart
- Thermometer Chart
- Different Shape Chart
- Creating Pencil Chart
- Creating Amazing Pie Chart
- Creating Smiley Chart
- Creating Gradient Fill Chart
- Creating Traffic Light Chart
- Creating Star Chart
- Combining Different Charts
- Using Form Controls
- Insert Line Spark Line
- Insert Column Spark Line
- Insert Win/Loss Spark Line
- Creating In Cell Graphs
- Spark Lines Alternative
- Invert Colors For Negative
- Vary Colors By Points
- Adding Conditional Color
- Primary & Secondary Axes
- Using Chart Styles & Layouts
- Creating Template For Chart
- Using Chart Templates
44.Data Consolidation
- Using Formula
- Using Consolidation Tool
- Using Pivot Table
- → Using Default Page Field
- → Customizing Page Fields
- → Managing Page Fields
- → Page Field Concept
- → Report Filter Pages
45.Creating Hyperlinks
- Hyperlinks Using Tool
- → Existing File / Web Page
- → Within Same Workbook
- → Create New File / Email
- → Changing Text To Display
- → Changing Screen Tip
- Hyperlinks Using Formula
- → Same / Another Sheet
- → Web Page
- → Folder / Share Drive / File
- → Google Search
- → Dynamic Hyperlink List
46.Power Pivot & Power View
- Pre-Requisites
- Installing Power Pivot
- Creating
- → Table
- → Link Table
- → Relationship
- → Power Pivot Table
- → Multiple Power Pivot Tables
- → Power Pivot Charts
- → Multiple Power Pivot Charts
- → Power View Dashboards