Home > Microsoft Office Training:
Microsoft Excel Training Toronto - Formulas and Functions
Microsoft Office 2007/2010
Application Overview:
Microsoft Excel
Microsoft Excel is a powerful tool to create and format spreadsheets and to analyze, evaluate, and calculate data. You can use advanced features to manage, track, and chart critical business data to create rich data visualization, PivotTable views, and professional-looking charts. If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps.
Users:
IT, Database, Engineering, Finance, Legal, Marketing and Admin Professionals as well as anyone who needs create and edit spreedsheets that can be searched, sorted, analysed, reported, updated and shared with other users.
Microsoft Office 2007-2010 Training Courses
Microsoft Excel 2007/2010
Formulas and Functions
Course Information
Duration: Two-Day Course
Platform: Windows and Macintosh
Fee: $595 CDN (+HST)
This class is limited to eight people.
Course Overview
Now that you have been working with the advanced features in Microsoft Excel, you are ready to take your skills to the next level. Please note that you should know how to use the commands shown in the Excel Intermediate and Advanced course before attending this course. This course will focus especially on Excel formulas and functions and how to create an efficient and well-designed spreadsheet.
Course Content
Create a Well Designed Spreadsheet
- Create efficient formulas using formula operators, order of precedence, relative, absolute and mixed cell references.
- Use range name in formulas.
- Combine the Conditional Formatting command with the ISBLANK AND ISERROR functions.
Logical Functions
- Nesting or combining the AND and OR function in the IF function.
Text Functions
- Converting the case of text from lowercase to uppercase or proper case, joining several strings of text from two or more cells and extracting data from a cell and using the Text to Columns command to separate data into multiple columns
Date and Time Functions
- Subtract dates using the DATEDIF function and calculate the difference between two times. Also learn how to use the YEAR, MONTH and DAY functions and quickly generate many different date series.
Statistical Functions
- Learn when to use the AVERAGEA and COUNTA function.
- Use the new COUNTIFS functions.
Lookup and Reference Functions
- Learn when to use the VLOOKUP, HLOOKUP, CHOOSE, LOOKUP, MATCH and OFFSET function to look up the data.
Database Functions
- Use the database functions to extract information from your database without rearranging the data. For example use, DSUM, DAVERAGE, DCOUNT, DMAX and DMIN.
Mathematical & Trigonometric Functions
- Learn when to use the ROUND, ROUNDUP, ROUNDDOWN, CEILING or FLOOR function.
- Use the new SUMIFS function.
- Financial Functions
- Use the PMT function to calculate repayment of a loan
Data Tables
- Learn how to use data tables to test different input values without having to retype or copy the formula for each value.
Data Validation
- Restrict the data that may be entered into a cell by combining the Data Validation command with formulas.
Pivot Tables
- Add your own calculated fields to the pivot table and use a dynamic range and that will expand or collapse as required with a pivot table.
Array Functions and What If Analysis (time permitting)
- Use array functions to ensure consistency in your formulas, For example, use the SUMPRODUCT function in an array.
- Learn how to use the Goal Seek and Scenario command.
Calendar Dates:
Feb 28/29, 2012
Mar 29/30, 2012
Apr 4/5, 2012
May 2/3, 2012
Jun 13/14, 2012
Are you using an
older version of
Microsoft Office?
Download PDF of Course Outline
If you have Adobe Reader installed you can view it now:
Related Training
DPA Training
Course Discounts
Receive a discount off of the regular fee for this course!
Register for two or more courses and you automatically qualify for a discount of 10% off the regular fees for all courses.
Register for five or more courses at one time and the discount increases to 15% per courses
(Note: Only the 10% discount applies to Microsoft Office or FileMaker Pro training)
(PDF 55 kb)