« Back to Class Listing

Excelling at Excel: Formulas and Functions

Description


Knowing how to work efficiently with Excel’s formulas and functions is a critical skill whether you are creating a monthly budget or working in complex worksheets at a Fortune 100 company.

Excel helps us perform very complex tasks using very simple built-in functions. In this course, you will learn different cell references—Relative, Absolute/Mixed, and 3-D—and use them to begin creating and using formulas. From there, you will begin introductory functions such as SUM, AVERAGE, COUNT, and TODAY to help track your monthly budget and expenses. We will discuss how to more efficiently create, copy, and organize worksheets before creating an annual budget using summary worksheets and Mixed and Absolute cell references to forecast your budget for the future.

We will also calculate loan payments, amortization, and repayment schedules using the Name Box, Name Manager, PMT, and CONCATENATE functions. Finally, we begin looking at different database functions like VLOOKUP/HLOOKUP and IF…THEN…ELSE to locate information in a workbook and avoid duplicate entries.

Audience: We designed this course for current, experienced users of Excel who want to know more about formulas and functions. This course builds on "Excelling at Excel: The FUNdamentals" and “Excelling at Excel: The 5Fs.”

Software: We teach this course using Microsoft Excel 2016 on our lab PCs, but the content should also apply to Excel 2007, 2010, and 2013 for Windows. Material learned in this course may not apply to Excel for Mac.

After completing this course, participants will be able to:

  • Prepare formulas using basic, intermediate, and advance functions
  • Differentiate between cell references in Excel – Relative, Absolute/Mixed, and 3-D
  • Optimize budgeting with introductory functions including SUM, AVERAGE, COUNT, and TODAY
  • Evaluate loan repayment options using intermediate functions – PMT and CONCATENATE
  • Construct advanced functions to find and display data with VLOOKUP/HLOOKUP and IF-THEN-ELSE.

Section: MS-EXCEL-FORMFUN (02JUN17)
Dates: Jun 02, 2017 - Jun 02, 2017 Time: 9:30 AM
Tuition: $185.00 Instructor: Greg Creech Inquire

Lesson 1: Formula/Functions Syntax and Referencing
  • Symbols for continuous and noncontiguous formula/function ranges
  • Absolute and Mixed Reference symbols
  • 3-D and linkage symbols
  • Relative, Absolute/Mixed and 3-D References

Lesson 2: Introductory Formulas and Functions
  • SUM, AVERAGE, COUNT, COUNTA, TODAY
  • Calculating days and years
  • Linking worksheets with a formula/function (3-D Reference)

Lesson 3: Intermediate Functions
  • Financial – PMT
  • Text – CONCATENATE, LEFT/RIGHT
  • Lookup and Reference – VLOOKUP/HLOOKUP
  • Time – Calculating hours and minutes for timesheets

Lesson 4: Advanced Functions
  • IF…THEN…ELSE
  • Functions based on criteria - SUMIFS, AVERAGEIF, and COUNTIFS
  • Database functions: DSUM, DCOUNT, and DAVERAGE

Class Schedule

Date Times Meeting Type Location
06/02/2017 9:30 AM - 12:30 PM Classroom Executive Park

Additional Information

Final Enrollment:
 
Clock Hours: 3.0