George - Mastering Excel 2019 An All-in-One Tutorial Resource

Mastering Excel 2019

An All-in-One Step by Step Tutorial Resource

Nathan George

Other Books by Author

Excel 2019 Macros and VBA: An Introduction to Excel Programming

Excel XLOOKUP and Other Lookup Functions

Website: https://www.excelbytes.com

Copyright 2020 Nathan George

All rights reserved. The right of Nathan George to be identified as the author of this work has been asserted by him in accordance with the Copyright, Designs and Patents Act, 1988. It is illegal to copy, distribute, or create derivative works from this book in whole or in part or to contribute to the copying, distribution, or creating of derivative works of this book.

***

The information given in this book is given in good faith and belief in its accuracy at the time of publication. The author and publishers disclaim any liability arising directly or indirectly from the use, or misuse, of the information contained in this book.

Introduction

Welcome to Mastering Excel 2019 , your all-in-one guide to the latest and most powerful version of Excel from Microsoft 365. This book contains everything you need to know to master the basics of Excel and a selection of advanced topics relevant to real-world productivity tasks. This guide has been designed to be a resource for you whether youre an Excel beginner, intermediate user, or a power user.

This book is concise and to the point, using clear and practical examples that you can adapt to your own needs. The aim is to show you how to perform tasks in Excel as quickly as possible without getting bogged down with unnecessary verbosity and filler text that you may find in some other Excel books. This book does not only show you how to use specific features but also in what context those features need to be used.

Mastering Excel 2019 is made up of three books that have been brought together and organized as an all-in-one guide.

How This Book Is Organized

Book 1: Excel Basics

This book starts with the very basics. You first get to familiarize yourself with the Excel user interface and Ribbon. You learn how to create, edit, format, organize and print your worksheets. You learn how to enter formulas and perform different types of calculations in Excel. You learn how to use named ranges, Excel tables, charts, and drop-down lists. You learn how to sort and filter your data in different ways. This book provides you with most of what you would need for everyday Excel tasks including all the fundamentals you need to progress to topics in Books 2 and 3 like functions and pivot tables.

Book 2: Excel Advanced Topics

This book covers a selection of topics that will enable you to take advantage of more powerful tools in Excel to perform tasks more geared for the power user. In this book youll learn how to transform data with various data tools; import and use data from external sources like CSV files and Microsoft Access databases; troubleshoot and fix formula errors; deploy macros to automate repetitive tasks; create and analyze projections with What-If Analysis; analyze large sets of data with Pivot Tables and Pivot Charts; and protect workbooks, worksheets, or ranges.

Book 3: Excel Functions

This book is a deep dive into Excel functions and covers over 70 of the most useful functions in Excel from different categories. The functions covered include lookup and reference functions like XLOOKUP and VLOOKUP; math and statistical functions like SUM, SUMIF, COUNTIF, and COUNTA; logical functions like IF and IFS; date functions like DATEDIF, NETWORKDAYS, and TIME; text functions like MID, TEXTJOIN, and CONCAT; and financial functions like PV, FV, and NPV.

The functions covered in this book have been carefully selected based on how often theyre used in common Excel tasks and specialized work. Each function is covered in detail including the syntax, description of arguments, and practical examples to demonstrate its use. You also learn how to combine different functions to solve more complex problems.

How to Use This Book

Mastering Excel 2019 can be used as a step-by-step training guide as well as a reference manual that you come back to from time to time. If youre a beginner, you ideally want to read all the chapters in Book 1 in sequential order. Book 1 provides all the fundamentals you need to proceed to selected topics of your choosing in Books 2 and 3. You also want to cover commonly used functions in Book 3 like SUM, IF, IFS, and XLOOKUP as these will come in handy for everyday Excel use.

If youre an intermediate or power user, you can read this book cover to cover or skip to specific chapters. Although the topics have been organized logically, as much as possible, each topic has been designed to enable you to read it as a standalone tutorial to learn how to perform a specific task. Book 3 in particular has been designed as a resource guide for Excel functions so the chapters are as self-contained as possible.

There are many ways to carry out the same task in Excel, so, for brevity, I have focused on the most efficient way of carrying out a task. On some occasions, however, I also provided alternative ways to carry out a task.

As much as possible, the menu items and commands mentioned are bolded to distinguish them from the other text. I have also included many images to illustrate the features and tasks being discussed.

Assumptions

The software and hardware assumptions made when writing this book is that you already have Excel 2019 installed on your computer and that youre working on the Windows 10 platform.

Important : Excel 2019 is the first version of Excel that is not compatible with previous versions of Windows. If you have an earlier version of Windows, for example, Windows 7 or 8, and youre subscribed to Microsoft 365 (previously Office 365), then the newest version of Excel you can run will be Excel 2016.

If you are running Excel 2016 you can still use this book (as long as youre aware that some of the dialog boxes shown may look slightly different). Many of the features covered are present in previous versions of Excel.

If you are using Excel 2019 on a Mac, then simply substitute any Windows keyboard commands mentioned in the book for the Mac equivalent. All the features within Excel remain the same for both platforms.

If youre using Excel on a tablet or touchscreen device, again, simply substitute any keyboard commands mentioned in the book with the equivalent on your touchscreen device.

Practice Files

Downloadable Excel files have been provided to save you time if you want to practice in Excel as you follow the examples in the book. All examples are fully detailed in the book and these files have simply been provided to save you some time in recreating the sample data, so theyre optional.

You can practice by changing the data to view different results. Please note that sample files have only been included for chapters where the examples use a sizable amount of sample data. You can download the files from the following link:

https://www.excelbytes.com/masteringexcel2019dl

Notes:

• The files have been zipped into one download. Windows 10 comes with the functionality to unzip files. If your OS does not have this functionality, youll need to get a piece of software like WinZip or WinRAR to unzip the file.
• The files are Excel 2019 files so you will need to have Excel installed on your computer to open and use these files (preferably Excel 2013 and above).

• If you are having any problems downloading these files, please contact me at . Include the title of this book in your email, and the practice files will be emailed directly to you.

Book 1

Excel Basics

Book 1

Excel Basics starts from the basics, you learn how to create, edit, format, and print your worksheets. You learn how to enter formulas and carry out various types of calculations with Excel. This book provides you with most of what you would need for everyday Excel tasks including the fundamentals to progress to topics in Books 2 and 3 like functions, pivot tables, security, and macros.