Untitled Document

Microsoft Excel Course Titles

Excel Introduction

Duration: 1 day | Book this course
Cost: $295.00 + GST

Course Description

This one-day introductory class covers all the basics of Microsoft Excel. Participants will learn how to enter and edit information in a worksheet; manage and navigate worksheets and workbooks; perform basic calculations and manipulate worksheet data; format the worksheet contents; and prepare, set up, and print information.

Prerequisites

Prerequisites
Familiarity with the Windows operating system is essential. If you are unsure, please complete a Training Needs Assessment form by clicking here or phone our Bookings Administrator on (07) 857.0776.

Who Should Take This Course
New users of Microsoft Excel.

Course Objectives

Course Objectives

Upon completing this course, you will have learned how to:

  1. Create, save and close a workbooks.
  2. Enter and edit text and numbers in a worksheet.
  3. Navigate worksheets and workbooks.
  4. Perform basic calculations on data.
  5. Manipulate rows and columns
  6. Format the contents of a worksheet.
  7. Prepare, set up, and print information.

Course Outline

Course Outline

Worksheets and Workbooks

  • The Excel screen (The File menu, the Ribbon, Quick Access Toolbar, Zoom, Views)
  • Creating, Saving, and Closing Workbooks
  • Understanding different file types including Compatability mode
  • Understanding the difference between a workbook and a worksheet

Using Excel

  • Selection Techniques
  • Entering information (Text, Numbers, Formulas and Dates)
  • Editing information in cells
  • Undoing and redoing an action

Manipulating rows and columns

  • Inserting and deleting rows and columns
  • Resizing rows and columns (including best-fit option)
  • Freezing row and column headers

Copying, Moving and Deleting Information

  • Copying and moving cell content (cut, copy, paste)
  • Deleting cell content

Worksheets

  • Inserting a new worksheet
  • Renaming and deleting a worksheet
  • Copying or moving worksheets between workbooks

Using Excel Functions to perform basic calculations

  • Understanding the difference between a Formula and a Function
  • AUTOSUM function
  • AVERAGE function
  • MAX function
  • MIN function
  • COUNT function

Writing Formulas

  • Introduction to Formulas
  • Easy ways to create formulas
  • Copying formulas using AutoFill
  • Formula error messages
  • Relative, Absolute, and Mixed cell referencing

Formatting

  • Font formatting
  • Alignment formatting
  • Format Painter
  • Number formatting

Checking spelling in a Worksheet

Printing

  • Comparing workbooks side by side
  • Print Preview
  • Setting the Page Options and Margins
  • Adding Headers and Footers
  • Inserting and Removing a Page Break
  • Printing a Worksheet

Excel Intermediate

Duration: 1 day | Book this course
Cost: $295.00 + GST

Course Description

This in-depth course goes beyond Excel's basic features . Participants learn how to create and edit data; create and modify charts; format chart objects; work with multiple worksheets and workbooks, and use financial, logical and date functions. Participants also gain hands-on experience using styles, custom formats, and conditional formatting.

Prerequisites

Prerequisites
Participants should have completed the Excel Introduction course or have equivalent experience. If you are unsure, please complete a Training Needs Assessment form by clicking here, or feel free to contact our Bookings Administrator on (07) 857 0776.

Who Should Take This Course
Current users of Excel who want to learn how to create IF statements and Lookups along with learning how to create charts, sorting and filtering and formatting data.

Course Objectives

Course Objectives

Upon completing this course, you will have learned how to:

  1. Enter and edit Excel formulas.
  2. Use a logical IF statement to evaluate values and make decisions.
  3. Use a Lookup function to return values in a database.
  4. Sort and filter databases.
  5. Insert and modify charts.
  6. Embed and link a chart to another application.
  7. Use different types of Excel formatting.

Course Outline

 Course Outline 

Review of Excel Formulas

  • Using AutoCalculate, AutoSum, and AutoFill
  • Entering Formulas
  • Naming cells

Using IF, LOOKUP and Date and Time Functions

Managing Databases and Lists

  • Understanding Excel Databases
  • Sorting data, including custom sorts
  • Filtering data and using the Advanced Filter option
  • Generating Subtotals Automatically

Inserting Charts

  • Inserting and formatting charts
  • Understanding chart axes, plot area, and gridlines
  • Moving and resizing charts
  • Embedding and linking charts to another application

Using Sparklines (Excel 2010 & 2013 only)

Formatting Techniques

  • Standard Formatting
  • Format Painter
  • Cell Styles and Table Styles
  • Conditional Formatting

Managing Worksheets

  • Freezing row and column titles
  • Splitting a worksheet
  • Hiding and un-hiding rows and columns
  • Hiding / un-hiding worksheets
  • Grouping and ungrouping worksheets

Excel Advanced

Duration:  2 days | Book this course
Cost: $590.00 + GST

