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:
- Create, save and close a workbook.
- Enter and edit text and numbers in a worksheet.
- Navigate worksheets and workbooks.
- Perform basic calculations on data.
- Manipulate rows and columns.
- Format the contents of a worksheet.
- 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:
- Enter and edit Excel formulas.
- Use a logical IF statement to evaluate values and make decisions.
- Use a Lookup function to return values in a database.
- Sort and filter databases.
- Insert and modify charts.
- Embed and link a chart to another application.
- 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:
- Sort and manage data in lists.
- Filter and query data.
- Use outlines on worksheets.
- Use the following built-in Excel functions: lookup and reference, database, date and time, text, financial, and information.
- Use Pivot tables to display and chart data.
- Use data tables and scenarios to make projections about data.
- Use Goal Seek and the Solver add-in to analyse data.
- Audit worksheets.
- Protect worksheets and workbooks.
- Create and use macros (note: this course doesn’t cover VBA)
- Work with custom views.
- 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:
- Understanding Templates and Macros
- Looking at the Visual Basic Editor
- Recording a macro
- Designing Forms
- Learning to write code
- Understanding Procedures
- Using Variables and constants
- Understanding Properties, Methods, Events and Objects
- Understanding the Range Object (in Excel)
- Control structures
- 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:
- Describe the importance of self-service Business Intelligence (BI) in an organisation.
- Insert, modify, and format Classic Pivot tables and Pivot charts.
- Insert various types of data sets into PowerPivot grid.
- Create and manage data relationships.
- Insert calculated columns using basic Data Analysis Expression (DAX) formulas.
- Modify and format PowerPivot Tables and PowerPivotCharts.
- 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