Here are just a few of the things you will learn with this book:
Pages: 641
ISBN: 978-1909253520
Dimensions: 8.27 x 1.45 x 11.69 inches
Weight: 4.09 pounds
Dynamic Arrays are one of the biggest brand new features for Excel 2021.
The book will teach you all of the new Dynamic Array functions: XLOOKUP (the modern replacement for VOOKUP), XMATCH, UNIQUE, SORT, SORTBY, FILTER, RANDARRY and SEQUENCE. You’ll learn to use Spilled Arrays that change best-practice for many common business problems.
Another huge advance in Excel 2021 is the ability to create your own Custom Data Types. For the first time, Excel can now create objects with multiple properties that you can use directly in your worksheets.
The book will teach you how to use custom data types to create the multi-lingual form shown above without using any formulas.
A really useful new Excel 2021 feature is the ability to use Fuzzy Logic to match data even when the match isn’t perfect.
The book will teach you to perform Fuzzy Logic using Jaccard similarity. All of the above mis-spellings were automatically matched perfectly. This feature could save you many hours of work when working with external data.
It is often said that the ability to fully understand Pivot Tables separates competent Excel users from Excel novices. The above grab shows two pivot tables and two slicers combined into a dashboard.
The book will teach you absolutely everything there is to know about Pivot Tables. including slicers, calculated fields, calculated items, grouping, filter fields and multiple summations.
The core idea behind Power Maps (also known as 3D Maps) is to show visualizations of Excel data upon the surface of a map but this doesn’t begin to describe the huge feature set of this powerful Excel tool.
The book covers Power Maps in depth and that includes geocoding, data cards, multiple layers, annotations, scenes and fly-pasts. You’ll even create a HD video as you fly over your data.
The above form was created in a few minutes using Excel’s simple-to-use Form Controls.
The book will teach you how to add Option Buttons, Check Boxes, Combo Boxes, Spin Buttons, Buttons and many other controls. Once added they are really easy to bring to life. The screen grab above shows a Mortgage Calculator that you’ll create in one of the book’s sessions.
Excel only supports a million rows, but when Excel teams with Power Pivot, Excel can analyse billions of rows of data in the blink of an eye using a Power Pivot Data Model.
The book will teach you to create the special Star Schema data models required by Excel using Power Query and Power Pivot. This new way of working is often called Modern Data Analysis.
This isn’t an ordinary Excel pivot table – it’s an Excel Power Pivot Table (also called an OLAP Pivot Table). You can see that it can analyse multiple related tables.
The book will teach you how to prepare the relational data models needed to work with Power Pivot tables. You’ll then have the power to work faster and smarter than ever before.
DAX functions are very different to Excel functions but you’ll find many that are named just like their Excel counterparts.
The book will teach you important DAX concepts including calculated columns, calculated measures, implicit measures, explicit measures, row and filter context and the DISTINCOUNT , CALCULATE and ALL functions.
Excel can directly access the aggregated data that resides within a Power Pivot data model using the CUBEVALUE function.
The book will teach you how to automatically and instantly create CUBEVALUE functions with their associated MDX queries so that you can access aggregations within a Power Pivot data model directly from an Excel worksheet.
Excel Experts don’t use old-fashioned A1 style references when referencing dynamic data. They use tables and modern structured table references.
The book will teach you the professional way to work with tables and structured references and not ranges and A1 style references.
Excel Names make cell references easy to read in formulas. In the above example the formula =Sales-Cost leaves no room for error.
The book will teach you all there is to know about names and that includes single-cell names, range names, two-dimensional range names, dynamic formula-based names and named constants.
