Microsoft Excel 365 Bible E-Book

Michael Alexander

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

Seitenzahl: 1459

Veröffentlichungsjahr: 2025

Beschreibung

Your complete guide to Excel 365, written for newbies all the way to seasoned professionals

Neatly organised and written using accessible language, Microsoft Excel 365 Bible contains everything that readers need to know to get up and running quickly with Excel. Covering the numerous updates to Excel 365 since the previous edition was published in 2022, this Second Edition contains many useful examples and tips and tricks that cover all essential aspects of Excel—from the basics, such as navigating the user interface, to more advanced topics, such as creating visualisations and crafting custom functions.

Written by an accomplished team of authors with decades of Excel and business intelligence experience, this book explores topics including:

• Entering and editing worksheet data, with information on data types, formulas, dates, times and other cell contents
• Building formulas with Excel table objects, correcting common formula errors and using formula variables
• Working with data series, changing basic chart elements and making use of chart customization
• Loading data from other sources, performing common transformations and applying conditional logic

Microsoft Excel 365 Bible earns a well-deserved spot on the bookshelves of all professionals and individuals seeking to get the most out of Excel 365, from beginners with limited knowledge of the software all the way to advanced users with years of experience under their belts.

Leseprobe

Table of Contents

Cover

Table of Contents

Title Page

Introduction

Looking at What's New in Excel 365

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

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

Using Excel on a Tablet

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

Adding Notes 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

Introducing Formula Variables

Using Formulas in Tables

Correcting Common Formula Errors

Using Advanced Naming Techniques

Working with Formulas

CHAPTER 10: Understanding and Using Array Formulas

Understanding Legacy Array Formulas

Introducing Dynamic Arrays

Exploring Dynamic Array Functions

CHAPTER 11: Using Common Formulas for Math and Dates

Calculating Percentages

Rounding Numbers

Counting Values in a Range

Using Excel's Conversion Functions

Understanding How Excel Handles Dates and Times

Using Excel's Date and Time Functions

CHAPTER 12: Using Formulas to Manipulate Text

Working with Text

Using Text 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 with Conditional Formatting

Highlighting Cells That Meet Certain Criteria

Highlighting Values That Exist in One List but Not the Other

Highlighting Values That Exist in List1 and List2

Highlighting Based on Dates

CHAPTER 16: 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 17: Getting Started with Excel Charts

What Is a Chart?

Basic Steps for Creating a Chart

Modifying and Customizing Charts

Understanding Chart Types

Advanced Chart Types for Excel

CHAPTER 18: Using Advanced Charting Techniques

Selecting Chart Elements

Exploring the User Interface Choices for Modifying Chart Elements

Modifying Basic Chart Elements

Working with Data Series

Creating Chart Templates

CHAPTER 19: Creating Sparkline Graphics

Sparkline Types

Creating Sparklines

Customizing Sparklines

Specifying a Date Axis

Auto-Updating Sparklines

Displaying a Sparkline for a Dynamic Range

CHAPTER 20: 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

Leveraging the

IMAGE

Function

Part IV: Managing and Analyzing Data

CHAPTER 21: Importing and Cleaning Data

Importing Data

Cleaning Up Data

Exporting Data

CHAPTER 22: 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 23: Creating and Using Worksheet Outlines

Introducing Worksheet Outlines

Creating an Outline

Working with Outlines

CHAPTER 24: 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 25: Introducing Pivot Tables

About Pivot Tables

Creating a Pivot Table Automatically

Creating a Pivot Table Manually

Seeing More Pivot Table Examples

Learning More

CHAPTER 26: Analyzing Data with Pivot Tables

Working with Non-Numeric Data

Grouping Pivot Table Items

Using a Pivot Table to Create a Frequency Distribution

Creating a Calculated Field or Calculated Item

Filtering Pivot Tables with Slicers

Filtering Pivot Tables with a Timeline

Referencing Cells Within a Pivot Table

Creating PivotCharts

Using the Data Model

CHAPTER 27: Performing Spreadsheet What-If Analysis

Looking at a What-If Example

Exploring Types of What-If Analyses

Analyzing Data with Artificial Intelligence

CHAPTER 28: 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 29: 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 30: 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 31: Introducing Power Pivot

Understanding the Power Pivot Internal Data Model

Loading Data from Other Data Sources

CHAPTER 32: Working Directly with the Internal Data Model

Directly Feeding the Internal Data Model

Managing Relationships in the Internal Data Model

Managing Queries & Connections

CHAPTER 33: Adding Formulas to Power Pivot

Enhancing Power Pivot Data with Calculated Columns

Utilizing DAX to Create Calculated Columns

Using Cube Functions to Free Your Data

Understanding Calculated Measures

CHAPTER 34: Introducing Power Query

Understanding Power Query Basics

Getting Data from External Sources

Getting Data from Other Data Systems

Managing Data Source Settings

Data Profiling with Power Query

CHAPTER 35: Transforming Data with Power Query

Performing Common Transformation Tasks

Creating Custom Columns

Grouping and Aggregating Data

Working with Custom Data Types

CHAPTER 36: Making Queries Work Together

Reusing Query Steps

Understanding the Append Feature

Understanding the Merge Feature

CHAPTER 37: Enhancing Power Query Productivity

Implementing Some Power Query Productivity Tips

Avoiding Power Query Performance Issues

Part VI: Automating Excel

CHAPTER 38: Introducing Visual Basic for Applications

Introducing VBA Macros

Displaying the Developer Tab

Learning About Macro Security

Saving Workbooks That Contain Macros

Looking at Two Types of VBA Macros

Creating VBA Macros

Learning More

CHAPTER 39: 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 40: 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 41: Using UserForm Controls in a Worksheet

Understanding Why to Use Controls on a Worksheet

Using Controls

Reviewing the Available ActiveX Controls

CHAPTER 42: Working with Excel Events

Understanding Events

Entering Event-Handler VBA Code

Using Workbook-Level Events

Working with Worksheet Events

