Outlines
MICROSOFT EXCEL 2010/2013/ 2016/2019/365/2021 : BEGINNER TO INTERMEDIATE
Course Objectives
The objectives of this course is to enable participants to understand intermediate Excel functions such as SUMIF, COUNTIF, VLOOKUP, HLOOKUP and how to use them to perform basic data analysis. Conditional formatting also will be taught so participants will know to highlight data based on specific criteria, such as color scales, data bars, and icon sets. Some useful Date function also will be taught.
The course also covers how to create advanced charts and graphs, such as combination charts to better illustrate data trends and relationships. On top of charts, The course also shows how to use advanced data visualization techniques, such as sparklines, data bars, and conditional formatting icons, to provide quick insights into data trends and patterns
Course Outline
Module 1 : Calculating With Formulas
How formulas work
SUM function
MAX and MIN function
AVERAGE and AVERAGEA function
COUNT and COUNTA function
Using “$” in formulas (Locking)
Module 2 : Calculations with Conditions
COUNTIF & SUMIF function
COUNTIFS & SUMIFS function
Module 3 : LOOKUP
VLOOKUP
HLOOKUP
Using VLOOKUP effectively according to your need
Module 4 : Conditional Formatting
How to use conditional formatting
Using conditional formatting presets
Managing and prioritizing rules
Using top bottom rules
Customizing data bars
Using formulas in conditional formatting
Module 5 : Charts
Creating a chart
Using Excel’s recommended charts
Formatting charts
Changing chart data
Filtering chart data
Creating a dual axis line chart
Saving a chart template
Module 6 : Sparklines
Inserting sparklines
Formatting sparklines
Module 7 : Text Function
Change the case of word easily!
Merging multiple cells
Removing spaces
Counting number of characters in a cell
Splitting cell contents
Module 8 : Useful Functions
ROUND
RANK
Module 9 : Date Calculations
YEAR
MONTH
DAY
DATEDIF
NETWORKDAYS / NETWORKDAYS.INTL
WORKDAY / WORKDAY.INTL
EDATE
EOMONTH
