Introduction

Excel 2019 is a roll-up of most of the features introduced to Office 365 subscribers in the three years since Excel 2016 was released. Although this book title is Excel 2019 Inside Out, I don’t actually expect that many readers will be using the perpetual version of Excel 2019. Instead, most customers have switched over to Office 365, where new features are introduced monthly.

The Excel team has been responsive to items requested through the Excel.UserVoice.com website, and many small features and improvements happened in Excel 2019. Here are the new features as of the official release of Office 2019:

• Co-authoring has been massively improved. If you have several people who must access the same workbook at the same time, you’ll have no issue as long as you save your workbook to OneDrive. Read more in Chapter 1, “What’s New in Excel 2019.”

• The charting team introduced two new chart types: Funnel and Filled Map Charts. The May 2018 announcement that Excel charts would soon support custom visuals from Power BI did not make it to Excel 2019, but the update will likely roll out to Office 365 in early 2019. Read about these charts in Chapter 23, “Graphing Data Using Excel Charts.”

• There are new features in Power Query (found in the Get & Transform group on the Data tab). You can now Split By Delimiter to Rows instead of columns, and you can create new columns by typing examples. Read about Power Query in Chapter 13, “Transforming Data.”

• Pivot tables will not Sum instead of Count if your data has a mix of numbers and empty cells. Another great improvement in pivot tables is the Pivot Table Defaults feature. If you want every new pivot table to start in tabular layout instead of compact layout, this feature is for you. Read more in Chapter 15, “Using Pivot Tables to Analyze Data.”

• Data types debut with support for Geography and Stock Quotes. It seems likely that more data types will be introduced for Office 365 subscribers. See Chapter 5, “Understanding Formulas.”

• Ideas is a new artificial intelligence feature. Excel analyzes up to 250,000 cells of data and uses artificial intelligence to provide more than 30 charts. For now, this feature is exclusive to Office 365. See Chapter 15, “Using Pivot Tables to Analyze Data.”

• Excel 2019 offers six new calculation functions. CONCAT, TEXTJOIN, MAXIFS, and MINIFS are covered in Chapter 8, “Using Everyday Functions: Math, Date and Time, and Text Functions.” The new SWITCH and IFS functions are covered in Chapter 9, “Using Powerful Functions: Logical, Lookup, and Database Functions.”

• Power Pivot is now freely available to anyone who uses Office 2019. You can insert new calculated fields using the DAX formula language. DAX improvements include the ability to add medians to pivot tables or report text in the values field of pivot tables. Read about Power Pivot in Chapter 17, “Mashing Up Data with Power Pivot.”

• Office 365 is getting a new ribbon interface. The icons have been redrawn, and the selected tab is now denoted with an underline instead of an outline. However, Excel 2019 is still sporting the old ribbon.

• Excel 2019 adds support for icons and 3D objects. The icon feature offers 180 icons that you can insert. Support for 3D objects lets you rotate and view various objects in Excel.

• Office 365 offers seven new array formulas: SEQUENCE, RANDARRAY, SORT, SORTBY, UNIQUE, FILTER, and SINGLE. A single formula can now spill into adjacent cells, eliminating the need to press Ctrl+Shift+Enter to generate an array. These are covered in Chapter 1, “What’s New in Excel 2019.”

• The Keep The Copy feature keeps copied cells on the Clipboard until you remove them by pressing Esc. In the past, it was often frustrating that simple tasks such as inserting one extra row would cause the Clipboard to clear. Now there’s a solution for that problem.

• Excel will no longer nag you when you close or save a CSV file. There used to be warnings that you would lose your formulas and formatting. These were particularly annoying when you only needed to open the CSV file to check something without making changes. Excel always threw a warning when you tried to close the file.

• Insert Data From Picture is a new image-recognition capability in Excel that lets you quickly convert a picture of a data table into an Excel file.

• Forms.Office.Com allows you to design an online survey, collect answers in real time, and then download the results to Excel. This is covered in Chapter 1, “What’s New in Excel 2019.”

• Although this book covers VBA as the macro language, there is one new interesting feature in programmability: Any user-defined functions written in JavaScript now work in both the desktop version of Excel and in Excel Online. JavaScript is still slow and clunky compared to VBA, but it could be the hint of what’s coming in the future.

• Microsoft has a new focus on accessibility. The new Modern Sound Scheme provides gentle audible feedback for a number of actions in Excel. See Chapter 1, “What’s New in Excel 2019.”

• Power BI Desktop and Power BI allow you to share your Excel workbooks and dashboards over the Internet or to a tablet or phone. Chapter 28, “Sharing Dashboards with Power BI,” introduces this new functionality.

• Picture transparency has been added to Office 365. Now you can see the values in cells behind your picture.

• The algorithm for VLOOKUP has been improved. If you are doing repetitive lookups, VLOOKUP speed could be ten times faster.

