Excelling at Excel: Formulas and Functions
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.