Excel Introduction

Duration: 1 day | Book this course
Cost: $395.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 download and 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: $395.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 download and 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

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: $810.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 work with functions including Nested IFs, CONCAT, AND/OR and SUMIF.  Participants will learn to work with tables including sorting and managing data.  You will learn some of the new Excel 365 features such as Dynamic Arrays and Data Types.  The always popular VLOOKUP will be covered along with XLOOKUP, MATCH & INDEX. In-class exercises will involve use of Pivot tables, Pivot charts, auditing worksheets and protecting worksheets. Additional topics include working with custom views and non-VBA macros.

Prerequisites

Participants should have completed the Excel Intermediate level or have equivalent experience. If you are unsure, please download and complete a Training Needs Assessment form by clicking here or email admin@ardito.co.nz.

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. Working with Functions including Nested IFs
2. Learning how to use SUMIF and SUMIFS
3. Working with Tables and Filtering data
4. Using Data validation to create easy entry of data and to audit workbooks
5. Learn some new Office 365 features such as Dynamic Arrays and Data types
6. Query data using Lookup and Reference functions
7. Work with Pivot tables and Pivot charts
8. Audit worksheets
9. Protect worksheets and workbooks.
10. Work with custom views
11. Create and use macros (note: this course doesn’t cover VBA)

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
• Joining several text strings into one text string with CONCAT function
• Using the NETWORKDAYS function to calculate workdays between two dates 
• Using SUMIF and SUMIFS

Working with Tables and Filtering Data

• Sorting/Filtering
• Text, Number and Date filters
• Using Wildcards (? *)
• Filter for Unique Values / Remove duplicates
• Format as Table, and apply Slicers
• Data Types

Data Validation

• Create a drop down list
• Circle invalid data
• Input Messages/Error Alerts
• Dependant drop down lists

Dynamic Arrays

• Spilling, Filter, Sort, Sort by and Unique

Lookup and Reference

• VLOOKUP – exact match & closest match
• XLOOKUP – exact match, closest match, using wildcards & returning multiple columns
• MATCH & INDEX

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

Auditing Worksheets

• Trace errors in formulas
• Tracing precendents and dependents
• Displaying all formulas in a worksheet
• Evaluating formulas

Protecting Worksheets and Workbooks

• Understanding levels of security
• Protecting worksheets and workbooks

Working with Custom Views

Working with Macros (non-VBA)

• Recording, running, and assigning Macros
• Understanding relative and absolute referencing in Macros
• Assign a macro to the Quick Access toolbar or a button

Excel Visual Basic for Applications

Duration:  2 days | Book this course
Cost: $1390.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
• The project explorer
• The code window
• The toolbox
• The properties window
• Looking at the code created by the recorder

Controls – command buttons, textboxes and labels

• Setting control properties
• Assigning code to respond to controls and click events
• Creating an event procedure

How code is stored

• Modules
• Forms
• Using comments

Writing code that is easy to read and navigate

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

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
• 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

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