Excel 2019 Bible E-Book

Michael Alexander

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Bible
• Sprache: Englisch

Seitenzahl: 1467

Veröffentlichungsjahr: 2018

Beschreibung

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.

Leseprobe

Table of Contents

Cover

Introduction

Is This Book for You?

Software Versions

Conventions Used in This Book

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

Using the Ribbon

Using Shortcut Menus

Customizing Your Quick Access Toolbar

Working with Dialog Boxes

Using Task Panes

Creating Your First Excel Workbook

CHAPTER 2: Entering and Editing Worksheet Data

Exploring Data Types

Entering Text and Values into Your Worksheets

Entering Dates and Times into Your Worksheets

Modifying Cell Contents

Applying Number Formatting

CHAPTER 3: Performing Basic Worksheet Operations

Learning the Fundamentals of Excel Worksheets

Controlling the Worksheet View

Working with Rows and Columns

CHAPTER 4: Working with Excel Ranges and Tables

Understanding Cells and Ranges

Copying or Moving Ranges

Using Names to Work with Ranges

Adding Comments to Cells

Working with Tables

CHAPTER 5: Formatting Worksheets

Getting to Know the Formatting Tools

Formatting Your Worksheet

Using Conditional Formatting

Using Named Styles for Easier Formatting

Understanding Document Themes

CHAPTER 6: Understanding Excel Files and Templates

Creating a New Workbook

Opening an Existing Workbook

Saving a Workbook

Using AutoRecover

Password-Protecting a Workbook

Organizing Your Files

Other Workbook Info Options

Closing Workbooks

Safeguarding Your Work

Working with Templates

CHAPTER 7: Printing Your Work

Doing Basic Printing

Changing Your Page View

Adjusting Common Page Setup Settings

Adding a Header or a Footer to Your Reports

Exploring Other Print-Related Topics

CHAPTER 8: Customizing the Excel User Interface

Customizing the Quick Access Toolbar

Customizing the Ribbon

Part II: Working with Formulas and Functions

CHAPTER 9: Introducing Formulas and Functions

Understanding Formula Basics

Entering Formulas into Your Worksheets

Editing Formulas

Using Cell References in Formulas

Using Formulas in Tables

Correcting Common Formula Errors

Using Advanced Naming Techniques

Working with Formulas

CHAPTER 10: Using Formulas for Common Mathematical Operations

Calculating Percentages

Rounding Numbers

Counting Values in a Range

Using Excel's Conversion Functions

CHAPTER 11: Using Formulas to Manipulate Text

Working with Text

Using Text Functions

CHAPTER 12: Using Formulas with Dates and Times

Understanding How Excel Handles Dates and Times

Using Excel's Date and Time Functions

CHAPTER 13: Using Formulas for Conditional Analysis

Understanding Conditional Analysis

Performing Conditional Calculations

CHAPTER 14: Using Formulas for Matching and Lookups

Introducing Lookup Formulas

Leveraging Excel's Lookup Functions

CHAPTER 15: Using Formulas for Financial Analysis

Performing Common Business Calculations

Leveraging Excel's Financial Functions

CHAPTER 16: Using Formulas for Statistical Analysis

Working with Weighted Averages

Smoothing Data with Moving Averages

Using Functions to Create Descriptive Statistics

Bucketing Data into Percentiles

Identifying Statistical Outliers with an Interquartile Range

Creating a Frequency Distribution

CHAPTER 17: Using Formulas with Tables and Conditional Formatting

Highlighting Cells That Meet Certain Criteria

Highlighting Values That Exist in List1 but Not List2

Highlighting Values That Exist in List1 and List2

Highlighting Based on Dates

CHAPTER 18: Understanding and Using Array Formulas

Understanding Array Formulas

Creating an Array Constant

Understanding the Dimensions of an Array

Naming Array Constants

Working with Array Formulas

Using Multicell Array Formulas

Using Single-Cell Array Formulas

CHAPTER 19: Making Your Formulas Error-Free

Finding and Correcting Formula Errors

Using Excel Auditing Tools

Searching and Replacing

Using AutoCorrect

Part III: Creating Charts and Other Visualizations

CHAPTER 20: Getting Started with Excel Charts

What Is a Chart?

Basic Steps for Creating a Chart

Modifying and Customizing Charts

Understanding Chart Types

New Chart Types for Excel

CHAPTER 21: Using Advanced Charting Techniques

Selecting Chart Elements

Exploring the User Interface Choices for Modifying Chart Elements

Modifying the Chart Area

Modifying the Plot Area

Working with Titles in a Chart

Working with a Legend

Working with Gridlines

Modifying the Axes

Working with Data Series

Creating Chart Templates

CHAPTER 22: Creating Sparkline Graphics

Sparkline Types

Creating Sparklines

Customizing Sparklines

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

Using Shapes and Icons as Visual Elements

Using SmartArt and WordArt

Working with Other Graphics Types

Using the Equation Editor

CHAPTER 24: Implementing Excel Dashboarding Best Practices

Preparing for a Dashboard Project

Implementing Dashboard Modeling Best Practices

Implementing Dashboard Design Best Practices

Part IV: Managing and Analyzing Data

CHAPTER 25: Importing and Cleaning Data

Importing Data

Cleaning Up Data

Exporting Data

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

Using Data Validation without Restricting Entry

CHAPTER 27: Creating and Using Worksheet Outlines

Introducing Worksheet Outlines

Creating an Outline

Working with Outlines

CHAPTER 28: Linking and Consolidating Worksheets

Linking Workbooks

Creating External Reference Formulas

Working with External Reference Formulas

Avoiding Potential Problems with External Reference Formulas

Consolidating Worksheets

CHAPTER 29: Introducing PivotTables

About PivotTables

Creating a PivotTable Automatically

Creating a PivotTable Manually

Seeing More PivotTable Examples

Learning More

CHAPTER 30: Analyzing Data with PivotTables

Working with Non-numeric Data

Grouping PivotTable Items

Using a PivotTable to Create a Frequency Distribution

Creating a Calculated Field or Calculated Item

Filtering PivotTables with Slicers

Filtering PivotTables with a Timeline

Referencing Cells within a PivotTable

Creating PivotCharts

Using the Data Model

CHAPTER 31: Performing Spreadsheet What-If Analysis

Looking at a What-If Example

Exploring Types of What-If Analyses

CHAPTER 32: Analyzing Data Using Goal Seeking and Solver

Exploring What-If Analysis, in Reverse

Using Single-Cell Goal Seeking

Introducing Solver

Seeing Some Solver Examples

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

CHAPTER 34: Protecting Your Work

Types of Protection

Protecting a Worksheet

Protecting a Workbook

Protecting a VBA Project

Related Topics

Part V: Understanding Power Pivot and Power Query

CHAPTER 35: Introducing Power Pivot

Understanding the Power Pivot Internal Data Model

Loading Data from Other Data Sources

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

Utilizing DAX to Create Calculated Columns

Understanding Calculated Measures

Using Cube Functions to Free Your Data

CHAPTER 38: Introducing Power Query

Understanding Power Query Basics

Getting Data from External Sources

Getting Data from Other Data Systems

Managing Data Source Settings

CHAPTER 39: Transforming Data with Power Query

Performing Common Transformation Tasks

Creating Custom Columns

Grouping and Aggregating Data

CHAPTER 40: Making Queries Work Together

Reusing Query Steps

Understanding the Append Feature

Understanding the Merge Feature

CHAPTER 41: Enhancing Power Query Productivity

Implementing Some Power Query Productivity Tips

Avoiding Power Query Performance Issues

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

Creating VBA Macros

Learning More

CHAPTER 43: Creating Custom Worksheet Functions

Introducing VBA Functions

Seeing a Simple Example

Learning about Function Procedures

Executing Function Procedures

Using Function Procedure Arguments

