Learn the Tools & Techniques you can apply in MS Excel to Analyze and Manage data to ensure that your work is well organized and helps you to calculate and visualize quantitative data thus helping you make important statistical and financial decisions at your workplace.
Learning outcomes include
Apply advanced formatting options, such as conditional formatting and customized number formatting, and handle worksheets.
Use functions such as those associated with logical, statistical, financial, and mathematical operations.
Create charts and apply advanced chart formatting features.
Work with tables and lists to analyze, filter, and sort data. Create and use scenarios.
Collaborate on and review spreadsheets. Apply spreadsheet security features.
Duration: 20 Hours
Entry Requirement: Basic knowledge of MS- Office and MS–Excel.
Certificate: Certificate of participation in Advanced Microsoft Excel
Software Training: – MS Excel 2016
Please Note:-
Study Materials: Students will get a printable softcopy of the study materials developed by ICDL. Along with the study materials they will also get revision papers to assist in the preparation for exams.
Exam Retakes: All of the above fees are inclusive of the ICDL registration and first exam attempt. A cost will be incurred for resits of the exams as required.
Award of Certifications: After the completion/ passing of each exam module, the candidate will get certification from ICDL. These certificates will include a list of all the module exams passed by candidates to date.
Faculty Qualifications: All of our teaching staff will attend special training organised by ICDL and will be certified by ICDL as a trainer.
Module 1. Custom & conditional formats
In this module, we’ll explore Custom and Conditional Formats in Excel, enhancing your skills to create
visually appealing and informative spreadsheets. Here’s what you’ll learn:
Conditional Formatting: Become proficient in applying conditional formatting to both numbers and text.
This skill will enable you to emphasize data patterns and trends within your spreadsheets, making your
information more insightful.
Custom Number Formats: Gain the ability to create unique custom number formats, allowing you to
effectively communicate with your audience and make your data more understandable.
Custom Cell Styles: Discover how to design and apply custom cell styles to give your spreadsheets a
consistent and professional look. Your data will stand out with a polished and uniform appearance.
Module 2. Advanced Functions – Part 1
We will delve into Excel’s Advanced Functions, equipping you with the skills to handle complex
calculations. You’ll start with Formula Basics and Named Ranges, and then progress to advanced Excel
functions…
Formula Basics: Start by mastering the fundamental principles of Excel formulas, setting a strong
foundation for advanced functions.
Named Ranges: Discover how to create and use named ranges to simplify formulas, enhance clarity, and
make your data more maintainable in Excel.
Math and Statistical Functions: Delve into functions like COUNTIFS, SUMIFS, AVERAGEIFS, MAX, MIN,
and more. These functions play a vital role in data analysis and allow you to perform various mathematical
and statistical operations on your data, helping you derive meaningful insights and make informed
decisions.
Logical Functions: Learn to use functions like IF, AND, OR, NOT, and nested IF functions. These are
crucial for decision-making and allow you to set up complex criteria and perform operations based on
specific conditions.
Lookup and Reference Functions: Explore functions like VLOOKUP, XLOOKUP, INDEX, MATCH, and
OFFSET. These functions assist in retrieving data from large datasets by searching for specific values and
returning corresponding data.
Module 3. Advanced Functions – Part 2
Continuing from where we left off, Module 3 will further enhance your proficiency in advanced Excel
functions:
Text Functions: Explore functions like CONCAT, TEXTJOIN, LEFT, RIGHT, MID, SEARCH, TEXT,
SUBSTITUTE, and more. Text functions are used in data cleaning, combining, extracting, or modifying text
strings.
Date and Time Functions: Learn how to calculate date and time differences, extract components of date
and time, and handle time-related data with ease. We’ll cover functions like DATE, TIME, NOW, TODAY,
EOMONTH, EDATE, NETWOKDAY.INT, and more.
Finance Functions: Gain expertise in finance functions indispensable for financial modelling and analysis,
including NPV, IRR, PMT, and FV. These functions are used for calculating critical financial parameters.
Module 4. Data Organisation & Validation
In this module, you will explore a comprehensive set of tools and techniques to enhance your data
organization and validation skills in Excel. The following detailed topics will be covered:
Duplicate Identification and Removal: Discover how to identify and remove duplicates in your data to
maintain data integrity.
Text to Columns: Learn how to split text into different columns for analysis using the “Text to Column”
feature, based on a delimiter (e.g., comma, space, or a custom character).
Flash Fill: Explore the use of the Flash Fill tool to automate the process of splitting text efficiently.
Data Validation: Understand how to use data validation to ensure that your data is accurate and
consistent by defining constraints on the type of data that can be entered into a cell.
Worksheet and Workbook Protection: learn how to protect individual worksheets as well as securing the
whole workbook. Explore the concept of allowing users to edit specific ranges within protected
worksheets.
Excel Tables or List Objects: Discover the benefits of using Excel Tables or List Objects to keep your data
organized and dynamic.
Advanced Sorting and Filtering: Learn how to carry out custom sorting and advanced filtering of data,
enabling you to efficiently manage and analyse your datasets.
Module 5. Summarising and Analysing Data
This module will equip you with the tools to efficiently summarize and analyse data. You’ll learn Pivot
Tables for quick insights and Power Query for data import, as well as What-If Analysis by use of scenario
manager, data tables and goal seek.
Pivot Tables: you will learn how to use Pivot Tables to summarize and analyse large datasets in Excel
quickly and effectively. You’ll understand how to create dynamic tables that can instantly calculate sums,
averages, counts, and more, giving you the ability to extract meaningful insights from your data.
Power Query: you will discover how to import data from various sources into Excel. You’ll learn to
seamlessly bring data from text files, databases, the web, or other Excel workbooks. You will master the art
of connecting to multiple data sources, cleaning and transforming data into the format you need for
analysis.
Spreadsheet What-If Analysis: you will learn how to use Scenario Manager to perform What-If Analysis,
by changing input values and observing their impact on results. You will also learn how to perform
sensitivity analysis using Data Tables.
Goal Seeking: You will learn how to set a target result and let Excel automatically adjust a specific input
value to achieve that goal.
Module 6. Advanced Charting
In this module, you’ll dive into the fascinating realm of data visualization and learn how to create
captivating charts to effectively convey insights. you’ll explore:
Customizing Chart Elements: In this section, you will become a master at customizing every aspect of
your charts. Learn how to format axes, gridlines, titles, and data labels, ensuring your charts are not only
visually appealing but also informative.
Combination Charts: Combination charts allow you to plot multiple data series with different chart types
on a single chart. You’ll discover how to combine line and column charts, bar and scatter plots, and other
combinations.
Advanced Chart Types: In this part, you’ll explore more advanced chart types beyond the basic bar,
column, and line charts. You’ll delve into Gant charts, area charts, bullet charts, bubble charts, and maps,
among others.
Dynamic Charting with Pivot Charts: You’ll explore how to create dynamic charts that automatically
update as new data is added and when changes are made to the pivot tables. You will also learn how to
control specific charts with slicers by defining slicers report connections.
Module 7. Introduction to VBA Macros
In this module, you will delve into the world of VBA (Visual Basic for Applications) Macros, empowering you
with automation skills to enhance your Excel proficiency. The topics covered include:
Introduction to VBA Macros: Gain a fundamental understanding of VBA macros, their applications, and
how they can simplify your tasks in Excel.
Recording VBA Macros: Learn how to record macros, allowing Excel to automatically generate VBA code
based on your actions. This is a quick and easy way to automate repetitive tasks.
Running VBA Macros: Explore how to execute VBA macros, enabling you to automate a wide range of
actions and processes in Excel.
Editing a Macro: Master the techniques for editing and fine-tuning your VBA macros to ensure they
perform exactly as desired.
Macro Security: Understand the importance of macro security in Excel, ensuring a safe and secure
automation environment.
