Excel 2016 Formulas and Functions Made Easy

Coexcel 2013 iconurse Length

Three hours

 Prerequisites

Excel 2016 Introduction or equivalent experience

 Course Description

In this class, you will work with a variety of formulas and functions designed to streamline your work with Excel. You will learn basic functions such as SUM, AVERAGE, COUNT, MIN, and MAX that every Excel user needs to know. In addition, the course includes a variety of functions in the Date & Time, Text, Lookup & Reference, Financial, Math & Trig, and Logical categories. Formula auditing, linking and connecting, and array formulas are also covered. Learn how to use formulas and functions to make Excel work for you.

Course Topics

  • Enter and edit formulas
  • Understand the order of operations
  • Enter a function using the Insert Function dialog box, the Function Library, and Formula AutoComplete
  • Use the AutoSum button and drop-down list
  • Use the AutoFill feature
  • Change a cell reference to absolute or mixed
  • Create and remove subtotals and a grand total
  • Use the trace error button to correct a formula
  • Trace precedents and remove precedent arrows
  • Trace dependent cells and remove dependent arrows
  • Evaluate a formula
  • Show formulas
  • Trace an error and check for common errors in formulas
  • Change calculation options
  • Return the current date using TODAY
  • Return the current date and time using NOW
  • Return any date using DATE
  • Return the number of days between two dates using DAYS
  • Change the case of text using UPPER, LOWER, and PROPER
  • Remove excess spaces from a string using TRIM
  • Repeat text using REPT
  • Extract a substring using LEFT, RIGHT, and MID
  • Join text strings using CONCATENATE, CONCAT, & TEXTJOIN
  • Perform a vertical lookup using VLOOKUP
  • Perform a horizontal lookup using HLOOKUP
  • Calculate a payment using PMT
  • Return the future value using FV
  • Round a number using ROUND, ROUNDUP, and ROUNDDOWN
  • Truncate a number using TRUNC
  • Sum cells using SUMIF
  • Perform a logical test using IF
  • Use the nested IF function
  • Test for multiple criteria using AND and OR With IF
  • Create an array formula
  • Link cells by connecting worksheets
  • Link worksheets using a connecting formula and a connecting function
  • Link workbooks using a connecting formula and a connecting function

What Participants Receive

Each participant receives a guide containing step-by-step instructions and practice exercises.