Excel – Advanced Formula & Functions

1.     Information Functions

1)     Introduction of data type

2)     Collection of ‘ISXXXX’ function

3)     Introduction of Error Type & Error.Type

4)     Use IFERROR unction with extreme care &caution

5)     FORMULATEXT function – formula consistency tracker

6)     Everything Is ‘cell’ based

7)     Ever quickest way to find excelversion/operating system version

8)     Create ‘page number’ for your worksheets


2.     LogicalFunctions

1)     Summary of logical functions

2)     Introduction of special logical operators

3)     Synergy of logical functions & operators

4)     How functions interact with date, number &text

5)     Running count & running total


3.     Reference Functions

1)     Switch source by CHOOSE function

2)     Range protection without ‘protect sheet’

3)     Mixed application of VLOOKUP, MATCH & INDEX

4)     Deep dive into offset

5)     ‘One-click’ vs ‘memory’

6)     How to maintain hyperlink

7)     Charm of indirect reference


4.     MathematicFunctions

1)     ‘Random’ power

2)     Equally distributed data in random group

3)     Convert between different measurements

4)     Rounding functions are far more than ‘round’

5)     Comparison between three advanced roundingfunctions

6)     Round to nearest 5 cents/5 minutes


5.     Text Functions

1)     Locate and extract text string from data

2)     Combine text string with CONCAT & TEXTJOINfunctions

3)     Convert data types with VALUE & NUMBERVALUEfunctions

4)     Case: Auto-updated comments in financial reports

5)     Use functions to process data cleansing

6)     Case: Convert data to handle dataconfidentiality issues

7)     ASNI code for text characters

8)     Case: Add line break in textbox

9)     N & T functions in number calculations

10)  Exact comparison of text string


6.     Date and Time Functions

1)     Date & time functions structure

2)     Case: Maturity date of term deposit note

3)     Case: dynamic date and time entry and versioncontrol

4)     Use DATEIF functions to get probationary periodending date

5)     Calculating end-of-month dates with EDATE and EOMONTH

6)     Case: Employee’s age for long service leaveprovision

7)     Use WORKDAY function to get the completion datefor projects

8)     Case: Calculate YTD and YTG sales budget amount

9)     Case: Count working days in each month

10)  Date & time data format and conversion

11)  Case: Cash flow forecast – Payroll Payments

12)  How to get week number for a given date


7.     StatisticalFunctions

1)     Use CONTA to build dynamic data range

2)     Case: How to use DISTINCTCOUNT to get customer numbers

3)     Case: Get MAX / MIN amount based on criteria

4)     Case: How many percentage of sales contributedfrom top 5 customers

5)     Rank data with RANK, RANK.EQ and RANK.AVEfunctions

6)     Get average value with AVERAGE, MEDIAN and MODE functions

7)     Case: Sales transactions distribution indifferent amount groups

8)     Case: Sales forecast based on sales team FTE information


8.     Financial Functions

1)     Analysing Loans, Payments and Interest

2)     PMT, PPMT and IPMT to calculate the principaland interest each loan payment

3)     Different repayments for even-principal methodcomparing with even-payment

4)     Case: How to help your children to buy his/herfirst $2m investment property

5)     Difference between nominal and effective interest rate

6)     Case: Accrued interest in bond investments

7)     Case: Timeshare scheme investment

8)     Case: How many periods needed to invest to reachto target

9)     Calculate depreciation using thestraight-line method

10)  Calculate depreciation using the decliningbalance method

11)  Calculate depreciation using the double-decliningbalance method

12)  Calculate depreciation using thedouble-declining balance method

13)  Case: How to calculate net present value given irregular inputs

14)  Case: How to calculate internal rate of return for irregular cash flows


  1. 评论晚了,还是更喜欢Terry老师的讲课风格,简单明了直接,没有一句废话,Charles老师讲的部分说实话有一些没有听懂。课上讲的东西都非常的实用,这个费用花的很值得,谢谢两位老师

  2. Terry老师真的非常认真负责,同学的问题都给一一讲解,课程内容也很丰富。如果有一些更加comprensive的例子介绍工作中运用到的场景会更棒。

  3. 这绝对是让人收获颇丰的一堂课,在一天内学到的Excel知识超过了过去五年工作里学到的Excel知识。但是希望提供详细课程解释备注和Notes,以便复习巩固。

  4. 非常充实,干货满满的一节课。老师抓紧时间教给大家有用的信息,没有一分钟浪费。收获了很多,接下来就是要好好运用到实际生活中去。谢谢terry老师,一如既往的受益匪浅!

  5. 课程内容很不错,,但希望在一些比较比较复杂的function和比较复杂的case (i.e. Payroll, depreciation)里,能够放一些explanation notes .尤其是到了下午,课程难度比上午课程大,也比较赶。有些explanation notes 在那里会稍微容易一些跟上课程进度

  6. Generally is really good, but some answers for some exercises still not clear. Some quick access like ctrl +shift +enter or down is better to put it on the screen And when exactly it use rather than just said it, otherwise students who attended online will not follow it easily.

  7. 课程内容海量,物有所值。但信息量之大一天的课程感觉很匆忙,有些跟不上。个人感觉这个课程需要两天的时间。期待重听!两位老师辛苦了,谢谢!

  8. 还不错,温故知新了,系统梳理了一下还有所拓展。

  9. 很系统地介绍了excel函数的计算和作用。有针对性,也很实用。感谢老师们的精心准备!唯一美中不足就是时间太有限,8个小时的课程只能让我们大概了解一下,如果能课后布置额外的练习和答疑,对我们的帮助将更大。

  10. Terry讲解的很清楚,练习也多,感觉收获很大。Charles术语很专业,但是重点不突出,而且讲的多联系少,跟的很辛苦……

  11. 谢谢你的意见。这次课程老师加了很多新内容,有很多技术想分享给大家, 所以确实拖了比较长时间才结束。下次上课我们在这方面会注意。

  12. 感谢Terry和Charls费心整理各类函数应用,非常全面、系统。通过这次讲座可以温故而知新,很有收获。希望自己多练习,融会贯通,在实际工作中能灵活运用。

  13. Although some sections can not been linked with the workplace practices at the moment, it is so useful to be understood and ‘be known’ therefore it can be applied whenever need later.

    The course is intensive. It would be great the course runs more frequently with the oppunities to attend it again.

  14. 课程内容不错 1. 但是早上开始比较晚 没准时开始上 所以后面上课时间很赶。 @@2. 课程广度有 但是特别希望有多个复杂例子对 offset indirect indext 的综合例子 这是adv excel 还是很有期待的 希望可以开发一下脑洞 3老师很有经验 各种问题回答很好。都到位 辛苦了 期待下次

    • 谢谢您的反馈意见。这一次上课的同学比较多,课前准备工作耗时比较多,推迟了10多分钟开始。另外因为课程内容很多,我们想尽量多把函数的实战应用都告诉同学们,所以下午延长了1个小时才结束。时间控制方面下一次我们要改进,谢谢您的意见。

      2. offset indirect index的综合多用于动态区域和动态商业图表。这部分我们在相关的图表课程里有详细的讲解。为避免重复,这部分我们没做很深入的展开,把时间主要集中在函数思维框架的搭建和各类函数在数据处理与商业分析实战应用。


Leave a Reply