2 / 9
MICROSOFT EXCEL - INTERMEDIATE

# 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 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
• 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

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
• Removing Duplicate Values
Topic C: Use Subtotal and Database Functions to Calculate Data
• SubTotal Functions
• Summary Functions in Tables
Topic D: Data Validation
• Data Validation Using Lists
• Data Validation Using Date
• Data Validation Using Formulas

## 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
• 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
• What are 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