Microsoft Advanced Excel online tutorial is designed in such a way that it gives you the complete knowledge of features and functions of Microsoft Excel. It also allows viewing the data in the form of line graphs, charts and histograms along with a limited three-dimensional graphical display. Furthermore, it allows sectioning of data to view any it’s dependencies on numerous factors for different perspectives.

Basic MS Excel introduces the audience to ways of using functions, presentation of data in various chart formats, formatting the data etc. It also teaches techniques of finding value using V LOOKUP and H LOOKUP, along with methods of creating and formatting Pivot Tables, generating scripts and methods for using macros.

Course Introduction:

Microsoft Advanced Excel enables the course undertakers to achieve expertise in the handling of basic excel formulae and tools. The training program assists the users to not only enhance, but master their basic understanding about the tools used for visualizing, calculating, organizing, and managing the data... Basics of the course undertakers about the basic features, but it teaches them about how to use the statistical, numerical, logical and financial functions, along with making them understand what each and every element present in the function is used for.

Course Objectives:

The objectives of the course are:
  • Understanding detailed abilities of MS Excel along with its Functions
  • Understanding the specialized elements in numerical, logical, functional and statistical functions
  • Understanding the advanced options of the Pivot Table
  • Manipulating the options in existing Pivot Charts
  • Summarizing data by using Sparklines
  • Recording and playing a macro
  • Editing a macro using a VBA Editor
  • Explaining how to manage scenarios
  • Describing the restrictions and manipulating techniques
  • Managing different versions of a workbook and removing the workbook metadata
  • Identifying the stages for setting the tracking alternatives and limiting the editors
  • Creating custom accounting, font formats and colors

Course Pre-Requisites:

  • Knowledge about MS Excel Basics
  • Windows Operating system
  • Microsoft Office
  • 500 MHz processor, along with 256 MB Ram and a 3 GB hard disk
  • Good Internet connection for sharing of files

Recommend Audience for the course:

  • Data scientists
  • Professionals wanting to perform accurate and swift calculations for simple to complex calculations in the field of finance, statistics or engineering can use Microsoft Advanced Excel.
  • Students who wish to hone their skills with respect to excel, so as to get their work done faster and more conveniently
  • Professionals looking to improve their job prospects
  • Mid-level managers who wish to analyze the productivity of the employees who report to them on an individual basis
  • Top level managers who wish to identify the financial status of the organization

Reason to join advanced excel course:

  • This Course enables the professionals in high-growth industries to score over their peers
  • This course helps increase the visibility in the organization
  • It assists a professional to maximize the value of his/her data, which helps organizations to not only control costs more efficiently but also to obtain vital business information
  • Lessening the burden of work with the right use of functions
  • 89% of companies use MS Excel for budgeting, planning and financial forecasting
  • 70% of companies use MS Excel for solving critical financial processes
  • 83% of companies use MS Excel for month end closing processes


Module 1: Basic and new excel features in different versions of Excel, Types of references to perform complex calculations

  • Introduction of Excel with different Excel versions
  • Work with Comments
  • Copy/Paste, Paste Special
  • Changing the font and font size
  • Changing font color
  • Fill Effects
  • Wrapping text within a cell
  • Cell Formatting
  • Filter, Advance Filter, Color Filter
  • Sorting, Advance Sorting, Custom Sorting
  • Flash Fill Feature in Excel 2013
  • Absolute, Relative and Mixed Cell References
  • Protect sheet and workbook
  • Lock cell
  • Table Feature
  • Freeze and unfreeze panes and Split
  • Excel Keyboard Shortcut Keys

Module 2: Custom Number Format, GOTO Special, Hyperlink, Remove Duplicates and Data Validation

  • Changing the number format
  • Understanding the number format codes
  • Changing the font color with number format codes
  • Adding the text with number format codes
  • Hide Number and Text based on criteria
  • GOTO SPEICAL functionality in Excel
  • Hyperlink from different sheet, workbook, cell, defined names and websites
  • Using Hyperlink function open any website, send email
  • Remove Duplicates
  • Data Validation: Avoid any duplicate entry in range, Create Validation List, create error message and input message etc.
  • Watch Window

