Microsoft Excel 365 Bible - Michael Alexander - E-Book

Microsoft Excel 365 Bible E-Book

Michael Alexander

0,0
36,99 €

-100%
Sammeln Sie Punkte in unserem Gutscheinprogramm und kaufen Sie E-Books und Hörbücher mit bis zu 100% Rabatt.
Mehr erfahren.
Beschreibung

Your personal, hands-on guide to the latest and most useful features in Microsoft Excel 365 Excel 365 is Microsoft's latest cloud-based version of its world-famous spreadsheet app. Powerful and user-friendly, it's an ideal solution for businesses and people looking to make sense of--and draw intelligence from--their data. The Excel 365 Bible carries over the best content from the best-selling Excel 2019 Bible while reflecting how a new generation uses Excel in Excel 365. The authoring team with their decades of Excel and business intelligence experience and recognition from the Excel community as Excel MVPs delivers an accessible and authoritative roadmap to Excel 365. Interested in the basics? You'll learn to create spreadsheets and workbooks and navigate the user interface. If you're ready for more advanced topics you can skip right to the material on creating visualizations, crafting custom functions, and using Visual Basic for Applications to script automations. You'll also get: * Over 900 pages of powerful tips, tricks, and strategies to unlock the full potential of Microsoft Excel 365 * Guidance on how to import, manage, and analyze large amounts of data * Advice on how to craft predictions and "What-If Analyses" based on data you already have Perfect for anyone new to Excel, as well as experts and advanced users, the Excel 365 Bible is your comprehensive, go-to guide for everything you need to know about the world's most popular, easy-to-use spreadsheet software.

Sie lesen das E-Book in den Legimi-Apps auf:

Android
iOS
von Legimi
zertifizierten E-Readern

Seitenzahl: 1422

Bewertungen
0,0
0
0
0
0
0
Mehr Informationen
Mehr Informationen
Legimi prüft nicht, ob Rezensionen von Nutzern stammen, die den betreffenden Titel tatsächlich gekauft oder gelesen/gehört haben. Wir entfernen aber gefälschte Rezensionen.



Table of Contents

Cover

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 Formulas for Common Mathematical Operations

Calculating Percentages

Rounding Numbers

Counting Values in a Range

Using Excel's Conversion Functions

CHAPTER 12: Using Formulas to Manipulate Text

Working with Text

Using Text Functions

CHAPTER 13: Using Formulas with Dates and Times

Understanding How Excel Handles Dates and Times

Using Excel's Date and Time Functions

CHAPTER 14: Using Formulas for Conditional Analysis

Understanding Conditional Analysis

Performing Conditional Calculations

CHAPTER 15: Using Formulas for Matching and Lookups

Introducing Lookup Formulas

Leveraging Excel's Lookup Functions

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

What Is a Chart?

Basic Steps for Creating a Chart

Modifying and Customizing Charts

Understanding Chart Types

Newer Chart Types for Excel

CHAPTER 19: 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 20: Creating Sparkline Graphics

Sparkline Types

Creating Sparklines

Customizing Sparklines

Specifying a Date Axis

Auto-Updating Sparklines

Displaying a Sparkline for a Dynamic Range

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

Part IV: Managing and Analyzing Data

CHAPTER 22: Importing and Cleaning Data

Importing Data

Cleaning Up Data

Exporting Data

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

Introducing Worksheet Outlines

Creating an Outline

Working with Outlines

CHAPTER 25: 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 26: Introducing PivotTables

About PivotTables

Creating a PivotTable Automatically

Creating a PivotTable Manually

Seeing More PivotTable Examples

Learning More

CHAPTER 27: 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 28: Performing Spreadsheet What-If Analysis

Looking at a What-If Example

Exploring Types of What-If Analyses

Analyzing Data with Artificial Intelligence

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

Understanding the Power Pivot Internal Data Model

Loading Data from Other Data Sources

CHAPTER 33: Working Directly with the Internal Data Model

