Course Content

Download Pdf

MODULE 1: -

1. Excel Introduction

• An overview of the screen, navigation
• basic spreadsheet concepts
• Various selection techniques
• Shortcut Keys
• Customizing the Ribbon
• Using and Customizing AutoCorrect
• Changing Excel’s Default Options

2. Create Worksheets and Workbooks

• Create a workbook
• Import data from a delimited text file
• Add a worksheet to an existing workbook
• Copy and move a worksheet
• Rename a worksheet
• Change worksheet order
• Insert and delete columns or rows
• Change workbook themes
• Adjust row height and column width
• Hide or unhide worksheets

3. Formatting and Proofing and Protecting

• Currency Format
• Format Painter
• Formatting Dates
• Custom and Special Formats
• Basic conditional formatting
• File Level Protection
• Workbook, Worksheet Protection
• Formatting Cells with Number formats, Font formats, Alignment, Borders,
• etc.

4. Using Basic Functions

• Formulae that Add / Subtract / Multiply / Divide
• BODMAS / Formula Error Checking
• The Sum Function
• LARGER / SMALLER Functions
• Using Functions –Average, Max, Min, Count, Counta
• Absolute, Mixed and Relative Referencing
• If Function
• The VLOOKUP/ HLOOKUP Functions

5. Basic Text & Date Functions

• Upper, Lower, Proper
• Left, Mid, Right
• Trim, Len, Exact
• Concatenate
• Find, Substitute
• Today, Now
• Day, Month, Year
• Date,

6. Sorting and Filtering

• Filtering on Text, Numbers & Colors
• Sorting Options
• Custom sort
• Fill Command
• Find and Goto

7. Charts and slicers

• Charts i.e. Bar Charts / Pie Charts / Line Charts (Used in MIS Reports)
• Using SLICERS, Filter data with Slicers
• Manage Primary and Secondary Axis
• Combo Charts – Secondary Axis

8. Printing Workbooks

• Working on Page Layout Tab and Views
• Setting Up Print Area
• Customizing Headers & Footers
• Designing the structure of a template
• Print Titles –Repeat Rows / Columns

MODULE 2: -

1. Mathematical Functions

• Perform logical operations by using AND, OR, and NOT functions
• Perform logical operations by using nested functions
• IFERROR, SUMIF, SUMPRODUCT, COUNTIF, AVERAGEIF
• Perform statistical operations by using SUMIFS, AVERAGEIFS, and
• COUNTIFS functions
• FLOOR / CEILING/ MROUND

2. Lookup Functions

• The VLOOKUP/ HLOOKUP Functions
• INDEX & MATCH Functions
• MATCH with VLOOKUP Functions
• OFFSET / INDIRECT functions
• Creating Smooth User Interface Using Lookup
• Nested VLOOKUP (Double VLOOKUP)
• Reverse Lookup using Choose Function
• Worksheet linking using Indirect
• VLOOKUP with Helper Column

3. Text and Date & Time Functions

• Format and Modify Text by using Functions
• Using The DATEDIF / NETWORKDAYS / EOMONTH Functions
• Using The WEEKNUM Functions
• Using The EDATE / NETWORKDAYS. Intl / WEEKDAYS.INTL Functions

4. Financial Functions

• PV (Present Value) &FV (Future Value)
• RATE & NPER (Number of Periods)
• PMT (EMI Payment)
• PPMT (Principal Payment)
• IPMT (Interest Payment)

5. Advanced Filters & Sorting

• Extracting Records with Advanced Filter
• Creating / Deleting Custom List
• Sort by using Custom List
• Sort by Color

6. Hyperlinking & Define Named & Paste Special

• Hyper linking data, within sheet / workbook
• Linking & Updating links between workbooks & application
• Paste Formulas, Paste Formats
• Paste Validations
• Transpose Tables
• Name data ranges Name tables
• Manage named ranges and objects

7. Data Analysis & Data Validation

• What If Analysis
• Goal Seek & Scenario Analysis
• Solver Tool
• Import, transform, combine, display, and connect to data
• Consolidate data
• Number, Date & Time Validation
• Text and List Validation
• Custom validations based on formula for a cell
• Dynamic Dropdown List Creation

8. Advanced Conditional Formatting

• Create custom conditional formatting rules
• Highlighting Cell, Row and Column Rues
• Conditional formatting rules that use formulas
• Manage conditional formatting rules
• Convert a cell range to a table
• Create and Manage Tables
• Remove duplicate records

9. Pivot Tables

• Creating Simple Pivot Tables
• Basic and Advanced Field Setting
• Filtering & Modifying PivotTable a
• Grouping based on numbers and Dates
• Calculated Field & Calculated Items
• What are the Array Formulas, Use of the Array Formulas?
• Array with Lookup functions.
• Adv. Use of formulas with Array.

10. Charts

• New Charts – Tree map & Waterfall
• Sunburst, Box and whisker Charts
• Combo Charts – Secondary Axis
• Charts in Pivot Table
• Smart Lookup and manage Store
• Using SLICERS, Filter data with Slicers
• Forecast Sheet
• Sparkline’s -Line, Column & Win/ Loss
• Working on MIS Reports

11. Week Special Class for MIS Reports

Course Details

Hours: 2 Months

Intermediate

Course Fee: 6000

Course Features

2 Free Demo Classes

Free Course Material

Completion Certificate

Lifetime Membership