Excel 2019: 3 in 1: Beginner's Guide + Formulas and Functions + Advanced Methods to Learn Excel

362 Pages·2020·4.648 MB·English

by  Cane, Alexander

© Copyright 2020 by Alexander Cane - All rights reserved.
All rights reserved. No part of this guide may be reproduced in any form without permission in writing
from the publisher except in the case of brief quotations embodied in critical articles or reviews.
Legal & Disclaimer
The information contained in this book and its contents is not designed to replace or take the place of
any form of medical or professional advice; and is not meant to replace the need for independent
medical, financial, legal or other professional advice or services, as may be required. The content and
information in this book have been provided for educational and entertainment purposes only.
The content and information contained in this book have been compiled from sources deemed reliable,
and it is accurate to the best of the Author's knowledge, information, and belief. However, the Author
cannot guarantee its accuracy and validity and cannot be held liable for any errors and/or omissions.
Further, changes are periodically made to this book as and when needed. Where appropriate and/or
necessary, you must consult a professional (including but not limited to your doctor, attorney, financial
advisor or such other professional advisor) before using any of the suggested remedies, techniques, or
information in this book.
Upon using the contents and information contained in this book, you agree to hold harmless the Author
from and against any damages, costs, and expenses, including any legal fees potentially resulting from
the application of any of the information provided by this book. This disclaimer applies to any loss,
damages or injury caused by the use and application, whether directly or indirectly, of any advice or
information presented, whether for breach of contract, tort, negligence, personal injury, criminal intent,
or under any other cause of action.
You agree to accept all risks of using the information presented inside this book.
You agree that by continuing to read this book, where appropriate and/or necessary, you shall consult a
professional (including but not limited to your doctor, attorney, or financial advisor or such other
advisor as needed) before using any of the suggested remedies, techniques, or information in this book.
Table of Contents
EXCEL 2019
A COMPREHENSIVE BEGINNERS GUIDE
TO LEARN EXCEL 2019 STEP BY STEP FROM A - Z
Introduction
Chapter 1: The Basics of Excel 2019
Excel 2019’s New Look and Feel
The Excel Start Screen
The Ribbon User Interface
Help
Starting and Leaving Excel
Chapter 2: Personalizing Your Excel 2019
Personalizing the Quick Access Toolbar
Exploring your Options
Using Office Add-ins
Using Excel’s own add-ins
Chapter 3: Facing Your Worksheet
Spreadsheet Design
Kinds of Cell Entries
Basics of Data Entry
Saving your Data
Document Recovery
Chapter 4: Worksheet Formatting
Selecting cells
Handling Columns and Rows
Formatting Tables
Cell Styles
Conditional Formatting
Chapter 5: Printing Your Worksheet
Different ways to print
Page Setup Options
Using Print Options
Headers and Footers
Page Break Issues and How to Solve Them
Printing Formulas
Chapter 6: Basic Formulas
What you need to know about formulas
How to Copy Formulas
Array Formulas
Range Names
Linking Formulas
Formula Recalculation
Circular References
Chapter 7: Error Trapping and Logical Functions
Error Values
Error Trapping
Logical Functions
Formula Auditing
Error Checking
Removing Errors in Printout
Chapter 8: Date and Time
Understanding Date and Time
Date functions
Time Functions
Chapter 9: Financial Formulas
Basics of Financial Functions
PV, NPV and FV
PMT Function
Depreciation Functions
Chapter 10: Math and Statistical Formulas
Math and Trigonometry Functions
Statistical Functions
Chapter 11: Protecting Your Workbook and Worksheet Data
Protecting the Workbook with a Password
Protecting the Worksheet
Chapter 12: Hyperlinks
The Basics of Hyperlinks
Using the HYPERLINK Function
Chapter 13: How to Prepare a Workbook for Distribution
Prepping for Review
Annotations
Chapter 14: Sharing Your Workbook and Worksheet
Sharing Workbooks Online
Basics of Excel 2019 Data Sharing
Exporting Workbooks to Different Usable File Formats
Chapter 15: Charting Your Worksheet Data
The Basics of Worksheet Charting
Adding Sparkline Graphics
Adding Infographics
Printing Charts
Chapter 16: Adding Graphic Objects
Basics of Graphic Objects
Different Types of Graphics
Drawing Graphic Objects
Adding Screenshots
Themes
Chapter 17: Building Data Lists
The Basics of Data Lists
Data Sorting
Subtotalling Your Data
Chapter 18: Filters and Queries
Data List Filtering
Filtering Data
Database Functions
External Data Query
Chapter 19: What-if Scenarios
Data Tables
Exploring Different Scenarios
Hide and Goal Seeking
Using the Solver
Chapter 20: Large Scale Data Analysis
Building Pivot Tables
Modifying the Pivot Table
Creating Pivot Charts
Creating Forecast Worksheets
EXCEL 2019:
EXPLORE THE POWERFUL FORMULAS
AND FUNCTIONS OF EXCEL 2019
Chapter 1: Introduction
Microsoft Excel - What is it used for?
Uses of Microsoft Excel
New Features in Microsoft Excel 2019
Worksheets and Workbooks
How to move around a Worksheet
How to move a Worksheet with your Keyboard
Moving with your Mouse
How to use the Ribbons
Commands on the Ribbon
How to Work with Dialog Boxes
Chapter 2: Editing and Entering Worksheet Data
How to Explore Data Types
Useful Data-Entry Techniques
How to Apply Number Formatting
Chapter 3: Carrying Out Basic Worksheet Operations
How to Work with Excel Windows
Resizing and Moving Windows
How to Add a New Worksheet to your Workbook
How to Control your Worksheet View
Chapter 4: How to Work with Ranges and Tables in Excel
Understanding Ranges and Cells
How to Use Names to Work with Ranges
How to Add Comments to Cells
How to Work with Tables
Chapter 5: How to Format Worksheets
Understanding Formatting Tools
Utilizing Colors and Shades
How to Create Formula-Based Rules
Understanding Document Themes
Chapter 6: Understanding Templates and Excel Files
How to Create a New Workbook
How to Open Workbooks Automatically
How to Recover Versions of the Previous Workbook
How to Recover Unsaved Work
How to Configure AutoRecover
Protecting Your Workbook with a Password
Protecting Your Work
Chapter 7: How to Print Your Work
Basic Printing
How to Adjust Common Page Setup Settings
How to Insert a Watermark
How to Add Footer or Header to Reports
Chapter 8: How You can Customize the Excel User Interface
How to Customize the Quick Access Toolbar
How to Customize the Ribbon
How to Share User Interface Customizations
Chapter 9: Introducing Formulas and Functions
Understanding Formula Basics
Function Arguments
Insertion of Functions into Formulas
Chapter 10: Using Formulas to Perform
Mathematical Operations
Calculation of Percentages
Calculation of Percent of Goal
Calculation of Percent Variance
Calculation of Percent Distribution
Calculation of Running Total
Handling Division by Zero Errors
Rounding of Numbers
Using the Conversion Functions
Chapter 11: Manipulating Text using Formulas
Working with Text
Setting a Text to Sentence Case
Finding a Character’s Second Instance
Counting a Cell’s Specific Characters
Cleaning of Unknown Characters from any Text Field
Chapter 12: Using Formulas with Times and Dates
Conversion of Decimal Seconds, Minutes, or Hours to Time Using
Formulas
Addition of Seconds, Minutes, or Hours
Chapter 13: Making Use of Formulas for Conditional Analysis
Comprehending Conditional Analysis
Checking for Multiple Conditions
Performing Conditional Calculations
Chapter 14: Matching and Lookups Using Formulas
Introducing Excel’s Lookup Formulas
Leveraging the Lookup Functions of Excel 19
Finding the Best Match from some Banded Values
Finding the Best Match with the MATCH
and INDEX Functions
Making use of Default Values for Match
Chapter 15: Performing Financial Analysis Using Formulas