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 create reports 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
  • 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
• Visualize data as graphics

Key Content

Unit 1: Performing Calculations

• Relative references
• Absolute references
• Mixed references
• Understanding mixed cell references

Unit 2: Creating Advanced Formulas

• Range names
• Adding range names using the Name Box
• Adding a range name using the New Name dialog box
• Editing and deleting range names
• Using range names in formulas

Unit 3: Working with Functions

• COUNTA Function
• COUNTIFS Function
• AVERAGEA Function
• AVERAGEIFS Function

• SUMIFS Function

• IFERROR Function
• IF Function
• Nested IF Function

• DSUM Function
• DCOUNT Function
• DAVERAGE Function
• DMAX Function
• DMIN Function

Unit 4: Organizing Worksheet Data with Tables

• Tables and their components
• Create table dialog box
• Table tools design contextual tab
• Styles and Quick Style sets
• Customizing row display
• Table modification options

• Difference between sorting and filtering
• Sorting data
• Advanced filtering
• Removing duplicate values

• SubTotal Function
• Summary function in tables

• Validating your data
• Data validation using lists
• Data validation using date
• Data validation using formulas

Unit 5: Visualizing Data as Graphics

• Highlight cell rules
• Using data bars
• Using icon sets
• Creating new rules with formulas

• Chart types and insertion methods
• Resizing and moving chart
• Adding additional data
• Switching between rows and columns

• Chart elements and tools
• Minimize extraneous chart elements
• Formatting chart with styles
• Adding legends

• What are sparklines?
• Adding and editing sparklines
• Sparkline tools tab
• Removing sparklines

• Show/hide data
• Change styles and marker colors
• Set sparkline options

Unit 6: Managing Views

• Using custom view
• Using page layout view
• Express header & footer

• Arranging workbooks
• Comparing workbooks side by side
• Synchronous scrolling and resetting window

Methodology

Informative presentation, group discussion and presentation, hands-on practical session.