Excel Introduction

Duration: 1 day | Book this course
Cost: $375.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
Familiarity with the Windows operating system is essential. If you are unsure, please complete a Training Needs Assessment form by clicking here or email admin@ardito.co.nz.

Who Should Take This Course
New users of Microsoft Excel.

Course Objectives

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

  1. Create, save and close a workbook.
  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

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 Compatibility 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
  • 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: $375.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
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 email admin@ardito.co.nz.

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

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

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

Formatting Techniques

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

Managing Worksheets

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

Excel Advanced

Duration:  2 days | Book this course
Cost: $750.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 Pivot tables. In-class exercises will involve use of data tables and scenarios to make projections about data and use Goal Seek, Solver add-in to analyse data. Students will also learn how to formula audit worksheets, protect worksheets and workbooks, create and use macros, and import and export information. Additional topics include consolidating data and working with custom views.

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

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 Pivot tables 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 (note: this course doesn’t cover VBA)
  11. Work with custom views.
  12. Consolidate data.

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 (e.g. DSUM, DCOUNT etc.)
  • Using COUNTIF to count the occurrences of a value
  • Using SUMIF to find the Sum of specific records
  • Using AVERAGEIF to find the average of specific records

Working with Pivot tables

  • Creating and editing Pivot tables
  • Updating information in a Pivot table
  • Grouping Pivot table data
  • Filtering Pivot table data using the Slicer
  • Adding page fields and displaying page fields on separate sheets
  • Creating Pivot charts

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 (non-VBA)

  • Recording, running, and assigning Macros
  • Understanding relative and absolute referencing in Macros
  • Editing, renaming, and deleting Macros

Working with Custom Views

Consolidating Data

    Excel Visual Basic for Applications

    Duration:  2 days | Book this course 
    Cost: $895.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
    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

    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

     

    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, text boxes 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 days | Book this course 

    Cost: $390.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 Pivot table 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
    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

    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 Pivot tables and Pivot charts.
    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

     

    What is Business Intelligence?

    Introduction to PivotTable

    • What is a Pivot table?
    • Inserting a Pivot table
    • Dropping Data into the Pivot table
    • Modifying Data and Refreshing the Pivot table
    • Grouping Data with Pivot table
    • Adding and Removing Fields from a Pivot table
    • Performing Basic Calculations within the Pivot table
    • Formatting the Pivot table
    • Using Slicers to Filter Pivot table 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