Course Code: 5527

Excel Formulas and Functions - Basic - 2021/2019/2016

Class Dates:
1/23/2024
11/10/2023
12/12/2023
2/20/2024
2/21/2024
Length:
1 Days
Cost:
$295.00
Class Time:
Technology:
Office
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • Learn how to use formulas to do basic math in Excel, how to make formula results update automatically, and how to use predefined formulas called functions that do things like calculate the amount of monthly payments. Learn how to use functions in your formula's. You'll explore specific types of formulas that can help you on multiple levels, such as financial and array formulas.

    You'll learn how to apply formulas to charts and pivot tables, troubleshoot your formulas, develop custom functions, and much more.

    After completing this course, you will understand and be able to apply Excel formulas and functions to solve a variety of data analysis problems.

  • Audience
  • Anyone who is familiar with the basics of Excel, but would like to expand their knowledge.

    Users would include people from a wide variety of job roles from almost all areas of professional, student, and personal life. Some of the roles users might take on include, but are not limited to:

    • Accountants
    • Clerical, Office professionals
    • Consultants
    • Executives/Managers
    • Help desk personnel
    • Instructors/Trainers
    • Program/Project Managers
    • Sales
    • Students
    • Other members of the general population
    • Charting
    • Create analytical, financial, etc. reports
    • Data entry
    • Family budget
    • Format numerical (financial, statistical, etc.) reports
    • Forms
    • Graphing
    • Process data
    • Reporting
    • Technical support
    • Trending

Prerequisites

  • Familiarity with Excel basics.

    Before coming to the class, attendees should have an understanding of Excel fundamentals including formatting, navigation, and basic formulas.

Course Disclaimer

*Course cost listed does not include the cost of courseware. Please contact us at learn@vtec.org or 207-775-0244 for additional pricing information, or if you have any questions. Course is subject to a minimum enrollment to run. This course may run as a virtual instructor led course if the minimum enrollment has not been met.

Course Details

  • What You Will Learn
  • Gain what-if capability with the IF, AND, OR, and CHOOSE functions
  • Cross-reference data with the table-lookup functions VLOOKUP, INDEX, and MATCH
  • Use frequently-overlooked Text functions to clean and manipulate data
  • Discover creative and powerful array formulas and functions
  • Summarize data with the COUNTIF, SUMIF family of functions
  • Instantly display all formulas in a worksheet
  • Highlight all formula cells in a worksheet; highlight all numerical value cells
  • Use entire row and column references in formulas
  • Debug formulas quickly and effectively with a keystroke shortcut
  • Use AutoSum (and its extended capabilities) more efficiently
  • 1. Getting the Most Out of Ranges
  • Advanced Range-Selection Techniques
  • Data Entry in a Range
  • Filling a Range
  • Creating a Series
  • Advanced Range Copying and Pasting
  • Clearing a Range
  • Applying Conditional Formatting to a Range
  • 2. Using Range Names
  • Defining a Range Name
  • Working with Range Names
  • 3. Building Basic Formulas
  • Understanding Formula Basics
  • Understanding Operator Precedence
  • Controlling Worksheet Calculation
  • Copying and Moving Formulas
  • Displaying Worksheet Formulas
  • Converting a Formula to a Value
  • Working with Range Names in Formulas
  • Working with Links in Formulas
  • Formatting Numbers, Dates, and Times
  • 4. Creating Advanced Formulas
  • Working with Arrays
  • Using Integration and Circular References
  • Consolidating Multi-sheet Data
  • Applying Data-Validation Rules to Cells
  • Using Dialog Box Controls on a Worksheet
  • 5. Troubleshooting Formulas
  • Understanding Excel's Error Values
  • Fixing Other Formula Errors
  • Handling Formula Errors
  • Using the Formula Error Checker
  • Auditing a Worksheet
  • 6. Understanding Functions
  • About Excel's Functions
  • The Structure of a Function
  • Typing a Function into a Formula
  • Using the Insert Function Feature
  • Loading the Analysis Tool Pack
  • 7. Working with Text Functions
  • Excel's Text Functions
  • Working with Characters and Codes
  • Converting Text
  • Formatting Text
  • Manipulating Text
  • Searching for Substrings
  • Substituting One Substring for Another
  • 8. Working with Logical and Information Functions
  • Adding Intelligence with Logical Functions
  • Getting Data with Information Functions
  • 9. Working with Lookup Functions
  • Excel's Lookup Functions
  • Understanding Lookup Tables
  • The Choose Function
  • Looking Up Values in Tables