Chapter 1: Excel Formulas - A Quick Primer Chapter Goal: Start the journey to mastering formulas in Excel
No of pages: 25
This chapter is the first of many on formulas in Excel. It will start with basic mathematical operations, explain the structure of formulas inside-out, look at some basic functions and cover absolute cell addresses.
Chapter 2: Logical Formulas Chapter Goal: Learn the important logical functions in Excel.
No of pages: 35
This chapter will explain how and when to use logical functions including IF, IFS, AND, OR, XOR, IFERROR, IFNA and SWITCH. These functions are the initial building blocks of automated Excel models and reports. Their importance cannot be understated.
Chapter 3: Defined Names Chapter Goal: Understand the use and hidden brilliance of defined names.
No of pages: 25
This chapter explores defined names in-depth. This little-known feature has huge benefits in how we use and deploy ranges and formulas in Excel.
Chapter 4: You Need to Start Using Tables
Chapter Goal: To understand and master the use of Tables
No of pages: 30
This chapter will introduce the reader to tables in Excel. It will explain the many benefits to their use and how to use them effectively.
Chapter 5: Manipulating Text Chapter Goal: Learn the most useful text functions of Excel
No of pages: 40
This chapter will look at the formulas to extract, combine, replace, and format text. These include but are not limited to LEN, MID, SEARCH, DOLLAR, CONCAT and VALUE.
Chapter 6: Working with Dates and Times Chapter Goal: Learn the popular date and time functions of Excel
No of pages: 40
This chapter will explain how dates and times are stored, and are to be used, in Excel. We will use different date and time functions to perform transformations and various calculations.
Chapter 7: The Infamous VLOOKUP Function Chapter Goal: Master this vital function
No of pages: 35
This chapter is dedicated to the third most used function in Excel. It fully explains how to use VLOOKUP in a way people have probably not heard before. It will also cover some insider tricks to avoid common VLOOKUP limitations and mistakes. A variety of examples will be shown.
Chapter 8: Advanced Lookup Formulas
Chapter Goal: Learn more advanced lookup functions.
No of pages: 40
In this chapter, we explore functions such as INDEX, OFFSET, MATCH, CHOOSE and INDIRECT to build upon the concepts learnt with VLOOKUP. We will see alternative ways to complete a task dependent upon the scenario.
Chapter 9: The Awesome SUMIFS, COUNTIFS and Friends Chapter Goal: Learn how to use these two exceptional functions.
No of pages: 40
This chapter will explain these two functions with various examples. They are two of the best and have come to my aid so many times in the past. The chapter also shows the AVERAGEIFS, MINIFS and MAXIFS functions.
Chapter 10: Next Level Aggregation Functions Chapter Goal: Learn the SUMPRODUCT and AGGREGATION functions.
No of pages: 30
This chapter will look at the SUMPRODUCT and AGGREGATE functions. This chapter follows on nicely from the previous one as these functions have more power than SUMIFS and COUNTIFS.
Chapter 11: Dynamic Array Formulas Chapter Goal: Understand how to efficiently use dynamic array formulas
No of pages: 40
This chapter introduces the reader to dynamic array formulas. These formulas are available to 365 users only. Therefore, it is essential that it is understood how these formulas are best used, and how they may affect you if you are not a 365 user.
Chapter 12: XLOOKUP - The New Kid on the Block Chapter Goal: Learn how and when to use the XLOOKUP function
No of pages: 20
This chapter is all about the XLOOKUP function. What does it offer that the other lookup function could not? When is the best time to use it? All will be covered in this chapter.
Chapter 13: FILTER Function - The Game Changer Chapter Goal: Learn how to effectively use the FILTER function
No of pages: 20
This chapter focuses on the impressive FILTER function. Several examples are shown to demonstrate the FILTER function performing tasks that other functions cannot do.
Chapter 14: Table Rankings and Top N Lists Chapter Goal: Learn to create different dynamic table rankings
No of pages: 20
This chapter uses functions and skills learnt over previous chapters to create different table ranking and top N lists. These are common tasks, but difficult to perform without advanced Excel formula skills.
Chapter 15: Getting Status Information Chapter Goal: Learn how to retrieve status of cells and the Excel environment
No of pages: 20
This chapter will look at the CELL, TYPE, and INFO functions of Excel. They are used to return information about cells, data types and the operating environment. We can the utilise this information in other functions.
Chapter 16: The LET and LAMBDA Functions Chapter Goal: Understand these two new functions
No of pages: 30
This chapter will look the LET and LAMBDA functions introduced to Excel 365 in 2020/2021. They are freshly released and change how Excel users will write complex formulas. This chapter will explain how and when to use these power functions.
