Programs

Course Content:

SECTION 1

ADVANCED WORKSHEET FUNCTIONS.

CONDITIONAL & LOGICAL FUNCTIONS

• If Statements
• Logical Test Value
• If True / False
• Nested If

COUNTING AND TOTALLING CELLS CONDITIONALLY

• Statistical If Statements
• Sumif
• Countif
• Averageif
• Averageifs
• Sumifs
• Countifs
• AND, OR, NOT
• Iserror
• Iferror
• LOOKUP FUNCTIONS
• Lookup
• Vector Lookup
• Hlookup
• Vlookup
• Nested Lookups

SECTION 2

USING EXCEL TO MANAGE LISTS

• EXCEL LISTS,
• LIST TERMINOLOGY
• Row and Column Column Labels
• List Size And Location
• SORTING DATA
• Quick Sort
• Multi Level Sort
• Custom Sorting Options
• Creating a Custom Sort Order SUBTOTALS
• Organizing the List for Subtotals
• Create Subtotals
• Summarizing a Subtotaled
• List
• Show and Hide by Level Remove Subtotals
• FILTERING A LIST
• Auto filters
• Search
• Criteria - Custom Criteria and – Or Wildcards
• Turning off Auto filter

ADVANCED FILTERING

• Set Criteria in Advanced Filter
• CRITERIA
• MULTIPLE CRITERIA
• Using Multiple Rows in the Criteria
• Range
• CALCULATED CRITERIA
• Basic Calculation
• Calculated Criteria Using Functions
• Copying Filtered Data
• Unique Records
• Database Functions
• PIVOTTABLES
• Styles
• SLICERS

SECTION 3

CHARTS

• CHARTING
• CREATING CHARTS
• Embedded
• Data Layout
• Shortcut Menu (Right
• Chart Types
• Default Chart Type
• FORMATTING
• Formatting Chart Elements
• Resetting Custom Formats
• Format
• SPARKLINES
• What are Spark lines?
• Create Spark lines
• Customize Spark lines
• Axis options

SECTION 4

TEMPLATES

INTRODUCTION TO TEMPLATES

• Template Types
• Normal Template
• Sample
• Create Custom Templates
• To Use Custom Templates
• Opening and Editing Templates
• Template Properties
• Auto templates

SECTION 5

EXCEL TOOLS

• Comments
• Protecting
• Tracking
• Use a Shared Workbook To Collaborate
• Share a Workbook
• Links
• Working With a Shared Workbook
• Conflicts
• Stop Sharing
• AUDITING
• Tool Information
• Go To Special
• Error Checking
• Correct an Error Value
• Watch Window
• Dependants and Precedents
• PROOFING TOOLS
• Spelling and Grammar
• Thesaurus
• Translation
• Show or Hide

Section 6

 Project

Dash Board

• Visualization concept
  1. Tell your story :

Build a compelling story to communicate your data in a more appealing way by learning the following:

• Story structure.
• How to craft a story.
• How to tell a story.
  1. Keep it simple:
• Know the different chart types.
• When to use every chart.
• How to transform complex chart to simple chart.

1. Using Colors :

• The role of the colors.
• Colors meaning.
• Colors schemes.

1. Drive the focus:

How to grab the attention of your customer by using different techniques.

1. Say it with pictirues :
   Know how to use pictures to make your story more powerful.

• Data Analysis
• Explore pivot table options.
• Chart Formatting .
• Formatting numbers.
• Root Cause analysis using pivot table:
  How to extract insights from your data using a pivot table.
• Scatter plot analysis:

How to use the scatter plot chart to do segmentation for the data .

• Dashbord creation:
• How to design a dashboard.
• Create a summary dashboard.

Visualize the insights in a detailed dashoard