Welcome to our free Intermediate Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Intermediate Microsoft Excel 2013 Training course.
In this lesson, you will learn about advanced formulas.
Lesson Goals
IF
function and its variants in formulas.PMT
function to calculate payments for loans.VLOOKUP
function.HLOOKUP
function.CONCATENATE
function to join the contents of numerous cells.PROPER
, UPPER
, and LOWER
functions to alter the casing of text.LEFT
, RIGHT
, and MID
functions to return characters from the start or end of a string, or a specific number of text characters.Some of the advantages to naming and labeling cells include:
Here is a list of things you need to know about cell names:
To name a cell:
To name a range of cells:
To quickly name cells using their row and column headings:
In this exercise, you will practice naming cells and will use named cells in a formula.
Hint: If step 3 isn't working for you, make sure that you named cell B1 "QuarterlyIncome" with no space and that you did not include a space within "QuarterlyIncome" in the formula for B13.
To create a formula that spans multiple worksheets:
In this exercise, you will enter a formula using data from multiple sheets first without names and then using named cells.
The IF function can be used to execute formulas only under certain conditions or to execute different formulas based on specified conditions. To use the IF function, you need to know:
Here are some things to know about the IF function:
To use the IF function:
Note that when you see a small picture of a worksheet with a red arrow on it next to a data entry field, you can click this image to select a cell, rather than typing the cell's location into the data entry field. For example:
This is especially useful when referring to cells on a separate worksheet.
Here are some examples of the IF statement in use:
There are a few variations of the IF function that may be useful to be aware of when working with Excel.
The SUMIF
function is a variation of the IF
function, which allows you to specify criteria for a sum. For example, you may want to sum only the numbers in a column that are above 100.
To use SUMIF
, you need to know:
AVERAGEIF
does what it sounds like: it averages a range of cells. For example, you could average students' grades in a spreadsheet.
To use AVERAGEIF
, you need to know:
The COUNTIF
function allows you to count the number of cells in a range that meet the criteria you specify. For example, you can count the number of students who received As.
To use the COUNTIF function, you need to know:
The AND
and OR
functions are similar to the IF function in that they are logical functions.
The syntax of the AND function is: =AND(logical1,logical2, ...)
. It returns TRUE
if all arguments are true.
The syntax of the OR function is: =OR(logical1,logical2, ...)
. It returns TRUE
if any arguments are true.
In this exercise, you will practice using the IF function.
The PMT function is used to calculate payments on loans. In order to use the PMT function, you need to know:
To use the PMT function:
Here are some examples:
Note that 15 years = 180 months.
In this exercise, you will...
The LOOKUP function returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form.
The LOOKUP
function is used to pull a value from a range that is one row or one column, or from an array. It has two syntaxes: vector and array.
To use the LOOKUP
function, you need to know:
To use the LOOKUP
function:
The VLOOKUP function is used to pull a value from a list or table based on a corresponding value. For example, if you have a worksheet with a table showing employee names, hire date, and salary, you could use VLOOKUP in a separate worksheet to pull the hire date and salary for individual employees from the first worksheet. In this example, the employee name serves as a key, identifying which information from the first worksheet you wish to pull.
To use the VLOOKUP function, you need to know:
To use the VLOOKUP function:
In this exercise, you will use the VLOOKUP function to automatically fill in the description and price of items on an invoice based on the item number.
The HLOOKUP function is very similar to the VLOOKUP function. The only significant difference is that while the VLOOKUP function looks for a value in the left-most column of a table and returns a value on the same row as that value, the HLOOKUP function looks for a value in the top row of a table and returns a value in the same column as that value. To use the HLOOKUP function, you need to know:
To use the HLOOKUP function:
The CONCATENATE function is used to join the contents of multiple cells. For example, if you have a worksheet with first names in one column and last names in another column, you can use the CONCATENATE function to join the first and last names into one column.
Here are some things to know about the CONCATENATE function:
To use the CONCATENATE function:
In this exercise, you will practice using the CONCATENATE function.
You can use the TRANSPOSE
function to return a horizontal range of cells as a vertical range or a vertical range as a horizontal range.
To use TRANSPOSE
, you must know the array, the range of cells you want to transpose and also preselect the new location (making sure the number of cells match). To use the TRANSPOSE
function:
The PROPER
function is used to make the first letter in each word uppercase and all other letters lowercase. To use the PROPER
function:
The UPPER
function is used to make all letters in words uppercase.
The LOWER
function is used to make all letters in words lowercase.
Another text function you may use is the TRIM function. TRIM
allows you to remove the spaces in phrases, leaving only single spaces between words.
In this exercise, you will practice using the PROPER function.
The LEFT and RIGHT functions are used to return characters from the start or end of a string. For example, you could use the LEFT and RIGHT functions to:
To use the LEFT and RIGHT functions:
The MID
function is used when you want to return a specific amount of characters from a string of text. You specify the number of characters.
To use the MID function:
In this exercise, you will practice using the LEFT and RIGHT functions.
The three most commonly used date functions are YEAR, MONTH, and DAY. These functions are used to return only the year, month, or day from a date. To use the YEAR, MONTH, and DAY functions:
Two other date functions are NOW
and TODAY
. They are useful when you need to show the current date information or need to calculate something based on that information.
NOW
: Returns the current date and time.TODAY
: Returns the current date, but the time is set to 12:00:00 AM.In this exercise, you will practice using the YEAR, MONTH, and DAY functions.
In Excel 2013, scenarios are input values that you can create and save, which return different calculated results. You can use them in what-if scenarios.
The Watch Window is a feature that allows you to keep formulas that you need to view in sight, rather than having to jump around in a worksheet.
To use the Watch Window:
To consolidate data from multiple worksheets into one master worksheet:
You can enable iterative calculations to locate circular references.
To enable iterative calculations:
The DATA tab's Data Tools group contains the What-If Analysis drop-down list, which contains a number of what-if tools. One of these tools is Goal Seek.
To use Goal Seek:
To manage what-if models so you can quickly access them, you can use the Scenario Manager.
To use the Scenario Manager:
The Excel 2013 financial functions are complex financial formulas that contain multiple steps. These functions cover things as calculating net present value, the depreciation of an asset, and loan payments, amongst others.
To access the financial functions, from the FORMULAS tab, in the Function Library group, select Financial.