Introduction

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 of the essential aspects of Excel-from the basics to more advanced topics.

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 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.

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 new 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. The topic of VBA is a rich one that deserves its own book, so we have also written Excel 2019 Power Programming with VBA (Wiley, 2019). Nonetheless, this book offers a robust set of chapters that will get you started leveraging VBA to automate and enhance your Excel solutions.

Software Versions

This book was written for the desktop version of Microsoft® Excel 2019 for Windows. 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. Excel 2016 and Excel 2013 users will also find the information in this book relevant.

If you are using the Office 365 version of Excel, you may very well see features in your version of Excel that are not covered here. Over the last few years, Microsoft has adopted an agile release cycle, releasing updates to Office 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 this section to learn some of the typographical and organizational conventions that this book uses.

Excel commands

Excel uses a context-sensitive Ribbon system. The words along the top (such as File, Insert, Page Layout, and so on) are known as tabs. Click a tab, and the Ribbon displays the commands for the selected tab. Each command has a name, which is (usually) displayed next to or below the icon. The commands are arranged in groups, and the group name appears at the bottom of the Ribbon.

The convention we use is to indicate the tab name, followed by the group name, followed by the command name. So, the command used to toggle word wrap within a cell is indicated as follows:

• Home ? Alignment ? Wrap Text

You'll learn more about the Ribbon user interface in Chapter 1, "Introducing Excel."

Typographical conventions

Anything that you're supposed to type using the keyboard appears in a bold monospaced font. Lengthy input usually appears on a separate line. Here's an example:

Names of the keys on your keyboard appear in normal type. When two keys should be pressed simultaneously, they're connected with a plus sign, like this: "Press Ctrl+C to copy the selected cells."

The four "arrow" keys are collectively known as the navigation keys.

Excel built-in worksheet functions appear in monospaced font in uppercase like this: "Note the SUMPRODUCT function used in cell C20."

Mouse conventions

You'll come across some of the following mouse-related terms, which are all standard fare:

• Mouse pointer This is the small graphic figure that moves on-screen when you move your mouse. The mouse pointer is usually an arrow, but it changes shape when you move to certain areas of the screen or when you're performing certain actions.
• Point Move the mouse so that the mouse pointer is on a specific item; for example, "Point to the Save button on the toolbar."
• Click Press the left mouse button once and release it immediately.
• Right-click Press the right mouse button once and release it immediately. The right mouse button is used in Excel to open shortcut menus that are appropriate for whatever is currently selected.
• Double-click Press the left mouse button twice in rapid succession.
• Drag Press the left mouse button and keep it pressed while you move the mouse. Dragging is often used to select a range of cells or to change the size of an object.

For Touchscreen Users

If you happen to be using one of these devices, you probably already know the basic touch gestures.

This book doesn't cover specific touchscreen gestures, but these three guidelines should work most of the time:

• When you read "click," you should tap. Quickly touching and releasing your finger on a button is the same as clicking it with a mouse.
• When you read "double-click," tap twice. Touching twice in rapid succession is equivalent to double-clicking.
• When you read "right-click," press and hold your finger on the item until a menu appears. Tap an item on the pop-up menu to execute the command.

Make sure you enable Touch mode from the Quick Access toolbar. Touch mode increases the spacing between the Ribbon commands, making it less likely that you'll touch the wrong command. If the Touch mode command is not in your Quick Access toolbar, touch the rightmost control and select Touch/Mouse Mode. This command toggles between normal mode and Touch mode.

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 probably find some new information here.
• 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.
• 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, 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, pivot tables, 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...