Using Special Application Events

CHAPTER 43: Creating Custom Excel Add-Ins

Understanding Add-Ins

Working with Add-Ins

Understanding When to Create Add-Ins

Creating Add-Ins

Looking at an Add-In Example

CHAPTER 44: Office Scripts for Excel

Introducing Office Scripts for Excel

Exploring the Automate Tab

Differences Between Office Scripts and VBA

Exploring the Office Scripts Environment

Creating Office Scripts

Index

Copyright

Dedication

About the Authors

About the Technical Editors

Acknowledgments

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

TABLE 2.2 Number format categories

Chapter 3

TABLE 3.1 Characters you can't use in sheet names

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 Example formulas and their results

TABLE 9.2 Operators used in formulas

TABLE 9.3 Example formulas using operators and their results

TABLE 9.4 Operator precedence in Excel formulas

TABLE 9.5 Excel error values

TABLE 9.6 Reference operators for ranges

Chapter 11

TABLE 11.1 Times of day and their corresponding serial numbers

TABLE 11.2 Time entry formats recognized by Excel

TABLE 11.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 17

TABLE 17.1 Limitations of Excel charts

Chapter 20

TABLE 20.1 Number-formatting buttons on the Ribbon

TABLE 20.2 Number-formatting keyboard shortcuts

TABLE 20.3 Common date and time format codes

Chapter 27

TABLE 27.1 Three scenarios for the production model

Chapter 28

TABLE 28.1 Constraints summary

Chapter 33

TABLE 33.1 DAX operators

Chapter 34

TABLE 34.1 Column-level actions

TABLE 34.2 Table-level actions

Chapter 35

TABLE 35.1 Common conversion functions

TABLE 35.2 Useful transformation functions

Chapter 40

TABLE 40.1 Constants used in the MsgBox function

TABLE 40.2 Toolbox controls

Chapter 41

TABLE 41.1 ActiveX controls

TABLE 41.2 Properties shared by multiple controls

Chapter 42

TABLE 42.1 Workbook events

TABLE 42.2 Worksheet events

Chapter 44

TABLE 44.1 The members of the ClearApplyTo enum

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, cel...

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 work...

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 li...

FIGURE 1.10 Add new icons to your Quick Access Toolbar by using the Quick Ac...

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

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

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

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

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 wor...

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

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

FIGURE 2.4 You can use the Advanced tab in the Excel Options dialog box to s...

FIGURE 2.5 This series was created by using AutoFill.

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

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 v...

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

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

FIGURE 2.11 A blank workbook on a tablet

FIGURE 2.12 Function arguments are displayed as buttons.

FIGURE 2.13 Convert a drawn shape into a native shape.

FIGURE 2.14 Handwrite a formula with the Action Pen.

FIGURE 2.15 Excel converts a handwritten formula into an actual formula in t...

Chapter 3

FIGURE 3.1 Use the Arrange Windows dialog box to arrange all open non-minimi...

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

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...

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

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

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

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

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

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

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

Chapter 4

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

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 cel...

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

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 context menu provide more control over how...

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

FIGURE 4.10 Use Paste All to convert multiple columns to one.

FIGURE 4.11 Excel offers several pasting options, with preview. Here, the in...

FIGURE 4.12 The Paste Special dialog box

FIGURE 4.13 Transposing a range changes the orientation as the information i...

FIGURE 4.14 Create names for cells or ranges by using the New Name dialog bo...

FIGURE 4.15 Use the Create Names From Selection dialog box to name cells usi...

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

FIGURE 4.17 Creating a comment attaches a new, blank comment to a cell.

FIGURE 4.18 Typing a comment and clicking the Post button finishes the comme...

FIGURE 4.19 Showing all comments in the Comments task pane

FIGURE 4.20 The first comment in a thread has an ellipsis menu to delete the...

FIGURE 4.21 A resolved comment displays Resolved, and buttons to reopen or d...

FIGURE 4.22 You can add a note to a cell to help document your worksheets.

FIGURE 4.23 This note contains an image.

FIGURE 4.24 The areas that make up a table

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

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

FIGURE 4.27 A table after performing a three-column sort

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

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

FIGURE 4.30 Specifying a more complex numeric filter

FIGURE 4.31 Use the Insert Slicers dialog box to specify which slicers to cr...

FIGURE 4.32 The table is filtered by two slicers.

FIGURE 4.33 Excel offers many different table styles.

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

Chapter 5

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

FIGURE 5.2 The Mini toolbar appears above or below the right-click context m...

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

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

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

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 c...

FIGURE 5.8 Rotate text for additional visual impact.

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

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

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

FIGURE 5.12 Two examples of color scale conditional formatting

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

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 mo...

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 ro...

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

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 colo...

Chapter 6

FIGURE 6.1 Choosing Blank Workbook from Excel’s Start screen

FIGURE 6.2 Saving a previously unsaved workbook

FIGURE 6.3 You can recover older versions of your workbook.

FIGURE 6.4 The Encrypt Document dialog box is where you specify a password f...

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

FIGURE 6.6 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 allows you to drag page breaks and print area ...

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...

FIGURE 7.6 A header image can be used as a watermark.

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

FIGURE 7.8 Use the Properties section in the object's Format Task pane to pr...

FIGURE 7.9 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 ...

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

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

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...

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

FIGURE 9.3 When starting a formula, Excel will display the list of possible ...

FIGURE 9.4 You can insert a function by selecting it from one of the functio...

FIGURE 9.5 The Insert Function dialog box

FIGURE 9.6 The Function Arguments dialog box

FIGURE 9.7 Copying a formula that contains relative references

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

FIGURE 9.9 Using mixed cell references

FIGURE 9.10 Using the

LET

function to simplify a nested

IF

statement

FIGURE 9.11 Using the

LET

function to simplify a

SWITCH

statement

FIGURE 9.12 A simple table with three columns of information

FIGURE 9.13 A drop-down list enables you to select a summary formula for a t...

FIGURE 9.14 The Difference column contains a formula.