Directly Feeding the Internal Data Model

Managing Relationships in the Internal Data Model

Managing Queries & Connections

CHAPTER 34: 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 35: 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 36: Transforming Data with Power Query

Performing Common Transformation Tasks

Creating Custom Columns

Grouping and Aggregating Data

Working with Custom Data Types

CHAPTER 37: Making Queries Work Together

Reusing Query Steps

Understanding the Append Feature

Understanding the Merge Feature

CHAPTER 38: Enhancing Power Query Productivity

Implementing Some Power Query Productivity Tips

Avoiding Power Query Performance Issues

Part VI: Automating Excel

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

Understanding Why to Use Controls on a Worksheet

Using Controls

Reviewing the Available ActiveX Controls

CHAPTER 43: Working with Excel Events

Understanding Events

Entering Event-Handler VBA Code

Using Workbook-Level Events

Working with Worksheet Events

Using Special Application Events

CHAPTER 44: Seeing Some VBA Examples

Working with Ranges

Working with Workbooks

Working with Charts

VBA Speed Tips

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

Index

Copyright

About the Authors

About the Technical Editor

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

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 13

TABLE 13.1 Times of Day and Their Corresponding Serial Numbers

TABLE 13.2 Time Entry Formats Recognized by Excel

TABLE 13.3

DATEDIF

Time Unit Codes

Chapter 14

TABLE 14.1 A Truth Table for the

AND

Function

TABLE 14.2 A Truth Table for an

OR

Function Nested in an

AND

Function

TABLE 14.3 Simple Rules for Using Comparison Operators

Chapter 15

TABLE 15.1 Functions Used in Lookup Formulas

Chapter 18

TABLE 18.1 Limitations of Excel Charts

Chapter 21

TABLE 21.1 Number-Formatting Buttons on the Ribbon

TABLE 21.2 Number-Formatting Keyboard Shortcuts

TABLE 21.3 Common Date and Time Format Codes

Chapter 28

TABLE 28.1 Three Scenarios for the Production Model

Chapter 29

TABLE 29.1 Constraints Summary

Chapter 35

TABLE 35.1 Column-Level Actions

TABLE 35.2 Table-Level Actions

Chapter 36

TABLE 36.1 Common Conversion Functions

TABLE 36.2 Useful Transformation Functions

Chapter 41

TABLE 41.1 Constants Used in the MsgBox Function

TABLE 41.2 Toolbox Controls

Chapter 42

TABLE 42.1 ActiveX Controls

TABLE 42.2 Properties Shared by Multiple Controls

Chapter 43

TABLE 43.1 Workbook Events

TABLE 43.2 Worksheet Events

Chapter 44

TABLE 44.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, 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 shortcut menu provide more control over ho...

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

FIGURE 4.11 The Paste Special dialog box

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

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

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

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

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

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

FIGURE 4.18 Showing all comments in the Comments task pane.

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

FIGURE 4.20 A resolved comment is grayed out and buttons to reopen or delete...

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

FIGURE 4.22 This note contains an image.

FIGURE 4.23 The areas that make up a table

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

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

FIGURE 4.26 A table after performing a three-column sort

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

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

FIGURE 4.29 Specifying a more complex numeric filter

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

FIGURE 4.31 The table is filtered by two slicers.

FIGURE 4.32 Excel offers many different table styles.

FIGURE 4.33 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 shortcut ...

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 This three-part header is one of Excel's predefined headers.

FIGURE 7.7 Use the Properties section in the object's Format task pane to pr...

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

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 You can insert a function by selecting it from one of the functio...

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 Using the

LET

function to simplify a nested

IF

statement

FIGURE 9.10 Using the

LET

function to simplify a

SWITCH

statement

FIGURE 9.11 A simple table with three columns of information

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

FIGURE 9.13 The Difference column contains a formula.

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

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

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

FIGURE 9.17 Creating names for all values in a table

Chapter 10