Debugging Custom Functions

Inserting Custom Functions

Learning More

CHAPTER 44: Creating UserForms

Understanding Why to Create UserForms

Exploring UserForm Alternatives

Creating UserForms: An Overview

Looking at a UserForm Example

Looking at Another UserForm Example

Enhancing UserForms

Learning More

CHAPTER 45: Using UserForm Controls in a Worksheet

Understanding Why to Use Controls on a Worksheet

Using Controls

Reviewing the Available ActiveX Controls

CHAPTER 46: Working with Excel Events

Understanding Events

Entering Event-Handler VBA Code

Using Workbook-Level Events

Working with Worksheet Events

Using Special Application Events

CHAPTER 47: Seeing Some VBA Examples

Working with Ranges

Working with Workbooks

Working with Charts

VBA Speed Tips

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

Index

End User License Agreement

List of Tables

Chapter 1

TABLE 1.1 Parts of the Excel Screen That You Need to Know

TABLE 1.2 Excel Worksheet Movement Keys

Chapter 2

TABLE 2.1 Number Formatting Keyboard Shortcuts

Chapter 4

TABLE 4.1 Go To Special Options

Chapter 7

TABLE 7.1 Where to Change Printer Settings

TABLE 7.2 Header and Footer Buttons and Their Functions

Chapter 9

TABLE 9.1 Operators Used in Formulas

TABLE 9.2 Operator Precedence in Excel Formulas

TABLE 9.3 Excel Error Values

TABLE 9.4 Reference Operators for Ranges

Chapter 12

TABLE 12.1 Times of Day and Their Corresponding Serial Numbers

TABLE 12.2 Time Entry Formats Recognized by Excel

TABLE 12.3

DATEDIF

Time Unit Codes

Chapter 13

TABLE 13.1 A Truth Table for the AND Function

TABLE 13.2 A Truth Table for an OR Function Nested in an AND Function

TABLE 13.3 Simple Rules for Using Comparison Operators

Chapter 14

TABLE 14.1 Functions Used in Lookup Formulas

Chapter 20

TABLE 20.1 Limitations of Excel Charts

Chapter 23

TABLE 23.1 Number-Formatting Buttons on the Ribbon

TABLE 23.2 Number-Formatting Keyboard Shortcuts

TABLE 23.3 Common Date and Time Format Codes

Chapter 31

TABLE 31.1 Three Scenarios for the Production Model

Chapter 32

TABLE 32.1 Constraints Summary

Chapter 38

TABLE 38.1 Column-Level Actions

TABLE 38.2 Table-Level Actions

Chapter 39

TABLE 39.1 Common Conversion Functions

TABLE 39.2 Useful Transformation Functions

Chapter 44

TABLE 44.1 Constants Used in the MsgBox Function

TABLE 44.2 Toolbox Controls

Chapter 45

TABLE 45.1 ActiveX Controls

TABLE 45.2 Properties Shared by Multiple Controls

Chapter 46

TABLE 46.1 Workbook Events

TABLE 46.2 Worksheet Events

Chapter 47

TABLE 47.1 VBA Data Types

List of Illustrations

Chapter 1

FIGURE 1.1 The Excel screen has many useful elements that you will use often.

FIGURE 1.2 The active cell is the one with the dark border—in this case, cell C...

FIGURE 1.3 The Home tab of the Ribbon

FIGURE 1.4 The Home tab when Excel's window is made narrower

FIGURE 1.5 The Home tab when Excel's window is made very narrow

FIGURE 1.6 When you select an object, contextual tabs contain tools for working...

FIGURE 1.7 The Merge & Center command is a split button control.

FIGURE 1.8 Pressing Alt displays the keytips.

FIGURE 1.9 Right-click to display a shortcut menu of commands you're most likel...

FIGURE 1.10 Add new icons to your Quick Access toolbar by using the Quick Acces...

FIGURE 1.11 Excel uses a dialog box to get additional information about a comma...

FIGURE 1.12 Use the dialog box tabs to select different functional areas of the...

FIGURE 1.13 The Format Picture task pane, docked on the right side of the windo...

FIGURE 1.14 Your worksheet after you've entered the column headings and month n...

FIGURE 1.15 Your worksheet after you've created the formulas

FIGURE 1.16 Your worksheet after you've converted the range to a table

FIGURE 1.17 The table and chart

Chapter 2

FIGURE 2.1 You can use values, text, and formulas to create useful Excel worksh...

FIGURE 2.2 The Formula bar, expanded in height to show more information in the ...

FIGURE 2.3 When you're editing a cell, the Formula bar enables two new icons: C...

FIGURE 2.4 You can use the Advanced tab in Excel Options to select a number of ...

FIGURE 2.5 This series was created by using AutoFill

FIGURE 2.6 AutoCorrect allows you to create shorthand abbreviations for text yo...

FIGURE 2.7 Excel's built-in data form can simplify many data-entry tasks.

FIGURE 2.8 Use numeric formatting to make it easier to understand what the valu...

FIGURE 2.9 You can find number formatting commands in the Number group of the H...

FIGURE 2.10 When you need more control over number formats, use the Number tab ...

Chapter 3

FIGURE 3.1 Use the Arrange Windows dialog box to arrange all open nonminimized ...

FIGURE 3.2 Use the tab scrolling controls to activate a different worksheet or ...

FIGURE 3.3 Excel's warning that you might be losing some data

FIGURE 3.4 Use the Move or Copy dialog box to move or copy worksheets in the sa...

FIGURE 3.5 Use multiple windows to view different sections of a workbook at the...

FIGURE 3.6 You can split the worksheet window into two or four panes to view di...

FIGURE 3.7 Freeze certain columns and rows to make them remain visible while yo...

FIGURE 3.8 When using a table, scrolling down displays the table headings where...

FIGURE 3.9 Use the Watch Window to monitor the value in one or more cells.

FIGURE 3.10 You can't add a new row or column if it causes nonblank cells to mo...

FIGURE 3.11 You can insert partial rows or columns by using the Insert dialog b...

Chapter 4

FIGURE 4.1 When you select a range, it appears highlighted, but the active cell...

FIGURE 4.2 Excel enables you to select noncontiguous ranges.

FIGURE 4.3 The worksheets in this workbook are laid out identically.

FIGURE 4.4 In Group mode, you can work with a three-dimensional range of cells ...

FIGURE 4.5 Use the Go To Special dialog box to select specific types of cells....

FIGURE 4.6 The Find and Replace dialog box, with its options displayed

FIGURE 4.7 The Find and Replace dialog box, with its results listed

FIGURE 4.8 The Paste icons on the shortcut menu provide more control over how t...

FIGURE 4.9 Use the Clipboard task pane to copy and paste multiple items.

FIGURE 4.10 Excel offers several pasting options, with preview. Here, the infor...

FIGURE 4.11 The Paste Special dialog box

FIGURE 4.12 Transposing a range changes the orientation as the information is p...

FIGURE 4.13 Create names for cells or ranges by using the New Name dialog box....

FIGURE 4.14 Use the Create Names from Selection dialog box to name cells using ...

FIGURE 4.15 Use the Name Manager to work with range names.

FIGURE 4.16 You can add comments to cells to help point out specific items in y...

FIGURE 4.17 This comment contains a graphics image.

FIGURE 4.18 Cell comments don't have to be rectangles.

FIGURE 4.19 The areas that make up a table

FIGURE 4.20 Use the Create Table dialog box to verify that Excel guessed the ta...

FIGURE 4.21 Each column in a table has sorting and filtering options.

FIGURE 4.22 A table after performing a three-column sort

FIGURE 4.23 Using the Sort dialog box to specify a three-column sort

FIGURE 4.24 This table is filtered to show the information for only one area.

FIGURE 4.25 Specifying a more complex numeric filter

