Microsoft Excel

Excel from Beginner to Advanced

Master Every Tool & Feature Of Ms Excel In Just 6 Months.

JOIN US TODAY

Microsoft Excel - Excel from Beginner to Advanced

KSM offers online training for Excel version 2013, and at levels for those new to Excel on up through advanced training courses like Excel Programming with Visual Basic for Applications (VBA), and Power features. These Excel Modules are perfect for any professional since Excel is one of the most widely used software tools in nearly every industry.

Career Growth

This program will be a countless value-add to your resume, which can help you chase a promising career.

Certified Training

At the end of your training program you will be awarded with certificate of completion.

Simulator based learning

Hands-on learning using advanced MS Excel simulator, These Excel Modules are perfect for any professional

Pivot tables, Macros

Master Pivot tables, Macros and complex Formulae of MS Excel

Self-assessment quizzes

Exercises at the end of each module to gauge your learning

High Quality Training

High quality eLearning solutions with practical hands-on examples

Course Modules

Module 1: Let's Brush UP Excel Spreadsheet

  • Creating, Editing, Formatting, Printing and Saving Spreadsheets
  • Password Protecting & Un-Protecting Worksheets
  • Web Development
  • Freeze Pane, Mail merge

Module 2: Useful Quick Excel Shortcuts

  • Shortcut to AutoSum, Copy, Paste
  • How to Repeat Previous task
  • Shortcut to Fill Down and Fill Right
  • How to Insert and Delete Rows & Columns
  • Show and Hide the Ribbon with Shortcut keys
  • Use of Ribbon without Mouse
  • How to Customize the Shortcuts
  • How to Customize Ribbon

Module 3: Basic Excel Calculation

  • Define Short Cut Key to Open Excel
  • Basic Excel Calculations
  • Define Ranges to Perform Calculations
  • Absolute and Relative Reference in Excel
  • Mixed Referencing
  • Write a 3D Function and How to consolidate

Module 4: Paste Special

  • Paste Value, Format, Formulas, Comment only with Paste Special
  • Update Value Without Formulas
  • How to Transpose Data From Column to Row
  • Link Data with Paste Special

Module 5: If Conditional Function

  • Use of IF Function for Single Criteria
  • Use of IF Function for Multiple Criteria
  • Use of IF & AND Function For Multiple Criteria
  • Use of IF & AND Function with OR Function
  • Nested IF Function for Multiple Grades

Module 6: Conditional Formatting

  • Use Conditional Formatting to Color Cell based on content
  • Identify Bottom And Top No's using Conditional Formatting
  • Display Data with Data Bar
  • Use a Formulas to Format the Cell

Module 7: Rounding Functions

  • Int, Trunc, Odd, Even Functions
  • Int, Trunc, Odd, Even Functions
  • Int, Trunc, Odd, Even Functions

Module 8: Mathematical Functions

  • Max, Min, Median, Average, Sum functions
  • Count, Counta, Countblank
  • Rank the list with Rank Function
  • Large and Small Functions
  • Use of SUMIF Function
  • Use of Average IF Function
  • Use of SumIFs and AverageIFs Functions
  • Use of Count IF Function
  • Use of CountIFs Function

Module 9: D Function

  • Introduction of Data Base Functions
  • Using DSUM Function for AND Criteria
  • Using DSUM Function for OR Criteria
  • Using DGET Function to Find Single Record

Module 10: Text Functions

  • Use of Right, Left, Mid Functions to Extract Text
  • Use of Find, Search, Len Function
  • Use of Trim, Proper, Upper, Lower Function
  • Use of & and Concatenate Function
  • Use of Rept and Len Function to Display Initial Zero
  • Use of Replace & Substitute Functions
  • Advanced use of Text Functions with Example
  • Advance use of Right, Mid, Left Functions
  • How to Extract Worksheet Name with Text Functions
  • How to Extract Workbook Name With Text Functions

Module 11: Validation and Protections

  • How to Prevent from Entering Incorrect Values
  • How to Validate Huge range of Values
  • How to Protect the Whole Worksheet with Password
  • How to Keep few Cells Unprotected while Entire worksheet is Protected
  • Document Encryption & Mark as Final

Module 12: Is and Lookup Functions

  • Introduction to IsNumber, IsError, IsBlank Functions
  • Simple Use of Choose Function
  • Use of Choose Function to make Scenario Analysis
  • Use of Vlookup to extract Record from Table like Data
  • Power use of Vlookup, IF, and IsError Functions
  • Use Hlookup Function with Match Function
  • Combined Use of Vlookup and Hlookup with Iferror
  • How Index Function is much better than Vlookup & Hlookup
  • Indirect Function- Prepare an Output Sheet

Module 13: Financial Functions

  • Use of FV & PV Functions to analyse Investment Returns
  • Use of FvSchedule function in case of Variable Interest Rates
  • Effective & Nominal Rates with Effect & Nominal Functions
  • Use of IPMT, PPMT, PMT functions to Prepare Debt Repayment Schedule
  • Use of CumIPMT and CumPrince Functions

Module 14: Date and Time Functions

  • Introduction of Date & Time Functions and Format Convention
  • How to change default Date and Time Format
  • Custom Formatting of Dates
  • Today & Now Functions to Enter Current Date and Time
  • Weekday Function to Identify the Day of Week
  • Weekday and Network day Functions
  • How to find Age with Dated IF Function
  • E date & Eo Month Functions
  • How to Extract Year, Month, Day from Given Dates
  • How to use AutoFill to Write Date Series

