Microsoft Excel: Intermediate

Microsoft Excel: Intermediate

Category: Microsoft Excel

Specifications
Details

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

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 name using the New Name dialog box
  • Editing and deleting range names
  • 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 and their 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 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 Graphics

Topic A - Conditional Formatting
  • Highlight cell rules
  • Using data bars
  • Using icon sets
  • Creating new rules with formulas
Topic B - Create Charts
  • Chart types and insertion methods
  • Resizing and moving chart
  • Adding additional data
  • Switching between rows and columns
Topic C - Modify and Format Charts
  • Chart elements and tools
  • Minimize extraneous chart elements
  • Formatting chart with styles
  • Adding legends
Topic D - Adding Sparklines
  • What are sparklines?
  • Adding and editing sparklines
  • Sparkline tools tab
  • Removing sparklines
Topic E - Editing Sparklines
  • Show/hide data
  • Change styles and marker colors
  • Set 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 window

Methodology

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


View more about Microsoft Excel: Intermediate on main site