FIGURE 4.26 Use the Insert Slicers dialog box to specify which slicers to creat...

FIGURE 4.27 The table is filtered by two slicers.

FIGURE 4.28 Excel offers many different table styles.

FIGURE 4.29 Use this dialog box to create a new table style.

Chapter 5

FIGURE 5.1 Simple formatting can greatly improve the appearance of your workshe...

FIGURE 5.2 The Mini toolbar appears above or below the right-click shortcut men...

FIGURE 5.3 The Font tab of the Format Cells dialog box gives you many additiona...

FIGURE 5.4 You can choose many different font formatting options for your works...

FIGURE 5.5 The full range of alignment options is available on the Alignment ta...

FIGURE 5.6 The same text, displayed with three types of horizontal alignment

FIGURE 5.7 Merge worksheet cells to make them act as if they were a single cell...

FIGURE 5.8 Rotate text for additional visual impact.

FIGURE 5.9 Use the Borders drop-down list to add lines around worksheet cells....

FIGURE 5.10 Use the Border tab of the Format Cells dialog box for more control ...

FIGURE 5.11 The length of the data bars is proportional to the track length in ...

FIGURE 5.12 Two examples of color scale conditional formatting

FIGURE 5.13 Use the New Formatting Rule dialog box to customize a color scale....

FIGURE 5.14 Using an icon set to indicate the status of projects

FIGURE 5.15 Changing the icon assignment rule

FIGURE 5.16 Using a modified rule and eliminating an icon makes the table more ...

FIGURE 5.17 Creating a conditional formatting rule based on a formula

FIGURE 5.18 Highlighting a row, based on a matching name

FIGURE 5.19 Using conditional formatting to apply formatting to alternate rows...

FIGURE 5.20 Conditional formatting produces these groups of alternating shaded ...

FIGURE 5.21 Excel displays samples of predefined cell styles.

FIGURE 5.22 Use the Style dialog box to modify named styles.

FIGURE 5.23 The elements in this worksheet use the default theme.

FIGURE 5.24 The worksheet after applying a different theme

FIGURE 5.25 Built-in Excel theme choices

FIGURE 5.26 Use this dialog box to specify two fonts for a theme.

FIGURE 5.27 If you're feeling creative, you can specify a set of custom colors ...

Chapter 6

FIGURE 6.1 Choosing Blank Workbook from Excel's Start screen

FIGURE 6.2 You can recover to older versions of your workbook.

FIGURE 6.3 The Encrypt Document dialog box is where you specify a password for ...

FIGURE 6.4 The New page in Backstage view allows you to search for templates.

FIGURE 6.5 A workbook created from a template

Chapter 7

FIGURE 7.1 In Normal view, dotted lines indicate page breaks

FIGURE 7.2 In Page Layout view, the worksheet resembles printed pages.

FIGURE 7.3 Page Break Preview mode gives you a bird's-eye view of your workshee...

FIGURE 7.4 The Margins tab of the Page Setup dialog box

FIGURE 7.5 Use the Sheet tab of the Page Setup dialog box to specify rows or co...

FIGURE 7.6 This three-part header is one of Excel's predefined headers.

FIGURE 7.7 Use the Properties tab of the object's Format dialog box to prevent ...

FIGURE 7.8 Use the Add View dialog box to create a named view.

Chapter 8

FIGURE 8.1 The default location for the Quick Access toolbar is on the left sid...

FIGURE 8.2 This drop-down list is one way to add a new command to the Quick Acc...

FIGURE 8.3 Use the Quick Access Toolbar tab in the Excel Options dialog box to ...

FIGURE 8.4 The Customize Ribbon tab of the Excel Options dialog box

FIGURE 8.5 The View tab, with two new groups added

Chapter 9

FIGURE 9.1 Excel sometimes suggests a syntactically correct formula, but not th...

FIGURE 9.2 Excel displays a drop-down list when you enter a formula.

FIGURE 9.3 You can insert a function by selecting it from one of the function c...

FIGURE 9.4 The Insert Function dialog box

FIGURE 9.5 The Function Arguments dialog box

FIGURE 9.6 Copying a formula that contains relative references

FIGURE 9.7 Formula references to the sales tax cell should be absolute.

FIGURE 9.8 Using mixed cell references

FIGURE 9.9 A simple table with three columns of information

FIGURE 9.10 A drop-down list enables you to select a summary formula for a tabl...

FIGURE 9.11 The Difference column contains a formula.

FIGURE 9.12 The formula AutoComplete feature is useful when creating a formula ...

FIGURE 9.13 Excel allows you to name a formula that doesn't exist in a workshee...

FIGURE 9.14 You can use a range intersection formula to determine values.

FIGURE 9.15 With names, using a range intersection formula to determine values ...

Chapter 10

FIGURE 10.1 Calculating the percent of goal

FIGURE 10.2 Calculating the percent of goal using a common goal

FIGURE 10.3 Calculating the percent variance between current year sales and pre...

FIGURE 10.4 Using the ABS function will give you an accurate percent variance w...

FIGURE 10.5 Calculating a percent distribution of revenue across regions

FIGURE 10.6 Calculating percent distribution with the SUM function

FIGURE 10.7 Calculating a running total

FIGURE 10.8 Applying a percent increase and decrease using a simple formula

FIGURE 10.9 Using the IF function to avoid a division-by-zero error

FIGURE 10.10 Rounding to the nearest penny

FIGURE 10.11 Rounding numbers to one significant digit

FIGURE 10.12 A demonstration of counting cells

FIGURE 10.13 Creating a unit-of-measure conversion table

Chapter 11

FIGURE 11.1 Joining first and last names

FIGURE 11.2 Using the

TEXTJOIN

function

FIGURE 11.3 Converting text into upper, lower, proper, and sentence case

FIGURE 11.4 Removing excess spaces from text

FIGURE 11.5 Using the

LEFT

,

RIGHT

, and

MID

functions

FIGURE 11.6 Using the

FIND

function to extract data based on the position of th...

FIGURE 11.7 Nesting the

FIND

function to extract everything after the second hy...

FIGURE 11.8 Fixing the apostrophe S issue with the

SUBSTITUTE

function

FIGURE 11.9 Calculating the number of words in a cell

FIGURE 11.10 The x-axis labels in this chart include a line break and a referen...

FIGURE 11.11 Using the

CHAR()

function to force a line break between the sales ...

FIGURE 11.12 Cleaning data with the

CLEAN

function

FIGURE 11.13 Padding Customer ID fields to 10 characters

FIGURE 11.14 Numbers joined with text do not inherently adopt number formatting...

FIGURE 11.15 Using the TEXT function lets you format numbers joined with text. ...

Chapter 12

FIGURE 12.1 Calculating the number of days between today and the invoice date

FIGURE 12.2 Calculating the number of workdays between two dates

FIGURE 12.3

NETWORKDAY.INTL

allows you to specify which days to exclude as wee...

FIGURE 12.4 Start with a sheet containing the last date of the previous year an...

FIGURE 12.5 Creating a list of business days

FIGURE 12.6 Extract the parts of a date.

FIGURE 12.7 Showing the years and months between dates

FIGURE 12.8 Converting standard dates into Julian dates

FIGURE 12.9 Calculating the percent of the year completed

FIGURE 12.10 Calculating the last day of each date

FIGURE 12.11 Calculating calendar quarters

FIGURE 12.12 Calculating fiscal quarters

FIGURE 12.13 Calculating fiscal months

FIGURE 12.14 A dynamic date table calculating the Nth occurrence of each weekda...

FIGURE 12.15 A dynamic date table calculating the last weekday in each month

FIGURE 12.16 Extract the parts of a time.

FIGURE 12.17 Calculating elapsed time

FIGURE 12.18 Rounding time values to 15- and 30-minute increments

FIGURE 12.19 Converting decimal hours to hours and minutes