Module 15: What if Analysis and subtotal

  • Use of Goal Seek to get a Particular Result
  • How to summarize Data using Sub Total tool
  • How to use Data Table for Sensitivity Analysis
  • Scenario Manager for Different Outcome

Module 16: Charts

  • Introduction to Chart Elements and Chart Types
  • How to Create First Chart and Display Specific Data
  • Learn to Create Instant Chart , Use Switch & Move Buttons
  • Learn to Change The Default Chart, Give Title, Save As Template
  • Understanding the use of Legends, Chart Title, Axis, Data Labels, Gridlines etc.
  • How to Add an Average to the Chart
  • How to Create Double Axis Chart to display values with huge difference
  • How to Create a Pie Chart
  • Formatting of Pie Chart & Key Features
  • Pie To Bar and Separate Pieces of Pie Chart
  • Sparkline's

Module 17: Pivot Table

  • Introduction to Pivot Table
  • How to Find out values as % of Grand Total and Column/Row Total in Pivot Table
  • How to Display Revenue By Quarter in Pivot Table
  • Use of Calculated Field for calculating Commission on revenue
  • Use of External Data Connections to Import Data from Different file
  • Consolidate Sheets Through Pivot Wizard
  • Filter the Pivot Table by Row and Column Label
  • Adding and Removing Subtotal And Grand Totals
  • Conditional Formatting in the Pivot Table
  • Create your First Pivot Chart
  • Slicer

Module 18: Array Function

  • Introduction to Array Functions
  • Multiple Condition Test with Array Function
  • How to Count Unique Entries in List
  • How to Combine Multiple Functions With Array

Module 19: Recording and Basics of Macro VBA

  • How to Record Basic Excel Work and Assign Shortcut
  • How to Enter Data In Same Worksheet - First Macro
  • How to Enter Data In Different Worksheet - Second Macro
  • Dealing with Macro Security
  • Use of Input Box and Formula Command
  • Use of Input box, Msg box, Formula in different way
  • Inserting Command Button And Assigning Short Cut Key
  • How to Create Personal Macro

Module 20: Use full VBA Commands

  • How to Write Value to a Cell using VBA
  • How to Cut and Paste Data from One Sheet to Another Sheet using Macro VBA
  • Break Points And Call Command in VBA
  • Select, Add, Rename and Delete Worksheet using VBA
  • How to Add Data in Another Sheet using Macro VBA
  • How to Handle the Error in Macro
  • Use of WITH Command with Example

Module 21: Use of Excel Formulas and Functions in VBA

  • How to use Offset Function to Put Variables in Excel Dynamically
  • If, Offset and For Next Command
  • Select Case
  • Use of Do While and Do Until
  • Use of Vbyesno on Msgbxo to Provide Flexibility in Running Macro
  • Use of Formula Command with For Next command
  • Use of Do While and For Next command
  • Recording Macros with Relative References
  • Transpose the Data with Do While Loop - Part 1
  • Transpose the Data with Do While Loop - Part 2
  • How to create User Defined Function

Module 22: Work Sheet and Work Book Events

  • Introduction to Events - Activate & Deactivate Events
  • Autofit Column Width with Change Event
  • Workbook Opening and Closing Events

Module 23: User Form in Excel

  • Understanding First Basic User form
  • Basic Example on How to Create User Form - Part 1
  • Basic Example on How to Create User Form - Part 2
  • Use of Combo box to Provide Defined List

Module 24: Case Studies on VBA

  • How to Transfer Data to Another Workbook Automatically with VBA

Module 25: Basic Excel Dashboard

  • How to Create Scrolling Table with Scroll Bar and Vlookup
  • How to Prepare a Scrolling Chart

Module 26: Chart Based Comparing Dashboards

  • Sample Comparative Dashboard
  • Comparative Dynamic Chart with Two Combo Box
  • Dynamic Dashboard with Multiple Check Boxes

Module 27: Use of Conditional Formatting in Dashboard

  • Roll Over Dashboard
  • How to Highlight Row and Column
  • Speedometer Chart
  • How to Prepare Performance Indicator with Chart

Who Should Attend?

MASTER EVERY TOOL & FEATURE OF MS EXCEL IN JUST 6 MONTHS.

  • A must for every professional from entry to senior level managers in any industry
  • Engineering & IT graduates B.Tech/BE, B.Sc-IT,BCA,MCA M.Sc-IT etc
  • Management Graduates - MBA, BBA etc
  • For students aspiring for entry level jobs in any domain this is mandatory
Introduction Video

Take a glance into our Advance Excel Program

Program Highlights:

15+ Hours recorded Lectures

Learn to use MS Excel 2010 components like Charting, Power Functions, HLOOKUP, VLOOKUP, Macros, PivotTables, VBA & many more.

Consolidating and managing data from multiple workbooks.

Performing complex calculations more efficiently, using various Excel functions. Organizing and analyzing large volumes of data.

Learning Outcome

Find new ways to visualize data

Reduce workload each month by automating repetitive tasks by using macros

Cut hours of labour by using Pivot tables to analyse data

Master Excel Functions and Formulas that saves time and increases productivity

Great value-add for student’s Resume which can help him/her get a great career.

15+ Recorded Videos

Full lifetime access

Access on mobile

Quizzes at the End of Module

Certificate of Completion

Copyright 2024 | All Right Reserved | KSM