FIGURE 9.15 The formula AutoComplete feature is useful when creating a formu...

FIGURE 9.16 Excel allows you to name a formula that doesn't exist in a works...

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

FIGURE 9.18 Creating names for all values in a table

Chapter 10

FIGURE 10.1 Column D contains formulas to calculate the total for each produ...

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

FIGURE 10.3 A simple formula that references a range

FIGURE 10.4 Excel automatically spills the results into the surrounding cell...

FIGURE 10.5 Dynamic arrays work with any traditional Excel function that acc...

FIGURE 10.6 Spill ranges will visually show a blue line around them.

FIGURE 10.7 A spill error caused by an obstruction in the spill range

FIGURE 10.8 Referencing a single cell in a spill range won't allow us to cap...

FIGURE 10.9 Using the spill range operator to apply a function to the entire...

FIGURE 10.10 Using the spill range operator to count all values in the refer...

FIGURE 10.11 Using the

SORT

function to sort students in descending order by...

FIGURE 10.12 Sorting by columns

FIGURE 10.13 Using the

SORTBY

function to apply a multicolumn sort

FIGURE 10.14 Adding the

[exactly_once]

argument extracts values that appear ...

FIGURE 10.15 Example usage of the

SEQUENCE

function

FIGURE 10.16 Getting a list of the three largest numbers in A19:A28

FIGURE 10.17 Getting a list of dates representing each month in 2025

FIGURE 10.18 Filtering records where the Change value is greater than 10

FIGURE 10.19 Getting the

FILTER

criteria from cell F1

FIGURE 10.20 Combining

SORT

with

FILTER

to sort results

FIGURE 10.21 Using multiple filter conditions

FIGURE 10.22 Criteria evaluation behind the scenes

FIGURE 10.23 Using the

+

operator to return results if the first condition i...

FIGURE 10.24 Filtering records where values are contained in a search term

FIGURE 10.25 A basic

XLOOKUP

function to find Customer Type based on revenue...

FIGURE 10.26 Enter a comma to see choices for the next argument.

FIGURE 10.27

XLOOKUP

results using approximate matching

FIGURE 10.28 Using wildcard characters to perform complex searches

Chapter 11

FIGURE 11.1 Calculating the percent of goal

FIGURE 11.2 Calculating the percent of goal using a common goal

FIGURE 11.3 Calculating the percent variance between current year sales and ...

FIGURE 11.4 Using the ABS function will give you an accurate percent varianc...

FIGURE 11.5 Calculating a percent distribution of revenue across regions

FIGURE 11.6 Calculating percent distribution with the

SUM

function

FIGURE 11.7 Calculating a running total

FIGURE 11.8 Applying a percent increase and decrease using a simple formula...

FIGURE 11.9 Using the

IF

function to avoid a division-by-zero error

FIGURE 11.10 Rounding to the nearest penny

FIGURE 11.11 Rounding numbers to one significant digit

FIGURE 11.12 A demonstration of counting cells

FIGURE 11.13 Creating a unit-of-measure conversion table

FIGURE 11.14 Calculating the number of days between today and the invoice da...

FIGURE 11.15 Calculating the number of workdays between two dates

FIGURE 11.16

NETWORKDAYS.INTL

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

FIGURE 11.17 Start with a sheet containing the last date of the previous yea...

FIGURE 11.18 Creating a list of business days

FIGURE 11.19 Extract the parts of a date.

FIGURE 11.20 Showing the years and months between dates

FIGURE 11.21 Converting standard dates into Julian dates

FIGURE 11.22 Calculating the percent of the year completed

FIGURE 11.23 Calculating the last day of each date

FIGURE 11.24 Calculating calendar quarters

FIGURE 11.25 Calculating fiscal quarters

FIGURE 11.26 Calculating fiscal months

FIGURE 11.27 Extract the parts of a time.

FIGURE 11.28 Calculating elapsed time

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

FIGURE 11.30 Converting decimal hours to hours and minutes

FIGURE 11.31 Adding a set number of hours and minutes to an existing time va...

Chapter 12

FIGURE 12.1 Joining first and last names

FIGURE 12.2 Using the

TEXTJOIN

function

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

FIGURE 12.4 Removing excess spaces from text

FIGURE 12.5 Using the

LEFT

,

RIGHT

, and

MID

functions

FIGURE 12.6 Using the

FIND

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

FIGURE 12.7 Nesting the

FIND

function to extract everything after the second...

FIGURE 12.8 Fixing the apostrophe S issue with the

SUBSTITUTE

function

FIGURE 12.9 Calculating the number of words in a cell

FIGURE 12.10 The x-axis labels in this chart include a line break and a refe...

FIGURE 12.11 Using the

CHAR()

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

FIGURE 12.12 Cleaning data with the

CLEAN

function

FIGURE 12.13 Padding Customer ID fields to 10 characters

FIGURE 12.14 Numbers joined with text do not inherently adopt number formatt...

FIGURE 12.15 Using the

TEXT

function lets you format numbers joined with tex...

FIGURE 12.16 The

TEXTSPLIT

function separates text by a given delimiter.

FIGURE 12.17 Use the

row_delimiter

argument to separate text into rows by a ...

FIGURE 12.18 Use the

col_delimiter

and

row_delimiter

arguments to separate t...

FIGURE 12.19 Using multiple delimiters in a

TEXTSPLIT

function

FIGURE 12.20 Using the

VSTACK

function to consolidate text into a single lis...

FIGURE 12.21

VSTACK

will return the

#N/A

error for any column that doesn't e...

FIGURE 12.22 Using the

HSTACK

function

Chapter 13

FIGURE 13.1 Monthly gas prices by state

FIGURE 13.2 A model for selecting an automobile

FIGURE 13.3 A different auto-selector model

FIGURE 13.4 An inventory listing

FIGURE 13.5 A modified inventory listing

FIGURE 13.6 A revised discount scheme

FIGURE 13.7 Summing values less than zero

FIGURE 13.8 List of regions and sales values

