Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas

ISBN-13: 9781484271247 / Angielski / Miękka / 2022 / 230 str.

Alan Murray

Opis

Intermediate

Spis treści

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.

O autorze

Alan Murray is a Microsoft MVP and Excel trainer. He has been helping people in Excel for over 20 years. He loves training and the joy he gets from knowing he is making peoples working lives easier.

Alan runs his own blog - Computergaga (https://computergaga.com) and writes for multiple other websites. His YouTube channel has over 550 videos and over 35 million views.

He organizes a free monthly Excel meetup in London where anyone can come learn Excel, chat, and enjoy each other’s company (https://www.meetup.com/London-Excel-Meetup-Group/).

Blurb

Enhance and upgrade your Excel knowledge with this comprehensive guide to formulas in Excel. Over 150 of the most useful Excel functions are covered with numerous practical examples of their use. This book is fully updated and includes examples of the most recently released functions in 2022. It is written in a manner that you can read it cover-to-cover or pick it up and learn something new in just 5 minutes.

The book begins with a primer on Excel formulas and functions. Starting from a basic level, but diving into intricate detail and building a solid understanding of the fundamentals. It may not always be sexy, but learning why, in addition to how, is important to truly understanding Excel formulas. Having strong fundamentals is the most advanced skill.

The book covers all the most popular functions in Excel including VLOOKUP, SUMPRODUCT, COUNTIFS, MATCH, SORT and so much more. You will learn how to return multiple results with a single formula. Harness the power of the dynamic array engine and functions such as SEQUENCE, SORTBY, UNIQUE, XLOOKUP and FILTER to create top N Lists, models and reports that would seem impossible without array formulas. In the final chapter, we discover the amazing LAMBDA function in Excel. Create your own Excel functions to simplify future Excel formulas and share them with others. 

In this book, the formulas are not only written to return values to the grid, but also for use with other Excel features such as charts and Conditional Formatting, to take them to another level. Practice files are provided to follow all examples shown in the book.

You will:

• Gain intimate knowledge of Excel formulas
• Understand efficient and practical use of Defined Names and Tables
• Master the most popular functions of Excel – VLOOKUP, COUNTIFS, MATCH, SUMPRODUCT, and so much more
• Learn to return multiple results from a single formula with the magical array formulas.
• Know the best lookup functions for dynamic spreadsheets
• Know true power of classic Excel functions such as IF, SUM, and INDEX
• Use new Excel functions including XLOOKUP, VSTACK, LET, and LAMBDA
  

​