Skip to content
Program
EXCEL ADVANCED FORMULAS & TECHNIQUES FOR DATA ANALYSIS
Program
NO.
CONTENT
CONTENT 2
1
A
INTRODUCTION
INTRODUCTION
2
3
B
Getting to know participants’ challenges
Participants’ wish-list (course expectations)
Getting to know participants’ challenges
Participants’ wish-list (course expectations)
4
C
Introducing course material and the course model
Introducing course material and the course model
5
D
Introducing Data Analysis – The concept, the data fields, the analysis dimensions and the analysis methods.
Introducing Data Analysis – The concept, the data fields, the analysis dimensions and the analysis methods.
6
7
GETTING STARTED: UNDERSTANDING THE EXCEL ENVIRONMENT
GETTING STARTED: UNDERSTANDING THE EXCEL ENVIRONMENT
8
E
The Microsoft Excel Environment
Brief discussion on the Excel Environment Including Ribbon, Tabs, Groups, Contextual Tabs, Quick Access Toolbar, Name Box, Formula Bar, Row & Column Headers, Cells, Commands, etc.
9
10
GETTING READY TO ANALYSE DATA: LEARNING FORMULAS & FUNCTIONS
GETTING READY TO ANALYSE DATA: LEARNING FORMULAS & FUNCTIONS
11
F
Functions Introduction
Excel Functions Library, Golden Function rules when working in excel
12
G
Formula Referencing
Copying formulas down and across workbooks – Relative, Mixed & Absolute referencing.
13
H
Date & Time Functions
Extracting the YEAR, MONTH, DAY, WEEKDAY and WEEKNUM from dates for further multi-dimensional analysis. Introducing Date & Time functions: TODAY(), NOW(), HOUR, MINUTE, SECOND, EOMONTH, NETWORKDAYS, YEARFRAC
Performing basic time calculations
14
I
Static Naming of Cells & Ranges
STATIC NAMING OF CELLS & RANGES: The fulcrum of model development: Giving names to cells and ranges statically and their practical application.
15
J
Text Functions
Introducing Text functions: CONCATENATE, CONCAT, LOWER, UPPER, PROPER, TEXT, EXACT, SEARCH, FIND, REPT and their application.
16
K
Logical functions
Dynamic logical functions – using the IF function and understanding different types of ERRORS & use of the IFERROR, AND, OR, NA, IFNA FUNCTIONS.
17
Information (IS) functions
ISERROR, ISERR , ISLOGICAL, ISTEXT, ISEVEN, ISODD, ISNUMBER, ISNA, ISBLANK, ISNONTEXT
18
L
Math & Trig functions
Introducing some useful Maths & Trig functions: ABS, AGGREGATE, SUBTOTAL, SUMIF, SUMIFS, ROUND, ROUNDUP, ROUNDDOWN, PRODUCT, SUMPRODUCT
19
M
Lookup and Reference Functions including Newer Lookup functions
LOOKUP function
Static VLOOKUP & HLOOKUP functions
XLOOKUP explained
INDEX & MATCH functions, XMATCH, OFFSET,
ROW, ROWS, COLUMN, COLUMNS,
CHOOSE, INDIRECT, ADDRESS, TRANSPOSE, HYPERLINK
20
21
TECHNIQUES FOR PREPARING DATA FOR ANALYSIS
TECHNIQUES FOR PREPARING DATA FOR ANALYSIS
22
N
Splitting Data Fields
Converting Text to columns, and the use MID, RIGHT, LEFT, LEN, VALUE, in manipulating text.
23
O
Combining Data Fields & Other Techniques
The TRIM, CLEAN, SUBSTITUTE, REPLACE, CONCATENATE Functions
Using the ampersand (&) to join text
24
P
Workbook Consolidation and linking techniques
Consolidating multiple files using summing through sheets technique, consolidation by category and by position as well as the best practice method of linking spreadsheets.
25
26
ANALYZING AND SUMMARISING THE DATA USING FORMULAS & CHARTS
ANALYZING AND SUMMARISING THE DATA USING FORMULAS & CHARTS
27
Q
Readying Data for Analysis
Preparing Data for Analysis by naming columns and creating calculated fields. Building the report template.
28
R
Statistical & Math functions
Extracting useful information from data using COUNTING, SUMING, AVERAGING , MIN, MAX, LARGE, SMALL, MODE functions.
29
S
Charts intro
CHARTS & CHARTS FORMATTING IN BRIEF. How to quickly create appropriate and various kinds of charts and to give them a professional look.
30
T
Variance Analysis
Analysing budget variances and adding dynamic comments to variance reports.
31
U
Financial Functions
Depreciation functions
32
ANALYZING DATA USING EXCEL BUILT-IN DATA MANAGEMENT TOOLS
ANALYZING DATA USING EXCEL BUILT-IN DATA MANAGEMENT TOOLS
33
V
Sorting & Filtering data
Single, Multilevel sorting
Auto filter, Advanced filter
34
W
Create Custom lists
Creating custom lists in excel
35
X
Get External Data
Importing data from a text file into excel
Creating a Data Connection to an online data source
36
Y
Data Validation
Predefined data validation, Custom validation using formulas
Creating a dynamic data validation list
37
Z
Steps to Data Analysis
Naming of Cells and ranges statically and DYNAMICALLY using OFFSET function
38
AA
Excel Tables
Converting a range to a table and the benefits thereof.
39
AB
Pivot Tables & Charts intro
Multi-dimensional data analysis using Pivot Tables & Pivot Charts.
Exploring features of pivot tables; calculated fields, slicers, timelines
40
41
REPORT FORMATTING AND PRESENTATION
REPORT FORMATTING AND PRESENTATION
42
AC
Conditional Formatting
Applying conditional formatting rules to cells and ranges
Identifying and removing duplicate data records
43
AD
Grouping & Ungrouping
Grouping & Ungrouping tools for data summarisation & drill down
Using Subtotal in grouped data
44
AE
Excel meets Word
Smart Reporting - Combining Microsoft Excel and Microsoft Word in reports.
45
46
AF
Course Recap
Summarizing the course
47
AG
Course Evaluation & Review
There are no rows in this table
To Download PDF Program, click link below :
Course Program 2023.pdfCourse Program 2023.pdf127.6 kB
Course Program 2023.pdf
Course Program 2023.pdf
127.6 kB
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.