Master Functions in Excel
4.8 (2 ratings)
109 students enrolled
Wishlisted Wishlist

# Master Functions in Excel

Become a master in using formulas to perform complex calculations and automate queries.
4.8 (2 ratings)
109 students enrolled
Last updated 11/2016
English
English
Price: \$20
30-Day Money-Back Guarantee
Includes:
• 1.5 hours on-demand video
• 2 Supplemental Resources
• Access on mobile and TV
• Certificate of Completion
What Will I Learn?
View Curriculum
Requirements
• This course is designed for those, who can perform basic calculations and can navigate in Excel 2016.
Description

You already know how to get Microsoft Office Excel 2016 to perform simple calculations to make your job easier. However, manually entering formulas will take you only so far. As you progress with Excel, you'll need to know how to ask Excel more complex questions about your data and to get the answers you expect.

There are over 300 functions in Excel. No need to learn them all. This course is right for you if you want - or need - to perform more complex calculations, e.g. calculate with Date and Time or perform calucaltion based if a criteria is met or not. You will learn how to use the most important function in each category.

You will

• Understand the difference between formuals and functions.
• Work with logical functions.
• Word with DATE and TIME functions.
• Work with TEXT functions.

Because you will understand how functions work, you are able to locate and work with other functions you need.

Who is the target audience?
• As you progress with Excel, you'll need to know how to ask Excel more complex questions about your data and to get the answers you expect.
Compare to Other Excel Courses
Curriculum For This Course
22 Lectures
01:17:22
+
First Steps
4 Lectures 12:53

In Excel, functions are simply built-in, predefined formulas that you can quickly and easily insert into worksheet cells. Learn how to 'talk' to Excel at a higher level to get the most out of your data.

Preview 01:09

Learn how to use functions will save you time, effort, and possibly a few headaches down the road. (Functions, Basic Function Syntax, The Function Library Group, TheFunction Arguments Dialog Box, Graphical Cell and Range Reference Entry, The AutoSum Feature, Other Commonly Used Functions, The Formula AutoComplete Feature)

Insert Functions
05:44

By reusing your content, you can eliminate the hassle and reduce the likelihood of entering numerous errors in your workbooks.

Reuse Formulas and Functions - Introduction
00:58

Excel provides you with a number of methods to reuse nearly any of your content, including formulas and functions, basically by using a variety of copy and paste techniques. (Formulas and the Cut, Copy, and Paste Commands, Drag-and-Drop Editing, The Paste Options, The Paste Special Options, Relative References, Absolute References, Mixed References, AutoFill and Formulas, Worksheet References, Excel Errors)

Reuse Formulas and Functions
05:02
+
Cell Name and Range Name
4 Lectures 14:48

Learn how to ease your work by name ranges for use in functions and formulas.

Preview 00:56

Cell names and range names are exactly what they sound like. They are meaningful names you assign to a given cell or range to make it easier to both understand what calculations are being performed in a formula and to reuse the references for a number of purposes. (Cell and Range Names, Names and the Name Box, Name Manager, The Create from Selection Command)

Preview 05:34

Although it's certainly helpful to be able to name a range or a cell for easy navigation, the real power of this feature lies in the ability to easily identify references in formulas and to quickly and accurately insert references into multiple formulas. (Cell and Range Names in Formulas, Manually Entering Cell or Range Names, The Use in Formula Command Method, The Formula AutoComplete Method)

Use Defined Names in a Formula
03:12

Learn how to perform far more complex tasks in Excel beyond adding up rows and columns and multiplying the sum by some other figure. (Function Categories, The Excel Function Reference, Comparison Operator Basics, Function Syntax, The COUNTIF Function, The TODAY Function)

Locate and Use Specialized Functions
05:06
+
Work with Logical Functions
5 Lectures 23:54

The logical functions in Excel provide a method for testing various conditions to calculate a result of a value, text, or a calculation which enable you to ask questions of your data.

Preview 02:13

The IF function returns one value if the logical test you enter as an argument is true, and it returns a different value if the logical test is not true.

The IF Function
04:29

Learn how to combine logical tests. (AND Function, OR Function, NOT Function)

The AND OR and NOT Functions
05:40

The key to combining multiple calculations into a function in a single cell is nesting. Nesting is, simply, using a function as an argument within another function.

Combine Functions
06:18

Learn how to use other powerful IF Functions. (SUMIF, COUNTIF, AVARAGEIF)

Functions Similar to the IF Function
05:14
+
Work with Date and Time Functions
3 Lectures 09:46

Learn how to work with of some of the more commonly used date and time functions.

Preview 01:24

Learn how to master the most basic date and time functions.
TODAY() NOW() DATE()
02:54

Learn how to work with more advanced date and time functions - not only important to all, who frequently deal with scheduling and analyzing data for particular periods of time. (The NETWORKDAYS Function, The WEEKDAY Function, The WORKDAY Function, The ISOWeeknum Function)

NETWORKDAY() WEEKDAY() WORKDAY() and ISOWeeknum()
05:28
+
Text Functions
6 Lectures 16:01

Excel's text functions let you manipulate text in cells to extract portions of text to other cells or combine them.

Preview 00:47

This basic text functions let you manipulate text in cells to extract portions of text to other cells or combine them to produce e.g. full names or addresses. (The LEFT Function, The RIGHT Function, The MID Function)

LEFT() RIGHT() and MID()
02:40

To extract text from cells that contain values of varying characters, Excel also includes the FIND function. Very helpful, e.g. when you want to seperate names like 'Lastname, Firstname' into two separate cells.

Find()
03:19

This feature splits a single column of text into multiple columns. For example, you can create first and last name columns from one column of full names. Actually its not a Function ... but a helpful feature that need to be mentioned here!

The 'Text to Columns' Feature
03:38

One of the most powerful text functions available in Excel is the CONCATENATE function. This function enables you to concatenate, or join together, text strings from multiple cells into a single cell. This function can save you massive amounts of time when you need to pull together data from multiple cells that already exists in your worksheets. (The CONCATENATE Function, Text Concatenation with the Ampersand, &)

The CONCATENATE() or '&' Function
02:59

Excel contains three text functions that are very useful for formatting text to display it in the desired format.

UPPER() LOWER () and PROPER()
02:38
+
Quiz
0 Lectures 00:00
Quiz
5 questions