About
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.
Venue
Research Management Centre, International Islamic University Malaysia