Module 3: Name Manager, COUNT Functions, COUNTIF with wild card, Running Count and Arrange Data with Function

  • Define Name using Name Manager
  • Create from Selection
  • Name Convention in Name Manager
  • COUNT, COUNTA, COUNTBLANK
  • COUNTIF,COUNTIFS
  • COUNTIF with wildcard
  • Arrange Data
  • Unique Entry using COUNTIF function
  • Find Duplicates using COUNTIF Function
  • Running Count

Module 4: SUMPRODUCT Function, logical Functions and Grouping and ungrouping in Excel

  • SUMPRODUCT, SUMIF, SUMIFS, Group, ungroup and Subtotal, Freeze Panes and Split
  • Learn to use SUMPRODUCT for COUNT, SUM, AVERAGE, COUNTIF, SUMIF and AVERAGEIF etc.
  • Group Rows
  • Group Columns
  • Ungroup Rows and Columns
  • Subtotal Feature
  • Subtotal Function
  • Logical Functions: IF, Nested IF, AND, OR, NOT, XOR, IFERROR

Module 5: Text functions in Excel to solve complex text problems, text to column functionality

  • Different TEXT Functions used in Excel
  • Using multiple text functions for complex problems
  • Text to column functionality of excel
  • What If Analysis in Excel

Module 6: Date Functions in Excel, creating dynamic calendar for whole months and years

  • Date Functions in excel.
  • Create and Design Calendar using Different Date Functions (WEEKDAY, WEEKNUM, EOMONTH, EDATE) in excel.
  • Calculate Age using DATEDIF Function
  • count working days using NETWORKDAYS Function

Module 7: LOOKUP, VLOOKUP, HLOOKUP function and OFFSET functions

  • VLOOKUP, HLOOKUP, LOOKUP, OFFSET AND CHOOSE
  • 30 Different usage of VLOOKUP
  • VLOOKUP with Exact Match
  • VLOOKUP with Approximate Match
  • OFFSET function with drop down list

Module 8: Array formula, INDEX, MATCH, SMALL, LARGE, ROW and COLUMN functions

  • Advance uses of Array Formula INDEX, MATCH, SMALL, LARGE, ROW,COLUMN
  • Use of INDEX Formula
  • To get INDEX number using MATCH function
  • SMALL and LARGE Functions with array
  • Difference and use of ROW and ROWS functions
  • COLUMN and COLUMNS function
  • Lookup image using INDEX Function

Module 9: Advanced Conditional Formatting and Data Bars

  • Advanced Conditional Formatting
  • Highlight Cell Rule
  • Text that contains
  • Highlight Duplicate cell value
  • Top/Bottom Rules
  • Data Bars
  • color Scale
  • Icon Set
  • using formula in Conditional Formatting

Module 10: Advanced Formula, uses of INDIRECT function, creating range by text strings

  • Advance Formula
  • Create Dependent Validation List using INDIRECT function
  • Create range by TEXT strings
  • Use of ACTIVEX Control
  • Create Dashboard using INDIRECT and ACTIVEX Control

Module 11: Working on Pivots, creating Pivots from different sources and adding fields into it

  • Pivot table, Slicer, GETPIVOTDATA function, Pivot Chart
  • Design pivot table
  • use of slicer
  • Add Calculated Field and Item in Pivot
  • Create Pivot table from multiple sheets
  • Create Pivot table from External Data Sources
  • Introduction to Different types of Pivot Charts
  • Dynamic Pivot Charts

Module 12: Creating dashboards and reports by using excel features and functions, live experience to create dashboard

  • Special Class for Dashboard Creation
  • How to Design Dashboard using Pivot table, Pivot Chart
  • Use of ACTIVEX Control in Dashboard
  • Effective way of using Color Combination
  • Use of Binary format Excel


What is MS Excel?

MS Excel is spreadsheet based software developed by Microsoft in 1985. It can run on various Operating Systems like Microsoft windows, Mac OS X, iOS. It is designed to office work and taking care of records and creating reports out of it. It has various features like calculation, pivot tables and many other functions. It has additional functionality with Macro programming language called Visual Basic for Applications.

