This course will enable participants to streamline repetitive tasks and display spreadsheet data in more visually effective ways, where participants will gain the skills necessary to manage worksheets, sort & filter data, create charts, and use Functions. This course is also suitable for those interested in step-up a level with existing knowledge of the basics of Excel, including how to create, edit, format, and print basic worksheets.

In addition, this course is also designed for those who are geared up to speed with Microsoft Excel but would now like to automate some common tasks and apply advanced analysis techniques to more complex data sets. This course is aimed at someone desiring to gain the skills necessary to learn advanced formulas, create macros, use What If Analysis tools, create Pivot Tables and Pivot Charts, subtotalling and securing your data.

Programme Structure

Day 1:

Managing Worksheets

Organizing Worksheets, Working with Multiple Worksheets, Finding and Replacing Data

Format as Table

Using Quick Style, Removing Style from a Table, Defining a Title, Using the Total Row Command, Removing Duplicates, Using a Slicer to View Table Data, Converting a Table into a Range

Using Conditional Formatting

Apply a Specific Conditional Format, Apply Multiple Conditional Formatting Rules, Use the Rules Manager to Apply Conditional Formats

Data Validation

Restrict Cell Entries to Certain Data Types, Allow Only Specific Values to Be Entered into Cells, Remove Duplicate Rows from a Worksheet

Sorting Data

Sort Data on a Single Criterion, Sort Data on Multiple Criteria, Sort Data Using Cell Attributes

Filtering Data

Using AutoFilter, Creating a Custom AutoFilter, Filtering Data Using Cell Attributes

Creating Charts

Building Charts, Formatting a Chart with a Quick Style, Formatting the Parts of a Chart Manually, Modifying a Chart, Using New Quick Analysis Tools

Using Functions

Displaying Dates and Times with Functions, Summarizing Data with Functions, Uncovering Formula Errors

Day 2:

Using Advanced Formulas

Using Formulas to Conditionally Summarize Data, Using Formulas to Look Up Data in a Workbook, Adding Conditional Logic Functions to Formulas, Using Formulas to Modify Text

Securing Your Work with Passwords

Protecting a Worksheet, Protecting a Workbook

Day 3:

Outlining and Subtotaling Data

Grouping and Ungrouping Data, Auto-Outlining Data, Collapsing Groups of Data in an Outline, Subtotaling Data in Outlines

PivotTables and PivotCharts

Creating a Basic PivotTable, Adding a PivotChart

What If Analysis

Using PMT function as a Platform, Goal Seek, Data Table, Scenario Manager

Programme Duration

3-day programme – 1st, 8th & 9th of March 2023

Entry Requirement

Participants should be familiar with using computers and have used a mouse and keyboard. Participants should be comfortable in the Windows environment and be able to use Windows to manage information on their computer. To get the most out of this course, we recommend completion of one of the following courses, or equivalent knowledge from another source: Windows 7 and above, Microsoft Excel 2019 Basic and Microsoft Excel 2019 Intermediate.


Research Management Centre, International Islamic University Malaysia