FIGURE 12.20 Adding a set number of hours and minutes to an existing time value...

Chapter 13

FIGURE 13.1 Monthly gas prices by state

FIGURE 13.2 A model for selecting an automobile

FIGURE 13.3 Data validation using INDIRECT

FIGURE 13.4 A different auto-selector model

FIGURE 13.5 An inventory listing

FIGURE 13.6 A modified inventory listing

FIGURE 13.7 A revised discount scheme

FIGURE 13.8 Summing values less than zero

FIGURE 13.9 List of regions and sales values

FIGURE 13.10

SUMIF

using the

TODAY

function

FIGURE 13.11 A partial listing of countries and their gross domestic product

FIGURE 13.12 Summing values that are between two dates

FIGURE 13.13 A partial listing of countries and their gross domestic product

FIGURE 13.14 1972 Alpine Skiing Olympic medalists

FIGURE 13.15 Averaging results based on a country

FIGURE 13.16 Averaging on three conditions

Chapter 14

FIGURE 14.1 A table of employee information

FIGURE 14.2 A simplified paystub form

FIGURE 14.3 A list of stores with their city and state locations

FIGURE 14.4 A table of cities and temperatures

FIGURE 14.5 A report of CEO salaries

FIGURE 14.6 A cleaner report

FIGURE 14.7 Computing income tax withholding

FIGURE 14.8 The same withholding table as Figure 14.7 except that the data is s...

FIGURE 14.9 Computing income tax withholding from two tables

FIGURE 14.10 The New Name dialog box

FIGURE 14.11 Sales data by region and year

FIGURE 14.12 Returning totals from the sales data

FIGURE 14.13 A table of departmental budgets

FIGURE 14.14 A list of invoices

Chapter 15

FIGURE 15.1 Financial statement for a manufacturing company

FIGURE 15.2 Markup and gross margin percent from a single product

FIGURE 15.3 An income statement with EBIT and EBITDA calculations

FIGURE 15.4 Calculating cost of goods sold

FIGURE 15.5 A return on assets calculation

FIGURE 15.6 A break-even calculation

FIGURE 15.7 Calculating the churn rate

FIGURE 15.8 Annual churn rate of monthly recurring revenue

FIGURE 15.9 Customer lifetime value calculation

FIGURE 15.10 Monthly employment changes over one year

FIGURE 15.11 A partial amortization schedule to compute the effective rate

FIGURE 15.12 A simple loan payment calculator

FIGURE 15.13 A partial amortization schedule

FIGURE 15.14 Conditional formatting to hide rows

FIGURE 15.15 A variable rate amortization schedule

FIGURE 15.16 A date-based amortization schedule

FIGURE 15.17 A straight-line depreciation schedule

FIGURE 15.18 An accelerated depreciation schedule

FIGURE 15.19 A present value calculation

FIGURE 15.20 The present value of a series of future payments

FIGURE 15.21 The net present value of expected future cash flows

FIGURE 15.22 The net present value of both positive and negative cash flows

FIGURE 15.23 The internal rate of return of a series of future cash flows

FIGURE 15.24 The internal rate of return of nonperiodic cash flows

FIGURE 15.25 Four years of monthly sales data

FIGURE 15.26 The Create Forecast Worksheet dialog box

FIGURE 15.27 The forecast worksheet contains a table and a chart.

Chapter 16

FIGURE 16.1 An investment portfolio with rates of return

FIGURE 16.2 Expanding a weighted average calculation into adjacent cells

FIGURE 16.3 A partial listing of golf scores

FIGURE 16.4 A graph of raw golf scores over time

FIGURE 16.5 The moving average is charted over the raw scores.

FIGURE 16.6 The demand of a product over 26 weeks

FIGURE 16.7 Demand of a product visualized

FIGURE 16.8 Average low temperatures by month

FIGURE 16.9 The results of a bowling tournament

FIGURE 16.10 The results of a race

FIGURE 16.11 A list of students and grades

FIGURE 16.12 The grades listing sorted by grade

FIGURE 16.13 Identifying quartiles for product defects

FIGURE 16.14 Sorted data and demarcation lines

FIGURE 16.15 Identifying outliers using a leveraged interquartile range

FIGURE 16.16 Leveraged interquartile ranges expand the fences outward.

FIGURE 16.17 Calculating the frequency with custom bins

FIGURE 16.18 Using the COUNTIFS function to create a frequency distribution

Chapter 17

FIGURE 17.1 The cells in this table are conditionally formatted to show a red b...

FIGURE 17.2 Configure the New Formatting Rule dialog box to apply the needed fo...

FIGURE 17.3 The cells in this table are conditionally formatted to show a red b...

FIGURE 17.4 Configure the New Formatting Rule dialog box to apply the needed fo...

FIGURE 17.5 You can conditionally format the values that exist in one list but ...

FIGURE 17.6 Configure the New Formatting Rule dialog box to apply the needed fo...

FIGURE 17.7 You can conditionally format the values that exist in both lists

FIGURE 17.8 Configure the New Formatting Rule dialog box to apply the needed fo...

FIGURE 17.9 You can conditionally format any weekend dates in a list of dates....

FIGURE 17.10 Configure the New Formatting Rule dialog box to apply the needed f...

FIGURE 17.11 You can conditionally format dates that fall between a start and e...

FIGURE 17.12 Configure the New Formatting Rule dialog box to apply the needed f...

FIGURE 17.13 You can conditionally format dates based on due date.

FIGURE 17.14 Configure the New Formatting Rule dialog box to apply the needed f...

Chapter 18

FIGURE 18.1 Column D contains formulas to calculate the total for each product....

FIGURE 18.2 The array formula in cell C9 calculates the total sales without usi...

FIGURE 18.3 A 3 × 4 array entered into a range of cells

FIGURE 18.4 A 3 × 4 array entered into a 10 × 5 cell range

FIGURE 18.5 Creating a named array constant

FIGURE 18.6 Using a named array constant in an array formula

FIGURE 18.7 Excel's warning message reminds you that you can't edit just one ce...

FIGURE 18.8 Creating an array from a range

FIGURE 18.9 After you press F9, the Formula bar displays the array constant.

FIGURE 18.10 Performing a mathematical operation on an array

FIGURE 18.11 Multiplying each array element by itself

FIGURE 18.12 Using the TRANSPOSE function to transpose a rectangular array

FIGURE 18.13 Using an array formula to generate consecutive integers

FIGURE 18.14 The goal is to count the number of characters in a range of text....

FIGURE 18.15 An array formula returns the sum of the three smallest values in A...

FIGURE 18.16 An array formula returns the number of text cells in the range.

FIGURE 18.17 Without an array formula, calculating the average change requires ...

FIGURE 18.18 You can replace the lookup table in D1:E10 with an array constant....

Chapter 19

FIGURE 19.1 Excel can check your formulas for potential errors.

FIGURE 19.2 The Evaluate Formula dialog box shows a formula being calculated on...

FIGURE 19.3 Use the Find and Replace dialog box to locate information in a work...

FIGURE 19.4 Use the AutoCorrect dialog box to control the spelling corrections ...

Chapter 20

FIGURE 20.1 A simple column chart depicts the sales volume for each month.

FIGURE 20.2 This line chart displays two data series.

FIGURE 20.3 Activating a chart displays additional tabs on the Excel Ribbon.

FIGURE 20.4 A chart on a chart sheet

FIGURE 20.5 Parts of a chart

FIGURE 20.6 A pie chart

FIGURE 20.7 A 3-D column chart

FIGURE 20.8 This data would make a good chart.

FIGURE 20.9 The icons in the Insert ⇨ Charts group expand to show a gallery of ...

FIGURE 20.10 A column chart with two data series

FIGURE 20.11 The column chart, after swapping the row/column orientation

FIGURE 20.12 The Change Chart Type dialog box

