Learn Excel Fast in Simple and Easy Steps
Mastering Excel 2019 is your all-in-one, step by step, tutorial 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 you'll encounter in your job, business or home.
This guide has been designed to be a resource for you whether you're an Excel beginner, intermediate user, or a power user. You will learn not only how to use specific features, but in what context those features need to be used.
Excel Made Easy Without the Jargon
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 the unnecessary filler text you may find in some other Excel books.
Three Books in One!
Mastering Excel 2019 is made up of Excel 2019 Basics, Excel 2019 Advanced Topics, and Excel 2019 Functions. Now brought together and organized as an all-in-one powerful guide. This book brings you all the powerful material in those books and more.
Mastering Excel 2019 also comes with downloadable sample files to enable you to follow the examples in the book hands-on.By reading this book, you will
Master all the fundamentals to quickly get up to speed with creating solutions for common Excel tasks.
Create basic and advanced formulas for different types of calculations.
Use Excel tables to easily manage and manipulate your data.
Filter, sort, and apply styles to data lists and Excel tables.
Analyze your data visually with charts and sparklines.
Use data from external sources like Access databases, CSV text files, and websites.
Deploy macros to automate repetitive tasks and increase your efficiency.
Create dynamic summaries of your data with pivot tables and pivot charts.
Analyze different data scenarios and projections with What-If Analysis tools.
Password-protect workbooks, worksheets, ranges, or even specific cells.
Create formulas with over 70 of the most useful and powerful Excel functions, including new dynamic array functions like XLOOKUP, XMATCH and FILTER.
Author(s): George, Nathan
Year: 2020
Language: English
Commentary: Mastering Excel 2019, An All-in-One Tutorial Resource
Pages: 537
Tags: Mastering Excel 2019, An All-in-One Tutorial Resource
Contents
Title
Copyright
Introduction
How This Book Is Organized
How to Use This Book
Assumptions
Practice Files
Book 1: Excel Basics
Chapter 1: Getting Started with Excel
1.1 Creating a New Excel Workbook
1.2 The Excel User Interface
1.3 Using AutoSave
1.4 Customising the Ribbon
1.5 Getting Help in Excel
Chapter 2: Entering and Editing Data
2.1 Entering and Editing Data Manually
2.2 Using AutoFill
2.3 Using Flash Fill
Chapter 3: Design and Organize Workbooks
3.1 Adding New Worksheets
3.2 Moving and Copying Worksheets
3.3 Freezing Rows and Columns
3.4 Applying Themes to Your Worksheet
Chapter 4: Organizing Your Data
4.1 Copying, Moving and Deleting Data
4.2 Find and Replace Data
4.3 Sorting Data
4.4 Filtering Data
Applying a Custom Filter
Changing the Sort Order of a Filtered List
Chapter 5: Formatting Cells
5.1 Arrange Cells, Rows and Columns
5.2 Applying Number Formats
5.3 Copy Cell Formatting
5.4 Conditional Formatting
Chapter 6: Performing Calculations with Formulas
6.1 Operators in Excel
6.2 Entering a Formula
6.3 Calculating Percentages
6.4 The AutoSum Tool
6.5 Quick Sum with the Status Bar
6.6 Calculating Date and Time
6.7 Relative and Absolute Cell Reference
6.8 Using Data from Other Worksheets
Chapter 7: Use Drop-down Lists and Validation Rules
7.1 How to Create a Drop-down List
7.2 Creating Data Validation Rules
Chapter 8: Named Ranges
8.1 What is a Named Range?
8.2 Creating a Named Range
8.3 Editing a Named Range
8.4 How to Use a Named Range
Chapter 9: Working with Tables
9.1 Preparing Your Data
9.2 Create an Excel Table
9.3 Choosing a Table Style
9.4 Sorting Data in a Table
9.5 Filtering Table Data
9.6 Adding a Totals Row to Your Table
9.7 Removing Table Attributes
Chapter 10: Creating Charts
10.1 Creating a Chart via the Quick Analysis Tool
10.2 Creating a Chart via the Excel Ribbon
10.3 Customising Charts
Switching the X and Y Axes
Change the Data Source
Adding Axis Titles
Chart Styles
10.4 Creating Sparkline Charts
Chapter 11: Printing Your Worksheet
Page Setup
Setting the Print Area
Preview and Print Your Worksheet
Book 2: Excel Advanced Topics
Chapter 1: Working with Multiple Workbooks
1.1 Managing Multiple Windows
1.2 Moving Data Between Workbooks
1.3 Moving Worksheets Between Workbooks
Chapter 2: Transform Data with Data Tools
2.1 Remove Duplicates
2.2 Delete Blank Rows
2.3 Convert Text to Columns
2.4 Data Consolidation
Chapter 3: Using External Data
3.1 Importing Data from Microsoft Access
3.2 Importing Text files
Using the Convert Text to Columns Wizard
3.3 Importing Data from a Website
3.4 Other Database Sources
Chapter 4: Troubleshoot and Fix Formula Errors
4.1 Trace Precedents and Dependents
4.2 Evaluate a Formula
4.3 Using the Watch Window
Chapter 5: Use Macros to Automate Excel Tasks
5.1 First Things First
The Three Ways to Start the Macro Recorder
5.2 Recording a Macro
5.3 Running a Macro
5.4 Add a Macro Button to the Ribbon
5.5 Assign a Macro to a Button on the Quick Access Toolbar
5.6 Assign a Macro to a Graphic Object
5.7 Macro Security
Trust Center Macro Settings
Trusted Locations
5.8 Editing Recorded Macros
Editing the Macro in the Visual Basic Editor
Chapter 6: Analyze Alternate Data Sets with What-If Analysis
6.1 Data Tables
Creating a One-Variable Data Table
Creating a Two-Variable Data Table
6.2 Scenario Manager
6.3 Goal Seeking
6.4 The Solver Add-in
Solver Results
Solver Options
Saving and Loading Solver Problem Models
Solver Reports
Chapter 7: Analyze Data Dynamically with PivotTables and PivotCharts
7.1 Creating PivotTables
Preparing Your Data
Create a Pivot Table with the Quick Analysis Tool
Create a Recommended PivotTable
Creating a PivotTable Manually
Summarizing Data by Date
Applying Formatting
Filter and Sort a PivotTable
7.2 Create Dynamic Charts with PivotCharts
Filtering a Pivot Chart
Moving the Pivot Chart
Generate a PivotTable and a PivotChart Simultaneously
Chapter 8: Protect Workbooks, Worksheets, and Ranges
8.1 Password-Protect Your Workbook
8.2 Set Different Access Levels
8.3 Protect the Workbook Structure
8.4 Protect Worksheets
8.5 Protect Specific Ranges
Book 3: Excel Functions
Chapter 1: How to Enter a Function
Using the Insert Function Dialog Box
Chapter 2: Lookup and Reference Functions
Find Data with XLOOKUP
Vertical Lookup
Horizontal Lookup
Simultaneous Vertical and Horizontal Lookup
Return Multiple Values with Horizontal Spill
Return Multiple Values with Vertical Spill
Common XLOOKUP Errors and Solutions
Find Data with VLOOKUP
Standard VLOOKUP Example
Finding an Approximate Match with VLOOKUP
Using VLOOKUP and the CHOOSE Function for Left Lookups
VLOOKUP and Error Handling
VLOOKUP and Wildcards
Common VLOOKUP Errors and Solutions
Find Data with HLOOKUP
MATCH Function
CHOOSE Function
TRANSPOSE Function
ADDRESS function
FORMULATEXT Function
COLUMNS Function
ROWS Function
INDEX Function
XMATCH Function
Using INDEX with XMATCH to Find Data
Chapter 3: Logical Functions
IF Function
Nested IF Functions
Advanced IF Functions
IFS Function
SWITCH Function
IFERROR Function
AND Function
OR Function
Chapter 4: Math Functions
SUM Function
SUMIF Function
SUMIFS Function
AGGREGATE Function
MOD Function
RANDBETWEEN Function
ROUND Function
ROUNDUP Function
ROUNDDOWN Function
SQRT Function
Chapter 5: Statistical Functions
COUNT Function
COUNTIF Function
COUNTIFS Function
COUNTA Function
COUNTBLANK Function
AVERAGE Function
AVERAGEIF Function
AVERAGEIFS Function
MAX, MIN, MEDIAN Functions
MAXIFS, MINIFS Functions
Chapter 6: Date and Time Functions
Date Formats
DAY, MONTH, YEAR Functions
DATE Function
DATEDIF Function
DAYS Function
EDATE Function
DATEVALUE Function
NETWORKDAYS Function
NOW Function
TODAY Function
TIME Function
Chapter 7: Text Functions
FIND Function
FINDB
CONCAT Function
TEXTJOIN Function
TRIM Function
UPPER, LOWER Functions
LEN Function
MID Function
PROPER Function
LEFT, RIGHT Functions
LEFTB, RIGHTB Functions
Chapter 8: Financial Functions
Definitions
PV Function
FV Function
NPV Function
PMT Function
SLN Function
SYD Function
DB Function
DDB Function
Afterword: Next Steps
Appendix: Keyboard Shortcuts (Excel for Windows)
Glossary
Other Books by Author
Leave a Review
