Excel 2019 All-in-One For Dummies

Title Page
Copyright Page
Table of Contents
Introduction
About This Book
Foolish Assumptions
How This Book Is Organized
Book 1: Excel Basics
Book 2: Worksheet Design
Book 3: Formulas and Functions
Book 4: Worksheet Collaboration and Review
Book 5: Charts and Graphics
Book 6: Data Management
Book 7: Data Analysis
Book 8: Macros and VBA
Conventions Used in This Book
Icons Used in This Book
Beyond the Book
Where to Go from Here
Book 1 Excel Basics
Chapter 1 The Excel 2019 User Experience
Excel 2019’s Sleek Look and Feel
Excel’s Start Screen
Excel’s Ribbon User Interface
Going behind the scenes to Excel’s Backstage view
Ripping through the Ribbon
Adjusting to the Quick Access toolbar
Fooling around with the Formula bar
What’s up with the Worksheet area?
Taking a tour of the Status bar
Getting Help
Show-and-tell help with the Tell Me feature
Getting Help from the Help tab on the Ribbon
Launching and Quitting Excel
Starting Excel from the Windows 10 Start menu
Starting Excel from the Windows 10 Search text box
Telling Cortana to Start Excel for you
Starting Excel from the Windows 10 Metro view in Tablet mode
When it’s quitting time
Chapter 2 Customizing Excel 2019
Tailoring the Quick Access Toolbar to Your Tastes
Adding Ribbon commands to the Quick Access toolbar
Adding non-Ribbon commands to the Quick Access toolbar
Adding macros to the Quick Access toolbar
Exercising Your Options
Changing some of the more universal settings on the General tab
Changing common calculation options on the Formulas tab
Digging the options on the Data tab
Changing correction options on the Proofing tab
Changing various save options on the Save tab
Changing the Office 2019 language preferences
Changing a whole lot of other common options on the Advanced tab
Customizing the Excel 2019 Ribbon
Using Office Add-ins
Using Excel’s Own Add-ins
Managing the standard Excel Add-ins
Managing Excel COM add-ins
Purchasing third-party add-ins
Book 2 Worksheet Design
Chapter 1 Building Worksheets
Designer Spreadsheets
Take it from a template
Designing a workbook from scratch
It Takes All Kinds (Of Cell Entries)
What’s in a label?
What’s the value?
Data Entry 101
Data entry keyboard style
Doing data entry with the Touch keyboard
You AutoComplete this for me
You AutoCorrect this right now!
Constraining data entry to a cell range
Getting Excel to put in the decimal point
You AutoFill it in
Flash Fill to the rescue
Saving the Data
Saving workbooks in other commonly used file formats
Changing the default file location
Saving a new workbook in the old file format
Document Recovery to the Rescue
Chapter 2 Formatting Worksheets
Making Cell Selections
Selecting cells with the mouse
Selecting cells by touch
Selecting cells with the keyboard
You AutoSelect that range!
Selecting cells with Go To
Name that range!
Adjusting Columns and Rows
You AutoFit the column to its contents
Adjusting columns the old fashioned way
Setting a new standard width
Hiding out a column or two
Rambling rows
Formatting Tables from the Ribbon
Formatting Tables with the Quick Analysis Tool
Formatting Cells from the Ribbon
Formatting Cell Ranges with the Mini-Toolbar
Using the Format Cells Dialog Box
Assigning number formats
Altering the alignment
Fancy fonts and colors
Basic borders, fills, and patterns
Hiring Out the Format Painter
Using Cell Styles
Using the Number Format cell styles
Defining a custom cell style by example
Creating a new cell style from scratch
Merging styles into other workbooks
Conditional Formatting
Graphical conditional formatting
Formatting with the Quick Analysis tool
Identifying particular values or text entries in a cell range
Highlighting duplicate values in a cell range
Creating your own conditional formatting rules
Managing conditional formatting rules
Chapter 3 Editing and Proofing Worksheets
Opening a Workbook
Using the Open screen in the Backstage view
Using the Open dialog box
Opening more than one workbook at a time
Finding misplaced workbooks
Using the other Open options
Cell Editing 101
Undo and Redo
Get that out of here!
Can I just squeeze this in here?
A Spreadsheet with a View
“Zoom, zoom, zoom”
Freezing window panes
Saving custom views
Copying and Moving Stuff Around
Doing it with drag-and-drop
Carried away with cut-and-paste
Find and Replace This Disgrace!
Finding stuff
Finding and replacing stuff
Spell Checking Heaven
Changing the spelling options
Adding words to the custom dictionary
Looking Up and Translating Stuff
Marking Invalid Data
Eliminating Errors with Text to Speech
Chapter 4 Managing Worksheets
Reorganizing the Worksheet
Inserting and deleting columns and rows
Eradicating columns and rows
Adding new columns and rows
Splitting the worksheet into panes
Outlining worksheets
Reorganizing the Workbook
Renaming sheets
Designer sheets
Adding and deleting sheets
Changing the sheets
Group editing
“Now you see them; now you don’t”
Opening windows on different sheets
Working with Multiple Workbooks
Comparing windows on different workbooks
Transferring data between open windows
Transferring sheets from one workbook to another
Consolidating Worksheets
Consolidating by position
Consolidating by category
Linking consolidated data
Chapter 5 Printing Worksheets
Printing from the Excel 2019 Backstage View
Selecting the printer to use
Previewing the printout
Checking the paging in Page Layout view
Previewing the pages of the report
Quick Printing the Worksheet
Working with the Page Setup Options
Using the buttons in the Page Setup group
Using the buttons in the Scale to Fit group
Using the Print Options on the Sheet tab of the Page Setup dialog box
Headers and Footers
Adding a ready-made header or footer
Creating a custom header or footer
Solving Page Break Problems
Printing the Formulas in a Report
Book 3 Formulas and Functions
Chapter 1 Building Basic Formulas
Formulas 101
Formula building methods
Editing formulas
When you AutoSum numbers in a spreadsheet
Totals and sums with the Quick Analysis tool
Building formulas with computational operators
Using the Insert Function button
Copying Formulas
Absolute references
A mixed bag of references
Adding Array Formulas
Building an array formula
Editing an array formula
Range Names in Formulas
Defining range names
Naming constants and formulas
Using names in building formulas
Creating names from column and row headings
Managing range names
Applying names to existing formulas
Adding Linking Formulas
Controlling Formula Recalculation
Circular References
Chapter 2 Logical Functions and Error Trapping
Understanding Error Values
Using Logical Functions
Evaluating the many talents of the IFS function
SWITCH it out
Error-Trapping Formulas
Whiting-Out Errors with Conditional Formatting
Formula Auditing
Tracing precedents
Tracing dependents
Error checking
Changing the Error Checking options
Error tracing
Evaluating a formula
Removing Errors from the Printout
Chapter 3 Date and Time Formulas
Understanding Dates and Times
Changing the Regional date settings
Building formulas that calculate elapsed dates
Building formulas that calculate elapsed times
Using Date Functions
TODAY
DATE and DATEVALUE
DAY, WEEKDAY, MONTH, and YEAR
DAYS360
Other special Date functions
Using Time Functions
NOW
TIME and TIMEVALUE
HOUR, MINUTE, and SECOND
Chapter 4 Financial Formulas
Financial Functions 101
The PV, NPV, and FV Functions
Calculating the Present Value
Calculating the Net Present Value
Calculating the Future Value
The PMT Function
Depreciation Functions
Analysis ToolPak Financial Functions
Chapter 5 Math and Statistical Formulas
Math & Trig Functions
Rounding off numbers
POWER and SQRT
The SUM of the parts
Conditional summing
Statistical Functions
AVERAGE, MAX, and MIN
Counting cells
Using specialized statistical functions
Chapter 6 Lookup, Information, and Text Formulas
Lookup and Reference
Looking up a single value with VLOOKUP and HLOOKUP
Performing a two-way lookup
Reference functions
Information, Please . . .
Getting specific information about a cell
Are you my type?
Using the IS functions
Much Ado about Text
Using text functions
The T function
TEXTJOIN function
Concatenating text
Book 4 Worksheet Collaboration and Review
Chapter 1 Protecting Workbooks and Worksheet Data
Password-Protecting the File
Protecting the workbook when saving the file
Assigning a password to open from the Info screen
Entering the password to gain access
Entering the password to make changes
Changing or deleting a password
Protecting the Worksheet
Changing a cell’s Locked and Hidden Protection formatting
Protecting the worksheet
Enabling cell range editing by certain users
Doing data entry in the unlocked cells of a protected worksheet
Protecting the workbook
Chapter 2 Using Hyperlinks
Hyperlinks 101
Adding hyperlinks
Follow that link!
Editing hyperlinks
Using the HYPERLINK Function
Chapter 3 Preparing a Workbook for Distribution
Getting Your Workbook Ready for Review
Adding properties to a workbook
Digitally signing a document
Annotating Workbooks
Adding notes
Marking up a worksheet with digital ink
Chapter 4 Sharing Workbooks and Worksheet Data
Sharing Your Workbooks Online
Sharing workbooks saved on your OneDrive
E-mailing workbooks
Sending Workbooks as Adobe PDF Files for Shared Commenting
Editing worksheets in Excel Online
Excel 2019 Data Sharing Basics
Excel and Word 2019
Excel and PowerPoint 2019
Exporting Workbooks to Other Usable File Formats
Saving and exporting worksheets as PDF files
Saving worksheets as XPS files
Saving worksheets as ODS files
Saving worksheets as HTML files
Book 5 Charts and Graphics
Chapter 1 Charting Worksheet Data
Worksheet Charting 101
Embedded charts versus charts on separate chart sheets
Inserting recommended charts
Inserting specific chart types from the Ribbon
Inserting charts with the Quick Analysis tool
Creating a chart on a separate chart sheet
Refining the chart from the Design tab
Customizing chart elements from the Format tab
Customizing the elements of a chart
Formatting elements of a chart
Saving a customized chart as a template
Adding Sparkline Graphics to a Worksheet
Adding Infographics to a Worksheet
Printing Charts
Chapter 2 Adding Graphic Objects
Graphic Objects 101
Manipulating graphics
Moving graphic objects to new layers
Aligning graphic objects
Grouping graphic objects
Managing graphic objects in the Selection task pane
Inserting Different Types of Graphics
Inserting 2-D online images
Inserting 3-D online images
Inserting local pictures
Editing pictures
Formatting photos and line art pictures
Formatting 3-D model images
Drawing Graphics
Drawing predefined shapes
Adding text boxes
Inserting WordArt
Inserting SmartArt graphics
Adding Screenshots of the Windows 10 Desktop
Using Themes
Book 6 Data Management
Chapter 1 Building and Maintaining Data Lists
Data List Basics
Designing the basic data list
Add new records to a data list
Eliminating records with duplicate fields
Sorting Data
Sorting records on a single field
Sorting records on multiple fields
Sorting the columns of a data list
Sorting a data list on font and fill colors and cell icons
Subtotaling Data
Chapter 2 Filtering and Querying a Data List
Data List Filtering 101
Filtering Data
Using AutoFilter
Using the Advanced Filter
Using the Database Functions
External Data Query
Retrieving data from Access database tables
Retrieving data from the web
Retrieving data from text files
Querying data from other data sources
Transforming a data query in the Power Query Editor
Book 7 Data Analysis
Chapter 1 Performing What-If Scenarios
Using Data Tables
Creating a one-variable data table
Creating a two-variable data table
Exploring Different Scenarios
Creating new scenarios
Producing a summary report
Hide and Goal Seeking
Using the Solver
Setting up and defining the problem
Solving the problem
Changing the Solver options
Saving and loading a model problem
Creating Solver reports
Chapter 2 Performing Large-Scale Data Analysis
Creating Pivot Tables
Pivot tables with the Quick Analysis tool
Recommended pivot tables
Manually created pivot tables
Formatting a Pivot Table
Refining the pivot table layout and style
Formatting the parts of the pivot table
Sorting and Filtering the Pivot Table Data
Filtering the report
Filtering individual Column and Row fields
Slicing the pivot table data
Using timeline filters
Sorting the pivot table
Modifying the Pivot Table
Changing the summary functions
Adding Calculated Fields
Changing the pivot table options
Creating Pivot Charts
Moving a pivot chart to its own sheet
Filtering a pivot chart
Formatting a pivot chart
Using the Power Pivot Add-in
Data modeling with Power Pivot
Switching between the Data View and Diagram View
Adding calculated columns courtesy of DAX
Using the 3D Map feature
Creating Forecast Worksheets
Book 8 Macros and VBA
Chapter 1 Recording and Running Macros
Macro Basics
Recording macros
Running a macro
Assigning Macros to the Ribbon and the Quick Access Toolbar
Adding your macros to a custom tab on the Ribbon
Adding your macros to custom buttons on the Quick Access toolbar
Macro Security
Chapter 2 VBA Programming
Using the Visual Basic Editor
Editing recorded macros
Writing new macros in the Visual Basic Editor
Creating Custom Excel Functions
Adding a description to a user-defined function
Using a custom function in your spreadsheet
Saving custom functions in add-in files
Index
EULA