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