MICROSOFT EXCEL - INTERMEDIATE
MICROSOFT EXCEL - INTERMEDIATE MICROSOFT OFFICE STANDARD Microsoft Office Application Training Selangor, Malaysia, Kuala Lumpur (KL), Shah Alam Training, Workshop | Iconic Training Solutions Sdn Bhd

MICROSOFT EXCEL – INTERMEDIATE
DURATION: 2 DAYS

WHO IS THIS COURSE FOR?
This course is intended for participants who wish to gain more knowledge from the foundation level of Excel.  For participants who are working with lots of formulas and creates report to understand the necessary technique on how an electronic spreadsheet works.

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.
BENEFITS
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 Function
  • Nested Function
  • Database Function
  • Validate data in a Worksheet
  • Filter data using Auto & Advanced Filters
  • Advanced Chart Formatting
  • Clean Duplicate Records
  • Visualizing Data as a Graphics
KEY CONTENT
Unit 1: Performing Calculations
 
Topic A: Reuse Formulas
  • Relative References
  • Absolute References
  • Mixed References
  • Understanding Mixed Cell References
 
Unit 2: Creating Advanced Formulas 
Topic A: Apply Range Names
  • Range Names
  • Adding Range Names Using the Name Box
  • Adding a Range Names Using the New Name Dialog Box
  • Editing a Range Name and Deleting a Range Name
  • Using Range Names in Formulas
 
Unit 3: Working with Functions 
Topic A: Using Statistical Function
  • COUNTA Function
  • COUNTIFS Function
  • AVERAGEA Function
  • AVERAGEIFS Function
Topic B: Using Mathematical Function
  • SUMIFS Function
Topic C: Using Logical Function
  • IFERROR Function
  • IF Function
  • Nested If Function  
Topic D: Using Database Function
  • DSUM Function
  • DCOUNT Function
  • DAVERAGE Function
  • DMAX Function
  • DMIN Function
 
Unit 4: Organizing Worksheet Data with Tables 
Topic A: Create and Modify Tables 
  • Tables
  • Table Components
  • Create table Dialog Box
  • Table Tools Design Contextual Tab
  • Styles and Quick Style Sets
  • Customizing Row Display
  • Table Modification Options 
Topic B: Sort and Filter Data 
  • Difference Between Sorting and Filtering
  • Sorting Data
  • Advanced Filtering
  • Removing Duplicate Values 
Topic C: Use Subtotal and Database Functions to Calculate Data 
  • SubTotal Functions
  • Summary Functions in Tables 
Topic D: Data Validation
  • Validating your Data
  • Data Validation Using Lists
  • Data Validation Using Date
  • Data Validation Using Formulas 

Unit 5: Visualizing Data as a Graphics

Topic A: Conditional Formatting

  • Highlight Cell Rules
  • Using Data Bars
  • Using Icon Sets
  • Creating New Rules with Formula
Topic B: Create Charts
  • 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
  • Difference Between Modifying and Formatting
  • 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
  • What are Sparklines?
  • Adding Sparklines
  • About the Sparkline Tools Tab
  • Editing Sparkline Data
  • Removing Sparklines
Topic E: Editing Sparklines
  • Showing and Hiding Data
  • Changing the Style
  • Changing the Sparkline and Marker Color
  • Setting Sparkline Options 
 
Unit 6: Managing Views 
Topic A: Working with Different Views 
  • Using Custom view
  • Using Page Layout View
  • Express Header & Footer 
Topic B: Managing Multiple Windows 
  • Arranging Workbooks
  • Comparing Workbooks Side by Side
  • Synchronous Scrolling and Resetting a Window 
 
METHODOLOGY
Informative presentation, Group Discussion & Presentations, Hands on Session Practical Session

Please leave your enquiry here, we will reply as soon as possible.


*only support gif, jpeg, jpg, png, pdf
Switch To Desktop Version