Course Code: exbspk
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. Using search functions to find data
    1.1. Using the VLOOKUP() function
           1.1.1. Usage
           1.1.2. Limitations
    1.2. Using the INDEX() and MATCH() function

2. Validating your data
    2.1. Implementing Data Validation rules
    2.2. Finding invalid data

3. Analyzing data using PivotTable
    3.1. Using Calculated Fields
    3.2. Using Calculated Items
    3.3. Filtering data using Slicers and TimeLines

4. Visualizing PivotTable data using PivotCharts
    4.1. Formatting PivotCharts to better present the output

5. Essential reporting requirement skills
    5.1. Rules of pivot tables and pivot charts
    5.2. Retrieving External Data Using Microsoft Query
    5.3. Importing Text Files Using MS Query

6. Building Report Solutions
    6.1. Conceptualizing and Understanding Report Solutions
    6.2. Configuring Spreadsheet Report Data Options
    6.3. Enabling Background Refresh
    6.4. Refreshing Data When Opening the File
    6.5. Combo-box modeling
    6.6. List-box modeling
    6.7. Spinner
    6.8. Option-button modeling
    6.9. Check-box data models