FIGURE 20.13 One-click design variations of a column chart

FIGURE 20.14 Using the Format Data Series task pane

FIGURE 20.15 Use the Move Chart dialog box to move an embedded chart to a chart...

FIGURE 20.16 The same data, plotted by using six chart types

FIGURE 20.17 This clustered column chart compares monthly sales for two product...

FIGURE 20.18 This stacked column chart displays sales by product and depicts th...

FIGURE 20.19 This 100% stacked column chart displays monthly sales as a percent...

FIGURE 20.20 3-D column charts.

FIGURE 20.21 If you have lengthy category labels, a bar chart may be a good cho...

FIGURE 20.22 A line chart often can help you spot trends in your data.

FIGURE 20.23 This line chart displays three series.

FIGURE 20.24 This 3-D line chart does not present the data very well.

FIGURE 20.25 A pie chart with one slice exploded

FIGURE 20.26 A bar of pie chart that shows detail for one of the pie slices

FIGURE 20.27 An XY chart shows the relationship between two variables.

FIGURE 20.28 A stacked area chart

FIGURE 20.29 This 3-D area chart is not a good choice.

FIGURE 20.30 Plotting ski sales using a radar chart with 12 categories and two ...

FIGURE 20.31 A surface chart

FIGURE 20.32 A bubble chart

FIGURE 20.33 The four stock chart subtypes

FIGURE 20.34 Plotting temperature data with a stock chart

FIGURE 20.35 Displaying a student grade distribution using a histogram chart

FIGURE 20.36 A Pareto chart displays the number of complaints graphically.

FIGURE 20.37 A waterfall chart showing positive and negative net cash flows

FIGURE 20.38 A box & whisker chart that summarizes data for four groups

FIGURE 20.39 A sunburst chart that depicts a music collection by genre and subg...

FIGURE 20.40 A treemap chart that depicts a music collection by genre and subge...

FIGURE 20.41 A Funnel chart visualizing the value in each stage of a sales pipe...

FIGURE 20.42 Map charts are ideal for visualizing location-based data.

FIGURE 20.43 Map charts come with unique customization options.

Chapter 21

FIGURE 21.1 The Chart Elements control (in the upper-left corner) displays the ...

FIGURE 21.2 Using the Chart Elements control in the Mini toolbar

FIGURE 21.3 Use the Format task pane to set the properties of a selected chart ...

FIGURE 21.4 Chart customization buttons

FIGURE 21.5 The Chart Area element uses No Fill, so the underlying cells are vi...

FIGURE 21.6 Reducing the size of the Plot Area makes room for the shape.

FIGURE 21.7 Use the Select Data Source dialog box to change the name of a data ...

FIGURE 21.8 Using shapes as callouts in lieu of a legend

FIGURE 21.9 The Format Axis task pane for a value axis

FIGURE 21.10 These two charts show the same data but use different value axis b...

FIGURE 21.11 The right chart uses the Values in Reverse Order option

FIGURE 21.12 These charts display the same data, but the bottom chart uses a lo...

FIGURE 21.13 The chart on the right uses display units of thousands.

FIGURE 21.14 Excel offers a great deal of flexibility in how you can display ax...

FIGURE 21.15 Some of the options available for a category axis

FIGURE 21.16 Excel recognizes dates and creates a time-based category axis

FIGURE 21.17 Overriding the Excel time-based category axis

FIGURE 21.18 Excel determines the way to display category axis labels.

FIGURE 21.19 This chart uses three columns of text for the category axis labels...

FIGURE 21.20 Changing a chart's data series by dragging the range outline

FIGURE 21.21 The Edit Series dialog box

FIGURE 21.22 These charts use data labels and don't display axes.

FIGURE 21.23 Data labels linked to text in an arbitrary range

FIGURE 21.24 Three options for dealing with missing data

FIGURE 21.25 This line chart series displays error bars based on percentage.

FIGURE 21.26 Applying a trend line

FIGURE 21.27 The trendline depicts the relationship between height and weight....

FIGURE 21.28 The Precipitation series is barely visible.

FIGURE 21.29 Using the Change Chart dialog box to convert a chart into a combin...

FIGURE 21.30 This combination chart includes a data table that displays the val...

Chapter 22

FIGURE 22.1 Three groups of Sparklines

FIGURE 22.2 Data to be summarized with Sparklines

FIGURE 22.3 Use the Create Sparklines dialog box to specify the data range and ...

FIGURE 22.4 Column Sparklines summarize the precipitation data for nine cities....

FIGURE 22.5 A Sparkline at various sizes

FIGURE 22.6 The Hidden and Empty Cell Settings dialog box

FIGURE 22.7 Highlighting options for Line Sparklines

FIGURE 22.8 The bottom group of Sparklines shows the effect of using the same a...

FIGURE 22.9 Sparklines display the number of pages read per month.

FIGURE 22.10 Using Win/Loss Sparklines to display goal achievement

FIGURE 22.11 The axis in the Sparklines represents the goal.

FIGURE 22.12 The Sparkline displays the values as if they are at equal time int...

FIGURE 22.13 After specifying a date axis, the Sparkline shows the values accur...

FIGURE 22.14 Creating a Sparkline from data in a table

FIGURE 22.15 Using a dynamic range name to display only the last seven data poi...

Chapter 23

FIGURE 23.1 The Type input box allows you to customize the syntax for the numbe...

FIGURE 23.2 Formatting numbers applies only a cosmetic look. Look in the formul...

FIGURE 23.3 Custom number formatting that shows zeros as

n/a

FIGURE 23.4 Dates and times can also be formatted using the Format Cells dialog...

FIGURE 23.5 Use symbols to add an extra layer of analysis to charts.

FIGURE 23.6 Our starting data with a holding cell for our symbols

FIGURE 23.7 Use the Symbol dialog box to insert the desired symbols into your h...

FIGURE 23.8 Copy the newly inserted symbols to the Clipboard.

FIGURE 23.9 Create a custom number format using the symbols.

FIGURE 23.10 Your symbols are now part of your number formatting.

FIGURE 23.11 The Shapes gallery

FIGURE 23.12 The Microsoft Office Icons Library

FIGURE 23.13 Shapes and icons have different contextual tabs.

FIGURE 23.14 Peekaboo tab

FIGURE 23.15 Deconstructed view of the peekaboo tab

FIGURE 23.16 A visual banner made with shapes

FIGURE 23.17 Combine shapes with a chart to save dashboard real estate.

FIGURE 23.18 Use the Edit Points feature to construct your own shape.

FIGURE 23.19 Using a newly constructed shape to create custom infographic eleme...

FIGURE 23.20 Text Box shapes can be linked to cells.

FIGURE 23.21 Pasting a linked picture

FIGURE 23.22 Using linked pictures to enhance visualizations

FIGURE 23.23 Inserting a SmartArt graphic

FIGURE 23.24 Entering text for an organizational chart

FIGURE 23.25 An equation created by the Equation Editor

Chapter 25

FIGURE 25.1 Filtering by file extension in the Open dialog box

FIGURE 25.2 Excel's title bar displays the opened file's name.

FIGURE 25.3 Enabling the legacy import wizard

FIGURE 25.4 This CSV file will be imported.

FIGURE 25.5 Step 1 of the Text Import Wizard

FIGURE 25.6 Select the delimiter in step 2 of the Text Import Wizard.

FIGURE 25.7 Using the Import Data dialog box to import a CSV file

FIGURE 25.8 This range contains data imported directly from a CSV file.

FIGURE 25.9 Use the Remove Duplicates dialog box to delete duplicate rows.

FIGURE 25.10 Using formulas to identify duplicate rows

FIGURE 25.11 The imported data was put in one column rather than multiple colum...

FIGURE 25.12 The first dialog box in the Convert Text to Columns Wizard

FIGURE 25.13 The goal is to extract the numbers in column A.