Who This Book Is For

This book is for anyone who uses Excel twenty hours a week or more. Whether you use Excel for organizing your to-do list or to analyze 5 million rows of call center data every day, this book includes the information you need to solve problems quickly and easily.

Assumptions About You

I like to believe most of my readers use Excel 40 hours a week, and those are the weeks you are on vacation. At the very least, I’m assuming you regularly use Excel for your job. You are comfortable using Excel formulas beyond AutoSum. You likely know and use VLOOKUP and Pivot Tables regularly. You are looking for the fastest and most efficient ways to finish tasks in Excel.

How This Book Is Organized

This book gives you a comprehensive look at the various features you will use. This book is structured in a logical approach to all aspects of using the Windows-based versions of Excel 2019 or Excel in Office 365.

Part I, “The Excel Interface,” covers the ribbon, customizing Excel, and keyboard shortcuts.

Part II, “Calculating with Excel,” covers all Excel calculation functions.

Part III, “Data Analysis with Excel,” covers Power Query, Pivot Tables, and other features that help you perform data analysis.

Part IV, “Excel Visuals,” covers charting, 3D Map, and Power BI.

About the Companion Content

I have included the Excel workbooks I used to create the screenshots in this book to enrich your learning experience. You can download this book’s companion content from the following page:

microsoftpressstore.com/Excel2019InsideOut/downloads

The companion content includes the following:

• Workbooks used to create the examples in the workbook

• Sample data that you can use to practice the concepts in the book

• VBA macros from Chapter 19

Acknowledgments

Thanks to all the Excel project managers who were happy to take the time to discuss the how or why behind a feature. At various times, Ash Sharma, Sam Radakovitz, David Gainer, Johnnie Thomas, Sangeeta Mudnal, Joe McDaid, Urmi Gupta, and Prash Shirolkar pitched in to help with a particular issue. Thanks to former Excel project managers Rob Collie and Dany Hoter for their assistance. Thanks to Tracy Syrstad, Barb Jelen, Jeannie Muncy, Zeke Jelen, and Suat Ozgur for making up the MrExcel.com team.

Other Excel MVPs often offered their take on potential bugs. I could send a group email over a weekend, and someone like Ken Puls, Roger Govier, Liam Bastick, Jon Peltier, Jan-Karel Pieterse, Charles Williams, Brad Yundt, or Ingeborg Hawighorst would usually respond. I particularly loved launching a missive just after the Microsoft crew in Building 36 went home on Friday evening, knowing they would return on Monday morning with 40 or 50 responses to the conversation. Without any Excel project managers to temper the discussion, we would often have designed massive improvements that we would have liked to have implemented in Excel. Someone would show up on Monday and tell us why that could never be done.

Thanks to my fellow YouTube Excel MVPs: Mynda Treacy, Oz Du Soleil, Jon Acompora, Leila Gharani, and Mike Girvin. Check out our creative and funny #ExcelHash competitions on YouTube.

Bob Umlas is the smartest Excel guy that I know, and I was thrilled to have him as the technical editor for this book.

Putting together a book requires careful coordination with editors, proofreaders and compositors. My sincere thanks to Charlotte and Rick Kughen, Sarah Kearns, and Tricia Bronkella.

Indexing is a tough job that requires attention to detail. The index created by Cheryl Lenser gets two thumbs up from Nellie Jay.

I’ve been writing books for Loretta Yates since 2004. If my spreadsheet is correct, this is our 27th project. Thanks for 15 years of trusting me with your books.

Thanks to Master Chief Rusty Pedersen for all of the fiberglass and carburetor knowledge that went in to this book. When I am not dealing with Excel, Mary Ellen Jelen and I photograph rocket launches at Cape Canaveral Air Force Station. Thanks to my associates at WeReportSpace.com: Michael Seeley and Jared Haworth. Robert F. Jelen, my 96-year-old father, lives next door to us. I enjoy our daily lunches. I apologize that I had to miss a few when deadlines for this book were looming.

I wrote this book at the Kola Mi Writing Camp. The staff there was fantastic.

Mary Ellen Jelen did a great job of keeping me on track with this book.

Support and Feedback

The following sections provide information on errata, book support, feedback, and contact information.

Stay in Touch

Let’s keep the conversation going! We’re on Twitter:

http://twitter.com/MicrosoftPress

http://twitter.com/MrExcel

Errata, Updates, and Book Support

We’ve made every effort to ensure the accuracy of this book and its companion content. You can access updates to this book—in the form of a list of submitted errata and their related corrections—at:

microsoftpressstore.com/Excel2019InsideOut/errata

If you discover an error that is not already listed, please submit it to us at the same page.

If you need additional support, email Microsoft Press Book Support at:

[email protected].

Please note that product support for Microsoft software and hardware is not offered through the previous addresses. For help with Microsoft software or hardware, go to http://support.microsoft.com.