Excel All-IN-ONE

Title Page
Copyright Page
Table of Contents
Introduction
	About This Book
	Foolish Assumptions
	Conventions Used in This Book
	Icons Used in This Book
	Beyond the Book
	Where to Go from Here
Book 1 Excel Basics
	Chapter 1 Excel: The 50-Cent Tour
		A Bird’s-Eye View of Excel
		Checking Out Excel’s Start Screen
		Getting Comfy with the Excel User Interface
			Going behind the scenes to Excel’s Backstage view
				Getting the lowdown on the Info screen
				Sizing up other File menu commands
				Checking user and product information on the Account screen
			Ripping through the Ribbon
				Keeping tabs on the Excel Ribbon
				Adding the Developer tab to the Ribbon
				Selecting with mouse and keyboard
				Selecting Ribbon commands by touch
			Working with the Quick Access toolbar
			Fooling around with the Formula bar
			What’s up with the Worksheet area?
				Moving around the worksheet
				Keystroke shortcuts for moving the cell pointer
				Tips on using the scroll bars
				Surfing the sheets in a workbook
			Taking a tour of the Status bar
		Getting Help
			Show-and-tell help with the Search feature
			Getting Help from the Help tab on the Ribbon
		Launching and Quitting Excel
			Starting Excel from the Windows Start menu
			Starting Excel from the Windows Search text box
			Telling Cortana to Start Excel for you
			Starting Excel from the Windows Metro view in Tablet mode
			When it’s quitting time
	Chapter 2 Customizing Excel
		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
		Exercising Your Options
			Changing some of the more universal settings on the General tab
				The User Interface Options group
				The When Creating New Workbooks group
				The Personalize Your Copy of Microsoft Office group
				The Privacy Settings group
				The LinkedIn Features group
				The Start Up Options group
			Changing common calculation options on the Formulas tab
				The Calculation Options group
				The Working with Formulas options group
				The Error Checking and Error Checking Rules groups
			Digging the options on the Data tab
			Changing correction options on the Proofing tab
				The AutoCorrect Options group
				The When Correcting Spelling in Microsoft Office Programs group
			Changing save options on the Save tab
			Changing the Office language preferences
			Setting some accessibility options
			Changing a whole lot of other common options on the Advanced tab
				Working the worksheet editing options
				Playing around with the display options
				Caring about the Formulas, Calculating, and General options
			Customizing the Excel Ribbon
				Customizing the Ribbon’s tabs
				Adding custom tabs to the 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
		Creating Fresh Workbooks
			Take it from a template
				Downloading the template to use
				Saving changes to your customized templates
				Creating your own workbook templates
			Designing a workbook from scratch
				Planning your workbook
				Planning for economy
				Planning for functionality
				Opening new blank workbooks
		Getting to Know Excel’s Data Types
			What’s in a label?
			What’s the value?
				Inputting numbers
				Dates and times
				Inputting dates and times using recognized formats
				Understanding how Excel treats two-digit years
				Numeric formulas
		Data Entry 101
			Data entry keyboard style
			Doing data entry with the Touch keyboard
			You AutoComplete me
			You AutoCorrect me
			Constraining data entry to a cell range
			Getting Excel to put in the decimal point
			You AutoFill me
				AutoFill on a Touchscreen
				AutoFill via the Fill button on the Ribbon
				AutoFill series with custom increments
				Creating custom AutoFill lists
			Flash Fill to the rescue
				Limiting data entry with Data Validation
		Linking to Other Documents
			Hyperlinks: the basics
			Inserting links
			Follow that link!
			Editing links
				Removing a link
				Selecting a link without activating it
			Using the HYPERLINK Function
		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
		Selecting Cells and Ranges
			Selecting cells with the mouse
			Selecting cells by touch
			Selecting cells with the keyboard
			Selecting cells with AutoSelect
			Selecting cells with Go To
			Name that range!
		Adjusting Columns and Rows
			Fitting the column to its contents automatically
			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
				Using one of the predefined number formats
				Deciphering the Custom number formats
				Designing your own number formats
			Altering the alignment
				Wrapping text entries to new lines in their cells
				Reorienting your entries
			Fancy fonts and colors
				Selecting fonts and colors from the Ribbon
				Selecting fonts and colors in the Format Cells dialog box
			Basic borders, fills, and patterns
				Right on the borderline
				Fun fills, great-looking gradients, and pretty patterns
		Hiring Out the Format Painter
		Easier Formatting with 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
		Analyzing Data with Conditional Formatting
			Graphical conditional formatting
			Conditional 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
			Opening more than one workbook at a time
			Finding misplaced workbooks
			Using the other Open options
		Editing a Cell
			Changing your mind with Undo and Redo
			Clearing cells
			Inserting cells
		A Worksheet with a View
			Zooming in and zooming back out again
			Freezing window panes
			Saving custom views
		Copying and Moving Stuff Around
			Copying and moving with drag-and-drop
				Moving cells with drag-and-drop
				Copying cells with drag-and-drop
			Copying and moving with copy, cut, and paste
				“Paste it again, Sam”
				Taking it out of the Clipboard task pane
				Inserting rather than replacing copied cells
				Pasting just the good parts with Paste Special
		Finding and Replacing Worksheet Data
			Finding data
			Finding and replacing data
		Getting It Right: Spell Checking Your Worksheets
			Changing the spelling options
			Adding words to the custom dictionary
		Looking Up and Translating Stuff
		Marking Invalid Data
	Chapter 4 Managing Worksheets
		Reorganizing the Worksheet
			Being cautious when deleting and inserting columns and rows
			Deleting columns and rows
			Adding new columns and rows
			Splitting the worksheet into panes
			Outlining worksheets
				Creating the outline
				Applying outline styles
				Displaying and hiding different outline levels
				Manually adjusting the outline levels
				Removing an outline
				Creating different custom views of the outline
		Reorganizing the Workbook
			Renaming a worksheet
			Designer sheets
				Color coding sheet tabs
				Assigning a graphic image as the sheet background
			Adding worksheets
			Deleting worksheets
			Changing the sheets
			Editing and formatting multiple worksheets as a group
			Hiding worksheets
			Opening windows on different sheets
				Comparing worksheet windows side by side
				Window arrangements
			Viewing workbook stats
		Working with Multiple Workbooks
			Comparing windows on different workbooks
			Transferring data between open windows
			Moving or copying sheets from one workbook to another
		Consolidating Worksheets
			Consolidating by position
			Consolidating by category
			Linking consolidated data
	Chapter 5 Printing Worksheets
		Printing from the 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
				Setting and clearing the Print Area
				Massaging the margins
				Getting the lay of the landscape
				Putting out the print titles
			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
				Creating unique first-page headers and footers
				Creating different even and odd page headers and footers
		Solving Page Break Problems
		Printing the Formulas in a Report
