IT and Computing > Microsoft Excel 2019 Intermediate

Microsoft Excel 2019 Intermediate

Course Objectives

Create simple to complex formulas and functions, like:
o COUNTA & COUNTIFS Function
o AVERAGEA &AVERAGEIFS Function
o SUMIF & SUMIFS Function
o IF Functions
o Nested Functions
o Database Function
Validate data in a Worksheet
Filter data using Auto & Advanced Filters
Advanced Chart Formatting
Clean Duplicate Records
Visualizing Data as a Graphics

Target Audience

REQUIRED 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.

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.

Methodology

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise.

Course Modules

Module 1: Referencing Calculations
Topic A: Formula Reference
• Relative References
• Absolute References
• Mixed References
Topic B: Apply Range Names
• Range Names
• Adding Range Names Using the Name Box
• Adding a Range Names Using Define Name
• Adding multiple names using Create from Selection
• Editing and Deleting Range Name Using Name Manager
• Using Range Names in Formulas
Module 2: Working with Functions
Topic A: Data Summary with Function
• COUNTA
• AVERAGEA
Topic B: Conditional Data Summary 1
• COUNTIF / COUNTIFS
• AVERAGEIF / AVERAGEIFS
• SUMIF / SUMIFS
• MAXIF
• MINIF
Topic C: Conditional Data Extraction
• IFERROR
• IF
• IFS or Nested IF
Topic D: Conditional Data Summary 2
• DSUM
• DCOUNT / DCOUNTA
• DAVERAGE
• DMAX
• DMIN
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
Topic D: Data Validation
• Data Validation Using Lists
• Data Validation Using Whole Number / Decimal
• Data Validation Using Date
• Data Validation Using Formulas
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
Topic D: Adding Sparklines
• Adding Sparklines
• Editing Sparkline Data
• Removing Sparklines
Topic E: Editing Sparklines
• Showing and Hiding Data
• Changing the Style
• Changing the Sparkline and Marker Color
• Setting Axis Options
Module 5: Setting Up Reports as Pages
Topic A: Working with Different Views
• Using Page Break Preview for Page Setup
• Using Page Layout View for Express Header & Footer
• Using Custom view
Topic B: Managing Multiple Windows
• Arranging Workbooks
• Comparing Workbooks Side by Side
• Synchronous Scrolling and Resetting a Window