The course is presented in five parts:

First, the course reveals the fact Excel has as many as 7 languages that are needed for proper modeling!

Next, the course shows as many as 4 types of formulas in Excel. They are:

  • Regular formulas
  • Text formulas
  • Table formulas
  • Conditional formulas

Third, we explain in detail how to decipher the custom formatting language, and proper ways of displaying in thousands, in color, or hidden in its entirety.

Fourth, we review the formulas of calculated fields and calculated items used in Pivot tables. Also, we demonstrate ways to build a dynamic named range for easy reference.

Last, we explore basic VBA codes, the M language and the DAX language in Excel. They include:

  • For Next loop
  • Conditional formula in M
  • SUMX() and RELATED() in DAX

Course Key Concepts: Table formula, Conditional formula, Custom formatting, Calculated field, Calculated item, Dynamic named range, VBA macro, M language, DAX language, Relationship.

Learning Objectives
  • Discover and understand 4 types of formulas to use in Excel.
  • Identify 2 methodologies to format a cell display.
  • Recognize 2 calculation formulas built in a pivot table.
  • Discover ways to build a dynamic named range in Excel
  • Explore and understand basic VBA codes, M language, and DAX language in Excel.
Last updated/reviewed: August 9, 2023
1 Review (20 ratings)

Reviews

5
Anonymous Author
I liked that the course provided an overall discussion on the different Excel languages. The instructor was clear in providing an understanding of the 7 languages in Excel.
Prerequisites
Course Complexity: Foundational

Basic Excel knowledge
Example: be able to open one Excel file and connect to external data files, etc.
It is recommended to take other Excel Magic series courses by Lenny Wu.
Excel Magic 1: Building Dynamic Formulas
Excel Magic 2: Building Your Report Generation "Bot"
Excel Magic 3: Automating Data Entry with Data Feeds
Excel Magic 4: Ultimate API Reporting in Excel
Excel Magic 5: Building Interactive Budgeting Model in Excel
Excel Magic 6: Excel's 7 Languages
Excel Magic 7: Creating Excel Charts like a PRO
Excel Magic 8: Transforming a Pro-forma into a Dashboard
Excel Magic 9: Building a Project Tracking “Gantt” Chart

Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Questions and Answers(2 Questions)
There are no questions.         Answers to Frequently Asked Questions (FAQs)
User picture
Anonymous
(Manager of Financial Reporting) | May 28, 2021

The last question in the Final exam seems to be worded wrong. Does it miss a "NOT" in the question? Thanks for checking.

Member's Profile
Dominic Villanueva
(Sr Accountant at individual) | Sep 6, 2022

Hi Anonymous (mgr of FR)!

Must say have to agree with you. The first three are all relationships for DAX. The last one, however, doesn't make sense as part of the question and by elimination. Nice to know of your observation.

Instructor for this course
  Course Overview CPE
Course Syllabus
INTRODUCTION AND OVERVIEW
  Introduction to Excel Magic 6: “Speaking” the 7 Languages in Excel0:37
  Re-cap1:16
  Instructor Introduction1:09
  Other similar courses1:22
  What you will get from this course?0:31
Excel Language No. 1
  Language No. 1: Formulas - 14:24
  Language No. 1: Formulas - 25:06
  Language No. 1: Formulas - 34:11
  Language No. 1: Formulas - 47:04
Excel Language No. 2
  Language No. 2: Formatting - 15:53
  Language No. 2: Formatting - 25:25
Excel Language No. 3
  Language No. 3: Pivot Table Formulas - 12:23
  Language No. 3: Pivot Table Formulas - 23:41
Excel Language No. 4
  Language No. 4: Named Range Formulas - 12:10
  Language No. 4: Named Range Formulas -27:07
Excel Language No. 5
  Language No. 5: VBA Macro - 12:26
  Language No. 5: VBA Macro - 23:45
Excel Language No. 6
  Language No. 6: M Language in Power Query - 14:33
  Language No. 6: M Language in Power Query - 24:05
Excel Language No. 7
  Language No. 7: DAX Language in Power Pivot - 14:37
  Language No. 7: DAX Language in Power Pivot - 22:48
  Language No. 7: DAX Language in Power Pivot - 34:11
CONCLUSION
  Takeaways1:16
  Next Course & Q&A 0:26
CONTINUOUS PLAY
  Excel Magic 6: “Speaking” the 7 Languages in Excel1:20:24
SUPPORTING MATERIAL
  Slides: Excel Magic 6: “Speaking” the 7 Languages in ExcelPDF
  Excel Magic 6: “Speaking” the 7 Languages in Excel Glossary/ IndexPDF
  Workbook: Excel Magic 6: “Speaking” the 7 Languages in ExcelXLSM
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam