« 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 5 Fs.”

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 (21MAR18)
Dates: Mar 21, 2018 - Mar 21, 2018 Time: 9:30 AM
Tuition: $185.00 Instructor: Greg Creech Register

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: Name Box and Name Manager
  • Name cells, tables, and areas of a worksheet
  • Use Names for:
  • Absolute Reference
  • Navigation
  • Selection
  • Values
  • Formulas and Functions

Lesson 4: Intermediate Functions
  • Financial – PMT
  • Text – CONCATENATE, LEFT/RIGHT
  • VALUE and DATEVALUE

Lesson 5: Advanced Functions
  • Lookup and Reference – VLOOKUP/HLOOKUP
  • IF…THEN…ELSE

Class Schedule

Date Times Meeting Type Location
03/21/2018 9:30 AM - 12:30 PM Classroom Executive Park

Additional Information

Final Enrollment:
 
Clock Hours: 3.0