Book 3 Formulas and Functions
	Chapter 1 Building Basic Formulas
		Getting Started with Formulas
			Pointing at formula cells
			Editing formulas
			Using AutoSum to add numbers
			Using Quick Analysis to add numbers
			Building formulas with computational operators
				More about operators
				Order of operator precedence
		Augmenting Formulas with Worksheet Functions
			Inserting a function
				Entering a function manually
				Using the Insert Function button
				Using the Ribbon’s Formulas tab
			Entering function arguments
		Copying Formulas
			Copying with relative cell references
			Copying with absolute references
			Copying with mixed cell references
		Adding Array Formulas
			Creating an array formula
			Editing a dynamic array formula
			Building old-fashioned array formulas
			Editing an old-fashioned array formula
		Naming Cells and Ranges
			Defining cell and range names
			Naming constants and formulas
			Using names in building formulas
			Creating names from column and row headings
			Managing 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
			Getting to know the powerful IF function
			Evaluating the many talents of the IFS function
			Learning about the SWITCH function
		Error-Trapping Formulas
			Error-trapping with IF
			Upping your error-trapping game with ISERROR
		Whiting-Out Errors with Conditional Formatting
		Checking It Twice: Auditing Your Formulas
			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 date locale
			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 useful Date functions
				EDATE
				EOMONTH
				NETWORKDAYS
				WEEKNUM
				WORKDAY
				YEARFRAC
		Using Time Functions
			NOW
			TIME and TIMEVALUE
			HOUR, MINUTE, and SECOND
	Chapter 4 Finagling Financial Formulas
		Some Financial Function Basics Before You Begin
		The PV, NPV, and FV Functions
			Calculating the Present Value
			Calculating the Net Present Value
			Calculating the Future Value
		The PMT Function
		Depreciation Functions
		Excel’s Advanced Financial Functions
	Chapter 5 Math and Statistical Formulas
		Math & Trig Functions
			Rounding off numbers
				The whole number and nothing but the whole number
				Let’s call it even or odd
				Building in a ceiling
			POWER and SQRT
			The SUM of the parts
			Conditional summing
				Summing certain cells with SUMIF
				Summing on multiple criteria with SUMIFS
		Statistical Functions
			AVERAGE, MAX, MIN, and MEDIAN
			Counting cells
				Building counting formulas
				Counting occupied cells in entire rows, columns, and worksheets
				Conditional counting
			Using specialized statistical functions
	Chapter 6 Lookup, Information, and Text Formulas
		Looking Up Stuff with the Lookup Functions
			Modern lookups with XLOOKUP
			Old-fashioned lookups with VLOOKUP and HLOOKUP
			Performing an old-fashioned two-way lookup with MATCH and INDEX
		Getting Cell Reference Info with the Reference Functions
			Get the skinny on columns and rows
			Transposing cell ranges
		Interrogating Excel
			Getting information about a cell
			Are you my TYPE?
			Using the IS functions
		Working with Text Functions and Formulas
			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 Workbook
			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
		Setting Up Worksheet Data Protection
			Changing a cell’s Locked and Hidden Protection formatting
			Protecting the worksheet
				Selecting what actions are allowed in a protected sheet
				Assigning a password to unprotect the sheet
				Removing protection from a worksheet
			Enabling cell range editing by certain users
			Doing data entry in the unlocked cells of a protected worksheet
		Protecting the Workbook Structure
	Chapter 2 Preparing a Workbook for Distribution
		Getting Your Workbook Ready for Review
			Adding properties to a workbook
			Digitally signing a document
		Annotating Workbooks
			Adding notes
			Displaying and hiding notes
				Editing and formatting notes
				Deleting notes
			Marking up a worksheet with digital ink
	Chapter 3 Sharing Workbooks and Worksheet Data
		Sharing Your Workbooks Online
			Sharing a workbook saved on your OneDrive
			Copying a sharing link
		Sharing Your Workbooks via Email
			Emailing a copy of a workbook
			Emailing a workbook as a PDF file
		Sharing Excel Data with Office Programs
			Sharing data between Excel and Word
				Getting Excel data into a Word document
				Happily embedded after
				Editing embedded stuff
				The links that bind
				Editing linked data
			Sharing data between Excel and PowerPoint
		Making Shared Workbooks Accessible
			Some useful accessibility guidelines and tweaks
			Adding alternative text to graphics
			Running the Accessibility Checker
		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
		Collaborating Conversationally with Comments
			Inserting a comment
			Displaying and hiding comments
			Editing a comment
			Replying to a comment
			Deleting a comment
