Certificate in

Advance Excel
(includes MS 365)
Learning & Development Academy
by Grant Thornton Bharat
Why MS Excel
With businesses evolving constantly and increasing in complexity, MS Excel has become a popular tool for data analysis in
organisations across the globe. It is used in every function, be it strategy, finance, accounting, HR or sales. Many organisations
rely on Excel to create financial reports, budgets, forecasts and plans that are necessary for making informed business decisions.
MS Excel’s ability to automate calculations and manipulate large data sets saves time and increases productivity. It is a powerful
tool for data analysis because it allows users to sort, filter and analyse data to identify trends, patterns and insights. Knowledge
of Excel is highly valued, and it can help professionals work efficiently, analyse data, make informed business decisions, and gain
a competitive edge in the job market.

Programme objectives Who should attend

• MS Excel is widely used in organisations across
departments. A basic understanding of MS Excel is
Simplify complex spreadsheets important for employees at all levels across verticals (HR,
finance, sales and marketing). Grant Thornton Learning &
Development Academy has designed an advance-level MS
Excel programme which is meant for people working on large
Enhance spreadsheets with templates, spreadsheets and responsible for reporting and managing
charts, graphics and Excel formulas MIS.
and streamline operational work
• The course is also designed for students who want to learn
how to sort and filter data, import and export data sets, and
analyse data using the rich features provided in Excel. It will
Use advanced functions of MS Excel to impart the necessary skills to collaborate with others, audit
improve productivity and analyse worksheet data, incorporate multiple data
sources, and import and export data.

Deliverables
Apply visual elements and advanced
formulas to a worksheet to display
data in various formats +hoursoflearningcontent
• MSExcelEssentials-hours
• MS-hours

Automate common tasks, apply

advanced analysis techniques to more Grant Thornton
complex data sets, and collaborate on Certificate of
Bharat
completion
worksheets with others presentations as
course material

Two-way
communication Quiz and final
and query
assessment
resolution from
an expert

LMS access of
Case studies
one year

Certificate in Advance Excel (includes MS 365)

Course coverage Certificate
Certificate of completion post-assessment

Fees
MS Excel Essentials: Functions and Tools INR, +GST
Data management and formatting Programme Manager: Surubhi Sharma
Referencing in Excel M: +91 81309 79069
E: [email protected]
Data split
Data validation
Using Range Names
Adding hyperlinks
Excel Table
Logical functions
What If Analysis
Lookup functions
Protecting Worksheet and Workbook
Conditional functions
Dashboards and Data Summarization
SUMPRODUCT function
Large and small values
Microsoft 365 Excel
MS-365 Excel overview
Data concatenation
Create dynamic sequence
Working with dates and time
Unique, sort and filter
Financial functions
New age lookup
Text-oriented functions
Text-oriented functions
Aggregate function
New data types
OFFSET function
Create customised functions in Excel
INDIRECT function
Other functions
Formula auditing
Assessment and study material
Statistical functions

Detailed course outline

MS Excel Essentials: Functions and Data validation
• Data validation with different validation criteria
Tools • Giving comments using input messages in data validation
• Creating in-cell drop downs
Data management and formatting
• Setting up data in an appropriate structure and applying Adding hyperlinks
initial formatting in the worksheet • Including static hyperlinks
• Remove duplicate records, conditional formatting, data • Using functions to add hyperlinks
sorting and applying filters Logical functions
• Including in-cell data bars and icons for data presentation • IF function to check condition and display result
and categorisation • Nested IF vs IFS function
Referencing in Excel Lookup functions
• Referencing in Excel - Absolute, Relative, and Hybrid • Looking for values in a vertical layout and horizontal layout
referencing using conventional VLOOKUP and HLOOKUP functions
• Practical cases to apply referencing concepts while pasting • Lookup values using an approximate match
a formula • Lookup using INDEX and MATCH combination
Data split • Using INDEX with reference to multiple tables
• Split data from a single column to multiple columns
• Using flash fill to automatically fill in values
Conditional functions INDIRECT function
• COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS and MINIFS functions • Using INDIRECT function to refer to a cell/range
to perform conditional computation • Case study on data consolidation using
SUMPRODUCT function INDIRECT function
• Return the sum of product of two or more arrays Formula auditing
• Using SUMPRODUCT to work with other mathematical • Tracing precedents
operations • Tracing dependents
• Checking conditions using SUMPRODUCT • Evaluating formula
• Working with cross-tabulated data Statistical functions
Large and small values • Computing median, mode, quartiles and
• Getting k-th largest and smallest value from a list percentiles
Data concatenation • Getting standard deviation, variance, covariance,
correlation and slope
• Combining data using & symbol
• Practical case to computed levered beta of a
Working with dates and time stock
• Correct way to input dates
Using range names
• Understanding date-related functions like DAY, MONTH, YEAR,
• Creating range names
YEARFRAC, EDATE, EOMONTH and DATE
• Using range names in formulas
• Working with times and understanding time-related functions
like HOUR, MINUTE, SECOND and TIME • Creating range names from headings
• Practical cases to apply date-related and time-related • Deleting range names
functions Excel Table
Financial functions • Converting range of data into Excel Table
• Computing present value and future value using PV and FV • Using Table designs to format tabular data
functions • Entering formula in Table
• Computing loan installments using equated repayment • Converting Table to Range
structure with PMT, IPMT and PPMT functions What If analysis
• Getting cumulative interest and principal repayments over a • Using data table to run sensitivity analysis
given period using CUMIPMT and CUMPRINC functions
• Using goal seek to arrive at a desired output
• Computing effective rate of interest using the RATE function value
Text-oriented functions • Solving optimisation problems using Solver add-in
• Using LEFT, RIGHT, MID and LEN functions to extract a given Protecting worksheet and workbook
number of characters from a text in a cell
• Protecting a worksheet
• FIND vs SEARCH functions
• Protecting a range of cells with password
• TEXT function to display data in a given format
• Protecting a workbook
Aggregate function • Password protect the Excel file
• Working with visible data using the AGGREGATE function Dashboards and data summarisation
• Ignore error values while data computation
• Overview and prerequisites for creating
• Ignore subtotals dashboards
OFFSET function • Using pivot tables to manage data, grouping
• Reference to a single cell using OFFSET function data and inserting slicers
• Generating a range of cells with OFFSET • Case studies to create dashboards for finance,
• Case studies to understand practical usage HR and sales professionals
 • Getting stock prices and allied details with stocks data type
