Details

---

Microsoft Excel - Intermediate

Course 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 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