FIGURE 10.1 Column D contains formulas to calculate the total revenue for ea...

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 multi-column 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 2022

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

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

Chapter 13

FIGURE 13.1 Calculating the number of days between today and the invoice dat...

FIGURE 13.2 Calculating the number of workdays between two dates

FIGURE 13.3

NETWORKDAY.INTL

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

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

FIGURE 13.5 Creating a list of business days

FIGURE 13.6 Extract the parts of a date.

FIGURE 13.7 Showing the years and months between dates

FIGURE 13.8 Converting standard dates into Julian dates

FIGURE 13.9 Calculating the percent of the year completed

FIGURE 13.10 Calculating the last day of each date

FIGURE 13.11 Calculating calendar quarters

FIGURE 13.12 Calculating fiscal quarters

FIGURE 13.13 Calculating fiscal months

FIGURE 13.14 A dynamic date table calculating the Nth occurrence of each wee...

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

FIGURE 13.16 Extract the parts of a time.

FIGURE 13.17 Calculating elapsed time

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

FIGURE 13.19 Converting decimal hours to hours and minutes

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

Chapter 14

FIGURE 14.1 Monthly gas prices by state

FIGURE 14.2 A model for selecting an automobile

FIGURE 14.3 Data validation using

INDIRECT

FIGURE 14.4 A different auto-selector model

FIGURE 14.5 An inventory listing

FIGURE 14.6 A modified inventory listing

FIGURE 14.7 A revised discount scheme

FIGURE 14.8 Summing values less than zero

FIGURE 14.9 List of regions and sales values

FIGURE 14.10

SUMIF

using the

TODAY

function

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

FIGURE 14.12 Summing values that are between two dates

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

FIGURE 14.14 1972 Alpine Skiing Olympic medalists

FIGURE 14.15 Averaging results based on a country

FIGURE 14.16 Averaging on three conditions

Chapter 15

FIGURE 15.1 A table of employee information

FIGURE 15.2 A simplified paystub form

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

FIGURE 15.4 A table of cities and temperatures

FIGURE 15.5 A report of CEO salaries

FIGURE 15.6 A cleaner report

FIGURE 15.7 Computing income tax withholding

FIGURE 15.8 The same withholding table as Figure 15.7 except the data is sor...

FIGURE 15.9 Computing income tax withholding from two tables

FIGURE 15.10 The New Name dialog box

FIGURE 15.11 Sales data by region and year

FIGURE 15.12 Returning totals from the sales data

FIGURE 15.13 A table of departmental budgets

FIGURE 15.14 A list of invoices

Chapter 16

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

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

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

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

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

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

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

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

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

FIGURE 16.10 Using the

WEEKDAY

function to evaluate the weekday number of th...

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

FIGURE 16.12 Using the

AND

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

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

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

Chapter 17

FIGURE 17.1 Excel can check your formulas for potential errors.

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

FIGURE 17.3 Use the Find and Replace dialog box to locate information in a w...

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

Chapter 18

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

FIGURE 18.2 This line chart displays two data series.

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

FIGURE 18.4 A chart on a chart sheet

FIGURE 18.5 Parts of a chart

FIGURE 18.6 A pie chart

FIGURE 18.7 A 3-D column chart

FIGURE 18.8 This data would make a good chart.

FIGURE 18.9 The icons in the Insert ➪ Charts group expand to show a gallery ...

FIGURE 18.10 A column chart with two data series

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

FIGURE 18.12 The Change Chart Type dialog box

FIGURE 18.13 One-click design variations of a column chart

FIGURE 18.14 Using the Format Data Series task pane

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

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

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

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

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

FIGURE 18.20 A 3-D column chart

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

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

FIGURE 18.23 This line chart displays three series.

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

FIGURE 18.25 A pie chart with one slice exploded

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

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

FIGURE 18.28 A stacked area chart

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

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

FIGURE 18.31 A surface chart

FIGURE 18.32 A bubble chart

FIGURE 18.33 The four stock chart subtypes

