Outlines

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

• Using “$” in formulas

• SUM function

• MAX and MIN function

• AVERAGE and AVERAGEA function

• COUNT and COUNTA function

• COUNTIF & SUMIF function

Module 2 : LOOKUP

• VLOOKUP

• HLOOKUP

• Using VLOOKUP effectively according to your need

Module 3 : Conditional Formatting

• How to use conditional formatting

• Using conditional formatting presets

• Managing and prioritising rules

• Using top bottom rules

• Customising data bars

• Using formulas in conditional formatting

Module 4 : 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 5 : Sparklines

• Inserting sparklines

• Formatting sparklines

Module 6 : Text Function

• Change the case of word easily!

Module 7 : Useful Functions

• ROUND

• RANK

• DATE Calculation