FIGURE 13.9

SUMIF

using the

TODAY

function

FIGURE 13.10 A partial listing of countries and their gross domestic product...

FIGURE 13.11 Summing values that are between two dates

FIGURE 13.12 A partial listing of countries and their gross domestic product...

FIGURE 13.13 1972 Alpine Skiing Olympic medalists

FIGURE 13.14 Averaging results based on a country

FIGURE 13.15 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 in Figure 14.7, except that the da...

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 The cells in this table are conditionally formatted to show a re...

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

FIGURE 15.3 The cells in this table are conditionally formatted to show a re...

FIGURE 15.4 Compare our target cell (E3) with the value in the comparison ce...

FIGURE 15.5 You can conditionally format the values that exist in one list b...

FIGURE 15.6 Apply the conditional format if there are zero instances of the ...

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

FIGURE 15.8 Apply the conditional format if there is at least one instance (...

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

FIGURE 15.10 Using the

WEEKDAY

function to evaluate the weekday number of th...

FIGURE 15.11 You can conditionally format dates that fall between a start an...

FIGURE 15.12 Using the

AND

function to compare the date in our target cell (...

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

FIGURE 15.14 Evaluating whether today's date is greater than 90 days past th...

Chapter 16

FIGURE 16.1 Excel can check your formulas for potential errors.

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

FIGURE 16.3 Use the Find And Replace dialog box to locate information in a w...

FIGURE 16.4 Use the AutoCorrect dialog box to control the spelling correctio...

Chapter 17

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

FIGURE 17.2 This line chart displays two data series.

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

FIGURE 17.4 A chart on a chart sheet

FIGURE 17.5 Parts of a chart

FIGURE 17.6 A pie chart

FIGURE 17.7 A 3D column chart

FIGURE 17.8 This data would make a good chart.

FIGURE 17.9 The icons in the Charts group expand to show a gallery of chart ...

FIGURE 17.10 A column chart with two data series

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

FIGURE 17.12 The Change Chart Type dialog box

FIGURE 17.13 One-click design variations of a column chart

FIGURE 17.14 Using the Format Data Series task pane

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

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

FIGURE 17.17 This clustered column chart compares monthly sales for two prod...

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

FIGURE 17.19 This 100% stacked column chart displays monthly sales as a perc...

FIGURE 17.20 A 3D column chart

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

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

FIGURE 17.23 This line chart displays three series.

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

FIGURE 17.25 A pie chart with one slice exploded

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

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

FIGURE 17.28 A stacked area chart

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

FIGURE 17.30 Plotting ski sales using a radar chart with 12 categories and t...

FIGURE 17.31 A surface chart

FIGURE 17.32 A bubble chart

FIGURE 17.33 The four stock chart subtypes

FIGURE 17.34 Plotting temperature data with a stock chart

FIGURE 17.35 Displaying a student grade distribution using a histogram chart...

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

FIGURE 17.37 A waterfall chart showing positive and negative net cash flows...

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

FIGURE 17.39 A sunburst chart that depicts a music collection by genre and s...

FIGURE 17.40 A treemap chart that depicts a music collection by genre and su...

FIGURE 17.41 A funnel chart visualizing the value in each stage of a sales p...

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

FIGURE 17.43 Map charts come with unique customization options.

Chapter 18

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

FIGURE 18.2 Using the Chart Elements control in the Mini toolbar

FIGURE 18.3 Use the Format task pane to set the properties of a selected cha...

FIGURE 18.4 Chart customization buttons

FIGURE 18.5 The Plot Area has been formatted to stand out from the rest of t...

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

FIGURE 18.7 Use the Select Data Source dialog box to change the name of a da...

FIGURE 18.8 Using shapes as callouts in lieu of a legend

FIGURE 18.9 The Format Axis task pane for a value axis

FIGURE 18.10 These two charts show the same data but use different value axi...

FIGURE 18.11 The right chart uses the Values in Reverse Order option.

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

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

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

FIGURE 18.15 Some of the options available for a category axis

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

FIGURE 18.17 Overriding the Excel time-based category axis

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

FIGURE 18.19 This chart uses three columns of text for the category axis lab...

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

FIGURE 18.21 The Edit Series dialog box

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

FIGURE 18.23 Data labels linked to text in an arbitrary range

FIGURE 18.24 Three options for dealing with missing data

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

FIGURE 18.26 Applying a trend line

FIGURE 18.27 Use the Format Trendline task pane to customize your trend line...

FIGURE 18.28 The Precipitation series is barely visible.

FIGURE 18.29 Using the Change Chart Type dialog box to convert a chart into ...

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

Chapter 19

FIGURE 19.1 Three groups of Sparklines

FIGURE 19.2 Data to be summarized with Sparklines

FIGURE 19.3 Use the Create Sparklines dialog box to specify the data range a...

FIGURE 19.4 Column Sparklines summarize the precipitation data for nine citi...

FIGURE 19.5 A Sparkline at various sizes

FIGURE 19.6 The Hidden And Empty Cell Settings dialog box

FIGURE 19.7 Highlighting options for Line Sparklines

FIGURE 19.8 The bottom group of Sparklines shows the effect of using the sam...

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

FIGURE 19.10 Using Win/Loss Sparklines to display goal achievement

FIGURE 19.11 The axis in the Sparklines represents the goal.

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

FIGURE 19.13 After we specify a date axis, the Sparkline shows the values ac...

FIGURE 19.14 Creating a Sparkline from data in a table

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

Chapter 20

FIGURE 20.1 The Type input box allows you to customize the syntax for the nu...

FIGURE 20.2 Formatting numbers applies only a cosmetic look. Look in the For...

FIGURE 20.3 Custom number formatting that shows zeroes as

n/a

FIGURE 20.4 Dates and times can also be formatted using the Format Cells dia...

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

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

FIGURE 20.7 Use the Symbol dialog box to insert the desired symbols into you...

FIGURE 20.8 Copy the newly inserted symbols to the Clipboard.

FIGURE 20.9 Create a custom number format using the symbols.

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

FIGURE 20.11 The Shapes gallery

FIGURE 20.12 The Microsoft Office Icons Library

FIGURE 20.13 A 3D model in action

FIGURE 20.14 Peekaboo tab

FIGURE 20.15 Deconstructed view of the peekaboo tab

FIGURE 20.16 A visual banner made with shapes

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

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

FIGURE 20.19 Using a newly constructed shape to create custom infographic el...

FIGURE 20.20 Text Box shapes can be linked to cells.

FIGURE 20.21 Pasting a linked picture

FIGURE 20.22 Using linked pictures to enhance visualizations

FIGURE 20.23 Inserting a SmartArt graphic

FIGURE 20.24 Entering text for an organizational chart

FIGURE 20.25 Use the symbols on the Equation tab to write your equation.

FIGURE 20.26 You can save time and simply draw your equation.

FIGURE 20.27 Using the

IMAGE

function to render an image

FIGURE 20.28 Using a QR code generator service, you can embed your own QR co...

Chapter 21

FIGURE 21.1 Filtering by file extension in the Open dialog box

FIGURE 21.2 Data from a picture is imported into a worksheet.

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

FIGURE 21.4 Enabling the legacy import wizard

FIGURE 21.5 This CSV file will be imported.

FIGURE 21.6 Step 1 of the Text Import Wizard

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

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

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

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

FIGURE 21.11 Using formulas to identify duplicate rows

FIGURE 21.12 The imported data was put in one column rather than multiple co...

FIGURE 21.13 The first screen in the Convert Text To Columns Wizard

FIGURE 21.14 The goal is to extract the numbers from column A.

FIGURE 21.15 Using manually entered examples in B1 and B2, Excel's Flash Fil...

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

FIGURE 21.17 Using Flash Fill to combine names

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

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

FIGURE 21.20 The

TEXTJOIN

function inserts delimiters between cell values.

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

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

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

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

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

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

FIGURE 21.27 To replace only the second hyphen in these cells, Find And Repl...

FIGURE 21.28 The Trailing Minus For Negative Numbers option makes it easy to...

Chapter 22

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

FIGURE 22.2 The three tabs of the Data Validation dialog box

FIGURE 22.3 Excel can draw circles around invalid entries (in this case, cel...

FIGURE 22.4 This drop-down list was created using data validation.

FIGURE 22.5 Entering a data validation formula

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

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

FIGURE 22.8 The items displayed in the list in cell F2 depend on the list it...

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

FIGURE 22.10 Data Validation dialog box allowing a list based on a range

Chapter 23

FIGURE 23.1 A simple sales summary with subtotals

FIGURE 23.2 The worksheet after creating an outline

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

FIGURE 23.4 The worksheet after adding a column outline

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

FIGURE 23.6 An outline of this book, created manually

FIGURE 23.7 Use the Settings dialog box to adjust the position of the outlin...

Chapter 24

FIGURE 24.1 This message confirms that you want to save a workbook that cont...

FIGURE 24.2 This message explains that you can't AutoSave a workbook that ha...

FIGURE 24.3 Excel displays this message when you open a workbook that contai...

FIGURE 24.4 The Workbook Links task pane

FIGURE 24.5 Use the Refresh Settings icon in the Workbook Links task pane to...

FIGURE 24.6 The Workbook Links task pane lets you know the source workbook i...

FIGURE 24.7 Clicking a source workbook expands it to show a Refresh button....

FIGURE 24.8 The three-dot menu for each source has an option to change which...

FIGURE 24.9 Source workbooks with yellow triangles are not updating as expec...

FIGURE 24.10 Choosing Add in the Paste Special dialog box

FIGURE 24.11 The Consolidate dialog box enables you to specify ranges to con...

FIGURE 24.12 Three workbooks to be consolidated

FIGURE 24.13 The result of consolidating the information in three workbooks...

FIGURE 24.14 Expanding the outline to show more details

Chapter 25

FIGURE 25.1 This table will be used to create a Pivot Table.

FIGURE 25.2 A simple Pivot Table

FIGURE 25.3 A Pivot Table that uses a report filter

FIGURE 25.4 This range is not appropriate for a Pivot Table.

FIGURE 25.5 This range contains normalized data and is appropriate for a Piv...

FIGURE 25.6 A Pivot Table created from normalized data

FIGURE 25.7 Selecting a Recommended Pivot Table

FIGURE 25.8 In the Pivot Table From Table Or Range dialog box, you specify w...

FIGURE 25.9 Use the Pivot Table Fields task pane to build the Pivot Table.

FIGURE 25.10 After a few simple steps, the Pivot Table shows a summary of th...

FIGURE 25.11 Common Pivot Table terminology is shown using an example.

FIGURE 25.12 The Edit Default Layout dialog box

FIGURE 25.13 The Value Field Settings dialog box

FIGURE 25.14 Two fields are used for row labels.

FIGURE 25.15 The Pivot Table is filtered by date.

FIGURE 25.16 This Pivot Table shows daily totals for each branch.

FIGURE 25.17 This Pivot Table shows new account totals by day of the week.

FIGURE 25.18 This Pivot Table uses the Count function to summarize the data....

FIGURE 25.19 This Pivot Table counts the number of accounts that fall into e...

FIGURE 25.20 This Pivot Table uses a filter to show only the teller data.

FIGURE 25.21 This Pivot Table uses three filters.

Chapter 26

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

FIGURE 26.2 Changing the Pivot Table to show counts and percentages

FIGURE 26.3 A Pivot Table with two groups

FIGURE 26.4 Pivot Tables with options for subtotals, grand totals, and style...

FIGURE 26.5 You can use a Pivot Table to summarize the sales data by month....

FIGURE 26.6 The Pivot Table with Excel's automatic grouping

FIGURE 26.7 Use the Grouping dialog box to group Pivot Table items by dates....

FIGURE 26.8 The Pivot Table, after grouping by month and year

FIGURE 26.9 This Pivot Table shows sales by quarter and by year.

FIGURE 26.10 This Pivot Table is grouped by hours.

FIGURE 26.11 Creating a frequency distribution for these test scores is simp...

FIGURE 26.12 The Pivot Table and PivotChart show the frequency distribution ...

FIGURE 26.13 This data demonstrates calculated fields and calculated items....

FIGURE 26.14 This Pivot Table was created from the sales data.

FIGURE 26.15 The Insert Calculated Field dialog box

FIGURE 26.16 This Pivot Table uses a calculated field.

FIGURE 26.17 The Insert Calculated Item dialog box

FIGURE 26.18 This Pivot Table uses calculated items for quarterly totals.

FIGURE 26.19 The Pivot Table, after creating two groups and adding subtotals...

FIGURE 26.20 Turning a summary table into raw data

FIGURE 26.21 Using slicers to filter the data displayed in a Pivot Table

FIGURE 26.22 Using slicers to filter a Pivot Table and PivotChart by state a...

FIGURE 26.23 Using a timeline to filter a Pivot Table by date

FIGURE 26.24 The formulas in column F reference cells in the Pivot Table.

FIGURE 26.25 After expanding the Year field, formulas that use the

GETPIVOTD

...

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

FIGURE 26.27 This Pivot Table summarizes sales by region and by month.

FIGURE 26.28 The PivotChart uses the data displayed in the Pivot Table.

FIGURE 26.29 If you modify the Pivot Table, the PivotChart is also changed....

FIGURE 26.30 These three tables will be used for a Pivot Table, using the da...

FIGURE 26.31 The Pivot Table Fields task pane for a data model

FIGURE 26.32 The Create Relationship dialog box

FIGURE 26.33 Relating the Orders table to the Customers table

FIGURE 26.34 Relating the Customers and Regions tables by state

FIGURE 26.35 A Pivot Table based on the data model

FIGURE 26.36 Formatting applied to the data model Pivot Table

Chapter 27

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

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

FIGURE 27.3 Preparing to create a one-input data table

FIGURE 27.4 The Data Table dialog box

FIGURE 27.5 The result of the one-input data table

FIGURE 27.6 The setup for a two-input data table

FIGURE 27.7 This worksheet calculates the net profit from a direct-mail prom...

FIGURE 27.8 Preparing to create a two-input data table

FIGURE 27.9 The result of the two-input data table

FIGURE 27.10 A simple production model to demonstrate Scenario Manager

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

FIGURE 27.12 You enter the values for the scenario in the Scenario Values di...

FIGURE 27.13 Selecting a scenario to display

FIGURE 27.14 A Scenario Summary report produced by Scenario Manager

FIGURE 27.15 Bank accounts opened in a month

FIGURE 27.16 The Analyze Data task pane

FIGURE 27.17 Uncheck field names to narrow Excel's suggestions.

FIGURE 27.18 A suggested Pivot Table and PivotChart are inserted into a new ...

FIGURE 27.19 The Total Amount by Branch suggestion

FIGURE 27.20 Using Analyze Data to query the data

Chapter 28

FIGURE 28.1 A mortgage calculator with input cells and formula cells

FIGURE 28.2 The Goal Seek dialog box

FIGURE 28.3 Goal Seek has found a solution.

FIGURE 28.4 Use Solver to determine the number of units to maximize the tota...

FIGURE 28.5 The Solver Parameters dialog box

FIGURE 28.6 The Add Constraint dialog box

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

FIGURE 28.8 One of three reports that Solver can produce

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

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

FIGURE 28.11 Solver finds a solution to the linear equations.

FIGURE 28.12 This worksheet determines the least expensive way to ship produ...

FIGURE 28.13 The solution that was created by Solver

FIGURE 28.14 Using Solver to maximize profit when resources are limited

FIGURE 28.15 Solver determined how to use the resources to maximize the tota...

FIGURE 28.16 This worksheet is set up to maximize a credit union's investmen...

FIGURE 28.17 The results of the portfolio optimization

Chapter 29

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

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

FIGURE 29.3 The Correlation dialog box

FIGURE 29.4 Descriptive statistics output

FIGURE 29.5 Output from the f-Test tool

FIGURE 29.6 Use the histogram tool to generate distributions and graphical o...

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

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

FIGURE 29.9 The Regression dialog box

FIGURE 29.10 Output from the t-Test: Paired Two Sample For Means dialog box...

Chapter 30

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

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

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

FIGURE 30.4 The Allow Users to Edit Ranges dialog box

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

FIGURE 30.6 Opening this workbook requires a password.

FIGURE 30.7 The Protect Structure And Windows dialog box

FIGURE 30.8 Protecting a VBA Project with a password

FIGURE 30.9 The Document Inspector dialog box identifies hidden and personal...

FIGURE 30.10 Check Performance identifies unnecessary bloat in your workbook...

Chapter 31

FIGURE 31.1 The Power Pivot Ribbon interface

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

FIGURE 31.3 Convert your data range into an Excel table.

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

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

FIGURE 31.6 Each table you add to the data model adds another tab in Power P...

FIGURE 31.7 The Diagram view allows you to see all the tables in your data m...

FIGURE 31.8 To create a relationship, you simply click and drag a line betwe...

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

FIGURE 31.10 Use the Manage Relationships window to edit or delete existing ...

FIGURE 31.11 Use the Edit Relationship dialog box to adjust the tables and f...

FIGURE 31.12 You now have a Power Pivot–driven Pivot Table that aggregates d...

FIGURE 31.13 Activate the Table Import Wizard and select Microsoft SQL Serve...

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

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

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

FIGURE 31.17 The last page of the Table Import Wizard shows you the progress...

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

FIGURE 31.19 Select the worksheets you want to import.

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

FIGURE 31.21 The Paste Preview dialog box gives you a chance to see what you...

FIGURE 31.22 The Queries & Connections task pane

FIGURE 31.23 The Connection Properties dialog box lets you configure the cho...

FIGURE 31.24 Use the Existing Connections dialog box to reconfigure your Pow...

Chapter 32

FIGURE 32.1 This table shows transactions by employee number.

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

FIGURE 32.3 The Existing Connections dialog box lists all available Table ob...

FIGURE 32.4 Use the Import Data dialog box to add the Table object to the in...

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

FIGURE 32.6 Creating a new Pivot Table on a new worksheet

FIGURE 32.7 Select All in the Pivot Table Fields list to see both tables in ...

FIGURE 32.8 If no relationships exist for the tables in your Pivot Table, Ex...

FIGURE 32.9 Build the appropriate relationship using the Table and Column dr...

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

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

FIGURE 32.12 Use the Queries & Connections task pane to manage the queries a...

Chapter 33

FIGURE 33.1 Start your calculated column by entering your desired operation ...

FIGURE 33.2 Calculated columns automatically show up in your Pivot Table Fie...

FIGURE 33.3 You can use the formatting tools found on the Power Pivot window...

FIGURE 33.4 The new Gross Margin calculation uses the previously created [To...

FIGURE 33.5 Right-click and select Hide From Client Tools.

FIGURE 33.6 Hidden columns are grayed out, and calculated columns have a dar...

FIGURE 33.7 The Insert Function dialog box shows you all available DAX funct...

FIGURE 33.8 The DAX

SUM

function can only sum the column as a whole.

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

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

FIGURE 33.11 DAX calculations are immediately available in any connected Piv...

FIGURE 33.12 Month names in Power Pivot–driven Pivot Tables don't automatica...

FIGURE 33.13 The Sort By Column dialog box lets you define how your columns ...

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

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

FIGURE 33.16 Use the

RELATED

function to look up a field from another table....

FIGURE 33.17 The final Discounted Revenue calculated column using the Discou...

FIGURE 33.18 These cells are now a series of Cube functions.

FIGURE 33.19 Excel gives you the option of converting your report filter fie...

FIGURE 33.20 Cube functions give you the flexibility of restructuring your p...

FIGURE 33.21 Creating a new measure that will calculate Total Revenue

FIGURE 33.22 The results of a calculated measure can be seen by adding it to...

FIGURE 33.23 Creating a new measure that will calculate Total Units

FIGURE 33.24 DAX allows you to use existing measures as arguments in other m...

FIGURE 33.25 We need a calculated measure that returns OrderQuantity * UnitP...

FIGURE 33.26 The [Realized Sales] measure uses a simple

SUM

aggregator funct...

FIGURE 33.27 Our [Realized Sales] measure looks good at the granular OrderNu...

FIGURE 33.28 The math falls apart when we move to any granularity above Orde...

FIGURE 33.29 Using the

SUMX

function enables our measure to retain row conte...

FIGURE 33.30 Our new [Realized PriceX] measure remains accurate now at every...

FIGURE 33.31 We need to calculate the sum of Sales Amount in cell E1, but on...

FIGURE 33.32 After applying filters, your data has a new filter context.

FIGURE 33.33 Each cell in a Pivot Table contains its own filter context.

FIGURE 33.34 Filter context is propagated between table via relationships in...

FIGURE 33.35 The [Bike Sales] measure has its own product category context; ...

Chapter 34

FIGURE 34.1 Starting a Power Query web query

FIGURE 34.2 Enter the target URL for the source containing the data you need...

FIGURE 34.3 Select the correct data source and then click the Transform Data...

FIGURE 34.4 The Power Query Editor window allows you to shape, clean, and tr...

FIGURE 34.5 Select Use First Row As Headers.

FIGURE 34.6 Remove all but the first three columns.

FIGURE 34.7 The Import Data dialog box gives you more control over how the r...

FIGURE 34.8 Your final query pulled from the Internet: transformed and put i...

FIGURE 34.9 Query steps can be viewed and managed in the Applied Steps secti...

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

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

FIGURE 34.12 Click the Table Actions icon to see the table-level actions you...

FIGURE 34.13 Select the data sources with which you want to work and then cl...

FIGURE 34.14 The available tables and pages in the PDF are shown in the Navi...

FIGURE 34.15 Other systems Power Query can use as data sources

FIGURE 34.16 Edit a data source by selecting it and clicking the Edit Permis...

FIGURE 34.17 The credentials edit screen for your selected data source

FIGURE 34.18 Data Profiling options are found in the Data Preview group on t...

FIGURE 34.19 Choose Column Profiling Based On Entire Dataset to get a more c...

FIGURE 34.20 Exposing the quick actions for a column using the data column q...

FIGURE 34.21 Right-clicking a column profile histogram bar exposes the quick...

Chapter 35

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

FIGURE 35.2 Removing duplicate records

FIGURE 35.3 Undo the removal of records by deleting the Removed Duplicates s...

FIGURE 35.4 Replacing null values

FIGURE 35.5 Replacing empty strings with the word

Undefined

FIGURE 35.6 Merging the Type and Code fields

FIGURE 35.7 The Merge Columns dialog box

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

FIGURE 35.9 Reformatting the ContactName field to proper case

FIGURE 35.10 Replacing text values

FIGURE 35.11 Leading spaces can cause issues in analysis.

FIGURE 35.12 The Trim command

FIGURE 35.13 The Extract command allows you to pull out parts of the text fo...

FIGURE 35.14 Extracting the first three characters of the Phone field

FIGURE 35.15 Extracting the two middle characters of the SicCode

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

FIGURE 35.17 Splitting the ContactName column at every occurrence of a comma...

FIGURE 35.18 The ContactName field has successfully been split into three co...

FIGURE 35.19 Matrix layouts are problematic for data analysis.

FIGURE 35.20 All months are now in a tabular format.

FIGURE 35.21 Use Unpivot Other Columns when the number of matrix columns wil...

FIGURE 35.22 Pivoting the Month and Revenue columns

FIGURE 35.23 Confirm the aggregation operation to finalize the pivot transfo...

FIGURE 35.24 The Custom Column dialog box

FIGURE 35.25 A formula to merge the Type and Code columns

FIGURE 35.26 Use the Data Type drop-down to discover and select the data typ...

FIGURE 35.27 A formula to create a consistent CompanyNumber padded with 10 d...

FIGURE 35.28 Applying an

if

statement in a custom column

FIGURE 35.29 The Group By dialog box, configured to sum potential revenue by...

FIGURE 35.30 The resulting aggregate view by City and State

FIGURE 35.31 Creating a custom data type

FIGURE 35.32 Each value in a data type column contains the data for underlyi...

FIGURE 35.33 Data types will have a special icon next to each value and will...

FIGURE 35.34 Referencing a data type value and entering the dot (

.

) operator...

Chapter 36

FIGURE 36.1 This data can be used as the source for various levels of aggreg...

FIGURE 36.2 Merge the Last_Name and First_Name columns to create a new Emplo...

FIGURE 36.3 Group the Employee field and add the Sales Amount column to crea...

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

FIGURE 36.5 Naming the new query SalesByBusiness

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

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

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

FIGURE 36.9 Appending multiple queries to NorthData

FIGURE 36.10 The final consolidated table of all regional data

FIGURE 36.11 The kinds of joins supported by Power Query

FIGURE 36.12 You need to merge the Questions and Answers queries into one ta...

FIGURE 36.13 Activating the Merge dialog box

FIGURE 36.14 The completed Merge dialog box

FIGURE 36.15 Expand the NewColumn field and choose the merged fields for whi...

FIGURE 36.16 The final table with merged questions and answers

FIGURE 36.17 Right-click the Source query step and select Edit Settings to r...

FIGURE 36.18 The Merge dialog box with fuzzy matching selected

Chapter 37

FIGURE 37.1 Hover over a query to get quick information, including sneak pee...

FIGURE 37.2 Queries can be organized into groups.

FIGURE 37.3 Use the Choose Columns dialog box to find and select columns fas...

FIGURE 37.4 Right-click query steps to rename them.

FIGURE 37.5 Create a new query from an existing column.

FIGURE 37.6 The Query Dependencies dialog box

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

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

FIGURE 37.9 Disabling the Type Detection feature

FIGURE 37.10 Disabling the privacy level settings

FIGURE 37.11 Disabling automatic table relationships

Chapter 38

FIGURE 38.1 The Developer tab

FIGURE 38.2 The Macro Settings area of the Trust Center dialog box

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

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

FIGURE 38.5 A simple VBA procedure

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

FIGURE 38.7 The Record Macro dialog box

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

FIGURE 38.9 The

TimeStamp

procedure was generated by the Excel macro recorde...

FIGURE 38.10 This TimeStamp macro works correctly.

FIGURE 38.11 Use the Macro Options dialog box to add or change a shortcut ke...

FIGURE 38.12 Adding a button to a worksheet so that it can be used to execut...

FIGURE 38.13 The ListFormulas macro creates a list of all formulas in a work...

Chapter 39

FIGURE 39.1 A simple custom worksheet function

FIGURE 39.2 Creating a worksheet formula that uses a custom function

FIGURE 39.3 Entering a description for a custom function. This description a...

FIGURE 39.4 Using the Function Arguments dialog box to insert a custom funct...

Chapter 40

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

FIGURE 40.2 This dialog box is displayed by the VBA InputBox function.

FIGURE 40.3 A simple message box, displayed with the VBA MsgBox function

FIGURE 40.4 The second argument of the MsgBox function determines what appea...

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

FIGURE 40.6 An empty UserForm

FIGURE 40.7 The Properties window for a CommandButton control

FIGURE 40.8 The code pane showing the Click event of a CommandButton and the...

FIGURE 40.9 A Label control, after changing its Font and TextAlign propertie...

FIGURE 40.10 The code module for the UserForm

FIGURE 40.11 The UserForm after adding controls and adjusting some propertie...

FIGURE 40.12 Displaying the UserForm

FIGURE 40.13 Adjusting the tab order in a UserForm

Chapter 41

FIGURE 41.1 This worksheet uses UserForm controls.

FIGURE 41.2 Excel's two sets of worksheet controls

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

FIGURE 41.4 Double-clicking a control in Design mode activates the VBE and e...

FIGURE 41.5 A ComboBox control

FIGURE 41.6 This worksheet has three Scrollbar controls.

FIGURE 41.7 A TextBox control with a vertical scrollbar

Chapter 42

FIGURE 42.1 The best way to create an event procedure is to let the VBE do i...

FIGURE 42.2 Selecting a cell causes the active cell's row and column to beco...

Chapter 43

FIGURE 43.1 The Add-ins dialog box

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

FIGURE 43.3 The custom dialog box

FIGURE 43.4 Adding descriptive information about your add-in

FIGURE 43.5 Set a shortcut key in the Macro Options dialog box.

FIGURE 43.6 The Protection tab of the Project Properties dialog box

Chapter 44

FIGURE 44.1 The Automate tab

FIGURE 44.2 A new script in the Code Editor

FIGURE 44.3 An existing script in the Code Editor

FIGURE 44.4 The Output area shows the results of writing to the console.

FIGURE 44.5 Errors in code are highlighted by the editor.

FIGURE 44.6 The Record Actions task pane

FIGURE 44.7 The Code Editor task pane

FIGURE 44.8 The Insert Action button is at the bottom of the Code Editor tas...

FIGURE 44.9 The Code Editor task pane displays the saved status.

FIGURE 44.10 Out-of-scope variables produce an error.

FIGURE 44.11 The NamedItem as shown in Excel

FIGURE 44.12 The result of the loop output to the console

Guide

Cover

Title Page

Copyright

Dedication

About the Authors

About the Technical Editors

Acknowledgments

Introduction

Table of Contents

Begin Reading

Index

End User License Agreement

Pages

iii

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

70

71

72

73

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

116

117

118

119

120

121

122

123

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

152

153

154

155

156

157

158

159

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176