Book 5 Charts and Graphics
	Chapter 1 Charting Worksheet Data
		Worksheet Charting 101
			Embedded charts versus 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 Chart Design tab
				Modifying the chart layout and style
				Switching the rows and columns in a chart
				Editing the source of the data graphed in the chart
			Customizing chart elements from the Format tab
			Customizing the elements of a chart
				Adding data labels to the series in a chart
				Adding a data table to a chart
				Editing the chart titles
			Formatting elements of a chart
				Formatting chart titles with the Format Chart Title task pane
				Formatting chart axes with the Format Axis task pane
			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
		Going Graphical: The Basics of Working with Graphic Objects
			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
			Inserting icons, stock images, and other ready-made graphics
		Making Changes to Graphics
			Editing pictures
			Formatting photos and line art pictures
			Formatting 3-D model images
		Drawing Graphics
			Drawing predefined shapes
			Adding text boxes
				Formatting a text box
				Editing the text in a text box
				Adding an arrow to a text box
			Inserting WordArt
			Inserting SmartArt graphics
				Filling in the text for a new SmartArt graphic
				Adding images to a SmartArt picture
				Formatting a SmartArt graphic
		Adding Screenshots of the Windows Desktop
		Using Themes
Book 6 Data Management
	Chapter 1 Building and Maintaining Tables
		Table Basics
			Designing the basic table
				Converting a range to a table (and back again)
				Creating calculated fields
				Modifying the structure of the table
			Add new records to a table
				Using the data form
				Adding new records with the data form
				Editing records in the data form
				Finding records with the data form
				Deleting records with the data form
			Eliminating duplicate records
		Sorting Table Data
			Sorting records on a single field
			Sorting records on multiple fields
			Sorting a table on font and fill colors and cell icons
		Subtotaling Table Data
	Chapter 2 Filtering and Querying a Table
		Filtering Data
			Using AutoFilter
				Doing basic filtering by selecting specific field entries
				Using the Text Filters options
				Using the Date Filters options
				Using the Number Filters options
				Making it to the Top Ten!
				Filtering a table on a field’s font and fill colors or cell icons
				Custom AutoFilter at your service
			Using the Advanced Filter
				Specifying comparison criteria
				Setting up logical AND logical OR conditions
				Setting up calculated criteria
				Using the AND, OR, and NOT functions in calculated criteria
		Using the Database Functions
		Querying External Data
			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 Analysis
		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 Solver
			Setting up and defining the problem
			Solving the problem
			Changing Solver options
			Saving and loading a model problem
			Creating Solver reports
	Chapter 2 Performing Large-Scale Data Analysis
		Creating PivotTables
			Creating PivotTables with the Quick Analysis tool
			Creating recommended PivotTables
			Creating PivotTables manually
				Creating a PivotTable with local data
				Creating a PivotTable from external data
				Constructing the new PivotTable
		Formatting a PivotTable
			Refining the PivotTable layout and style
			Formatting the parts of the PivotTable
				Applying a new font, font size, or alignment to the PivotTable
				Applying a number format to the data cells
		Sorting and Filtering the PivotTable Data
			Filtering the report
			Filtering individual Column and Row fields
			Slicing the PivotTable data
			Using timeline filters
			Sorting the PivotTable
		Modifying the PivotTable
			Changing the summary functions
			Adding Calculated Fields
			Changing the PivotTable options
		Creating PivotCharts
			Moving a PivotChart to its own sheet
			Filtering a PivotChart
			Formatting a PivotChart
		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 Maps Feature
		Creating Forecast Worksheets
Book 8 Macros and VBA
	Chapter 1 Recording and Running Macros
		Macro Basics
			Getting ready to record
			Recording macros
			Running a macro
			Saving your recorded macros in a macro-enabled workbook
		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
				Finding and replacing code in the macro
				Changing settings for VBA properties
				Getting macro input by adding a dialog box
			Writing new macros in the Visual Basic Editor
			Running macros in the Visual Basic Editor
		Creating Custom Excel Functions
			Adding a description to a user-defined function
			Using a custom function in your worksheet
			Saving custom functions in add-in files
Index
EULA