Microsoft Excel 2016- Advanced Level

In this course, the participant will be introduced to the more advanced features of Microsoft Excel 2016. They will learn how to manage, filter and generate reports from Excel data. In addition they will learn how to summarize and analyse data using tools, and how to create macros to automate some repetitive tasks, as well as how to protect worksheets and workbooks

Target Audience:

This course is intended for students who are experienced Excel 2016 users and have a desire or need to advance their skills in working with some of the more advanced Excel features

Pre-requisite:

An understanding of MS Excel 2016 basic and intermediate level is required

Objectives:

By the end of the course, participants will be able to:

  • Build complex calculations with advanced functions
  • Filter and summarise worksheet data
  • Limit data entry using data validation and restrict changes by protecting workbooks and worksheets
  • Analyse data with simple Tables, PivotTables and Pivot Charts
  • Creating macros to automate repetitive tasks in Excel

Assessment Strategy:

  • Pre and Post training test to measure knowledge and learning levels
  • Engage trainees in exercises and group activities
  • Evaluation form to be filled by trainees

Module 1: Quick Review

Module 2: Using Conditional Formats

  • Applying Conditional Formats
  • Changing a Conditional Format
  • Adding a Conditional Format
  • Creating a Custom Conditional Format

Module 3: Using Templates

  • Saving a Workbook as a Template
  • Using a Template
  • Editing a Template
  • Finding Online Templates

Module 4: Working with Databases

  • Creating Subtotals in a List
  • Sorting Data by Multiple Levels
  • Using Custom Sort
  • Using Data Validation
  • Creating a Custom Error Message

Module 5: Revising Charting Features

  • Formatting an Axis
  • Formatting the Data Series
  • Adding Data from Different Worksheets
  • Changing Data Series Chart Types
  • Changing Source Data Range

Module 6: Pivot tables

  • Adding PivotTable Report Fields
  • Changing the Summary Function
  • Creating a Page Field Report
  • Formatting a PivotTable Report
  • Creating a PivotChart Report
  • Grouping Data Manually

Module 7: Lookup and search Functions

  • VLookup function
  • HLookup function
  • Using the IF Function
  • Using the ISERROR Function
  • Using the ROUND Function
  • Using COUNTIF Function
  • Using RANK Function
  • Using Financial Functions
  • Using Text Functions

Module 8: Using Scenarios

  • Creating a Scenario
  • Creating a Scenario Summary Report
  • Working with Data Tables
  • Placing Formulas in Data Tables
  • Creating a One-Variable Table
  • Creating a Two-Variable Table

Module 9: Using Worksheet Protection

  • Unlocking Cells in a Worksheet
  • Protecting a Worksheet
  • Creating Allow-Editing Ranges
  • Protecting Workbook Windows
  • Assigning a Password
  • Opening a Password-protected File
  • Removing a Password

Module 10: Using Macros

  • Recording a Macro
  • Saving a Macro-Enabled Workbook
  • Running a Macro
  • Assigning a Shortcut Key
  • Deleting a Macro
  • Adding a Macro to Quick Access Toolbar

Module 11: Comments

  • Inserting Comments
  • Viewing Comments
  • Reviewing Comments
  • Printing Comments
Experience Level:
How did you hear about us?
Code: IT2201
Language: Ar & En
Duration: 5 Days