K2's Advanced Excel Formulas and Functions
Excel has more than 450 functions, many of which are unused by business professionals. This session will put you on a path to improved Excel productivity by introducing you to powerful but little used Excel functions, such as IFS (for nested IF statements), AGGREGATE (best described as SUBTOTAL on steroids), GETPIVOTDATA (for producing formal reports from data summarized in a pivot table), CUBEVALUE (for building reports directly from the Excel Data Model) or TEXTJOIN (for joining text or creating CSV files quickly and easily).
learning objectives
Upon completing this session, you should be able to:
- Use Excel’s financial functions to calculate mortgage interest and principal reduction, or perform conventional cash flow analysis;
- List five Excel functions for making conditional calculations with multiple conditions;
- Explain the general process of using INDIRECT to make report assembly easier; and
- Describe the benefits of using GETPIVOTDATA for extracting summarized data from a PivotTable
MAJOR TOPICS COVERED
- New functions in Excel such as TEXTJOIN, IFS, and SWITCH
- Working with CUBE functions and Data Models
- Extracting data from PivotTables with the GETPIVOTDATA function
- Summarizing data with the SUBTOTAL and AGGREGATE functions
- Creating reports using EOMONTH, INDIRECT, and SUMIFS
- Working with Excel’s primary date-oriented functionsÂ
COURSE INFORMATION
CPE Credits: Recommended for 2 Hours of Computer Software and Applications
Instructional Delivery Method: On-demand learning, including review and final exam questions. To earn credit, participants must pass the final exam with a score of 70% or better within one year of registering for this course. Participants may attempt take the final exam up to 10 times.
Prerequisites: Fundamental knowledge of computer operations and Microsoft Office Excel
Program Level: Advanced
Advance Preparation: None
Designed For: Accountants and business professionals seeking to maximize the effectiveness when using Microsoft Office Excel. Note, the content of this program is targeted to those running Excel 2013 or newer (including through Office 365 accounts) on a Windows-based PC.