36,99 €
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:
Seitenzahl: 1422
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
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
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
Cover
Table of Contents
Title Page
Copyright
About the Authors
About the Technical Editor
Acknowledgments
Introduction
Begin Reading
Index
End User License Agreement
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