FIGURE 25.14 Using manually entered examples in B1 and B2, Excel's Flash Fill f...

FIGURE 25.15 After you enter an example of a decimal number, Excel gets all of ...

FIGURE 25.16 Using Flash Fill to split names

FIGURE 25.17 Using a lookup table to classify ages into age ranges

FIGURE 25.18 Using a lookup table to assign a region for a state

FIGURE 25.19 The

TEXTJOIN

function inserts delimiters between cell values.

FIGURE 25.20 The goal is to identify member numbers that are in the Resigned Me...

FIGURE 25.21 Vertical data that needs to be converted to three columns

FIGURE 25.22 Headers that are used to convert the vertical data into rows

FIGURE 25.23 A single formula transforms the vertical data into rows.

FIGURE 25.24 This report contains gaps in the Sales Rep column.

FIGURE 25.25 The gaps are gone, and this list can now be sorted.

FIGURE 25.26 To replace only the second hyphen in these cells, Find and Replace...

FIGURE 25.27 The Trailing Minus for Negative Numbers option makes it easy to fi...

Chapter 26

FIGURE 26.1 Displaying a message when the user makes an invalid entry

FIGURE 26.2 The three tabs of the Data Validation dialog box

FIGURE 26.3 Excel can draw circles around invalid entries (in this case, cells ...

FIGURE 26.4 This drop-down list (with an Input Message) was created using data ...

FIGURE 26.5 Entering a data validation formula

FIGURE 26.6 Using data validation to prevent duplicate entries in a range

FIGURE 26.7 Using data validation to ensure that the sum of a range does not ex...

FIGURE 26.8 The items displayed in the list in cell F2 depend on the list item ...

FIGURE 26.9 Data validation can be used to show messages to the user.

FIGURE 26.10 Data Validation dialog box

Chapter 27

FIGURE 27.1 A simple sales summary with subtotals

FIGURE 27.2 The worksheet after creating an outline

FIGURE 27.3 The worksheet after collapsing the outline to the second level

FIGURE 27.4 The worksheet after adding a column outline

FIGURE 27.5 The worksheet with both outlines collapsed at the second level

FIGURE 27.6 An outline of this book, created manually

FIGURE 27.7 Use the Settings dialog box to adjust the position of the outline s...

Chapter 28

FIGURE 28.1 This confirmation message indicates that the workbook you're saving...

FIGURE 28.2 Excel displays this dialog box when you open a workbook that contai...

FIGURE 28.3 The Edit Links dialog box

FIGURE 28.4 Use the Startup Prompt dialog box to specify how Excel handles link...

FIGURE 28.5 Choosing the Add operation in the Paste Special dialog box

FIGURE 28.6 The Consolidate dialog box enables you to specify ranges to consoli...

FIGURE 28.7 Three worksheets to be consolidated

FIGURE 28.8 The result of consolidating the information in three workbooks

FIGURE 28.9 Expanding the outline to show more details

Chapter 29

FIGURE 29.1 This table is used to create a PivotTable.

FIGURE 29.2 A simple PivotTable

FIGURE 29.3 A PivotTable that uses a report filter

FIGURE 29.4 This range is not appropriate for a PivotTable.

FIGURE 29.5 This range contains normalized data and is appropriate for a PivotT...

FIGURE 29.6 A PivotTable created from normalized data

FIGURE 29.7 Selecting a Recommended PivotTable

FIGURE 29.8 In the Create PivotTable dialog box, you tell Excel where the data ...

FIGURE 29.9 Use the PivotTable Fields task pane to build the PivotTable.

FIGURE 29.10 After a few simple steps, the PivotTable shows a summary of the da...

FIGURE 29.11 Two fields are used for row labels.

FIGURE 29.12 The PivotTable is filtered by date.

FIGURE 29.13 This PivotTable shows daily totals for each branch.

FIGURE 29.14 This PivotTable shows new account totals by day of the week.

FIGURE 29.15 This PivotTable uses the Count function to summarize the data.

FIGURE 29.16 This PivotTable counts the number of accounts that fall into each ...

FIGURE 29.17 This PivotTable uses a filter to show only the teller data.

FIGURE 29.18 This PivotTable uses three filters.

Chapter 30

FIGURE 30.1 This table doesn't have any numeric fields, but you can use it to g...

FIGURE 30.2 Changing the PivotTable to show counts and percentages

FIGURE 30.3 A PivotTable with two groups

FIGURE 30.4 PivotTables with options for subtotals and grand totals

FIGURE 30.5 You can use a PivotTable to summarize the sales data by month.

FIGURE 30.6 The PivotTable with Excel's automatic grouping

FIGURE 30.7 Use the Grouping dialog box to group PivotTable items by dates.

FIGURE 30.8 The PivotTable, after grouping by month and year

FIGURE 30.9 This PivotTable shows sales by quarter and by year.

FIGURE 30.10 This PivotTable is grouped by hours.

FIGURE 30.11 Creating a frequency distribution for these test scores is simple....

FIGURE 30.12 The PivotTable and PivotChart show the frequency distribution for ...

FIGURE 30.13 This data demonstrates calculated fields and calculated items.

FIGURE 30.14 This PivotTable was created from the sales data.

FIGURE 30.15 The Insert Calculated Field dialog box

FIGURE 30.16 This PivotTable uses a calculated field.

FIGURE 30.17 The Insert Calculated Item dialog box

FIGURE 30.18 This PivotTable uses calculated items for quarterly totals.

FIGURE 30.19 The PivotTable, after creating two groups and adding subtotals

FIGURE 30.20 Using slicers to filter the data displayed in a PivotTable

FIGURE 30.21 Using slicers to filter a PivotTable by state and by month

FIGURE 30.22 Using a timeline to filter a PivotTable by date

FIGURE 30.23 The formulas in column F reference cells in the PivotTable.

FIGURE 30.24 After expanding the PivotTable, formulas that use the

GETPIVOTDATA

FIGURE 30.25 This data will be used to create a PivotChart.

FIGURE 30.26 This PivotTable summarizes sales by region and by month.

FIGURE 30.27 The PivotChart uses the data displayed in the PivotTable.

FIGURE 30.28 If you modify the PivotTable, the PivotChart is also changed.

FIGURE 30.29 These three tables will be used for a PivotTable, using the data m...

FIGURE 30.30 The PivotTable Fields task pane for a data model

FIGURE 30.31 The Create Relationships dialog box

FIGURE 30.32 Relating the Orders table to the Customers table

FIGURE 30.33 Relating the Customers and Regions tables by state

FIGURE 30.34 A PivotTable based on the data model

FIGURE 30.35 Formatting applied to the data model PivotTable

Chapter 31

FIGURE 31.1 This simple worksheet model uses four input cells to produce the re...

FIGURE 31.2 How a one-input data table is set up

FIGURE 31.3 Preparing to create a one-input data table

FIGURE 31.4 The Data Table dialog box

FIGURE 31.5 The result of the one-input data table

FIGURE 31.6 The setup for a two-input data table

FIGURE 31.7 This worksheet calculates the net profit from a direct-mail promoti...

FIGURE 31.8 Preparing to create a two-input data table

FIGURE 31.9 The result of the two-input data table

FIGURE 31.10 A simple production model to demonstrate Scenario Manager

FIGURE 31.11 Use the Add Scenario dialog box to create a named scenario.

FIGURE 31.12 You enter the values for the scenario in the Scenario Values dialo...

FIGURE 31.13 Selecting a scenario to display

FIGURE 31.14 A Scenario Summary report produced by Scenario Manager

Chapter 32

FIGURE 32.1 A mortgage calculator with input cells and formula cells

FIGURE 32.2 The Goal Seek dialog box

FIGURE 32.3 Goal Seek has found a solution.

FIGURE 32.4 Use Solver to determine the number of units to maximize the total p...

FIGURE 32.5 The Solver Parameters dialog box

FIGURE 32.6 The Add Constraint dialog box

FIGURE 32.7 Solver displays this dialog box when it finds a solution to the pro...

FIGURE 32.8 One of three reports that Solver can produce

FIGURE 32.9 You can control many aspects of the way Solver solves a problem.

FIGURE 32.10 Solver will attempt to solve this series of linear equations.

FIGURE 32.11 Solver finds a solution to the linear equations.

FIGURE 32.12 This worksheet determines the least expensive way to ship products...

FIGURE 32.13 The solution that was created by Solver

FIGURE 32.14 Using Solver to maximize profit when resources are limited

FIGURE 32.15 Solver determined how to use the resources to maximize the total p...

FIGURE 32.16 This worksheet is set up to maximize a credit union's investments,...

FIGURE 32.17 The results of the portfolio optimization

Chapter 33

FIGURE 33.1 Select your tool from the Data Analysis dialog box.

FIGURE 33.2 Specifying parameters for a single-factor analysis of variance

FIGURE 33.3 The Correlation dialog box

FIGURE 33.4 Descriptive statistics output

FIGURE 33.5 Output from the F-test tool

FIGURE 33.6 Use the histogram tool to generate distributions and graphical outp...

FIGURE 33.7 A chart produced from data generated by the moving average tool

FIGURE 33.8 This dialog box enables you to generate a wide variety of random nu...

FIGURE 33.9 The Regression dialog box

FIGURE 33.10 Output from the paired T-test dialog box

Chapter 34

FIGURE 34.1 Use the Protect Sheet dialog box to protect a worksheet.

FIGURE 34.2 Use the Protection tab in the Format Cells dialog box to change the...

FIGURE 34.3 Excel warns you if you attempt to change a locked cell.

FIGURE 34.4 The Allow Users to Edit Ranges dialog box

FIGURE 34.5 Specify a workbook password in the Encrypt Document dialog box.

FIGURE 34.6 Opening this workbook requires a password.

FIGURE 34.7 The Protect Structure and Windows dialog box

FIGURE 34.8 Protecting a VBA Project with a password

FIGURE 34.9 The Document Inspector dialog box identifies hidden and personal in...

Chapter 35

FIGURE 35.1 The Power Pivot Ribbon interface

FIGURE 35.2 We want to use Power Pivot to analyze the data in the Customers, In...

FIGURE 35.3 Convert your data range into an Excel table.

FIGURE 35.4 Give your newly created Excel table a friendly name.

FIGURE 35.5 The Power Pivot window shows all the data that currently exists in ...

FIGURE 35.6 Each table you add to the data model will be placed on its own tab ...

FIGURE 35.7 The Diagram view allows you to see all the tables in your data mode...

FIGURE 35.8 To create a relationship, you simply click and drag a line between ...

FIGURE 35.9 When you create relationships, the Power Pivot diagram will show jo...

FIGURE 35.10 Use the Manage Relationships dialog box to edit or delete existing...

FIGURE 35.11 Use the Edit Relationship dialog box to adjust the tables and fiel...

FIGURE 35.12 You now have a Power Pivot–driven PivotTable that aggregates acros...

FIGURE 35.13 Activate the Table Import Wizard and select Microsoft SQL Server....

FIGURE 35.14 Choose to select from a list of tables and views.

FIGURE 35.15 The Table Import Wizard will display a list of tables and views.

FIGURE 35.16 The Preview & Filter screen allows you to exclude columns and ...

FIGURE 35.17 The last screen of the Table Import Wizard shows you the progress ...

FIGURE 35.18 Activate the Table Import Wizard and select Excel File.

FIGURE 35.19 Select the worksheets you want to import.

FIGURE 35.20 You can copy data straight out of Microsoft Word.

FIGURE 35.21 The Paste Preview dialog box gives you a chance to see what you're...

FIGURE 35.22 The Queries & Connections task pane

FIGURE 35.23 The Connection Properties dialog box lets you configure the chosen...

FIGURE 35.24 Use the Existing Connections dialog box to reconfigure your Power ...

Chapter 36

FIGURE 36.1 This table shows transactions by employee number.

FIGURE 36.2 This table provides information on employees: first name, last name...

FIGURE 36.3 The Existing Connection dialog box lists all available Table object...

FIGURE 36.4 Use the Import Data dialog box to add the Table object to the inter...

FIGURE 36.5 You can explicitly choose the internal data model as the source for...

FIGURE 36.6 Creating a new PivotTable on a new worksheet

FIGURE 36.7 Select All in the PivotTable Fields list to see both tables in your...

FIGURE 36.8 When Excel prompts you, choose to create the relationship between t...

FIGURE 36.9 Build the appropriate relationship using the Table and Column drop-...

FIGURE 36.10 You have achieved your goal of showing sales by job title.

FIGURE 36.11 The Manage Relationships dialog box enables you to make changes to...

FIGURE 36.12 Use the Queries & Connections task pane to remove any table fr...

Chapter 37

FIGURE 37.1 Start your calculated column by entering your desired operation in ...

FIGURE 37.2 Calculated columns automatically show up in your PivotTable Fields ...

FIGURE 37.3 You can use the formatting tools found on the Power Pivot window's ...

FIGURE 37.4 The new Gross Margin calculation uses the previously created [Total...

FIGURE 37.5 Right-click and select Hide from Client Tools.

FIGURE 37.6 Hidden columns are grayed out, and calculated columns have a darker...

FIGURE 37.7 The Insert Function dialog box shows you all available DAX function...

FIGURE 37.8 The DAX

SUM

function can only sum the column as a whole.

FIGURE 37.9 DAX functions can help enhance the InvoiceHeader data with Year and...

FIGURE 37.10 Using DAX functions to supplement a table with Year, Month, and Mo...

FIGURE 37.11 DAX calculations are immediately available in any connected PivotT...

FIGURE 37.12 Month names in Power Pivot–driven PivotTables don't automatically ...

FIGURE 37.13 The Sort by Column dialog box lets you define how your columns are...

FIGURE 37.14 Your month names now show in correct month order.

FIGURE 37.15 The Discount Amount value in the Customers table can be used in a ...

FIGURE 37.16 Use the

RELATED

function to look up a field from another table.

FIGURE 37.17 The final Discounted Revenue calculated column using the Discount%...

FIGURE 37.18 Creating a new calculated measure

FIGURE 37.19 Calculated measures can be seen in the PivotTable Fields list.

FIGURE 37.20 The Manage Measures dialog box lets you edit or delete your calcul...

FIGURE 37.21 Select the Convert to Formulas option to convert your PivotTable t...

FIGURE 37.22 These cells are now a series of Cube functions!

FIGURE 37.23 Excel gives you the option of converting your report filter fields...

Chapter 38

FIGURE 38.1 Starting a Power Query web query

FIGURE 38.2 Enter the target URL containing the data you need.

FIGURE 38.3 Select the correct data source and then click the Edit button.

FIGURE 38.4 The Power Query Editor window allows you to shape, clean, and trans...

FIGURE 38.5 Right-click the Date column and choose to change the data type to a...

FIGURE 38.6 Select the columns you want to keep and then select Remove Other Co...

FIGURE 38.7 You can click the Table Actions icon to select actions (such as Rem...

FIGURE 38.8 The Power Query Editor can be used to apply transformation actions ...

FIGURE 38.9 The Load To dialog box gives you more control over how the results ...

FIGURE 38.10 Your final query pulled from the Internet: transformed, put into a...

FIGURE 38.11 Query steps can be viewed and managed in the Applied Steps section...

FIGURE 38.12 Right-click any query step to edit, rename, delete, or move the st...

FIGURE 38.13 Right-click any query in the Queries & Connections pane to see...

FIGURE 38.14 Right-click any column to see the column-level actions that you ca...

FIGURE 38.15 Click the Table Actions icon in the upper-left corner of the Power...

FIGURE 38.16 Power Query has the ability to connect to a wide array of text, da...

FIGURE 38.17 Select the data sources with which you want to work and then click...

FIGURE 38.18 CSV files are brought into the Power Query Editor where you can ap...

FIGURE 38.19 Other systems Power Query can utilize as data sources

FIGURE 38.20 Edit a data source by selecting it and clicking the Edit button.

FIGURE 38.21 The credentials edit screen for your selected data source

Chapter 39

FIGURE 39.1 Are there duplicate records in this table? It depends on how you de...

FIGURE 39.2 Removing duplicate records

FIGURE 39.3 Undo the removal of records by deleting the Removed Duplicates step...

FIGURE 39.4 Replacing null

FIGURE 39.5 Replacing empty strings with the word

Undefined

FIGURE 39.6 Merging the Type and Code fields

FIGURE 39.7 The Merge Columns dialog box

FIGURE 39.8 The original columns are removed and replaced with a new merged col...

FIGURE 39.9 Reformatting the ContactName field to proper case

FIGURE 39.10 Replacing text values

FIGURE 39.11 Leading spaces can cause issues in analysis.

FIGURE 39.12 The Trim command

FIGURE 39.13 The Extract command allows you to pull out parts of the text found...

FIGURE 39.14 Extracting the first three characters of the Phone field

FIGURE 39.15 Extracting the two middle characters of the SicCode

FIGURE 39.16 The Split Column command can easily split the ContactName field in...

FIGURE 39.17 Splitting the ContactName column at every occurrence of a comma

FIGURE 39.18 The ContactName field has successfully been split into three colum...

FIGURE 39.19 Matrix layouts are problematic for data analysis.

FIGURE 39.20 All months are now in a tabular format.

FIGURE 39.21 Use Unpivot Other Columns when the number of matrix columns will b...

FIGURE 39.22 Pivoting the Month and Revenue columns

FIGURE 39.23 Confirm the aggregation operation to finalize the pivot transforma...

FIGURE 39.24 Adding a custom column

FIGURE 39.25 The Custom Column dialog box

FIGURE 39.26 A formula to merge the Type and Code columns

FIGURE 39.27 Use the Data Type drop-down to discover and select the data type f...

FIGURE 39.28 A formula to create a consistent CompanyNumber padded with 10 digi...

FIGURE 39.29 Applying an

if

statement in a custom column

FIGURE 39.30 The Group By dialog configured to sum potential revenue by State a...

FIGURE 39.31 The resulting aggregate view by State and City

Chapter 40

FIGURE 40.1 This data can be used as the source for various levels of aggregate...

FIGURE 40.2 Merge the Last_Name and First_Name columns to create a new Employee...

FIGURE 40.3 Group the Employee field and sum the Sales Amount column to create ...

FIGURE 40.4 All of the query steps before Grouped Rows are needed to prepare th...

FIGURE 40.5 Naming the new query SalesByBusiness

FIGURE 40.6 Your two queries are now sharing the extracted steps.

FIGURE 40.7 The data found on each region tab needs to be consolidated into one...

FIGURE 40.8 Create a connection-only query for each region.

FIGURE 40.9 Appending multiple queries to NorthData

FIGURE 40.10 The final consolidated table of all regional data

FIGURE 40.11 The kinds of joins supported by Power Query

FIGURE 40.12 You need to merge the Questions and Answers queries into one table...

FIGURE 40.13 Activating the Merge dialog box

FIGURE 40.14 The completed Merge dialog box

FIGURE 40.15 Expand the NewColumn field, and choose the merged fields you want ...

FIGURE 40.16 The final table with merged questions and answers

FIGURE 40.17 Click the gear icon next to the Source query step to reactivate th...

Chapter 41

FIGURE 41.1 Hover over a query to get quick information including sneak peeks o...

FIGURE 41.2 Queries can be organized into groups.

FIGURE 41.3 Group-level options

FIGURE 41.4 Use the Choose Columns command to find and select columns faster.

FIGURE 41.5 Right-click query steps to rename them.

FIGURE 41.6 Create a new query from an existing column.

FIGURE 41.7 Use the Global Data Load options to set a default load behavior.

FIGURE 41.8 Power Query automatically adds a step to change data types when dat...

FIGURE 41.9 Disabling the Type Detection feature

FIGURE 41.10 Disabling the privacy level settings

Chapter 42

FIGURE 42.1 The Developer tab

FIGURE 42.2 The Macro Settings section of the Trust Center dialog box

FIGURE 42.3 Excel displays a security warning if a workbook contains macros.

FIGURE 42.4 Excel warns you if your workbook contains macros and you attempt to...

FIGURE 42.5 A simple VBA procedure

FIGURE 42.6 This VBA function returns the cube root of its argument.

FIGURE 42.7 The Record Macro dialog box

FIGURE 42.8 The MyName procedure was generated by the Excel macro recorder.

FIGURE 42.9 The TimeStamp procedure was generated by the Excel macro recorder....

FIGURE 42.10 This TimeStamp macro works correctly.

FIGURE 42.11 Use the Macro Options dialog box to add or change a shortcut key f...

FIGURE 42.12 Adding a button to a worksheet so that it can be used to execute a...

FIGURE 42.13 The ListFormulas macro creates a list of all formulas in a workshe...

Chapter 43

FIGURE 43.1 A simple custom worksheet function

FIGURE 43.2 Creating a worksheet formula that uses a custom function

FIGURE 43.3 Entering a description for a custom function. This description appe...

FIGURE 43.4 Using the Function Arguments dialog box to insert a custom function...

Chapter 44

FIGURE 44.1 A UserForm that asks the user to select an option

FIGURE 44.2 This dialog box is displayed by the VBA

InputBox

function.

FIGURE 44.3 A simple message box, displayed with the VBA

MsgBox

function

FIGURE 44.4 The second argument of the

MsgBox

function determines what appears ...

FIGURE 44.5 A message box with a longer message and a title

FIGURE 44.6 An empty UserForm

FIGURE 44.7 The Properties window for a CommandButton control

FIGURE 44.8 A Label control, after changing its Font properties

FIGURE 44.9 The code module for the UserForm

FIGURE 44.10 The UserForm after adding controls and adjusting some properties

FIGURE 44.11 Displaying the UserForm

FIGURE 44.12 Adjusting the tab order in a UserForm

Chapter 45

FIGURE 45.1 This worksheet uses UserForm controls.

FIGURE 45.2 Excel's two sets of worksheet controls

FIGURE 45.3 Use the Properties window to adjust the properties of a control—in ...

FIGURE 45.4 Double-clicking a control in Design mode activates the VBE and ente...

FIGURE 45.5 A

ComboBox

control

FIGURE 45.6 This worksheet has three

ScrollBar

controls.

FIGURE 45.7 A

TextBox

control with a vertical scrollbar

Chapter 46

FIGURE 46.1 The best way to create an event procedure is to let the VBE do it f...

FIGURE 46.2 Selecting a cell causes the active cell's row and column to become ...

Chapter 47

FIGURE 47.1 This range can consist of any number of rows.

FIGURE 47.2 Using the VBA

InputBox

function to get a value from the user

FIGURE 47.3 You can instruct Excel not to display these types of alerts while a...

Chapter 48

FIGURE 48.1 The Add-Ins dialog box

FIGURE 48.2 This dialog box enables the user to change the case of text in the ...

FIGURE 48.3 The custom dialog box

FIGURE 48.4 Adding descriptive information about your add-in

FIGURE 48.5 The Protection tab of the Project Properties dialog box

Guide

Cover

Table of Contents

Begin Reading

Pages

i

ii

iii

v

vii

ix

xli

xlii

xliii

xliv

xlv

1

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549