Microsoft Excel 2013
Formulas and Functions

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. This course will focus especially on Excel formulas and functions and how to create an efficient and well-designed spreadsheet.

Note: Knowledge and experience using Microsoft Excel at the Intermediate/Advanced course is a prerequisite for this course.

Course InformationRelated Training

Duration: Two-Day Course
Platform: Windows Only
Fee: Call for information

We recommend one to eight people for this course.

Course Content Download PDF (57kb)

Create a Well Designed Spreadsheet

Creating efficient formulas using formula operators, order of precedence, relative, absolute and mixed cell references.
Using range name in formulas.
Combining 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. Also, using the Text to Columns command to separate data into multiple columns.
Using the new Flash Fill feature to extract the first name from a list.

Date and Time Functions

Subtracting dates using the DATEDIF function and calculating the difference between two times; using the YEAR, MONTH and DAY functions and quickly generating different date series.

Statistical Functions

Correctly using the AVERAGEA and COUNTA function.
Using the new COUNTIFS function.

Lookup and Reference Functions

Learning when to use the VLOOKUP, HLOOKUP, CHOOSE, LOOKUP, MATCH and OFFSET functions to look up data.

Database Functions

Using database functions to extract information from a database without rearranging the data. For example use, DSUM, DAVERAGE, DCOUNT, DMAX and DMIN.

Math & Trig Functions

Learning when to use the ROUND, ROUNDUP, ROUNDDOWN, CEILING or FLOOR function.
Using the SUMIF and SUMIFS function, based on one or more criteria.

Financial Functions

Using the PMT function to calculate the payment of a loan.

Data Validation

Restricting the data that may be entered into a cell by combining the Data Validation command with formulas.

Pivot Tables

Adding calculated fields to a pivot table and using a dynamic range and that will expand or collapse as required within a pivot table.
Using the new Recommended Pivot Table command to quickly create a meaningful Pivot Table.
Creating a new Timeline for a Pivot Table.

Quick Analysis Tool

Using the new Quick Analysis tool to simplify tasks: Formatting, Formulas, charts and more

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 Excel 2013 Formulas and Functions