Course Details
Course Code: EX19I- 1; Instructor-led
Audience
This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.
Prerequisites
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
- Have attended Microsoft Excel – Foundation Level; OR
- Able to switch between task applications
- Able to create a spreadsheet with simple formatting
- Able to create a basic chart
- Able to print a spreadsheet with headers and footers added
- Able to use Auto Filter command
- Able to apply Freeze Pane command
- Able to create basic formulas – Addition, Subtraction, Multiplication and Division
- Able to use basic functions – AutoSum, Count, Max, Min and Average functions.
Methodology
This program will be conducted with interactive lectures, PowerPoint presentation, discussion and practical exercise.
Course Objectives
Upon completion of this program, participants should be able to:
- Create simple to complex formulas and functions, like:
- COUNTA & COUNTIFS Function
- AVERAGEA &AVERAGEIFS Function
- SUMIF & SUMIFS Function
- IF Functions
- Filter data using Auto & Advanced Filters
- Clean Duplicate Records
- Visualizing Data as a Graphics
Outlines
Module 1: Referencing Calculations Topic A: Formula Reference
- Relative References
- Absolute References
- Mixed References
Module 2: Working with Functions
Topic A: Data Summary with Function
- COUNTA
- AVERAGEA
Topic B: Conditional Data Summary 1
- COUNTIF
- AVERAGEIF
- SUMIF
Topic C: Conditional Data Extraction
- IFERROR
- IF
Module 3: Organizing Dynamic Data Range with Tables
Topic A: Create and Modify Tables
- Create table
- Styles and Quick Style Sets
- Removing Duplicate Values
Topic B: Sort and Filter Data
- Sorting Data
- Filtering Data
- Advanced Filtering
Topic C: Use Subtotal to Calculate Data
- Total Row with SubTotal Functions
Module 4: Data Visualization
Topic A: Conditional Formatting
- Highlight Cell Rules
- Data Bars
- Icon Sets
- Creating New Rules with Formula
Topic B: Create Charts
- Chart Types
- Chart Insertion Methods
- Resizing and Moving the Chart
- Adding Additional Data
- Switching Between Rows and Columns
Topic C: Modify and Format Charts
- Chart Elements
- Minimize Extraneous Chart Elements
- The Chart Tools Contextual Tabs
- Formatting the Chart with a Style
- Adding a Legend to the Chart
Module 5: Managing Large Workbooks
Topic A: Format Worksheet Tabs
- Renaming Worksheet Tabs
- Changing Tab Colour
Topic B: Manage Worksheets
- Repositioning Worksheets
- Inserting or Deleting Worksheets
- Hiding and Unhiding Worksheets
- Worksheet References in Formulas
Topic C: Manage the view of Worksheets and Workbooks
- Split Command
- Freeze Panes options
- Arrange All Command
- Arrange Windows Dialog Box
- View Side by Side Command
- Switch windows Command
- New Window Command
Module 6: Printing Workbook Contents
Topic A: Define the Basic Page Layout for a Workbook
- The Print Options in Backstage View
- The Page Setup Dialog Box
- The Print Preview Option
- Headers and Footers
- Header and Footer Settings
- Page Margins
- Margins Tab Options
- Page Orientation
Topic B: Refine the Page Layout and Apply Print Options
- Zoom Options
- Page Breaks
- Page Break Options
- The Print Area
- Print Titles
- Scaling Options