Course Description

This in-depth two-day course introduces Microsoft Excel users to the advanced features of the application. Participants will learn how to sort and manage data in lists; filter and query data; use outlines on worksheets, lookup and database functions, and PivotTables. In-class exercises will involve use of data tables and scenarios to make projections about data and use Goal Seek, Solver, and the Analysis ToolPak to analyse data. Students will also learn how to formula audit worksheets, protect worksheets and workbooks, create and use macros and templates, and import and export information. Additional topics include using workgroup features and working with custom views.

Prerequisites

Prerequisites

Participants should have completed the Excel Intermediate level or have equivalent experience.

Who Should Take This Course
Microsoft Excel users who are familiar with the intermediate level functions and want to learn how to use the software's high end-user features.

Course Objectives

Course Objectives

Upon completing this course, you will have learned how to:

  1. Sort and manage data in lists.
  2. Filter and query data.
  3. Use outlines on worksheets.
  4. Use the following built-in Excel functions: lookup and reference, database, date and time, text, financial, and information.
  5. Use PivotTables to display and chart data.
  6. Use data tables and scenarios to make projections about data.
  7. Use Goal Seek and the Solver add-in to analyse data.
  8. Audit worksheets.
  9. Protect worksheets and workbooks.
  10. Create and use macros.
  11. Import and export information and documents.
  12. Use workgroup features.
  13. Work with custom views.
  14. Consolidate data.

Course Outline

Course Outline

Working with Functions

  • Applying Nested IFs to test multiple conditions
  • Combining the IF function with other functions such as AND, OR, ISBLANK, and ISERROR
  • Use the VLOOKUP function
  • Joining several text strings into one text string with CONCATENATE function
  • Using the NETWORKDAYS function to calculate workdays between two dates 

Working with Databases

  • Creating and Removing Subtotals
  • Using Data Validation to restrict entries
  • Displaying an Input Message
  • Displaying an Error Alert

Filtering and Querying Data

  • Filtering Data including custom Filters
  • Filtering with single and multiple comparison criterion
  • Using the Advanced Filter

Using Outlines

  • Creating Manual and Automatic Outlines
  • Hiding and Displaying Outline Levels

Using Lookup and Database Functions

  • Using LOOKUP function to automatically look up values in a Table
  • Working with Database Functions
  • Counting the Occurrences of a Value
  • Finding the Sum of Specific Records
  • Finding the Average of Specific Records

Working with PivotTables

  • Creating and editing PivotTables
  • Updating Information in a PivotTable
  • Grouping PivotTable Data
  • Filtering PivotTable Data using the Slicer (2010 and 2013 versions only)
  • Hiding and Showing Source Details
  • Adding Page Fields and displaying page fields on separate sheets
  • Creating PivotCharts

Making Projections About Data

  • Solving What-If Problems
  • Using Data Tables (One-input and two-input tables)
  • Creating and managing Scenarios
  • Creating a summary of scenario results

Analysing Data

  • Working with the Goal Seek Feature
  • Working with Solver add-in

Auditing Worksheets

  • Trace errors in formulas
  • Finding Referenced Cells
  • Finding Formulas that Refer to a Cell

Implementing Data Security

  • Understanding levels of security
  • Protecting worksheets and workbooks

Working with Macros

  • Recording, Running, and Assigning Macros
  • Understanding relative and absoute referencing in Macros
  • Editing, Renaming, and Deleting Macros

Importing Information

  • Importing Documents from Other Applications
  • Importing Text Files

Using Excel in a Workgroup

  • Sharing workbooks
  • Adding and deleting Comments to Cells
  • Reading and Editing Comments
  • Tracking Changes to Workbooks
  • Accepting or Rejecting Changes

Working with Custom Views

Excel Visual Basic for Applications

Duration:  2 days | Book this course
Cost: $795.00 + GST
(This course is guaranteed to run if the minimum class size of 4 is met.)

Course Description

This course aims to cover in detail the Visual Basic for Applications programming language. You will learn how to program in VBA in order to customise your work environment in Excel. You will learn to understand programming terminology such as modules, procedures, variables and constants. You will learn form design, control structures and how to debug and test your VBA application before using it in the work environment.

Prerequisites

Prerequisites
This course requires that you have an advanced knowledge of Excel . Ideally you should have completed the Excel Advanced Features course or have the equivalent experience.

Who Should Take This Course
Microsoft Excel users who are familiar with the advanced level functions and want to learn how to use the software's high end-user features.

Course Objectives

Course Objectives

Upon completing this course, you will have learned how to:

  1. Understanding Templates and Macros
  2. Looking at the Visual Basic Editor
  3. Recording a macro
  4. Designing Forms
  5. Learning to write code
  6. Understanding Procedures
  7. Using Variables and constants
  8. Understanding Properties, Methods, Events and Objects
  9. Understanding the Range Object (in Excel)
  10. Control structures
  11. Debugging and Error-trapping

