Course Code: excelbspk
Duration: 7 hours
Prerequisites:
  • Knowledge of Windows and the basics of MS Excel
Overview:

This training is intended to provide the learner with the knowledge to work with data in MS Excel, analyze it using formulas and functions, and using the pivot tables and charts to create reports to explore and drill-down the data to discover new insights.

Course Outline:

1. Working with formulas
      1.1. A refresher about the Reference types in MS Excel
      1.2. Using Range Names
      1.3. Using functions in MS Excel
            a) Statistical Function
                  • SUM(), SUMIF()
                  • COUNT(), COUNTA(), COUNTIF()
                  • AVERAGE(), AVERAGEIF()
            b) Logical Functions
                  • IF(), AND(), OR()
            c) Text Functions
                  • LEFT(), RIGHT(), CONCAT(), MID(), FIND(),LEN()
            d) Date Functions
                  • DAY(), MONTH(), YEAR(), DAYS()

2. Using search functions to find data
      2.1. Using the VLOOKUP() function
            a) Usage
            b) Limitations
      2.2. Using the INDEX() and MATCH() function

3. Validating your data
      3.1. Implementing Data Validation rules
      3.2. Finding invalid data

4. Analyzing data using PivotTable
      4.1. Using Calculated Fields
      4.2. Using Calculated Items
      4.3. Filtering data using Slicers and TimeLines

5. Visualizing PivotTable data using PivotCharts