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 techniques on how an electronic spreadsheet works.
Pre-requisites
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
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
AVERAGE A & AVERAGEIFS Function
SUMIF & SUMIFS function
IF Function
Nested Function
Database Function
Validate data in a worksheet
Filter data using Auto & Advanced Filtrers
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: Worksing 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 Styles 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 Function
Summary Function 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 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 legend to the chart
Topic D: Adding Sparklines
What are Sparklines?
Adding Sparklines
Sparkline Tools Tab
Editing Sparkline Data
Removing Sparkline
Topic E: Editing Sparklines
Showing and hiding data
Changing the style
Changing 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 Scholling and Resetting Window
Methodology
Informative presentation, group discussion and presentation, handson practical session
发信息至 Iconic Training Solutions Sdn Bhd
总办事处
Iconic Training Solutions Sdn Bhd 201701003651 (1217801-A)
S1-30-12, D'sara Sentral, Jalan Welfare, Sungai Buloh, 40160, Shah Alam, Selangor, Malaysia.
US 17282 
GB 12596 
CA 10352 
AU 7667 
BR 2895 
IE 2585 
NZ 1238 
SG 656 