FIGURE 18.34 Plotting temperature data with a stock chart

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

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

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

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

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

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

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

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

FIGURE 18.43 Map charts come with unique customization options.

Chapter 19

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

FIGURE 19.2 Using the Chart Elements control in the Mini toolbar

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

FIGURE 19.4 Chart customization buttons

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

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

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

FIGURE 19.8 Using shapes as callouts in lieu of a legend

FIGURE 19.9 The Format Axis task pane for a value axis

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

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

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

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

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

FIGURE 19.15 Some of the options available for a category axis

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

FIGURE 19.17 Overriding the Excel time-based category axis

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

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

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

FIGURE 19.21 The Edit Series dialog box

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

FIGURE 19.23 Data labels linked to text in an arbitrary range

FIGURE 19.24 Three options for dealing with missing data

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

FIGURE 19.26 Applying a trend line

FIGURE 19.27 Use the Format Trendline task pane to customize your trendline....

FIGURE 19.28 The Precipitation series is barely visible.

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

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

Chapter 20

FIGURE 20.1 Three groups of Sparklines

FIGURE 20.2 Data to be summarized with Sparklines

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

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

FIGURE 20.5 A Sparkline at various sizes

FIGURE 20.6 The Hidden and Empty Cell Settings dialog box

FIGURE 20.7 Highlighting options for Line Sparklines

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

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

FIGURE 20.10 Using Win/Loss Sparklines to display goal achievement

FIGURE 20.11 The axis in the Sparklines represents the goal.

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

FIGURE 20.13 After a date axis is specified, the Sparkline shows the values ...

FIGURE 20.14 Creating a Sparkline from data in a table

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

Chapter 21

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

FIGURE 21.2 Formatting numbers applies only a cosmetic look. Look in the for...

FIGURE 21.3 Custom number formatting that shows zeros as

n/a

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

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

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

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

FIGURE 21.8 Copy the newly inserted symbols to the Clipboard.

FIGURE 21.9 Create a custom number format using the symbols.

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

FIGURE 21.11 The Shapes gallery

FIGURE 21.12 The Microsoft Office Icons Library

FIGURE 21.13 A 3D model in action

FIGURE 21.14 Peekaboo tab

FIGURE 21.15 Deconstructed view of the peekaboo tab

FIGURE 21.16 A visual banner made with shapes

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

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

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

FIGURE 21.20 Text Box shapes can be linked to cells.

FIGURE 21.21 Pasting a linked picture

FIGURE 21.22 Using linked pictures to enhance visualizations

FIGURE 21.23 Inserting a SmartArt graphic

FIGURE 21.24 Entering text for an organizational chart

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

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

Chapter 22

FIGURE 22.1 Filtering by file extension in the Open dialog box

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

FIGURE 22.3 Enabling the legacy import wizard

FIGURE 22.4 This CSV file will be imported.

FIGURE 22.5 Step 1 of the Text Import Wizard

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

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

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

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

FIGURE 22.10 Using formulas to identify duplicate rows

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

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

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

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

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

FIGURE 22.16 Using Flash Fill to split names

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

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

FIGURE 22.19 The

TEXTJOIN

function inserts delimiters between cell values.

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

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

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

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

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

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

FIGURE 22.26 To replace only the second hyphen in these cells, Find and Repl...

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

Chapter 23

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

FIGURE 23.2 The three tabs of the Data Validation dialog box

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

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

FIGURE 23.5 Entering a data validation formula

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

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

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

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

FIGURE 23.10 Data Validation dialog box

Chapter 24

FIGURE 24.1 A simple sales summary with subtotals

FIGURE 24.2 The worksheet after creating an outline

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

FIGURE 24.4 The worksheet after adding a column outline

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

FIGURE 24.6 An outline of this book, created manually

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

Chapter 25

FIGURE 25.1 This message indicates that the workbook you saved contains refe...

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

FIGURE 25.3 The Edit Links dialog box