Course Outline

Course Outline


Understanding Templates and Macros

  • Workbook projects
  • Template projects
  • Using the Personal.xls in Excel

Looking at the Visual Basic Editor

  • The project explorer
  • The code window
  • The toolbox
  • The properties window

Recording a macro

  • Looking at the code created by the recorder

Designing Forms

  • Controls - command buttons, textboxes and labels
  • Setting control properties
  • Assigning code to respond to controls and click events
  • Creating an event procedure

Learning to write code

  • How code is stored
  • Modules
  • Forms
  • Using comments
  • Writing code that is easy to read and navigate

Understanding Procedures

  • Sub procedures
  • Function procedures
  • Public procedures
  • Private procedures

Using Variables and Constants

  • Data types
  • Declaring a variable or constant
  • Using built in constants

Understanding Properties, Methods, Events and Objects

  • Using With…End With to set multiple property values for a single object

Understanding the Range Object (in Excel)

  • Using cell addresses as range references
  • Using Range names
  • Relative referencing
  • Using the Offset property

Control structures

  • Decision structures
  • If.. Then
  • If…Then…Else
  • If…Then..Elseif
  • Select Case
  • Looping Structures
  • Do…Loop
  • Do While…Loop
  • Do Until…Loop
  • Do…Loop While
  • Do…Loop Until
  • For…Next
  • For Each…Next

Debugging and Error-trapping

  • How to handle errors
  • Designing an error handler
  • Using Break Mode
  • Using the Debugging window
  • Avoiding bugs

Excel PowerPivot Introduction

Duration:  1 day | Book this course
Cost: $360.00 + GST

Course Description

To analyse more complex data, Microsoft introduced the so-called self-service Business Intelligence or BI. PowerPivot is Microsoft’s first tool to handle self-service BI. In this course, you will learn the fundamentals of analysing not only a single table (an Excel PivotTable limitation) but you will query more tables at the same time. You will also learn how to produce engaging reports, i.e. dashboards, that can easily integrate information coming from various sources such as information from databases, Excel worksheets, and sources available on the Internet.

Prerequisites

Prerequisites
Participants should have completed either the Excel Intermediate or Excel Advanced level, or have equivalent experience. You must be a user of Microsoft Office 2010 or higher. Earlier versions are not supported.

Who Should Take This Course
This class is designed primarily for Excel users who are interested in using PowerPivot to generate Business Intelligence reports by themselves. This user group composes of, but not limited to, business analysts, project managers, marketing professionals, business development officers, or those people who are responsible for defining the strategic direction of the company.

Course Objectives

Course Objectives

Upon completing this course, you will have learned how to:

  1. Describe the importance of self-service Business Intelligence (BI) in an organisation.
  2. Insert, modify, and format Classic PivotTables and PivotCharts.
  3. Insert various types of data sets into PowerPivot grid.
  4. Create and manage data relationships.
  5. Insert calculated columns using basic Data Analysis Expression (DAX) formulas.
  6. Modify and format PowerPivot Tables and PowerPivotCharts.
  7. Create report dashboards.

Course Outline

Course Outline


What is Business Intelligence?

Introduction to PivotTable

  • What is a PivotTable?
  • Inserting a PivotTable
  • Dropping Data into the PivotTable
  • Modifying Data and Refreshing the PivotTable
  • Grouping Data with PivotTable
  • Adding and Removing Fields from a PivotTable
  • Performing Basic Calculations within the PivotTable
  • Formatting the PivotTable
  • Using Slicers to Filter PivotTable Data
  • Inserting a PivotChart
  • Formatting the PivotChart

Introduction to PowerPivot

  • What is a PowerPivot?
  • Downloading and Installing PowerPivot
  • System requirements
  • Installing PowerPivot
  • Uninstalling PowerPivot
  • Preparing your Data for PowerPivot
  • Exploring the PowerPivot Ribbon
  • Getting Your Data into PowerPivot
  • Linking an Excel Table
  • Adding Excel Data by Copy-Paste
  • Importing a Text File
  • Importing an Excel Worksheet
  • Importing an Access Database
  • Working with Data in the PowerPivot Window
  • Sorting and Filtering Data
  • Applying Numeric Formatting
  • Inserting, Deleting, and Rearranging Columns
  • Inserting Calculated Columns using Basic DAX formulas
  • Defining and Managing Relationships

Creating a Report Dashboard

  • Building a PowerPivot Table
  • Using the Field List to Create Reports
  • Using the Report Filter
  • Building a PowerPivot Chart
  • Inserting Horizontal and Vertical Slicers
  • Formatting the PowerPivot Table and Chart
  • Modifying and Removing Excel Interface Elements
  • Inserting your Corporate Brand Identity