What are the pre-requisites for this course?

You need to have basis computer knowledge and understanding of mathematical formulas.

What is live Webinar Classes?

Live webinar class are live virtual class led by expert of particular domain. It is just like class room training. You will be connected through virtual classroom and can clear all your doubts with our Instructor. You can communicate the instructor by using Audio, Video and Chat options.

In case of my absence how can I manage my class?

So you don’t need to worry if you miss your classes. You are responsibility of TechandMate to educate you on the technology you have enrolled. We will generate your learning account (LMS- Learning Management System). In case of missing the class, you will get all the recordings, presentation in your LMS and you can access the same in your leisure time.

Can I access my course module prior to join the class?

No you cannot access course module before enrollment.

Who are the Instructors / Experts / SME?

All our Instructors are experienced and working professional from IT Industry. They have rich experience on the technologies they are leading. Instructors are specially trained by our Learning & Development department to educate and become frontrunner for Live Virtual classes.

Can I see demo or sample class before registering the course?

Yes you can view the demo of class of your course. You can evaluate the teaching style of our SMEs and can further enroll for the course.

Will I get the Software?

Yes we will help you to install the software. We will provide you the link & necessary documents to download the same if it is an open source or demo version available.

Difference between Self Driven and Expert Driven learning?

Our course and training progression is vastly interred. In self-driven learning you will get the access of your LMS with all the modules and learning material available including recording of classes so that you can access it in your leisure time to learn. Expert driven learning would be live streaming webinar and our expert will clear all your doubts instantly. Self-Driven learning is almost 50% cheaper than Expert driven program. You can switch from SDL to EDL any time by paying the difference amount.

How can I access my learning tools?

Once you will enroll yourself for the course training, we will generate a LMS (Learning Management System) for you. All the course modules, learning tools will be available there.

How long I can access my learning tools?

Once you will enroll for the course you can access your learning tools for life time.

Tell me about my payment options?

You can choose any payment option as per your convenience like Credit Card, Debit Card, Net banking. For USD payment, it will be made by PayPal.

Can I convert my amount into EMI’s?

Yes you can convert your amount into EMIs.

What is the process to get the Certification of the course?

After accomplishment of all the modules, you will undergo a Project assessment. After successful submission, our official will review the same & you will be awarded with TechandMate verified Certification for the course.

Will I be working on Project?

Yes you will be working on live project.

What if I have more queries or concerns?

Our technical & Support team is always for your help and available 24x7 for you.


TechandMate certification process

At the end of your course, you will work on a real time Project. You will receive a Problem Statement along with a dataset to work.

Once you are successfully through with the project (reviewed by an expert), you will be awarded a certificate with a performance based grading.

If your project is not approved in 1st attempt, you can take additional assistance to understand the concepts better and reattempt the Project free of cost.



Shubham Bardhan
Shubham Bardhan

After wonderful experience with T&M I can truly say this is the best place to learn technologies. Amazing faculties, great technology and vision to educate people. Getting PPTs, Recordings, Lab Assignment has done it all for me. Cheers for TechandMate

Aslam Ansari
Aslam Ansari

T&M folks it was great involvement to experience your training sessions. Doing assignment on real time scenarios added quality in my learning. You are best in the industry.

Ravi Sharma
Ravi Sharma

Came to know about the practical aspect of work, the environment is like any good start up. The benefits may not be much but if you are looking for the skill set development then this place might be for you. Instructors having good amount of experience in their domain, gives the real time examples and teaches how to apply the same in your work. Learning on latest trends with the help of best technology available in the industry. Certainly TechandMate has made it Easy for me.

Jasmine Kaur
Jasmine Kaur

I thought the context was very well organized and well delivered. The overall structure in initial presentation was carried forward in more detailed discussion which made relating all the aspects of BI easier. Sufficient examples and discussions were provided.

Rakesh Chauhan
Rakesh Chauhan

I was in dilemma when I was forecasting to learn Hadoop online. I had real misgivings to learn this way. Learning through live virtual classes and attending the class for consecutive hours looks odd. But it was other way around; I was quite keen and waiting for next weekend to learn more. Instructor paced the course quiet well and never felt bored at all.