FIGURE 25.4 Use the Startup Prompt dialog box to specify how Excel handles l...

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

FIGURE 25.6 The Consolidate dialog box enables you to specify ranges to cons...

FIGURE 25.7 Three workbooks to be consolidated

FIGURE 25.8 The result of consolidating the information in three workbooks

FIGURE 25.9 Expanding the outline to show more details

Chapter 26

FIGURE 26.1 This table is used to create a PivotTable.

FIGURE 26.2 A simple PivotTable

FIGURE 26.3 A PivotTable that uses a report filter

FIGURE 26.4 This range is not appropriate for a PivotTable.

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

FIGURE 26.6 A PivotTable created from normalized data

FIGURE 26.7 Selecting a Recommended PivotTable

FIGURE 26.8 In the Create PivotTable dialog box, you tell Excel where the da...

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

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

FIGURE 26.11 Two fields are used for row labels.

FIGURE 26.12 The PivotTable is filtered by date.

FIGURE 26.13 This PivotTable shows daily totals for each branch.

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

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

FIGURE 26.16 This PivotTable counts the number of accounts that fall into ea...

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

FIGURE 26.18 This PivotTable uses three filters.

Chapter 27

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

FIGURE 27.2 Changing the PivotTable to show counts and percentages

FIGURE 27.3 A PivotTable with two groups

FIGURE 27.4 PivotTables with options for subtotals and grand totals

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

FIGURE 27.6 The PivotTable with Excel's automatic grouping

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

FIGURE 27.8 The PivotTable, after grouping by month and year

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

FIGURE 27.10 This PivotTable is grouped by hours.

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

FIGURE 27.12 The PivotTable and PivotChart show the frequency distribution f...

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

FIGURE 27.14 This PivotTable was created from the sales data.

FIGURE 27.15 The Insert Calculated Field dialog box

FIGURE 27.16 This PivotTable uses a calculated field.

FIGURE 27.17 The Insert Calculated Item dialog box

FIGURE 27.18 This PivotTable uses calculated items for quarterly totals.

FIGURE 27.19 The PivotTable, after creating two groups and adding subtotals...

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

FIGURE 27.21 Using slicers to filter a PivotTable and PivotChart by state an...

FIGURE 27.22 Using a timeline to filter a PivotTable by date

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

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

GETPIVOTD

...

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

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

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

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

FIGURE 27.29 These three tables will be used for a PivotTable, using the dat...

FIGURE 27.30 The PivotTable Fields task pane for a data model

FIGURE 27.31 The Create Relationship dialog box

FIGURE 27.32 Relating the Orders table to the Customers table

FIGURE 27.33 Relating the Customers and Regions tables by state

FIGURE 27.34 A PivotTable based on the data model

FIGURE 27.35 Formatting applied to the data model PivotTable

Chapter 28

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

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

FIGURE 28.3 Preparing to create a one-input data table

FIGURE 28.4 The Data Table dialog box

FIGURE 28.5 The result of the one-input data table

FIGURE 28.6 The setup for a two-input data table

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

FIGURE 28.8 Preparing to create a two-input data table

FIGURE 28.9 The result of the two-input data table

FIGURE 28.10 A simple production model to demonstrate Scenario Manager

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

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

FIGURE 28.13 Selecting a scenario to display

FIGURE 28.14 A Scenario Summary report produced by Scenario Manager

FIGURE 28.15 Bank accounts opened in a month

FIGURE 28.16 The Analyze Data task pane

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

FIGURE 28.18 A suggested PivotChart is inserted into a new worksheet.

FIGURE 28.19 The Total Amount by Customer suggestion

FIGURE 28.20 Using Analyze Data to query the data

Chapter 29

FIGURE 29.1 A mortgage calculator with input cells and formula cells

FIGURE 29.2 The Goal Seek dialog box

FIGURE 29.3 Goal Seek has found a solution.

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

FIGURE 29.5 The Solver Parameters dialog box

FIGURE 29.6 The Add Constraint dialog box

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

