Instructor for this course
more

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: June 18, 2021

Prerequisites

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 .

1 QuestionCourse Questions and Answers

User picture

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

Course Syllabus
INTRODUCTION AND OVERVIEW
  0:37Introduction to Excel Magic 6: “Speaking” the 7 Languages in Excel
  1:16Re-cap
  1:09Instructor Introduction
  1:22Other similar courses
  0:31What you will get from this course?
Excel Language No. 1
  4:24Language No. 1: Formulas - 1
  5:06Language No. 1: Formulas - 2
  4:11Language No. 1: Formulas - 3
  7:04Language No. 1: Formulas - 4
Excel Language No. 2
  5:53Language No. 2: Formatting - 1
  5:25Language No. 2: Formatting - 2
Excel Language No. 3
  2:23Language No. 3: Pivot Table Formulas - 1
  3:41Language No. 3: Pivot Table Formulas - 2
Excel Language No. 4
  2:10Language No. 4: Named Range Formulas - 1
  7:07Language No. 4: Named Range Formulas -2
Excel Language No. 5
  2:26Language No. 5: VBA Macro - 1
  3:45Language No. 5: VBA Macro - 2
Excel Language No. 6
  4:33Language No. 6: M Language in Power Query - 1
  4:05Language No. 6: M Language in Power Query - 2
Excel Language No. 7
  4:37Language No. 7: DAX Language in Power Pivot - 1
  2:48Language No. 7: DAX Language in Power Pivot - 2
  4:11Language No. 7: DAX Language in Power Pivot - 3
CONCLUSION
  1:16Takeaways
  0:26Next Course & Q&A
CONTINUOUS PLAY
  1:20:24Excel Magic 6: “Speaking” the 7 Languages in Excel
SUPPORTING MATERIAL
  PDFSlides: Excel Magic 6: “Speaking” the 7 Languages in Excel
  PDFExcel Magic 6: “Speaking” the 7 Languages in Excel Glossary/ Index
  XLSMWorkbook: Excel Magic 6: “Speaking” the 7 Languages in Excel
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM