Excel 2019 Bible

✍ Scribed by Michael Alexander, Richard Kusleika, John Walkenbach

• Publisher: Wiley
• Year: 2018
• Tongue: English
• Leaves: 1123
• Edition: 1
• Category: Library

✦ Synopsis

The complete guide to Excel 2019

Whether you are just starting out or an Excel novice, the Excel 2019 Bible is your comprehensive, go-to guide for all your Excel 2019 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more.

Navigate this powerful tool for business, home management, technical work, and much more with the only resource you need, Excel 2019 Bible.

• Create functional spreadsheets that work
• Master formulas, formatting, pivot tables, and more
• Get acquainted with Excel 2019's new features and tools

Whether you need a walkthrough tutorial or an easy-to-navigate desk reference, the Excel 2019 Bible has you covered with complete coverage and clear expert guidance.

✦ Table of Contents

Cover
Title Page
Copyright
About the Authors
About the Technical Editors
Credits
Acknowledgments
Contents
Introduction
Is This Book for You?
Software Versions
Conventions Used in This Book
Excel commands
Typographical conventions
Mouse conventions
How This Book Is Organized
How to Use This Book
What’s on the Website
Part I: Getting Started with Excel
Chapter 1: Introducing Excel
Understanding What Excel Is Used For
Looking at What’s New in Excel 2019
Understanding Workbooks and Worksheets
Moving around a Worksheet
Navigating with your keyboard
Navigating with your mouse
Using the Ribbon
Ribbon tabs
Contextual tabs
Types of commands on the Ribbon
Accessing the Ribbon by using your keyboard
Using Shortcut Menus
Customizing Your Quick Access Toolbar
Working with Dialog Boxes
Navigating dialog boxes
Using tabbed dialog boxes
Using Task Panes
Creating Your First Excel Workbook
Getting started on your worksheet
Filling in the month names
Entering the sales data
Formatting the numbers
Making your worksheet look a bit fancier
Summing the values
Creating a chart
Printing your worksheet
Saving your workbook
Chapter 2: Entering and Editing Worksheet Data
Exploring Data Types
Numeric values
Text entries
Formulas
Entering Text and Values into Your Worksheets
Entering numbers
Entering text
Using Enter mode
Entering Dates and Times into Your Worksheets
Entering date values
Entering time values
Modifying Cell Contents
Deleting the contents of a cell
Replacing the contents of a cell
Editing the contents of a cell
Learning some handy data-entry techniques
Automatically moving the selection after entering data
Selecting a range of input cells before entering data
Using Ctrl+Enter to place information into multiple cells simultaneously
Changing modes
Entering decimal points automatically
Using AutoFill to enter a series of values
Using AutoComplete to automate data entry
Forcing text to appear on a new line within a cell
Using AutoCorrect for shorthand data entry
Entering numbers with fractions
Using a form for data entry
Entering the current date or time into a cell
Applying Number Formatting
Using automatic number formatting
Formatting numbers by using the Ribbon
Using shortcut keys to format numbers
Formatting numbers by using the Format Cells dialog box
Adding your own custom number formats
Chapter 3: Performing Basic Worksheet Operations
Learning the Fundamentals of Excel Worksheets
Working with Excel windows
Moving and resizing windows
Switching among windows
Closing windows
Activating a worksheet
Adding a new worksheet to your workbook
Deleting a worksheet you no longer need
Changing the name of a worksheet
Changing a sheet tab color
Rearranging your worksheets
Hiding and unhiding a worksheet
Controlling the Worksheet View
Zooming in or out for a better view
Viewing a worksheet in multiple windows
Comparing sheets side by side
Splitting the worksheet window into panes
Keeping the titles in view by freezing panes
Monitoring cells with a Watch Window
Working with Rows and Columns
Inserting rows and columns
Deleting rows and columns
Changing column widths and row heights
Changing column widths
Changing row heights
Hiding rows and columns
Chapter 4: Working with Excel Ranges and Tables
Understanding Cells and Ranges
Selecting ranges
Selecting complete rows and columns
Selecting noncontiguous ranges
Selecting multisheet ranges
Selecting special types of cells
Selecting cells by searching
Copying or Moving Ranges
Copying by using Ribbon commands
Copying by using shortcut menu commands
Copying by using shortcut keys
Copying or moving by using drag-and-drop
Copying to adjacent cells
Copying a range to other sheets
Using the Office Clipboard to paste
Pasting in special ways
Using the Paste Special dialog box
Performing mathematical operations without formulas
Skipping blanks when pasting
Transposing a range
Using Names to Work with Ranges
Creating range names in your workbooks
Using the Name box
Using the New Name dialog box
Using the Create Names from Selection dialog box
Managing names
Adding Comments to Cells
Formatting comments
Changing a comment’s shape
Reading comments
Hiding and showing comments
Editing comments
Deleting comments
Working with Tables
Understanding a table’s structure
The header row
The data body
The total row
The resizing handle
Creating a table
Adding data to a table
Sorting and filtering table data
Sorting a table
Filtering a table
Filtering a table with slicers
Changing the table’s appearance
Chapter 5: Formatting Worksheets
Getting to Know the Formatting Tools
Using the formatting tools on the Home tab
Using the Mini toolbar
Using the Format Cells dialog box
Formatting Your Worksheet
Using fonts to format your worksheet
Changing text alignment
Choosing horizontal alignment options
Choosing vertical alignment options
Wrapping or shrinking text to fit the cell
Merging worksheet cells to create additional text space
Displaying text at an angle
Using colors and shading
Adding borders and lines
Using Conditional Formatting
Specifying conditional formatting
Using graphical conditional formats
Using data bars
Using color scales
Using icon sets
Creating formula-based rules
Understanding relative and absolute references
Conditional formatting formula examples
Identifying weekend days
Highlighting a row based on a value
Displaying alternate-row shading
Creating checkerboard shading
Shading groups of rows
Working with conditional formats
Managing rules
Copying cells that contain conditional formatting
Deleting conditional formatting
Locating cells that contain conditional formatting
Using Named Styles for Easier Formatting
Applying styles
Modifying an existing style
Creating new styles
Merging styles from other workbooks
Controlling styles with templates
Understanding Document Themes
Applying a theme
Customizing a theme
Chapter 6: Understanding Excel Files and Templates
Creating a New Workbook
Opening an Existing Workbook
Filtering filenames
Choosing your file display preferences
Saving a Workbook
Using AutoRecover
Recovering versions of the current workbook
Recovering unsaved work
Configuring AutoRecover
Password-Protecting a Workbook
Organizing Your Files
Other Workbook Info Options
Protect Workbook options
Check for Issues options
Manage Workbook options
Browser View options
Compatibility Mode section
Closing Workbooks
Safeguarding Your Work
Working with Templates
Exploring Excel templates
Viewing templates
Creating a workbook from a template
Modifying a template
Using default templates
Using the workbook template to change workbook defaults
Creating a worksheet template
Editing your template
Resetting the default workbook
Using custom workbook templates
Creating custom templates
Saving your custom templates
Using custom templates
Chapter 7: Printing Your Work
Doing Basic Printing
Changing Your Page View
Normal view
Page Layout view
Page Break Preview
Adjusting Common Page Setup Settings
Choosing your printer
Specifying what you want to print
Changing page orientation
Specifying paper size
Printing multiple copies of a report
Adjusting the page margins
Understanding page breaks
Inserting a page break
Removing manual page breaks
Printing row and column titles
Scaling printed output
Printing cell gridlines
Printing row and column headers
Using a background image
Adding a Header or a Footer to Your Reports
Selecting a predefined header or footer
Understanding header and footer element codes
Exploring other header and footer options
Exploring Other Print-Related Topics
Copying Page Setup settings across sheets
Preventing certain cells from being printed
Preventing objects from being printed
Creating custom views of your worksheet
Creating PDF files
Chapter 8: Customizing the Excel User Interface
Customizing the Quick Access Toolbar
About the Quick Access toolbar
Adding new commands to the Quick Access toolbar
Other Quick Access toolbar actions
Customizing the Ribbon
Why you may want to customize the Ribbon
What can be customized
How to customize the Ribbon
Creating a new tab
Creating a new group
Adding commands to a new group
Resetting the Ribbon
Part II: Working with Formulas and Functions
Chapter 9: Introducing Formulas and Functions
Understanding Formula Basics
Using operators in formulas
Understanding operator precedence in formulas
Using functions in your formulas
Examples of formulas that use functions
Function arguments
More about functions
Entering Formulas into Your Worksheets
Entering formulas manually
Entering formulas by pointing
Pasting range names into formulas
Inserting functions into formulas
Function entry tips
Editing Formulas
Using Cell References in Formulas
Using relative, absolute, and mixed references
Changing the types of your references
Referencing cells outside the worksheet
Referencing cells in other worksheets
Referencing cells in other workbooks
Using Formulas in Tables
Summarizing data in a table
Using formulas within a table
Referencing data in a table
Correcting Common Formula Errors
Handling circular references
Specifying when formulas are calculated
Using Advanced Naming Techniques
Using names for constants
Using names for formulas
Using range intersections
Applying names to existing references
Working with Formulas
Not hard-coding values
Using the Formula bar as a calculator
Making an exact copy of a formula
Converting formulas to values
Chapter 10: Using Formulas for Common Mathematical Operations
Calculating Percentages
Calculating percent of goal
Calculating percent variance
Calculating percent variance with negative values
Calculating a percent distribution
Calculating a running total
Applying a percent increase or decrease to values
Dealing with divide-by-zero errors
Rounding Numbers
Rounding numbers using formulas
Rounding to the nearest penny
Rounding to significant digits
Counting Values in a Range
Using Excel’s Conversion Functions
Chapter 11: Using Formulas to Manipulate Text
Working with Text
Using Text Functions
Joining text strings
Setting text to sentence case
Removing spaces from a text string
Extracting parts of a text string
Finding a particular character in a text string
Finding the second instance of a character
Substituting text strings
Counting specific characters in a cell
Adding a line break within a formula
Cleaning strange characters from text fields
Padding numbers with zeros
Formatting the numbers in a text string
Using the DOLLAR function
Chapter 12: Using Formulas with Dates and Times
Understanding How Excel Handles Dates and Times
Understanding date serial numbers
Entering dates
Understanding time serial numbers
Entering times
Formatting dates and times
Problems with dates
Excel’s leap year bug
Pre-1900 dates
Inconsistent date entries
Using Excel’s Date and Time Functions
Getting the current date and time
Calculating age
Calculating the number of days between two dates
Calculating the number of workdays between two dates
Using NETWORKDAYS.INTL
Generating a list of business days excluding holidays
Extracting parts of a date
Calculating number of years and months between dates
Converting dates to Julian date formats
Calculating the percent of year completed and remaining
Returning the last date of a given month
Using the EOMONTH function
Calculating the calendar quarter for a date
Calculating the fiscal quarter for a date
Returning a fiscal month from a date
Calculating the date of the Nth weekday of the month
Calculating the date of the last weekday of the month
Extracting parts of a time
Calculating elapsed time
Rounding time values
Converting decimal hours, minutes, or seconds to a time
Adding hours, minutes, or seconds to a time
Chapter 13: Using Formulas for Conditional Analysis
Understanding Conditional Analysis
Checking if a simple condition is met
Checking for multiple conditions
Validating conditional data
Looking up values
Checking if Condition1 AND Condition2 are met
Referring to logical conditions in cells
Checking if Condition1 OR Condition2 are met
Performing Conditional Calculations
Summing all values that meet a certain condition
Summing greater than zero
Summing all values that meet two or more conditions
Summing if values fall between a given date range
Using SUMIFS
Getting a count of values that meet a certain condition
Getting a count of values that meet two or more conditions
Finding nonstandard characters
Getting the average of all numbers that meet a certain condition
Getting the average of all numbers that meet two or more conditions
Chapter 14: Using Formulas for Matching and Lookups
Introducing Lookup Formulas
Leveraging Excel’s Lookup Functions
Looking up an exact value based on a left lookup column
Looking up an exact value based on any lookup column
Looking up values horizontally
Hiding errors returned by lookup functions
Finding the closest match from a list of banded values
Finding the closest match with the INDEX and MATCH functions
Looking up values from multiple tables
Looking up a value based on a two-way matrix
Using default values for match
Finding a value based on multiple criteria
Returning text with SUMPRODUCT
Finding the last value in a column
Finding the last number using LOOKUP
Chapter 15: Using Formulas for Financial Analysis
Performing Common Business Calculations
Calculating gross profit margin and gross profit margin percent
Calculating markup
Calculating EBIT and EBITDA
Calculating cost of goods sold
Calculating return on assets
Calculating return on equity
Calculating break even
Calculating customer churn
Calculating annual churn rate
Calculating average customer lifetime value
Calculating employee turnover
Leveraging Excel’s Financial Functions
Converting interest rates
Computing effective rate with FV
Creating a loan payment calculator
Creating an amortization schedule
Creating a variable-rate mortgage amortization schedule
Using dates instead of payment numbers
Calculating depreciation
Calculating accelerated depreciation
Calculating present value
Calculating the present value of future payments
Calculating net present value
Calculating positive and negative cash flows
Calculating an internal rate of return
Calculating nonperiodic future cash flows
Performing financial forecasting
Chapter 16: Using Formulas for Statistical Analysis
Working with Weighted Averages
Smoothing Data with Moving Averages
Applying exponential smoothing to volatile data
Using Functions to Create Descriptive Statistics
Getting the largest or smallest value
Getting the Nth largest or smallest value
Calculating mean, median, and mode
Bucketing Data into Percentiles
Identifying Statistical Outliers with an Interquartile Range
Creating a Frequency Distribution
An alternative to the FREQUENCY function
Chapter 17: Using Formulas with Tables and Conditional Formatting
Highlighting Cells That Meet Certain Criteria
Highlighting cells based on the value of another cell
Highlighting Values That Exist in List1 but Not List2
Highlighting Values That Exist in List1 and List2
Highlighting Based on Dates
Highlighting days between two dates
Highlighting dates based on a due date
Chapter 18: Understanding and Using Array Formulas
Understanding Array Formulas
A multicell array formula
A single-cell array formula
Creating an Array Constant
Understanding the Dimensions of an Array
One-dimensional horizontal arrays
One-dimensional vertical arrays
Two-dimensional arrays
Naming Array Constants
Working with Array Formulas
Entering an array formula
Selecting an array formula range
Editing an array formula
Expanding or contracting a multicell array formula
Using Multicell Array Formulas
Creating an array from values in a range
Creating an array constant from values in a range
Performing operations on an array
Using functions with an array
Transposing an array
Generating an array of consecutive integers
Using Single-Cell Array Formulas
Counting characters in a range
Summing the three smallest values in a range
Counting text cells in a range
Eliminating intermediate formulas
Using an array instead of a range reference
Chapter 19: Making Your Formulas Error-Free
Finding and Correcting Formula Errors
Mismatched parentheses
Cells are filled with hash marks
Blank cells are not blank
Extra space characters
Formulas returning an error
#DIV/0! errors
#N/A errors
#NAME? errors
#NULL! errors
#NUM! errors
#REF! errors
#VALUE! errors
Operator precedence problems
Formulas are not calculated
Problems with decimal precision
“Phantom link” errors
Using Excel Auditing Tools
Identifying cells of a particular type
Viewing formulas
Tracing cell relationships
Identifying precedents
Identifying dependents
Tracing error values
Fixing circular reference errors
Using the background error-checking feature
Using Formula Evaluator
Searching and Replacing
Searching for information
Replacing information
Searching for formatting
Spell-checking your worksheets
Using AutoCorrect
Part III: Creating Charts and Other Visualizations
Chapter 20: Getting Started with Excel Charts
What Is a Chart?
How Excel handles charts
Embedded charts
Chart sheets
Parts of a chart
Chart limitations
Basic Steps for Creating a Chart
Creating the chart
Switching the row and column orientation
Changing the chart type
Applying a chart layout
Applying a chart style
Adding and deleting chart elements
Formatting chart elements
Modifying and Customizing Charts
Moving and resizing a chart
Converting an embedded chart to a chart sheet
Copying a chart
Deleting a chart
Adding chart elements
Moving and deleting chart elements
Formatting chart elements
Copying a chart’s formatting
Renaming a chart
Printing charts
Understanding Chart Types
Choosing a chart type
Column charts
Bar charts
Line charts
Pie charts
XY (scatter) charts
Area charts
Radar charts
Surface charts
Bubble charts
Stock charts
New Chart Types for Excel
Histogram charts
Pareto charts
Waterfall charts
Box & whisker charts
Sunburst charts
Treemap charts
Funnel charts
Map charts
Chapter 21: Using Advanced Charting Techniques
Selecting Chart Elements
Selecting with the mouse
Selecting with the keyboard
Selecting with the Chart Elements control
Exploring the User Interface Choices for Modifying Chart Elements
Using the Format task pane
Using the chart customization buttons
Using the Ribbon
Using the Mini toolbar
Modifying the Chart Area
Modifying the Plot Area
Working with Titles in a Chart
Working with a Legend
Working with Gridlines
Modifying the Axes
Modifying the value axis
Modifying the category axis
Working with Data Series
Deleting or hiding a data series
Adding a new data series to a chart
Changing data used by a series
Changing the data range by dragging the range outline
Using the Edit Series dialog box
Editing the Series formula
Displaying data labels in a chart
Handling missing data
Adding error bars
Adding a trendline
Creating combination charts
Displaying a data table
Creating Chart Templates
Chapter 22: Creating Sparkline Graphics
Sparkline Types
Creating Sparklines
Customizing Sparklines
Sizing Sparkline cells
Handling hidden or missing data
Changing the Sparkline type
Changing Sparkline colors and line width
Highlighting certain data points
Adjusting Sparkline axis scaling
Faking a reference line
Specifying a Date Axis
Auto-Updating Sparklines
Displaying a Sparkline for a Dynamic Range
Chapter 23: Visualizing with Custom Number Formats and Shapes
Visualizing with Number Formatting
Doing basic number formatting
Using shortcut keys to format numbers
Using the Format Cells dialog box to format numbers
Getting fancy with custom number formatting
Formatting numbers in thousands and millions
Hiding and suppressing zeros
Applying custom format colors
Formatting dates and times
Using symbols to enhance reporting
Using Shapes and Icons as Visual Elements
Inserting a shape
Inserting SVG icon graphics
Formatting shapes and icons
Enhancing Excel reports with shapes
Creating visually appealing containers with shapes
Layering shapes to save space
Constructing your own infographic widgets with shapes
Creating dynamic labels
Creating linked pictures
Using SmartArt and WordArt
SmartArt basics
WordArt basics
Working with Other Graphics Types
About graphics files
Inserting screenshots
Displaying a worksheet background image
Using the Equation Editor
Chapter 24: Implementing Excel Dashboarding Best Practices
Preparing for a Dashboard Project
Establishing the audience and purpose for the dashboard
Delineating the measures for the dashboard
Cataloging the required data sources
Defining the dimensions and filters for the dashboard
Determining the need for drill-down features
Establishing the refresh schedule
Implementing Dashboard Modeling Best Practices
Separating data, analysis, and presentation
Starting with appropriately structured data
Spreadsheet reports make for ineffective data models
Flat data files lend themselves nicely to data models
Avoiding turning your data model into a database
Documenting and organizing your data model
Implementing Dashboard Design Best Practices
Keep it simple
Don’t turn your dashboard into a data repository
Avoid the fancy formatting
Limit each dashboard to one printable page
Format numbers effectively
Use titles and labels effectively
Part IV: Managing and Analyzing Data
Chapter 25: Importing and Cleaning Data
Importing Data
Importing from a file
Spreadsheet file formats
Database file formats
Text file formats
HTML files
XML files
Importing vs. opening
Importing a text file
Copying and pasting data
Cleaning Up Data
Removing duplicate rows
Identifying duplicate rows
Splitting text
Using Text to Columns
Using Flash Fill
Changing the case of text
Removing extra spaces
Removing strange characters
Converting values
Classifying values
Joining columns
Rearranging columns
Randomizing the rows
Extracting a filename from a URL
Matching text in a list
Changing vertical data to horizontal data
Filling gaps in an imported report
Checking spelling
Replacing or removing text in cells
Adding text to cells
Fixing trailing minus signs
Following a data cleaning checklist
Exporting Data
Exporting to a text file
CSV files
TXT files
PRN files
Exporting to other file formats
Chapter 26: Using Data Validation
About Data Validation
Specifying Validation Criteria
Types of Validation Criteria You Can Apply
Creating a Drop-Down List
Using Formulas for Data Validation Rules
Understanding Cell References
Data Validation Formula Examples
Accepting text only
Accepting a larger value than the previous cell
Accepting nonduplicate entries only
Accepting text that begins with a specific character
Accepting dates by the day of the week
Accepting only values that don’t exceed a total
Creating a dependent list
Using Data Validation without Restricting Entry
Showing an input message
Making suggested entries
Chapter 27: Creating and Using Worksheet Outlines
Introducing Worksheet Outlines
Creating an Outline
Preparing the data
Creating an outline automatically
Creating an outline manually
Working with Outlines
Displaying levels
Adding data to an outline
Removing an outline
Adjusting the outline symbols
Hiding the outline symbols
Chapter 28: Linking and Consolidating Worksheets
Linking Workbooks
Creating External Reference Formulas
Understanding link formula syntax
Creating a link formula by pointing
Pasting links
Working with External Reference Formulas
Creating links to unsaved workbooks
Opening a workbook with external reference formulas
Changing the startup prompt
Updating links
Changing the link source
Severing links
Avoiding Potential Problems with External Reference Formulas
Renaming or moving a source workbook
Using the Save As command
Modifying a source workbook
Using Intermediary links
Consolidating Worksheets
Consolidating worksheets by using formulas
Consolidating worksheets by using Paste Special
Consolidating worksheets by using the Consolidate dialog box
Viewing a workbook consolidation example
Refreshing a consolidation
Learning more about consolidation
Chapter 29: Introducing PivotTables
About PivotTables
A PivotTable example
Data appropriate for a PivotTable
Creating a PivotTable Automatically
Creating a PivotTable Manually
Specifying the data
Specifying the location for the PivotTable
Laying out the PivotTable
Formatting the PivotTable
Modifying the PivotTable
Seeing More PivotTable Examples
What is the daily total new deposit amount for each branch?
Which day of the week accounts for the most deposits?
How many accounts were opened at each branch, broken down by account type?
How much money was used to open the accounts?
What types of accounts do tellers open most often?
In which branch do tellers open the most checking accounts for new customers?
Learning More
Chapter 30: Analyzing Data with PivotTables
Working with Non-numeric Data
Grouping PivotTable Items
A manual grouping example
Automatic grouping examples
Grouping by date
Grouping by time
Using a PivotTable to Create a Frequency Distribution
Creating a Calculated Field or Calculated Item
Creating a calculated field
Inserting a calculated item
Filtering PivotTables with Slicers
Filtering PivotTables with a Timeline
Referencing Cells within a PivotTable
Creating PivotCharts
A PivotChart example
More about PivotCharts
Using the Data Model
Chapter 31: Performing Spreadsheet What-If Analysis
Looking at a What-If Example
Exploring Types of What-If Analyses
Performing manual what-if analysis
Creating data tables
Creating a one-input data table
Creating a two-input data table
Using Scenario Manager
Defining scenarios
Displaying scenarios
Modifying scenarios
Merging scenarios
Generating a scenario report
Chapter 32: Analyzing Data Using Goal Seeking and Solver
Exploring What-If Analysis, in Reverse
Using Single-Cell Goal Seeking
Looking at a goal-seeking example
Learning more about goal seeking
Introducing Solver
Looking at appropriate problems for Solver
Seeing a simple Solver example
Exploring Solver options
Seeing Some Solver Examples
Solving simultaneous linear equations
Minimizing shipping costs
Allocating resources
Optimizing an investment portfolio
Chapter 33: Analyzing Data with the Analysis ToolPak
The Analysis ToolPak: An Overview
Installing the Analysis ToolPak Add-In
Using the Analysis Tools
Introducing the Analysis ToolPak Tools
Analysis of variance
Correlation
Covariance
Descriptive statistics
Exponential smoothing
F-test (two-sample test for variance)
Fourier analysis
Histogram
Moving average
Random number generation
Rank and percentile
Regression
Sampling
T-test
Z-test (two-sample test for means)
Chapter 34: Protecting Your Work
Types of Protection
Protecting a Worksheet
Unlocking cells
Sheet protection options
Assigning user permissions
Protecting a Workbook
Requiring a password to open a workbook
Protecting a workbook’s structure
Protecting a VBA Project
Related Topics
Saving a worksheet as a PDF file
Marking a workbook as final
Inspecting a workbook
Using a digital signature
Getting a digital ID
Signing a workbook
Part V: Understanding Power Pivot and Power Query
Chapter 35: Introducing Power Pivot
Understanding the Power Pivot Internal Data Model
Activating the Power Pivot Ribbon
Linking Excel tables to Power Pivot
Preparing your Excel tables
Adding your Excel tables to the data model
Creating relationships between your Power Pivot tables
Managing existing relationships
Using Power Pivot data in reporting
Loading Data from Other Data Sources
Loading data from relational databases
Loading data from SQL Server
Loading data from other relational database systems
Loading data from flat files
Loading data from external Excel files
Loading data from text files
Loading data from the clipboard
Refreshing and managing external data connections
Manually refreshing your Power Pivot data
Setting up automatic refreshing
Editing your data connection
Chapter 36: Working Directly with the Internal Data Model
Directly Feeding the Internal Data Model
Managing Relationships in the Internal Data Model
Removing a Table from the Internal Data Model
Chapter 37: Adding Formulas to Power Pivot
Enhancing Power Pivot Data with Calculated Columns
Creating your first calculated column
Formatting your calculated columns
Referencing calculated columns in other calculations
Hiding calculated columns from end users
Utilizing DAX to Create Calculated Columns
Identifying DAX functions safe for calculated columns
Building DAX-driven calculated columns
Month sorting in Power Pivot–driven PivotTables
Referencing fields from other tables
Nesting functions
Understanding Calculated Measures
Editing and deleting calculated measures
Using Cube Functions to Free Your Data
Chapter 38: Introducing Power Query
Understanding Power Query Basics
Understanding query steps
Viewing the Advanced Query Editor
Refreshing Power Query data
Managing existing queries
Understanding column-level actions
Understanding table actions
Getting Data from External Sources
Importing data from files
Getting data from Excel workbooks
Getting data from CSV and text files
Importing data from database systems
Importing data from relational and OLAP databases
Importing data from Azure databases
Importing data using ODBC connections to nonstandard databases
Getting Data from Other Data Systems
Managing Data Source Settings
Editing data source settings
Chapter 39: Transforming Data with Power Query
Performing Common Transformation Tasks
Removing duplicate records
Filling in blank fields
Filling in empty strings
Concatenating columns
Changing case
Finding and replacing specific text
Trimming and cleaning text
Extracting the left, right, and middle values
Extracting first and last characters
Extracting middle characters
Splitting columns using character markers
Unpivoting columns
Unpivoting other columns
Pivoting columns
Creating Custom Columns
Concatenating with a custom column
Understanding data type conversions
Spicing up custom columns with functions
Adding conditional logic to custom columns
Grouping and Aggregating Data
Chapter 40: Making Queries Work Together
Reusing Query Steps
Understanding the Append Feature
Creating the needed base queries
Appending the data
Understanding the Merge Feature
Understanding Power Query joins
Merging queries
Chapter 41: Enhancing Power Query Productivity
Implementing Some Power Query Productivity Tips
Getting quick information about your queries
Organizing queries in groups
Selecting columns in your queries faster
Renaming query steps
Quickly creating reference tables
Copying queries to save time
Setting a default load behavior
Preventing automatic data type changes
Avoiding Power Query Performance Issues
Using views instead of tables
Letting your back-end database servers do some crunching
Upgrading to 64-bit Excel
Disabling privacy settings to improve performance
Disabling relationship detection
Part VI: Automating Excel
Chapter 42: Introducing Visual Basic for Applications
Introducing VBA Macros
Displaying the Developer Tab
Learning about Macro Security
Saving Workbooks That Contain Macros
Looking at the Two Types of VBA Macros
VBA Sub procedures
VBA functions
Creating VBA Macros
Recording VBA macros
Recording your actions to create VBA code: the basics
Recording a macro: a simple example
Examining the macro
Testing the macro
Editing the macro
Absolute versus relative recording
Another example
Running the macro
Examining the macro
Rerecording the macro
Testing the macro
More about recording VBA macros
Storing macros in your Personal Macro Workbook
Assigning a macro to a shortcut key
Assigning a macro to a button
Adding a macro to your Quick Access toolbar
Writing VBA code
The basics: entering and editing code
The Excel object model
Objects and collections
Properties
Methods
The Range object
Variables
Controlling execution
A macro that can’t be recorded
Learning More
Chapter 43: Creating Custom Worksheet Functions
Introducing VBA Functions
Seeing a Simple Example
Creating a custom function
Using the function in a worksheet
Analyzing the custom function
Learning about Function Procedures
Executing Function Procedures
Calling custom functions from a procedure
Using custom functions in a worksheet formula
Using Function Procedure Arguments
Creating a function with no arguments
Creating a function with one argument
Creating another function with one argument
Creating a function with two arguments
Creating a function with a range argument
Creating a simple but useful function
Debugging Custom Functions
Inserting Custom Functions
Learning More
Chapter 44: Creating UserForms
Understanding Why to Create UserForms
Exploring UserForm Alternatives
Using the InputBox function
Using the MsgBox function
Creating UserForms: An Overview
Working with UserForms
Adding controls
Changing the properties of a control
Handling events
Displaying a UserForm
Looking at a UserForm Example
Creating the UserForm
Testing the UserForm
Creating an event handler procedure
Looking at Another UserForm Example
Creating the UserForm
Creating event handler procedures
Showing the UserForm
Testing the UserForm
Making the macro available from a worksheet button
Making the macro available on your Quick Access toolbar
Enhancing UserForms
Adding accelerator keys
Controlling tab order
Learning More
Chapter 45: Using UserForm Controls in a Worksheet
Understanding Why to Use Controls on a Worksheet
Using Controls
Adding a control
Learning about Design mode
Adjusting properties
Using common properties
Linking controls to cells
Creating macros for controls
Reviewing the Available ActiveX Controls
CheckBox
ComboBox
CommandButton
Image
Label
ListBox
OptionButton
ScrollBar
SpinButton
TextBox
ToggleButton
Chapter 46: Working with Excel Events
Understanding Events
Entering Event-Handler VBA Code
Using Workbook-Level Events
Using the Open event
Using the SheetActivate event
Using the NewSheet event
Using the BeforeSave event
Using the BeforeClose event
Working with Worksheet Events
Using the Change event
Monitoring a specific range for changes
Using the SelectionChange event
Using the BeforeRightClick event
Using Special Application Events
Using the OnTime event
Using the OnKey event
Chapter 47: Seeing Some VBA Examples
Working with Ranges
Copying a range
Copying a variable-size range
Selecting to the end of a row or column
Selecting a row or column
Moving a range
Looping through a range efficiently
Prompting for a cell value
Determining the type of selection
Identifying a multiple selection
Counting selected cells
Working with Workbooks
Saving all workbooks
Saving and closing all workbooks
Working with Charts
Modifying the chart type
Modifying chart properties
Applying chart formatting
VBA Speed Tips
Turning off screen updating
Preventing alert messages
Simplifying object references
Declaring variable types
Chapter 48: Creating Custom Excel Add-Ins
Understanding Add-Ins
Working with Add-Ins
Understanding Why to Create Add-Ins
Creating Add-Ins
Looking at an Add-In Example
Learning about Module1
Learning about the UserForm
Testing the workbook
Adding descriptive information
Creating the user interface for your add-in macro
Protecting the project
Creating the add-in
Installing the add-in
Index
EULA