FIGURE 29.8 One of three reports that Solver can produce

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

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

FIGURE 29.11 Solver finds a solution to the linear equations.

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

FIGURE 29.13 The solution that was created by Solver

FIGURE 29.14 Using Solver to maximize profit when resources are limited

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

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

FIGURE 29.17 The results of the portfolio optimization

Chapter 30

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

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

FIGURE 30.3 The Correlation dialog box

FIGURE 30.4 Descriptive statistics output

FIGURE 30.5 Output from the F-Test tool

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

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

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

FIGURE 30.9 The Regression dialog box

FIGURE 30.10 Output from the paired t-Test dialog box

Chapter 31

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

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

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

FIGURE 31.4 The Allow Users to Edit Ranges dialog box

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

FIGURE 31.6 Opening this workbook requires a password.

FIGURE 31.7 The Protect Structure and Windows dialog box

FIGURE 31.8 Protecting a VBA Project with a password

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

Chapter 32

FIGURE 32.1 The Power Pivot Ribbon interface

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

FIGURE 32.3 Convert your data range into an Excel table.

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

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

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

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

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

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

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

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

FIGURE 32.12 You now have a Power Pivot–driven PivotTable that aggregates da...

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

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

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

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

FIGURE 32.17 The last screen of the Table Import Wizard shows you the progre...

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

FIGURE 32.19 Select the worksheets you want to import.

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

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

FIGURE 32.22 The Queries & Connections task pane

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

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

Chapter 33

FIGURE 33.1 This table shows transactions by employee number.

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

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

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

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

FIGURE 33.6 Creating a new PivotTable on a new worksheet

FIGURE 33.7 Select All in the PivotTable Fields list to see both tables in y...

FIGURE 33.8 If no relationships exist for the tables in your PivotTable, Exc...

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

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

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

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

Chapter 34

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

FIGURE 34.2 Calculated columns automatically show up in your PivotTable Fiel...

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

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

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

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

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

FIGURE 34.8 The DAX

SUM

function can only sum the column as a whole.

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

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

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

FIGURE 34.12 Month names in Power Pivot–driven PivotTables don't automatical...

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

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

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

FIGURE 34.16 Use the

RELATED

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

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

FIGURE 34.18 Creating a new calculated measure

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

FIGURE 34.20 The Manage Measures dialog box lets you edit or delete your cal...

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

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

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

Chapter 35

FIGURE 35.1 Starting a Power Query web query

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

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

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

FIGURE 35.5 Change the data type of the High, Low, and Close columns to curr...

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

FIGURE 35.7 Removing errors caused by text values that could not be converte...

FIGURE 35.8 The Power Query Editor can be used to apply transformation actio...

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

FIGURE 35.10 Your final query pulled from the Internet: transformed, put int...

FIGURE 35.11 Query steps can be viewed and managed in the Applied Steps sect...

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

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

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

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

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

FIGURE 35.17 Other systems Power Query can utilize as data sources

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

FIGURE 35.19 The credentials edit screen for your selected data source

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

FIGURE 35.21 Choose column profiling based on the entire data set to get a m...

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

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

Chapter 36

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

FIGURE 36.2 Removing duplicate records

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

FIGURE 36.4 Replacing null values

FIGURE 36.5 Replacing empty strings with the word

Undefined

FIGURE 36.6 Merging the Type and Code fields

FIGURE 36.7 The Merge Columns dialog box

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

FIGURE 36.9 Reformatting the ContactName field to proper case

FIGURE 36.10 Replacing text values

FIGURE 36.11 Leading spaces can cause issues in analysis.

FIGURE 36.12 The Trim command

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

FIGURE 36.14 Extracting the first three characters of the Phone field

FIGURE 36.15 Extracting the two middle characters of the SicCode

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

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

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

FIGURE 36.19 Matrix layouts are problematic for data analysis.

FIGURE 36.20 All months are now in a tabular format.

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

