Microsoft Excel Intermediate - Managing Data

Course Details

This course is designed for people who have a basic knowledge of Excel, and who want to increase their knowledge of the practical uses of Excel. By the completion of this course, participants will have a thorough knowledge of using Excel to manage data in multiple sheet workbooks.

Duration:

1 Day

Audience:

The course has been designed for users of Excel who have a basic knowledge of Excel and want to improve their usage of Excel formulas and functions.

Prerequisites:

Participants must have basic knowledge of entering data, formatting and simple formulas.

Excel 2016

Excel Formulas

  • Using Absolute References
  • Relative -v- Absolute
  • Absolute Formulas
  • Mastering Mixed References

Range Names

  • Navigating Named Ranges
  • Creating Range Names
  • Assigning Names
  • Using Range Names in Formulas
  • Managing Names

Functions

  • Date and Time functions
    • NOW, TODAY, DATE,
    • YEAR, MONTH,
    • WORKDAY, WEEKDAY
  • Statistical functions
    • COUNT, COUNTA
  • Financial functions
    • PMT, FV
  • Logical functions
    • IF, COUNTIF, SUMIF, SUMIFS
  • Lookup functions
    • VLOOKUP, HLOOKUP

Sorting and Filtering Data

  • Sorting Data
  • Using multiple sort keys
  • Using Filters
  • Filtering or Sorting by Colour

Data Validation

  • Restrict Cell entries to data from a list

Working with Large Worksheets

  • Managing Worksheet Views
  • Using Freeze Panes
  • Using an Outline to hide data
  • Using multiple sort keys

Multiple Worksheets & Workbooks

  • Working with Multiple Worksheets
  • Group and Ungroup Sheets
  • Move & Copy Worksheets
  • Print Selected Worksheets
  • 3-D Formulas

Linking

  • Linking formulas
  • Using Copy & Paste method
  • Updating and Managing links
  • Creating Hyperlinks

Paste Special

  • Copying Formats between worksheets
  • Copying Column Widths
  • Transposing data
  • Copying Column Widths

Managing Workbooks

  • Arranging Windows and Worksheets
  • Protect formulas
  • Protecting Worksheet Structure

Excel 2013

Excel Formulas

  • Using Absolute References
  • Relative -v- Absolute
  • Absolute Formulas
  • Mastering Mixed References

Range Names

  • Navigating Named Ranges
  • Creating Range Names
  • Assigning Names
  • Using Range Names in Formulas
  • Managing Names

Functions

  • Date and Time functions
    • NOW, TODAY, DATE,
    • YEAR, MONTH,
    • WORKDAY, WEEKDAY
  • Statistical functions
    • COUNT, COUNTA
  • Financial functions
    • PMT, FV
  • Logical functions
    • IF, COUNTIF, SUMIF, SUMIFS
  • Lookup functions
    • VLOOKUP, HLOOKUP

Sorting and Filtering Data

  • Sorting Data
  • Using multiple sort keys
  • Using Filters
  • Filtering or Sorting by Colour

Data Validation

  • Restrict Cell entries to data from a list

Working with Large Worksheets

  • Managing Worksheet Views
  • Using Freeze Panes
  • Using an Outline to hide data
  • Using multiple sort keys

Multiple Worksheets & Workbooks

  • Working with Multiple Worksheets
  • Group and Ungroup Sheets
  • Move & Copy Worksheets
  • Print Selected Worksheets
  • 3-D Formulas

Linking

  • Linking formulas
  • Using Copy & Paste method
  • Updating and Managing links
  • Creating Hyperlinks

Paste Special

  • Copying Formats between worksheets
  • Copying Column Widths
  • Transposing data
  • Copying Column Widths

Managing Workbooks

  • Arranging Windows and Worksheets
  • Protect formulas
  • Protecting Worksheet Structure

Excel 2010/ Excel 2007

 Excel Formulas

  • Using Absolute References
  • Relative -v- Absolute
  • Absolute Formulas
  • Mastering Mixed References

Range Names

  • Navigating Named Ranges
  • Creating Range Names
  • Assigning Names
  • Using Range Names in Formulas
  • Managing Names

Functions

  • Date and Time functions
    • NOW, TODAY, DATE,
    • YEAR, MONTH,
    • WORKDAY, WEEKDAY
  • Statistical functions
    • COUNT, COUNTA
  • Financial functions
    • PMT, FV
  • Logical functions
    • IF, COUNTIF, SUMIF, SUMIFS
  • Lookup functions
    • VLOOKUP, HLOOKUP

Sorting and Filtering Data

  • Sorting Data
  • Using multiple sort keys
  • Using Filters
  • Filtering or Sorting by Colour

Data Validation

  • Restrict Cell entries to data from a list

Working with Large Worksheets

  • Managing Worksheet Views
  • Using Freeze Panes
  • Using an Outline to hide data
  • Using multiple sort keys

Multiple Worksheets & Workbooks

  • Working with Multiple Worksheets
  • Group and Ungroup Sheets
  • Move & Copy Worksheets
  • Print Selected Worksheets
  • 3-D Formulas

Linking

  • Linking formulas
  • Using Copy & Paste method
  • Updating and Managing links
  • Creating Hyperlinks

Paste Special

  • Copying Formats between worksheets
  • Copying Column Widths
  • Transposing data
  • Copying Column Widths

Managing Workbooks

  • Arranging Windows and Worksheets
  • Protect formulas
  • Protecting Worksheet Structure

Event Properties

Event Date 27-10-2017
Event End Date 27-10-2017
Individual Price $429.00
Course Code XLMD1
Location
Corporate Training Solutions Australia
Level 19, Waterfront Place, 1 Eagle St Brisbane, QLD 4000 Australia
Corporate Training Solutions Australia

Group Rate

#Registrants Rate/Person($)
3 407.55
4 386.10
$429.00

Location Map

Share this event: