Welcome to the world of Excel. Okay, that's a bit cheesy. But if you look around the business world, the financial world, the manufacturing world, and any other industry you can think of, you will see people using Excel. Excel is everywhere. It is by far the most popular program in the history of business applications. So, we truly are living in a world of Excel. This is probably why you've picked up this book. You need a way to accelerate your learning and get up to speed quickly.

Well, worry not, dear reader. Whether you're boning up on Excel for a new job (congratulations, by the way), for a school project, or just for home use, this book is perfect for you.

In this book, we've organized everything that one would need to know to get up and running quickly with Excel. And we've made certain that this book contains many useful examples and lots of tips and tricks that cover all the essential aspects of Excel—from the basics to more advanced topics.

Looking at What's New in Excel 365

Here's an overview of what's available to those with Microsoft 365 subscriptions as of the October 2021 update and those using the stand-alone (perpetual license) version of Office/Excel 2021.

• Dynamic array inherent to Excel's calculation engine  Dynamic array behavior is now fundamentally part of Excel's calculation engine. When any function uses an array that returns multiple values, the results will be output to a spill range. This even includes older functions that weren't designed to output arrays. With dynamic arrays, Excel moves into a new era where you no longer need to be a formula guru to leverage the power of arrays. The underlying changes to Excel's calculation engine make legacy array formulas obsolete.
• New dynamic array functions  With the introduction of dynamic arrays, Microsoft released several new functions that leverage dynamic arrays to improve the ability to carry out complex formula operations with ease. These new functions can remove duplicates, extract unique values, filter data, dynamically sort data, and perform sophisticated look-ups.
• Formula variables  Formula variables enable you to create a container of sorts that holds the results of a function or calculation for use later in other calculations. Armed with the new LET function, you can simplify your formulas and potentially improve performance.
• Power Query Custom Data Types  The Custom Data feature of Power Query allows you to store multiple columns of data in one column as metadata. You can think of a Custom Data Type as a kind of container that allows you to store the data for many columns, then use that data elsewhere in your workbook.
• Power Query import from PDF  Power Query now allows you to import and transform data directly from PDF files.
• Power Query Data Profiler  Power Query's new data profiling capabilities allow you to know your data and identify potential issues before using it. You can leverage data profiling in Power Query to get a better understanding of your data and address problem areas before they become a problem later in your reporting processes.
• Import custom 3D models  Excel now allows you to import your own custom 3D model graphics, including 3D Manufacturing, Filmbox, Binary GL Transmission, Polygon, and StereoLithography files.
• Threaded comments  Threaded comments allow for discussions between contributors directly within a workbook. With the look and feel of comments on a blog or online forum, anyone with access to the workbook can enter their own comments or reply to yours. Traditional comments are now called notes.
• Action Pen  With the new Action Pen feature, you can handwrite directly in the cells and Excel will convert writing into data! Primarily designed for mobile devices, Action Pen makes it easy to edit your workbooks on the go.

Is This Book for You?

This book is designed to enhance the skillset of users at all levels (beginning, intermediate, and even advanced users).

Start at the beginning if you're new to Excel. Part I covers everything you'll need to get familiar with entering data, managing workbooks, formatting worksheets, and printing. You can then move on to Part II, where you'll discover the ins and outs of Excel formulas and functions.

If you're a seasoned analyst, hoping to enhance your data visualization and analytic toolset, check out Part III and Part IV. We've included many examples and tips for analyzing data and creating visually appealing Excel dashboards.

If you've been working with an earlier version of Excel, this book is for you too! Part V covers the Power Pivot and Power Query toolsets. In the past, these features were free Microsoft add-ins that were used peripherally. Now they've become an essential part of how Excel manages data and interacts with external data sources.

If you want to learn the basics of Visual Basic for Applications (VBA) programming, you'll find what you need in Part VI. This part offers a robust set of chapters that will get you started leveraging VBA to automate and enhance your Excel solutions.

Software Versions

This book covers features released as of the October 2021 update of Microsoft 365. The functionality explored here will be available to those on Microsoft 365 subscriptions and those using the stand-alone (perpetual license) version of Office/Excel 2021 for the desktop. Please note that this book is not applicable to Microsoft Excel for Mac.

Excel is available in several versions, including a web version and a version for tablets and phones. Though this book was written for the desktop version of Excel, much of the information here will also apply to the web and tablet versions.

Over the last few years, Microsoft has adopted an agile release cycle, releasing updates to Microsoft 365 practically on a monthly basis. This is great news for those who love seeing new features added to Excel. It's not so great if you're trying to document the features of these tools in a book.

Our assumption is that Microsoft will continue to add new bells and whistles to Excel at a rapid pace after publication of this book. Thus, you may encounter new functionality not covered in this book. That being said, Excel has a broad feature set, much of which is stable and here to stay. So, even though changes will be made to Excel, they won't be so drastic as to turn this book into a doorstop. The core functionality covered in these chapters will remain relevant—even if the mechanics change a bit.

Conventions Used in This Book

Take a minute to scan the following sections to learn some of the typographical and organizational conventions that this book uses.

="Part Name: " &VLOOKUP(PartNumber,PartList,2)

How This Book Is Organized

Notice that the book is divided into six main parts.

• Part I: Getting Started with Excel  This part consists of eight chapters that provide background about Excel. These chapters are considered required reading for Excel newcomers, but even experienced users will find new information on threaded comments and working with Excel on mobile devices.
• Part II: Working with Formulas and Functions  The chapters in Part II cover everything that you need to know to become proficient with performing calculations in Excel. Chapter 10 is required reading, even for seasoned professionals, as it covers the new dynamic array functionality inherent in Excel's calculation engine.
• Part III: Creating Charts and Other Visualizations  The chapters in Part III describe how to create effective charts. In addition, you'll find chapters on the conditional formatting visualization features and Sparkline graphics and a chapter with lots of tips on integrating graphics into your worksheet.
• Part IV: Managing and Analyzing Data  Data analysis is the focus of the chapters in Part IV. Here you'll find chapters focusing on data validation, PivotTables, conditional analyses, and more.
• Part V: Understanding Power Pivot and Power Query  The chapters in Part V take an in-depth look at the functionality found in Power Pivot and Power Query. Here you'll discover how to develop powerful reporting solutions with Power Pivot as well as how to leverage Power Query to automate and steps for cleaning and transforming data.
• Part VI: Automating Excel  Part VI is for those who want to customize Excel for their own use or who are designing workbooks or add-ins that are to be used by others. It starts with an introduction to recording macros and VBA programming, and then it provides coverage of UserForms, events, and add-ins.

How to Use This Book

This book is obviously not meant to be read cover to cover. Instead, it's a reference book that you can consult when you need help with the following situations:

• You're stuck while trying to do something.
• You need to do something that you've never done before.
• You have some time on your hands, and you're interested in learning something new about Excel.

The index is comprehensive, and each chapter typically focuses on a single broad topic. Don't be discouraged if some of the material is over your head. Most users get by just fine by using only a small subset of Excel's total capabilities. In fact, the 80/20 rule applies here: 80 percent of Excel users use only 20 percent of its features. However, knowing only 20 percent of Excel's features still gives you a lot of power at your fingertips.

What's on the Website

This book contains many examples, and you can download the workbooks for those examples from the Web. The files are arranged in directories that correspond to the chapters.

The URL is www.wiley.com/go/excel365bible.

Please note that the URL is case sensitive, so use all lowercase letters.