FIGURE 36.22 Pivoting the Month and Revenue columns

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

FIGURE 36.24 The Custom Column dialog box

FIGURE 36.25 A formula to merge the Type and Code columns

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

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

FIGURE 36.28 Applying an

if

statement in a custom column

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

FIGURE 36.30 The resulting aggregate view by City and State

FIGURE 36.31 Creating a Custom Data Type

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

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

FIGURE 36.34 Referencing a data type value and entering the dot (.) operator...

Chapter 37

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

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

FIGURE 37.3 Group the Employee field and sum the Sales Amount column to crea...

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

FIGURE 37.5 Naming the new query SalesByBusiness

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

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

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

FIGURE 37.9 Appending multiple queries to NorthData

FIGURE 37.10 The final consolidated table of all regional data

FIGURE 37.11 The kinds of joins supported by Power Query

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

FIGURE 37.13 Activating the Merge dialog box

FIGURE 37.14 The completed Merge dialog box

FIGURE 37.15 Expand the NewColumn field, and choose the merged fields you wa...

FIGURE 37.16 The final table with merged questions and answers

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

FIGURE 37.18 The Merge dialog box with fuzzy matching selected

Chapter 38

FIGURE 38.1 Hover over a query to get quick information including sneak peek...

FIGURE 38.2 Queries can be organized into groups.

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

FIGURE 38.4 Right-click query steps to rename them.

FIGURE 38.5 Create a new query from an existing column.

FIGURE 38.6 The Query Dependencies dialog box

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

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

FIGURE 38.9 Disabling the Type Detection feature

FIGURE 38.10 Disabling the privacy level settings

FIGURE 38.11 Disabling automatic table relationships

Chapter 39

FIGURE 39.1 The Developer tab

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

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

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

FIGURE 39.5 A simple VBA procedure

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

FIGURE 39.7 The Record Macro dialog box

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

FIGURE 39.9 The TimeStamp procedure was generated by the Excel macro recorde...

FIGURE 39.10 This TimeStamp macro works correctly.

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

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

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

Chapter 40

FIGURE 40.1 A simple custom worksheet function

FIGURE 40.2 Creating a worksheet formula that uses a custom function

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

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

Chapter 41

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

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

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

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

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

FIGURE 41.6 An empty UserForm

FIGURE 41.7 The Properties window for a CommandButton control

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

FIGURE 41.9 A Label control, after changing its Font properties

FIGURE 41.10 The code module for the UserForm

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

FIGURE 41.12 Displaying the UserForm

FIGURE 41.13 Adjusting the tab order in a UserForm

Chapter 42

FIGURE 42.1 This worksheet uses UserForm controls.

FIGURE 42.2 Excel's two sets of worksheet controls

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

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

FIGURE 42.5 A ComboBox control

FIGURE 42.6 This worksheet has three ScrollBar controls.

FIGURE 42.7 A TextBox control with a vertical scrollbar

Chapter 43

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

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

Chapter 44

FIGURE 44.1 The number of rows in this range can be different every time the...

FIGURE 44.2 Using the VBA InputBox function to get a value from the user

FIGURE 44.3 You can instruct Excel not to display these types of alerts whil...

Chapter 45

FIGURE 45.1 The Add-Ins dialog box

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

FIGURE 45.3 The custom dialog box

FIGURE 45.4 Adding descriptive information about your add-in

FIGURE 45.5 The Protection tab of the Project Properties dialog box

Guide

Cover

Table of Contents

Title Page

Copyright

About the Authors

About the Technical Editor

Acknowledgments

Introduction

Begin Reading

Index

End User License Agreement

Pages

iii

xxxix

xl

xli

xlii

xliii

xliv

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

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

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

116

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

152

153

154

155

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

206

207

208

209

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

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

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

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

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

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

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

441

442

443

444

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

519

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

550

551

553

554

555

556

557

558

559

560

561

562

563

564

565

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

697

698

699

700

701

702

703

704

705

706

707

708