Microsoft 365 Excel • Using geographies data type to get economic, demographic
M-Exceloverview or geographical parameters
• Working with dynamic arrays • Returning historical stock prices or currency rates using
STOCKHISTORY function
• Formulas can now spill
Create dynamic sequence Create customised functions in Excel
• Creating reusable functions in Excel without coding using
• Creating a dynamic sequence of numbers using SEQUENCE
LAMBDA function
function
• Assigning names to calculation results inside a formula
• Creating a sequence of month-end dates for the given year
using LET function
• Creating a dynamic loan amortisation schedule
• Using lambda helper functions like BYCOL, BYROW, MAP,
UNIQUE, SORT and FILTER
SCAN and REDUCE
• Returning unique values dynamically using UNIQUE function
• Sorting data in ascending or descending order using SORT
Other functions
and SORTBY functions • Returning columns or rows from an array using
• Filtering data based on a given set of conditions CHOOSECOLS/CHOOSEROWS functions
• Computing conditional median • Vertically or horizontally stacking arrays using VSTACK/
HSTACK functions
New age lookup
• Returning the array as one column or one row with TOCOL/
• Using XLOOKUP as a robust alternative to conventional TOROW functions
lookup functions • Wrapping a row or a column after a specified number of
• Looking for a value in a range or an array to return a value values with WRAPCOLS/WRAPROWS functions
from another range or an array • Using TAKE and DROP functions to take or drop array from
• Using XMATCH to return the relative position of an item in an start or end
array • Expanding an array to the specified dimensions with
Text-oriented functions EXPAND function
• Working with text data dynamically • Using ARRAYTOTEXT to return an array of text values from a
• Using TEXTBEFORE and TEXTAFTER to return text that is given range
before or after delimiting characters • Returning an array of random numbers with RANDARRAY
• Splitting text into rows or columns using delimiters with function
TEXTSPLIT function Assessment and study material
New data types • Proficient in Microsoft Excel, with advanced knowledge in
• Fetching currency rates directly in Excel using the currencies creating and formatting spreadsheets, utilising formulas
data type and functions, creating charts and graphs, and performing
data analysis

Why Grant Thornton

India’s Top 100 Most Attractive Customised and cost-effective solution
Employers Universum 2017-2022 with post-training support

Base in over 135 countries, with 62,000+

people and one of the largest fully Access to a network of international
integrated Assurance, Tax & Advisory firms with relevant experience
firms in India

Standard, globally accepted Knowledge of International GAAP,

training methodology commercial application

Certificate in Advance Excel (includes MS 365)

About Learning & Development
Academy by Grant Thornton Bharat
Learning & Development Academy is an upskilling service offering of Our other offerings
Grant Thornton Bharat providing result-oriented finance and accounting
courses to professionals and students. Our impactful programmes can be Business Intelligence using Power BI and Tableau
customised to help you gain skills and capabilities in your area of interest. Diploma in IFRS ACCA
In a digital age, we strongly believe in retaining the advantages of a Finance for Non-Finance Professionals
classroom environment, making the most of group learning while paying Certificate in GST
attention to resolving individual queries. Our trainers are seasoned experts
who will share their journey along with best practices followed by the Certification in Data Analytics using R
industry. Blockchain Training

The Learning & Development Academy will leverage our Firm’s rich Certification in Cyber Security Fundamentals
experience in different domains across various industries and its US Certified Public Accountant
knowledge base to share practical insights, helping participants
understand and learn complex concepts. ACCA Qualification – Association of Chartered
Certified Accountants

Learning & Development solution

• Exam-based courses – ACCA, CPA, etc.
Trained 13,000+ participants • Skill-based courses – accounts,
finance, analytics, compliance, etc.

Mode of delivery • Assessments

• Live online • Doubt sessions
• Self-paced e-learning • Mock tests

University tie-ups 250+ corporate trainings

Contact us:
Kapil Arora Pradeep Pandey Rohit Gupta
Director Director Associate Director
M: +91 9654190274 M: +91 7838000185 M: +91 98913 95363
E: [email protected] E: [email protected] E: [email protected]

© 2023 Grant Thornton Bharat LLP. All rights reserved.

“Grant Thornton Bharat” means Grant Thornton Advisory Private Limited, the sole member firm of Grant Thornton International
Limited (UK) in India, and those legal entities which are its related parties as defined by the Companies Act, 2013, including
Grant Thornton Bharat LLP.
Grant Thornton Bharat LLP, formerly Grant Thornton India LLP, is registered with limited liability with identity number AAA-7677
and has its registered office at L-41 Connaught Circus, New Delhi, 110001.
References to Grant Thornton are to Grant Thornton International Ltd. (Grant Thornton International) or its member firms. Grant
Scan the QR code to view
Thornton International and the member firms are not a worldwide partnership. Services are delivered independently by the
our office addresses
member firms.