35,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Serie: Bible
- Sprache: Englisch
- Veröffentlichungsjahr: 2018
The complete guide to Excel 2019 Whether you are just starting out or an Excel novice, the Excel 2019 Bible is your comprehensive, go-to guide for all your Excel 2019 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more. Navigate this powerful tool for business, home management, technical work, and much more with the only resource you need, Excel 2019 Bible. * Create functional spreadsheets that work * Master formulas, formatting, pivot tables, and more * Get acquainted with Excel 2019's new features and tools Whether you need a walkthrough tutorial or an easy-to-navigate desk reference, the Excel 2019 Bible has you covered with complete coverage and clear expert guidance.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 1467
Ähnliche
Table of Contents
Cover
Introduction
Is This Book for You?
Software Versions
Conventions Used in This Book
How This Book Is Organized
How to Use This Book
What's on the Website
Part I: Getting Started with Excel
CHAPTER 1: Introducing Excel
Understanding What Excel Is Used For
Looking at What's New in Excel 2019
Understanding Workbooks and Worksheets
Moving around a Worksheet
Using the Ribbon
Using Shortcut Menus
Customizing Your Quick Access Toolbar
Working with Dialog Boxes
Using Task Panes
Creating Your First Excel Workbook
CHAPTER 2: Entering and Editing Worksheet Data
Exploring Data Types
Entering Text and Values into Your Worksheets
Entering Dates and Times into Your Worksheets
Modifying Cell Contents
Applying Number Formatting
CHAPTER 3: Performing Basic Worksheet Operations
Learning the Fundamentals of Excel Worksheets
Controlling the Worksheet View
Working with Rows and Columns
CHAPTER 4: Working with Excel Ranges and Tables
Understanding Cells and Ranges
Copying or Moving Ranges
Using Names to Work with Ranges
Adding Comments to Cells
Working with Tables
CHAPTER 5: Formatting Worksheets
Getting to Know the Formatting Tools
Formatting Your Worksheet
Using Conditional Formatting
Using Named Styles for Easier Formatting
Understanding Document Themes
CHAPTER 6: Understanding Excel Files and Templates
Creating a New Workbook
Opening an Existing Workbook
Saving a Workbook
Using AutoRecover
Password-Protecting a Workbook
Organizing Your Files
Other Workbook Info Options
Closing Workbooks
Safeguarding Your Work
Working with Templates
CHAPTER 7: Printing Your Work
Doing Basic Printing
Changing Your Page View
Adjusting Common Page Setup Settings
Adding a Header or a Footer to Your Reports
Exploring Other Print-Related Topics
CHAPTER 8: Customizing the Excel User Interface
Customizing the Quick Access Toolbar
Customizing the Ribbon
Part II: Working with Formulas and Functions
CHAPTER 9: Introducing Formulas and Functions
Understanding Formula Basics
Entering Formulas into Your Worksheets
Editing Formulas
Using Cell References in Formulas
Using Formulas in Tables
Correcting Common Formula Errors
Using Advanced Naming Techniques
Working with Formulas
CHAPTER 10: Using Formulas for Common Mathematical Operations
Calculating Percentages
Rounding Numbers
Counting Values in a Range
Using Excel's Conversion Functions
CHAPTER 11: Using Formulas to Manipulate Text
Working with Text
Using Text Functions
CHAPTER 12: Using Formulas with Dates and Times
Understanding How Excel Handles Dates and Times
Using Excel's Date and Time Functions
CHAPTER 13: Using Formulas for Conditional Analysis
Understanding Conditional Analysis
Performing Conditional Calculations
CHAPTER 14: Using Formulas for Matching and Lookups
Introducing Lookup Formulas
Leveraging Excel's Lookup Functions
CHAPTER 15: Using Formulas for Financial Analysis
Performing Common Business Calculations
Leveraging Excel's Financial Functions
CHAPTER 16: Using Formulas for Statistical Analysis
Working with Weighted Averages
Smoothing Data with Moving Averages
Using Functions to Create Descriptive Statistics
Bucketing Data into Percentiles
Identifying Statistical Outliers with an Interquartile Range
Creating a Frequency Distribution
CHAPTER 17: Using Formulas with Tables and Conditional Formatting
Highlighting Cells That Meet Certain Criteria
Highlighting Values That Exist in List1 but Not List2
Highlighting Values That Exist in List1 and List2
Highlighting Based on Dates
CHAPTER 18: Understanding and Using Array Formulas
Understanding Array Formulas
Creating an Array Constant
Understanding the Dimensions of an Array
Naming Array Constants
Working with Array Formulas
Using Multicell Array Formulas
Using Single-Cell Array Formulas
CHAPTER 19: Making Your Formulas Error-Free
Finding and Correcting Formula Errors
Using Excel Auditing Tools
Searching and Replacing
Using AutoCorrect
Part III: Creating Charts and Other Visualizations
CHAPTER 20: Getting Started with Excel Charts
What Is a Chart?
Basic Steps for Creating a Chart
Modifying and Customizing Charts
Understanding Chart Types
New Chart Types for Excel
CHAPTER 21: Using Advanced Charting Techniques
Selecting Chart Elements
Exploring the User Interface Choices for Modifying Chart Elements
Modifying the Chart Area
Modifying the Plot Area
Working with Titles in a Chart
Working with a Legend
Working with Gridlines
Modifying the Axes
Working with Data Series
Creating Chart Templates
CHAPTER 22: Creating Sparkline Graphics
Sparkline Types
Creating Sparklines
Customizing Sparklines
Specifying a Date Axis
Auto-Updating Sparklines
Displaying a Sparkline for a Dynamic Range
CHAPTER 23: Visualizing with Custom Number Formats and Shapes
Visualizing with Number Formatting
Using Shapes and Icons as Visual Elements
Using SmartArt and WordArt
Working with Other Graphics Types
Using the Equation Editor
CHAPTER 24: Implementing Excel Dashboarding Best Practices
Preparing for a Dashboard Project
Implementing Dashboard Modeling Best Practices
Implementing Dashboard Design Best Practices
Part IV: Managing and Analyzing Data
CHAPTER 25: Importing and Cleaning Data
Importing Data
Cleaning Up Data
Exporting Data
CHAPTER 26: Using Data Validation
About Data Validation
Specifying Validation Criteria
Types of Validation Criteria You Can Apply
Creating a Drop-Down List
Using Formulas for Data Validation Rules
Understanding Cell References
Data Validation Formula Examples
Using Data Validation without Restricting Entry
CHAPTER 27: Creating and Using Worksheet Outlines
Introducing Worksheet Outlines
Creating an Outline
Working with Outlines
CHAPTER 28: Linking and Consolidating Worksheets
Linking Workbooks
Creating External Reference Formulas
Working with External Reference Formulas
Avoiding Potential Problems with External Reference Formulas
Consolidating Worksheets
CHAPTER 29: Introducing PivotTables
About PivotTables
Creating a PivotTable Automatically
Creating a PivotTable Manually
Seeing More PivotTable Examples
Learning More
CHAPTER 30: Analyzing Data with PivotTables
Working with Non-numeric Data
Grouping PivotTable Items
Using a PivotTable to Create a Frequency Distribution
Creating a Calculated Field or Calculated Item
Filtering PivotTables with Slicers
Filtering PivotTables with a Timeline
Referencing Cells within a PivotTable
Creating PivotCharts
Using the Data Model
CHAPTER 31: Performing Spreadsheet What-If Analysis
Looking at a What-If Example
Exploring Types of What-If Analyses
CHAPTER 32: Analyzing Data Using Goal Seeking and Solver
Exploring What-If Analysis, in Reverse
Using Single-Cell Goal Seeking
Introducing Solver
Seeing Some Solver Examples
CHAPTER 33: Analyzing Data with the Analysis ToolPak
The Analysis ToolPak: An Overview
Installing the Analysis ToolPak Add-In
Using the Analysis Tools
Introducing the Analysis ToolPak Tools
CHAPTER 34: Protecting Your Work
Types of Protection
Protecting a Worksheet
Protecting a Workbook
Protecting a VBA Project
Related Topics
Part V: Understanding Power Pivot and Power Query
CHAPTER 35: Introducing Power Pivot
Understanding the Power Pivot Internal Data Model
Loading Data from Other Data Sources
CHAPTER 36: Working Directly with the Internal Data Model
Directly Feeding the Internal Data Model
Managing Relationships in the Internal Data Model
Removing a Table from the Internal Data Model
CHAPTER 37: Adding Formulas to Power Pivot
Enhancing Power Pivot Data with Calculated Columns
Utilizing DAX to Create Calculated Columns
Understanding Calculated Measures
Using Cube Functions to Free Your Data
CHAPTER 38: Introducing Power Query
Understanding Power Query Basics
Getting Data from External Sources
Getting Data from Other Data Systems
Managing Data Source Settings
CHAPTER 39: Transforming Data with Power Query
Performing Common Transformation Tasks
Creating Custom Columns
Grouping and Aggregating Data
CHAPTER 40: Making Queries Work Together
Reusing Query Steps
Understanding the Append Feature
Understanding the Merge Feature
CHAPTER 41: Enhancing Power Query Productivity
Implementing Some Power Query Productivity Tips
Avoiding Power Query Performance Issues
Part VI: Automating Excel
CHAPTER 42: Introducing Visual Basic for Applications
Introducing VBA Macros
Displaying the Developer Tab
Learning about Macro Security
Saving Workbooks That Contain Macros
Looking at the Two Types of VBA Macros
Creating VBA Macros
Learning More
CHAPTER 43: Creating Custom Worksheet Functions
Introducing VBA Functions
Seeing a Simple Example
Learning about Function Procedures
Executing Function Procedures
Using Function Procedure Arguments
Debugging Custom Functions
Inserting Custom Functions
Learning More
CHAPTER 44: Creating UserForms
Understanding Why to Create UserForms
Exploring UserForm Alternatives
Creating UserForms: An Overview
Looking at a UserForm Example
Looking at Another UserForm Example
Enhancing UserForms
Learning More
CHAPTER 45: Using UserForm Controls in a Worksheet
Understanding Why to Use Controls on a Worksheet
Using Controls
Reviewing the Available ActiveX Controls
CHAPTER 46: Working with Excel Events
Understanding Events
Entering Event-Handler VBA Code
Using Workbook-Level Events
Working with Worksheet Events
Using Special Application Events
CHAPTER 47: Seeing Some VBA Examples
Working with Ranges
Working with Workbooks
Working with Charts
VBA Speed Tips
CHAPTER 48: Creating Custom Excel Add-Ins
Understanding Add-Ins
Working with Add-Ins
Understanding Why to Create Add-Ins
Creating Add-Ins
Looking at an Add-In Example
Index
End User License Agreement
List of Tables
Chapter 1
TABLE 1.1 Parts of the Excel Screen That You Need to Know
TABLE 1.2 Excel Worksheet Movement Keys
Chapter 2
TABLE 2.1 Number Formatting Keyboard Shortcuts
Chapter 4
TABLE 4.1 Go To Special Options
Chapter 7
TABLE 7.1 Where to Change Printer Settings
TABLE 7.2 Header and Footer Buttons and Their Functions
Chapter 9
TABLE 9.1 Operators Used in Formulas
TABLE 9.2 Operator Precedence in Excel Formulas
TABLE 9.3 Excel Error Values
TABLE 9.4 Reference Operators for Ranges
Chapter 12
TABLE 12.1 Times of Day and Their Corresponding Serial Numbers
TABLE 12.2 Time Entry Formats Recognized by Excel
TABLE 12.3
DATEDIF
Time Unit Codes
Chapter 13
TABLE 13.1 A Truth Table for the AND Function
TABLE 13.2 A Truth Table for an OR Function Nested in an AND Function
TABLE 13.3 Simple Rules for Using Comparison Operators
Chapter 14
TABLE 14.1 Functions Used in Lookup Formulas
Chapter 20
TABLE 20.1 Limitations of Excel Charts
Chapter 23
TABLE 23.1 Number-Formatting Buttons on the Ribbon
TABLE 23.2 Number-Formatting Keyboard Shortcuts
TABLE 23.3 Common Date and Time Format Codes
Chapter 31
TABLE 31.1 Three Scenarios for the Production Model
Chapter 32
TABLE 32.1 Constraints Summary
Chapter 38
TABLE 38.1 Column-Level Actions
TABLE 38.2 Table-Level Actions
Chapter 39
TABLE 39.1 Common Conversion Functions
TABLE 39.2 Useful Transformation Functions
Chapter 44
TABLE 44.1 Constants Used in the MsgBox Function
TABLE 44.2 Toolbox Controls
Chapter 45
TABLE 45.1 ActiveX Controls
TABLE 45.2 Properties Shared by Multiple Controls
Chapter 46
TABLE 46.1 Workbook Events
TABLE 46.2 Worksheet Events
Chapter 47
TABLE 47.1 VBA Data Types
List of Illustrations
Chapter 1
FIGURE 1.1 The Excel screen has many useful elements that you will use often.
FIGURE 1.2 The active cell is the one with the dark border—in this case, cell C...
FIGURE 1.3 The Home tab of the Ribbon
FIGURE 1.4 The Home tab when Excel's window is made narrower
FIGURE 1.5 The Home tab when Excel's window is made very narrow
FIGURE 1.6 When you select an object, contextual tabs contain tools for working...
FIGURE 1.7 The Merge & Center command is a split button control.
FIGURE 1.8 Pressing Alt displays the keytips.
FIGURE 1.9 Right-click to display a shortcut menu of commands you're most likel...
FIGURE 1.10 Add new icons to your Quick Access toolbar by using the Quick Acces...
FIGURE 1.11 Excel uses a dialog box to get additional information about a comma...
FIGURE 1.12 Use the dialog box tabs to select different functional areas of the...
FIGURE 1.13 The Format Picture task pane, docked on the right side of the windo...
FIGURE 1.14 Your worksheet after you've entered the column headings and month n...
FIGURE 1.15 Your worksheet after you've created the formulas
FIGURE 1.16 Your worksheet after you've converted the range to a table
FIGURE 1.17 The table and chart
Chapter 2
FIGURE 2.1 You can use values, text, and formulas to create useful Excel worksh...
FIGURE 2.2 The Formula bar, expanded in height to show more information in the ...
FIGURE 2.3 When you're editing a cell, the Formula bar enables two new icons: C...
FIGURE 2.4 You can use the Advanced tab in Excel Options to select a number of ...
FIGURE 2.5 This series was created by using AutoFill
FIGURE 2.6 AutoCorrect allows you to create shorthand abbreviations for text yo...
FIGURE 2.7 Excel's built-in data form can simplify many data-entry tasks.
FIGURE 2.8 Use numeric formatting to make it easier to understand what the valu...
FIGURE 2.9 You can find number formatting commands in the Number group of the H...
FIGURE 2.10 When you need more control over number formats, use the Number tab ...
Chapter 3
FIGURE 3.1 Use the Arrange Windows dialog box to arrange all open nonminimized ...
FIGURE 3.2 Use the tab scrolling controls to activate a different worksheet or ...
FIGURE 3.3 Excel's warning that you might be losing some data
FIGURE 3.4 Use the Move or Copy dialog box to move or copy worksheets in the sa...
FIGURE 3.5 Use multiple windows to view different sections of a workbook at the...
FIGURE 3.6 You can split the worksheet window into two or four panes to view di...
FIGURE 3.7 Freeze certain columns and rows to make them remain visible while yo...
FIGURE 3.8 When using a table, scrolling down displays the table headings where...
FIGURE 3.9 Use the Watch Window to monitor the value in one or more cells.
FIGURE 3.10 You can't add a new row or column if it causes nonblank cells to mo...
FIGURE 3.11 You can insert partial rows or columns by using the Insert dialog b...
Chapter 4
FIGURE 4.1 When you select a range, it appears highlighted, but the active cell...
FIGURE 4.2 Excel enables you to select noncontiguous ranges.
FIGURE 4.3 The worksheets in this workbook are laid out identically.
FIGURE 4.4 In Group mode, you can work with a three-dimensional range of cells ...
FIGURE 4.5 Use the Go To Special dialog box to select specific types of cells....
FIGURE 4.6 The Find and Replace dialog box, with its options displayed
FIGURE 4.7 The Find and Replace dialog box, with its results listed
FIGURE 4.8 The Paste icons on the shortcut menu provide more control over how t...
FIGURE 4.9 Use the Clipboard task pane to copy and paste multiple items.
FIGURE 4.10 Excel offers several pasting options, with preview. Here, the infor...
FIGURE 4.11 The Paste Special dialog box
FIGURE 4.12 Transposing a range changes the orientation as the information is p...
FIGURE 4.13 Create names for cells or ranges by using the New Name dialog box....
FIGURE 4.14 Use the Create Names from Selection dialog box to name cells using ...
FIGURE 4.15 Use the Name Manager to work with range names.
FIGURE 4.16 You can add comments to cells to help point out specific items in y...
FIGURE 4.17 This comment contains a graphics image.
FIGURE 4.18 Cell comments don't have to be rectangles.
FIGURE 4.19 The areas that make up a table
FIGURE 4.20 Use the Create Table dialog box to verify that Excel guessed the ta...
FIGURE 4.21 Each column in a table has sorting and filtering options.
FIGURE 4.22 A table after performing a three-column sort
FIGURE 4.23 Using the Sort dialog box to specify a three-column sort
FIGURE 4.24 This table is filtered to show the information for only one area.
FIGURE 4.25 Specifying a more complex numeric filter
FIGURE 4.26 Use the Insert Slicers dialog box to specify which slicers to creat...
FIGURE 4.27 The table is filtered by two slicers.
FIGURE 4.28 Excel offers many different table styles.
FIGURE 4.29 Use this dialog box to create a new table style.
Chapter 5
FIGURE 5.1 Simple formatting can greatly improve the appearance of your workshe...
FIGURE 5.2 The Mini toolbar appears above or below the right-click shortcut men...
FIGURE 5.3 The Font tab of the Format Cells dialog box gives you many additiona...
FIGURE 5.4 You can choose many different font formatting options for your works...
FIGURE 5.5 The full range of alignment options is available on the Alignment ta...
FIGURE 5.6 The same text, displayed with three types of horizontal alignment
FIGURE 5.7 Merge worksheet cells to make them act as if they were a single cell...
FIGURE 5.8 Rotate text for additional visual impact.
FIGURE 5.9 Use the Borders drop-down list to add lines around worksheet cells....
FIGURE 5.10 Use the Border tab of the Format Cells dialog box for more control ...
FIGURE 5.11 The length of the data bars is proportional to the track length in ...
FIGURE 5.12 Two examples of color scale conditional formatting
FIGURE 5.13 Use the New Formatting Rule dialog box to customize a color scale....
FIGURE 5.14 Using an icon set to indicate the status of projects
FIGURE 5.15 Changing the icon assignment rule
FIGURE 5.16 Using a modified rule and eliminating an icon makes the table more ...
FIGURE 5.17 Creating a conditional formatting rule based on a formula
FIGURE 5.18 Highlighting a row, based on a matching name
FIGURE 5.19 Using conditional formatting to apply formatting to alternate rows...
FIGURE 5.20 Conditional formatting produces these groups of alternating shaded ...
FIGURE 5.21 Excel displays samples of predefined cell styles.
FIGURE 5.22 Use the Style dialog box to modify named styles.
FIGURE 5.23 The elements in this worksheet use the default theme.
FIGURE 5.24 The worksheet after applying a different theme
FIGURE 5.25 Built-in Excel theme choices
FIGURE 5.26 Use this dialog box to specify two fonts for a theme.
FIGURE 5.27 If you're feeling creative, you can specify a set of custom colors ...
Chapter 6
FIGURE 6.1 Choosing Blank Workbook from Excel's Start screen
FIGURE 6.2 You can recover to older versions of your workbook.
FIGURE 6.3 The Encrypt Document dialog box is where you specify a password for ...
FIGURE 6.4 The New page in Backstage view allows you to search for templates.
FIGURE 6.5 A workbook created from a template
Chapter 7
FIGURE 7.1 In Normal view, dotted lines indicate page breaks
FIGURE 7.2 In Page Layout view, the worksheet resembles printed pages.
FIGURE 7.3 Page Break Preview mode gives you a bird's-eye view of your workshee...
FIGURE 7.4 The Margins tab of the Page Setup dialog box
FIGURE 7.5 Use the Sheet tab of the Page Setup dialog box to specify rows or co...
FIGURE 7.6 This three-part header is one of Excel's predefined headers.
FIGURE 7.7 Use the Properties tab of the object's Format dialog box to prevent ...
FIGURE 7.8 Use the Add View dialog box to create a named view.
Chapter 8
FIGURE 8.1 The default location for the Quick Access toolbar is on the left sid...
FIGURE 8.2 This drop-down list is one way to add a new command to the Quick Acc...
FIGURE 8.3 Use the Quick Access Toolbar tab in the Excel Options dialog box to ...
FIGURE 8.4 The Customize Ribbon tab of the Excel Options dialog box
FIGURE 8.5 The View tab, with two new groups added
Chapter 9
FIGURE 9.1 Excel sometimes suggests a syntactically correct formula, but not th...
FIGURE 9.2 Excel displays a drop-down list when you enter a formula.
FIGURE 9.3 You can insert a function by selecting it from one of the function c...
FIGURE 9.4 The Insert Function dialog box
FIGURE 9.5 The Function Arguments dialog box
FIGURE 9.6 Copying a formula that contains relative references
FIGURE 9.7 Formula references to the sales tax cell should be absolute.
FIGURE 9.8 Using mixed cell references
FIGURE 9.9 A simple table with three columns of information
FIGURE 9.10 A drop-down list enables you to select a summary formula for a tabl...
FIGURE 9.11 The Difference column contains a formula.
FIGURE 9.12 The formula AutoComplete feature is useful when creating a formula ...
FIGURE 9.13 Excel allows you to name a formula that doesn't exist in a workshee...
FIGURE 9.14 You can use a range intersection formula to determine values.
FIGURE 9.15 With names, using a range intersection formula to determine values ...
Chapter 10
FIGURE 10.1 Calculating the percent of goal
FIGURE 10.2 Calculating the percent of goal using a common goal
FIGURE 10.3 Calculating the percent variance between current year sales and pre...
FIGURE 10.4 Using the ABS function will give you an accurate percent variance w...
FIGURE 10.5 Calculating a percent distribution of revenue across regions
FIGURE 10.6 Calculating percent distribution with the SUM function
FIGURE 10.7 Calculating a running total
FIGURE 10.8 Applying a percent increase and decrease using a simple formula
FIGURE 10.9 Using the IF function to avoid a division-by-zero error
FIGURE 10.10 Rounding to the nearest penny
FIGURE 10.11 Rounding numbers to one significant digit
FIGURE 10.12 A demonstration of counting cells
FIGURE 10.13 Creating a unit-of-measure conversion table
Chapter 11
FIGURE 11.1 Joining first and last names
FIGURE 11.2 Using the
TEXTJOIN
function
FIGURE 11.3 Converting text into upper, lower, proper, and sentence case
FIGURE 11.4 Removing excess spaces from text
FIGURE 11.5 Using the
LEFT
,
RIGHT
, and
MID
functions
FIGURE 11.6 Using the
FIND
function to extract data based on the position of th...
FIGURE 11.7 Nesting the
FIND
function to extract everything after the second hy...
FIGURE 11.8 Fixing the apostrophe S issue with the
SUBSTITUTE
function
FIGURE 11.9 Calculating the number of words in a cell
FIGURE 11.10 The x-axis labels in this chart include a line break and a referen...
FIGURE 11.11 Using the
CHAR()
function to force a line break between the sales ...
FIGURE 11.12 Cleaning data with the
CLEAN
function
FIGURE 11.13 Padding Customer ID fields to 10 characters
FIGURE 11.14 Numbers joined with text do not inherently adopt number formatting...
FIGURE 11.15 Using the TEXT function lets you format numbers joined with text. ...
Chapter 12
FIGURE 12.1 Calculating the number of days between today and the invoice date
FIGURE 12.2 Calculating the number of workdays between two dates
FIGURE 12.3
NETWORKDAY.INTL
allows you to specify which days to exclude as wee...
FIGURE 12.4 Start with a sheet containing the last date of the previous year an...
FIGURE 12.5 Creating a list of business days
FIGURE 12.6 Extract the parts of a date.
FIGURE 12.7 Showing the years and months between dates
FIGURE 12.8 Converting standard dates into Julian dates
FIGURE 12.9 Calculating the percent of the year completed
FIGURE 12.10 Calculating the last day of each date
FIGURE 12.11 Calculating calendar quarters
FIGURE 12.12 Calculating fiscal quarters
FIGURE 12.13 Calculating fiscal months
FIGURE 12.14 A dynamic date table calculating the Nth occurrence of each weekda...
FIGURE 12.15 A dynamic date table calculating the last weekday in each month
FIGURE 12.16 Extract the parts of a time.
FIGURE 12.17 Calculating elapsed time
FIGURE 12.18 Rounding time values to 15- and 30-minute increments
FIGURE 12.19 Converting decimal hours to hours and minutes
FIGURE 12.20 Adding a set number of hours and minutes to an existing time value...
Chapter 13
FIGURE 13.1 Monthly gas prices by state
FIGURE 13.2 A model for selecting an automobile
FIGURE 13.3 Data validation using INDIRECT
FIGURE 13.4 A different auto-selector model
FIGURE 13.5 An inventory listing
FIGURE 13.6 A modified inventory listing
FIGURE 13.7 A revised discount scheme
FIGURE 13.8 Summing values less than zero
FIGURE 13.9 List of regions and sales values
FIGURE 13.10
SUMIF
using the
TODAY
function
FIGURE 13.11 A partial listing of countries and their gross domestic product
FIGURE 13.12 Summing values that are between two dates
FIGURE 13.13 A partial listing of countries and their gross domestic product
FIGURE 13.14 1972 Alpine Skiing Olympic medalists
FIGURE 13.15 Averaging results based on a country
FIGURE 13.16 Averaging on three conditions
Chapter 14
FIGURE 14.1 A table of employee information
FIGURE 14.2 A simplified paystub form
FIGURE 14.3 A list of stores with their city and state locations
FIGURE 14.4 A table of cities and temperatures
FIGURE 14.5 A report of CEO salaries
FIGURE 14.6 A cleaner report
FIGURE 14.7 Computing income tax withholding
FIGURE 14.8 The same withholding table as Figure 14.7 except that the data is s...
FIGURE 14.9 Computing income tax withholding from two tables
FIGURE 14.10 The New Name dialog box
FIGURE 14.11 Sales data by region and year
FIGURE 14.12 Returning totals from the sales data
FIGURE 14.13 A table of departmental budgets
FIGURE 14.14 A list of invoices
Chapter 15
FIGURE 15.1 Financial statement for a manufacturing company
FIGURE 15.2 Markup and gross margin percent from a single product
FIGURE 15.3 An income statement with EBIT and EBITDA calculations
FIGURE 15.4 Calculating cost of goods sold
FIGURE 15.5 A return on assets calculation
FIGURE 15.6 A break-even calculation
FIGURE 15.7 Calculating the churn rate
FIGURE 15.8 Annual churn rate of monthly recurring revenue
FIGURE 15.9 Customer lifetime value calculation
FIGURE 15.10 Monthly employment changes over one year
FIGURE 15.11 A partial amortization schedule to compute the effective rate
FIGURE 15.12 A simple loan payment calculator
FIGURE 15.13 A partial amortization schedule
FIGURE 15.14 Conditional formatting to hide rows
FIGURE 15.15 A variable rate amortization schedule
FIGURE 15.16 A date-based amortization schedule
FIGURE 15.17 A straight-line depreciation schedule
FIGURE 15.18 An accelerated depreciation schedule
FIGURE 15.19 A present value calculation
FIGURE 15.20 The present value of a series of future payments
FIGURE 15.21 The net present value of expected future cash flows
FIGURE 15.22 The net present value of both positive and negative cash flows
FIGURE 15.23 The internal rate of return of a series of future cash flows
FIGURE 15.24 The internal rate of return of nonperiodic cash flows
FIGURE 15.25 Four years of monthly sales data
FIGURE 15.26 The Create Forecast Worksheet dialog box
FIGURE 15.27 The forecast worksheet contains a table and a chart.
Chapter 16
FIGURE 16.1 An investment portfolio with rates of return
FIGURE 16.2 Expanding a weighted average calculation into adjacent cells
FIGURE 16.3 A partial listing of golf scores
FIGURE 16.4 A graph of raw golf scores over time
FIGURE 16.5 The moving average is charted over the raw scores.
FIGURE 16.6 The demand of a product over 26 weeks
FIGURE 16.7 Demand of a product visualized
FIGURE 16.8 Average low temperatures by month
FIGURE 16.9 The results of a bowling tournament
FIGURE 16.10 The results of a race
FIGURE 16.11 A list of students and grades
FIGURE 16.12 The grades listing sorted by grade
FIGURE 16.13 Identifying quartiles for product defects
FIGURE 16.14 Sorted data and demarcation lines
FIGURE 16.15 Identifying outliers using a leveraged interquartile range
FIGURE 16.16 Leveraged interquartile ranges expand the fences outward.
FIGURE 16.17 Calculating the frequency with custom bins
FIGURE 16.18 Using the COUNTIFS function to create a frequency distribution
Chapter 17
FIGURE 17.1 The cells in this table are conditionally formatted to show a red b...
FIGURE 17.2 Configure the New Formatting Rule dialog box to apply the needed fo...
FIGURE 17.3 The cells in this table are conditionally formatted to show a red b...
FIGURE 17.4 Configure the New Formatting Rule dialog box to apply the needed fo...
FIGURE 17.5 You can conditionally format the values that exist in one list but ...
FIGURE 17.6 Configure the New Formatting Rule dialog box to apply the needed fo...
FIGURE 17.7 You can conditionally format the values that exist in both lists
FIGURE 17.8 Configure the New Formatting Rule dialog box to apply the needed fo...
FIGURE 17.9 You can conditionally format any weekend dates in a list of dates....
FIGURE 17.10 Configure the New Formatting Rule dialog box to apply the needed f...
FIGURE 17.11 You can conditionally format dates that fall between a start and e...
FIGURE 17.12 Configure the New Formatting Rule dialog box to apply the needed f...
FIGURE 17.13 You can conditionally format dates based on due date.
FIGURE 17.14 Configure the New Formatting Rule dialog box to apply the needed f...
Chapter 18
FIGURE 18.1 Column D contains formulas to calculate the total for each product....
FIGURE 18.2 The array formula in cell C9 calculates the total sales without usi...
FIGURE 18.3 A 3 × 4 array entered into a range of cells
FIGURE 18.4 A 3 × 4 array entered into a 10 × 5 cell range
FIGURE 18.5 Creating a named array constant
FIGURE 18.6 Using a named array constant in an array formula
FIGURE 18.7 Excel's warning message reminds you that you can't edit just one ce...
FIGURE 18.8 Creating an array from a range
FIGURE 18.9 After you press F9, the Formula bar displays the array constant.
FIGURE 18.10 Performing a mathematical operation on an array
FIGURE 18.11 Multiplying each array element by itself
FIGURE 18.12 Using the TRANSPOSE function to transpose a rectangular array
FIGURE 18.13 Using an array formula to generate consecutive integers
FIGURE 18.14 The goal is to count the number of characters in a range of text....
FIGURE 18.15 An array formula returns the sum of the three smallest values in A...
FIGURE 18.16 An array formula returns the number of text cells in the range.
FIGURE 18.17 Without an array formula, calculating the average change requires ...
FIGURE 18.18 You can replace the lookup table in D1:E10 with an array constant....
Chapter 19
FIGURE 19.1 Excel can check your formulas for potential errors.
FIGURE 19.2 The Evaluate Formula dialog box shows a formula being calculated on...
FIGURE 19.3 Use the Find and Replace dialog box to locate information in a work...
FIGURE 19.4 Use the AutoCorrect dialog box to control the spelling corrections ...
Chapter 20
FIGURE 20.1 A simple column chart depicts the sales volume for each month.
FIGURE 20.2 This line chart displays two data series.
FIGURE 20.3 Activating a chart displays additional tabs on the Excel Ribbon.
FIGURE 20.4 A chart on a chart sheet
FIGURE 20.5 Parts of a chart
FIGURE 20.6 A pie chart
FIGURE 20.7 A 3-D column chart
FIGURE 20.8 This data would make a good chart.
FIGURE 20.9 The icons in the Insert ⇨ Charts group expand to show a gallery of ...
FIGURE 20.10 A column chart with two data series
FIGURE 20.11 The column chart, after swapping the row/column orientation
FIGURE 20.12 The Change Chart Type dialog box
FIGURE 20.13 One-click design variations of a column chart
FIGURE 20.14 Using the Format Data Series task pane
FIGURE 20.15 Use the Move Chart dialog box to move an embedded chart to a chart...
FIGURE 20.16 The same data, plotted by using six chart types
FIGURE 20.17 This clustered column chart compares monthly sales for two product...
FIGURE 20.18 This stacked column chart displays sales by product and depicts th...
FIGURE 20.19 This 100% stacked column chart displays monthly sales as a percent...
FIGURE 20.20 3-D column charts.
FIGURE 20.21 If you have lengthy category labels, a bar chart may be a good cho...
FIGURE 20.22 A line chart often can help you spot trends in your data.
FIGURE 20.23 This line chart displays three series.
FIGURE 20.24 This 3-D line chart does not present the data very well.
FIGURE 20.25 A pie chart with one slice exploded
FIGURE 20.26 A bar of pie chart that shows detail for one of the pie slices
FIGURE 20.27 An XY chart shows the relationship between two variables.
FIGURE 20.28 A stacked area chart
FIGURE 20.29 This 3-D area chart is not a good choice.
FIGURE 20.30 Plotting ski sales using a radar chart with 12 categories and two ...
FIGURE 20.31 A surface chart
FIGURE 20.32 A bubble chart
FIGURE 20.33 The four stock chart subtypes
FIGURE 20.34 Plotting temperature data with a stock chart
FIGURE 20.35 Displaying a student grade distribution using a histogram chart
FIGURE 20.36 A Pareto chart displays the number of complaints graphically.
FIGURE 20.37 A waterfall chart showing positive and negative net cash flows
FIGURE 20.38 A box & whisker chart that summarizes data for four groups
FIGURE 20.39 A sunburst chart that depicts a music collection by genre and subg...
FIGURE 20.40 A treemap chart that depicts a music collection by genre and subge...
FIGURE 20.41 A Funnel chart visualizing the value in each stage of a sales pipe...
FIGURE 20.42 Map charts are ideal for visualizing location-based data.
FIGURE 20.43 Map charts come with unique customization options.
Chapter 21
FIGURE 21.1 The Chart Elements control (in the upper-left corner) displays the ...
FIGURE 21.2 Using the Chart Elements control in the Mini toolbar
FIGURE 21.3 Use the Format task pane to set the properties of a selected chart ...
FIGURE 21.4 Chart customization buttons
FIGURE 21.5 The Chart Area element uses No Fill, so the underlying cells are vi...
FIGURE 21.6 Reducing the size of the Plot Area makes room for the shape.
FIGURE 21.7 Use the Select Data Source dialog box to change the name of a data ...
FIGURE 21.8 Using shapes as callouts in lieu of a legend
FIGURE 21.9 The Format Axis task pane for a value axis
FIGURE 21.10 These two charts show the same data but use different value axis b...
FIGURE 21.11 The right chart uses the Values in Reverse Order option
FIGURE 21.12 These charts display the same data, but the bottom chart uses a lo...
FIGURE 21.13 The chart on the right uses display units of thousands.
FIGURE 21.14 Excel offers a great deal of flexibility in how you can display ax...
FIGURE 21.15 Some of the options available for a category axis
FIGURE 21.16 Excel recognizes dates and creates a time-based category axis
FIGURE 21.17 Overriding the Excel time-based category axis
FIGURE 21.18 Excel determines the way to display category axis labels.
FIGURE 21.19 This chart uses three columns of text for the category axis labels...
FIGURE 21.20 Changing a chart's data series by dragging the range outline
FIGURE 21.21 The Edit Series dialog box
FIGURE 21.22 These charts use data labels and don't display axes.
FIGURE 21.23 Data labels linked to text in an arbitrary range
FIGURE 21.24 Three options for dealing with missing data
FIGURE 21.25 This line chart series displays error bars based on percentage.
FIGURE 21.26 Applying a trend line
FIGURE 21.27 The trendline depicts the relationship between height and weight....
FIGURE 21.28 The Precipitation series is barely visible.
FIGURE 21.29 Using the Change Chart dialog box to convert a chart into a combin...
FIGURE 21.30 This combination chart includes a data table that displays the val...
Chapter 22
FIGURE 22.1 Three groups of Sparklines
FIGURE 22.2 Data to be summarized with Sparklines
FIGURE 22.3 Use the Create Sparklines dialog box to specify the data range and ...
FIGURE 22.4 Column Sparklines summarize the precipitation data for nine cities....
FIGURE 22.5 A Sparkline at various sizes
FIGURE 22.6 The Hidden and Empty Cell Settings dialog box
FIGURE 22.7 Highlighting options for Line Sparklines
FIGURE 22.8 The bottom group of Sparklines shows the effect of using the same a...
FIGURE 22.9 Sparklines display the number of pages read per month.
FIGURE 22.10 Using Win/Loss Sparklines to display goal achievement
FIGURE 22.11 The axis in the Sparklines represents the goal.
FIGURE 22.12 The Sparkline displays the values as if they are at equal time int...
FIGURE 22.13 After specifying a date axis, the Sparkline shows the values accur...
FIGURE 22.14 Creating a Sparkline from data in a table
FIGURE 22.15 Using a dynamic range name to display only the last seven data poi...
Chapter 23
FIGURE 23.1 The Type input box allows you to customize the syntax for the numbe...
FIGURE 23.2 Formatting numbers applies only a cosmetic look. Look in the formul...
FIGURE 23.3 Custom number formatting that shows zeros as
n/a
FIGURE 23.4 Dates and times can also be formatted using the Format Cells dialog...
FIGURE 23.5 Use symbols to add an extra layer of analysis to charts.
FIGURE 23.6 Our starting data with a holding cell for our symbols
FIGURE 23.7 Use the Symbol dialog box to insert the desired symbols into your h...
FIGURE 23.8 Copy the newly inserted symbols to the Clipboard.
FIGURE 23.9 Create a custom number format using the symbols.
FIGURE 23.10 Your symbols are now part of your number formatting.
FIGURE 23.11 The Shapes gallery
FIGURE 23.12 The Microsoft Office Icons Library
FIGURE 23.13 Shapes and icons have different contextual tabs.
FIGURE 23.14 Peekaboo tab
FIGURE 23.15 Deconstructed view of the peekaboo tab
FIGURE 23.16 A visual banner made with shapes
FIGURE 23.17 Combine shapes with a chart to save dashboard real estate.
FIGURE 23.18 Use the Edit Points feature to construct your own shape.
FIGURE 23.19 Using a newly constructed shape to create custom infographic eleme...
FIGURE 23.20 Text Box shapes can be linked to cells.
FIGURE 23.21 Pasting a linked picture
FIGURE 23.22 Using linked pictures to enhance visualizations
FIGURE 23.23 Inserting a SmartArt graphic
FIGURE 23.24 Entering text for an organizational chart
FIGURE 23.25 An equation created by the Equation Editor
Chapter 25
FIGURE 25.1 Filtering by file extension in the Open dialog box
FIGURE 25.2 Excel's title bar displays the opened file's name.
FIGURE 25.3 Enabling the legacy import wizard
FIGURE 25.4 This CSV file will be imported.
FIGURE 25.5 Step 1 of the Text Import Wizard
FIGURE 25.6 Select the delimiter in step 2 of the Text Import Wizard.
FIGURE 25.7 Using the Import Data dialog box to import a CSV file
FIGURE 25.8 This range contains data imported directly from a CSV file.
FIGURE 25.9 Use the Remove Duplicates dialog box to delete duplicate rows.
FIGURE 25.10 Using formulas to identify duplicate rows
FIGURE 25.11 The imported data was put in one column rather than multiple colum...
FIGURE 25.12 The first dialog box in the Convert Text to Columns Wizard
FIGURE 25.13 The goal is to extract the numbers in column A.
FIGURE 25.14 Using manually entered examples in B1 and B2, Excel's Flash Fill f...
FIGURE 25.15 After you enter an example of a decimal number, Excel gets all of ...
FIGURE 25.16 Using Flash Fill to split names
FIGURE 25.17 Using a lookup table to classify ages into age ranges
FIGURE 25.18 Using a lookup table to assign a region for a state
FIGURE 25.19 The
TEXTJOIN
function inserts delimiters between cell values.
FIGURE 25.20 The goal is to identify member numbers that are in the Resigned Me...
FIGURE 25.21 Vertical data that needs to be converted to three columns
FIGURE 25.22 Headers that are used to convert the vertical data into rows
FIGURE 25.23 A single formula transforms the vertical data into rows.
FIGURE 25.24 This report contains gaps in the Sales Rep column.
FIGURE 25.25 The gaps are gone, and this list can now be sorted.
FIGURE 25.26 To replace only the second hyphen in these cells, Find and Replace...
FIGURE 25.27 The Trailing Minus for Negative Numbers option makes it easy to fi...
Chapter 26
FIGURE 26.1 Displaying a message when the user makes an invalid entry
FIGURE 26.2 The three tabs of the Data Validation dialog box
FIGURE 26.3 Excel can draw circles around invalid entries (in this case, cells ...
FIGURE 26.4 This drop-down list (with an Input Message) was created using data ...
FIGURE 26.5 Entering a data validation formula
FIGURE 26.6 Using data validation to prevent duplicate entries in a range
FIGURE 26.7 Using data validation to ensure that the sum of a range does not ex...
FIGURE 26.8 The items displayed in the list in cell F2 depend on the list item ...
FIGURE 26.9 Data validation can be used to show messages to the user.
FIGURE 26.10 Data Validation dialog box
Chapter 27
FIGURE 27.1 A simple sales summary with subtotals
FIGURE 27.2 The worksheet after creating an outline
FIGURE 27.3 The worksheet after collapsing the outline to the second level
FIGURE 27.4 The worksheet after adding a column outline
FIGURE 27.5 The worksheet with both outlines collapsed at the second level
FIGURE 27.6 An outline of this book, created manually
FIGURE 27.7 Use the Settings dialog box to adjust the position of the outline s...
Chapter 28
FIGURE 28.1 This confirmation message indicates that the workbook you're saving...
FIGURE 28.2 Excel displays this dialog box when you open a workbook that contai...
FIGURE 28.3 The Edit Links dialog box
FIGURE 28.4 Use the Startup Prompt dialog box to specify how Excel handles link...
FIGURE 28.5 Choosing the Add operation in the Paste Special dialog box
FIGURE 28.6 The Consolidate dialog box enables you to specify ranges to consoli...
FIGURE 28.7 Three worksheets to be consolidated
FIGURE 28.8 The result of consolidating the information in three workbooks
FIGURE 28.9 Expanding the outline to show more details
Chapter 29
FIGURE 29.1 This table is used to create a PivotTable.
FIGURE 29.2 A simple PivotTable
FIGURE 29.3 A PivotTable that uses a report filter
FIGURE 29.4 This range is not appropriate for a PivotTable.
FIGURE 29.5 This range contains normalized data and is appropriate for a PivotT...
FIGURE 29.6 A PivotTable created from normalized data
FIGURE 29.7 Selecting a Recommended PivotTable
FIGURE 29.8 In the Create PivotTable dialog box, you tell Excel where the data ...
FIGURE 29.9 Use the PivotTable Fields task pane to build the PivotTable.
FIGURE 29.10 After a few simple steps, the PivotTable shows a summary of the da...
FIGURE 29.11 Two fields are used for row labels.
FIGURE 29.12 The PivotTable is filtered by date.
FIGURE 29.13 This PivotTable shows daily totals for each branch.
FIGURE 29.14 This PivotTable shows new account totals by day of the week.
FIGURE 29.15 This PivotTable uses the Count function to summarize the data.
FIGURE 29.16 This PivotTable counts the number of accounts that fall into each ...
FIGURE 29.17 This PivotTable uses a filter to show only the teller data.
FIGURE 29.18 This PivotTable uses three filters.
Chapter 30
FIGURE 30.1 This table doesn't have any numeric fields, but you can use it to g...
FIGURE 30.2 Changing the PivotTable to show counts and percentages
FIGURE 30.3 A PivotTable with two groups
FIGURE 30.4 PivotTables with options for subtotals and grand totals
FIGURE 30.5 You can use a PivotTable to summarize the sales data by month.
FIGURE 30.6 The PivotTable with Excel's automatic grouping
FIGURE 30.7 Use the Grouping dialog box to group PivotTable items by dates.
FIGURE 30.8 The PivotTable, after grouping by month and year
FIGURE 30.9 This PivotTable shows sales by quarter and by year.
FIGURE 30.10 This PivotTable is grouped by hours.
FIGURE 30.11 Creating a frequency distribution for these test scores is simple....
FIGURE 30.12 The PivotTable and PivotChart show the frequency distribution for ...
FIGURE 30.13 This data demonstrates calculated fields and calculated items.
FIGURE 30.14 This PivotTable was created from the sales data.
FIGURE 30.15 The Insert Calculated Field dialog box
FIGURE 30.16 This PivotTable uses a calculated field.
FIGURE 30.17 The Insert Calculated Item dialog box
FIGURE 30.18 This PivotTable uses calculated items for quarterly totals.
FIGURE 30.19 The PivotTable, after creating two groups and adding subtotals
FIGURE 30.20 Using slicers to filter the data displayed in a PivotTable
FIGURE 30.21 Using slicers to filter a PivotTable by state and by month
FIGURE 30.22 Using a timeline to filter a PivotTable by date
FIGURE 30.23 The formulas in column F reference cells in the PivotTable.
FIGURE 30.24 After expanding the PivotTable, formulas that use the
GETPIVOTDATA
FIGURE 30.25 This data will be used to create a PivotChart.
FIGURE 30.26 This PivotTable summarizes sales by region and by month.
FIGURE 30.27 The PivotChart uses the data displayed in the PivotTable.
FIGURE 30.28 If you modify the PivotTable, the PivotChart is also changed.
FIGURE 30.29 These three tables will be used for a PivotTable, using the data m...
FIGURE 30.30 The PivotTable Fields task pane for a data model
FIGURE 30.31 The Create Relationships dialog box
FIGURE 30.32 Relating the Orders table to the Customers table
FIGURE 30.33 Relating the Customers and Regions tables by state
FIGURE 30.34 A PivotTable based on the data model
FIGURE 30.35 Formatting applied to the data model PivotTable
Chapter 31
FIGURE 31.1 This simple worksheet model uses four input cells to produce the re...
FIGURE 31.2 How a one-input data table is set up
FIGURE 31.3 Preparing to create a one-input data table
FIGURE 31.4 The Data Table dialog box
FIGURE 31.5 The result of the one-input data table
FIGURE 31.6 The setup for a two-input data table
FIGURE 31.7 This worksheet calculates the net profit from a direct-mail promoti...
FIGURE 31.8 Preparing to create a two-input data table
FIGURE 31.9 The result of the two-input data table
FIGURE 31.10 A simple production model to demonstrate Scenario Manager
FIGURE 31.11 Use the Add Scenario dialog box to create a named scenario.
FIGURE 31.12 You enter the values for the scenario in the Scenario Values dialo...
FIGURE 31.13 Selecting a scenario to display
FIGURE 31.14 A Scenario Summary report produced by Scenario Manager
Chapter 32
FIGURE 32.1 A mortgage calculator with input cells and formula cells
FIGURE 32.2 The Goal Seek dialog box
FIGURE 32.3 Goal Seek has found a solution.
FIGURE 32.4 Use Solver to determine the number of units to maximize the total p...
FIGURE 32.5 The Solver Parameters dialog box
FIGURE 32.6 The Add Constraint dialog box
FIGURE 32.7 Solver displays this dialog box when it finds a solution to the pro...
FIGURE 32.8 One of three reports that Solver can produce
FIGURE 32.9 You can control many aspects of the way Solver solves a problem.
FIGURE 32.10 Solver will attempt to solve this series of linear equations.
FIGURE 32.11 Solver finds a solution to the linear equations.
FIGURE 32.12 This worksheet determines the least expensive way to ship products...
FIGURE 32.13 The solution that was created by Solver
FIGURE 32.14 Using Solver to maximize profit when resources are limited
FIGURE 32.15 Solver determined how to use the resources to maximize the total p...
FIGURE 32.16 This worksheet is set up to maximize a credit union's investments,...
FIGURE 32.17 The results of the portfolio optimization
Chapter 33
FIGURE 33.1 Select your tool from the Data Analysis dialog box.
FIGURE 33.2 Specifying parameters for a single-factor analysis of variance
FIGURE 33.3 The Correlation dialog box
FIGURE 33.4 Descriptive statistics output
FIGURE 33.5 Output from the F-test tool
FIGURE 33.6 Use the histogram tool to generate distributions and graphical outp...
FIGURE 33.7 A chart produced from data generated by the moving average tool
FIGURE 33.8 This dialog box enables you to generate a wide variety of random nu...
FIGURE 33.9 The Regression dialog box
FIGURE 33.10 Output from the paired T-test dialog box
Chapter 34
FIGURE 34.1 Use the Protect Sheet dialog box to protect a worksheet.
FIGURE 34.2 Use the Protection tab in the Format Cells dialog box to change the...
FIGURE 34.3 Excel warns you if you attempt to change a locked cell.
FIGURE 34.4 The Allow Users to Edit Ranges dialog box
FIGURE 34.5 Specify a workbook password in the Encrypt Document dialog box.
FIGURE 34.6 Opening this workbook requires a password.
FIGURE 34.7 The Protect Structure and Windows dialog box
FIGURE 34.8 Protecting a VBA Project with a password
FIGURE 34.9 The Document Inspector dialog box identifies hidden and personal in...
Chapter 35
FIGURE 35.1 The Power Pivot Ribbon interface
FIGURE 35.2 We want to use Power Pivot to analyze the data in the Customers, In...
FIGURE 35.3 Convert your data range into an Excel table.
FIGURE 35.4 Give your newly created Excel table a friendly name.
FIGURE 35.5 The Power Pivot window shows all the data that currently exists in ...
FIGURE 35.6 Each table you add to the data model will be placed on its own tab ...
FIGURE 35.7 The Diagram view allows you to see all the tables in your data mode...
FIGURE 35.8 To create a relationship, you simply click and drag a line between ...
FIGURE 35.9 When you create relationships, the Power Pivot diagram will show jo...
FIGURE 35.10 Use the Manage Relationships dialog box to edit or delete existing...
FIGURE 35.11 Use the Edit Relationship dialog box to adjust the tables and fiel...
FIGURE 35.12 You now have a Power Pivot–driven PivotTable that aggregates acros...
FIGURE 35.13 Activate the Table Import Wizard and select Microsoft SQL Server....
FIGURE 35.14 Choose to select from a list of tables and views.
FIGURE 35.15 The Table Import Wizard will display a list of tables and views.
FIGURE 35.16 The Preview & Filter screen allows you to exclude columns and ...
FIGURE 35.17 The last screen of the Table Import Wizard shows you the progress ...
FIGURE 35.18 Activate the Table Import Wizard and select Excel File.
FIGURE 35.19 Select the worksheets you want to import.
FIGURE 35.20 You can copy data straight out of Microsoft Word.
FIGURE 35.21 The Paste Preview dialog box gives you a chance to see what you're...
FIGURE 35.22 The Queries & Connections task pane
FIGURE 35.23 The Connection Properties dialog box lets you configure the chosen...
FIGURE 35.24 Use the Existing Connections dialog box to reconfigure your Power ...
Chapter 36
FIGURE 36.1 This table shows transactions by employee number.
FIGURE 36.2 This table provides information on employees: first name, last name...
FIGURE 36.3 The Existing Connection dialog box lists all available Table object...
FIGURE 36.4 Use the Import Data dialog box to add the Table object to the inter...
FIGURE 36.5 You can explicitly choose the internal data model as the source for...
FIGURE 36.6 Creating a new PivotTable on a new worksheet
FIGURE 36.7 Select All in the PivotTable Fields list to see both tables in your...
FIGURE 36.8 When Excel prompts you, choose to create the relationship between t...
FIGURE 36.9 Build the appropriate relationship using the Table and Column drop-...
FIGURE 36.10 You have achieved your goal of showing sales by job title.
FIGURE 36.11 The Manage Relationships dialog box enables you to make changes to...
FIGURE 36.12 Use the Queries & Connections task pane to remove any table fr...
Chapter 37
FIGURE 37.1 Start your calculated column by entering your desired operation in ...
FIGURE 37.2 Calculated columns automatically show up in your PivotTable Fields ...
FIGURE 37.3 You can use the formatting tools found on the Power Pivot window's ...
FIGURE 37.4 The new Gross Margin calculation uses the previously created [Total...
FIGURE 37.5 Right-click and select Hide from Client Tools.
FIGURE 37.6 Hidden columns are grayed out, and calculated columns have a darker...
FIGURE 37.7 The Insert Function dialog box shows you all available DAX function...
FIGURE 37.8 The DAX
SUM
function can only sum the column as a whole.
FIGURE 37.9 DAX functions can help enhance the InvoiceHeader data with Year and...
FIGURE 37.10 Using DAX functions to supplement a table with Year, Month, and Mo...
FIGURE 37.11 DAX calculations are immediately available in any connected PivotT...
FIGURE 37.12 Month names in Power Pivot–driven PivotTables don't automatically ...
FIGURE 37.13 The Sort by Column dialog box lets you define how your columns are...
FIGURE 37.14 Your month names now show in correct month order.
FIGURE 37.15 The Discount Amount value in the Customers table can be used in a ...
FIGURE 37.16 Use the
RELATED
function to look up a field from another table.
FIGURE 37.17 The final Discounted Revenue calculated column using the Discount%...
FIGURE 37.18 Creating a new calculated measure
FIGURE 37.19 Calculated measures can be seen in the PivotTable Fields list.
FIGURE 37.20 The Manage Measures dialog box lets you edit or delete your calcul...
FIGURE 37.21 Select the Convert to Formulas option to convert your PivotTable t...
FIGURE 37.22 These cells are now a series of Cube functions!
FIGURE 37.23 Excel gives you the option of converting your report filter fields...
Chapter 38
FIGURE 38.1 Starting a Power Query web query
FIGURE 38.2 Enter the target URL containing the data you need.
FIGURE 38.3 Select the correct data source and then click the Edit button.
FIGURE 38.4 The Power Query Editor window allows you to shape, clean, and trans...
FIGURE 38.5 Right-click the Date column and choose to change the data type to a...
FIGURE 38.6 Select the columns you want to keep and then select Remove Other Co...
FIGURE 38.7 You can click the Table Actions icon to select actions (such as Rem...
FIGURE 38.8 The Power Query Editor can be used to apply transformation actions ...
FIGURE 38.9 The Load To dialog box gives you more control over how the results ...
FIGURE 38.10 Your final query pulled from the Internet: transformed, put into a...
FIGURE 38.11 Query steps can be viewed and managed in the Applied Steps section...
FIGURE 38.12 Right-click any query step to edit, rename, delete, or move the st...
FIGURE 38.13 Right-click any query in the Queries & Connections pane to see...
FIGURE 38.14 Right-click any column to see the column-level actions that you ca...
FIGURE 38.15 Click the Table Actions icon in the upper-left corner of the Power...
FIGURE 38.16 Power Query has the ability to connect to a wide array of text, da...
FIGURE 38.17 Select the data sources with which you want to work and then click...
FIGURE 38.18 CSV files are brought into the Power Query Editor where you can ap...
FIGURE 38.19 Other systems Power Query can utilize as data sources
FIGURE 38.20 Edit a data source by selecting it and clicking the Edit button.
FIGURE 38.21 The credentials edit screen for your selected data source
Chapter 39
FIGURE 39.1 Are there duplicate records in this table? It depends on how you de...
FIGURE 39.2 Removing duplicate records
FIGURE 39.3 Undo the removal of records by deleting the Removed Duplicates step...
FIGURE 39.4 Replacing null
FIGURE 39.5 Replacing empty strings with the word
Undefined
FIGURE 39.6 Merging the Type and Code fields
FIGURE 39.7 The Merge Columns dialog box
FIGURE 39.8 The original columns are removed and replaced with a new merged col...
FIGURE 39.9 Reformatting the ContactName field to proper case
FIGURE 39.10 Replacing text values
FIGURE 39.11 Leading spaces can cause issues in analysis.
FIGURE 39.12 The Trim command
FIGURE 39.13 The Extract command allows you to pull out parts of the text found...
FIGURE 39.14 Extracting the first three characters of the Phone field
FIGURE 39.15 Extracting the two middle characters of the SicCode
FIGURE 39.16 The Split Column command can easily split the ContactName field in...
FIGURE 39.17 Splitting the ContactName column at every occurrence of a comma
FIGURE 39.18 The ContactName field has successfully been split into three colum...
FIGURE 39.19 Matrix layouts are problematic for data analysis.
FIGURE 39.20 All months are now in a tabular format.
FIGURE 39.21 Use Unpivot Other Columns when the number of matrix columns will b...
FIGURE 39.22 Pivoting the Month and Revenue columns
FIGURE 39.23 Confirm the aggregation operation to finalize the pivot transforma...
FIGURE 39.24 Adding a custom column
FIGURE 39.25 The Custom Column dialog box
FIGURE 39.26 A formula to merge the Type and Code columns
FIGURE 39.27 Use the Data Type drop-down to discover and select the data type f...
FIGURE 39.28 A formula to create a consistent CompanyNumber padded with 10 digi...
FIGURE 39.29 Applying an
if
statement in a custom column
FIGURE 39.30 The Group By dialog configured to sum potential revenue by State a...
FIGURE 39.31 The resulting aggregate view by State and City
Chapter 40
FIGURE 40.1 This data can be used as the source for various levels of aggregate...
FIGURE 40.2 Merge the Last_Name and First_Name columns to create a new Employee...
FIGURE 40.3 Group the Employee field and sum the Sales Amount column to create ...
FIGURE 40.4 All of the query steps before Grouped Rows are needed to prepare th...
FIGURE 40.5 Naming the new query SalesByBusiness
FIGURE 40.6 Your two queries are now sharing the extracted steps.
FIGURE 40.7 The data found on each region tab needs to be consolidated into one...
FIGURE 40.8 Create a connection-only query for each region.
FIGURE 40.9 Appending multiple queries to NorthData
FIGURE 40.10 The final consolidated table of all regional data
FIGURE 40.11 The kinds of joins supported by Power Query
FIGURE 40.12 You need to merge the Questions and Answers queries into one table...
FIGURE 40.13 Activating the Merge dialog box
FIGURE 40.14 The completed Merge dialog box
FIGURE 40.15 Expand the NewColumn field, and choose the merged fields you want ...
FIGURE 40.16 The final table with merged questions and answers
FIGURE 40.17 Click the gear icon next to the Source query step to reactivate th...
Chapter 41
FIGURE 41.1 Hover over a query to get quick information including sneak peeks o...
FIGURE 41.2 Queries can be organized into groups.
FIGURE 41.3 Group-level options
FIGURE 41.4 Use the Choose Columns command to find and select columns faster.
FIGURE 41.5 Right-click query steps to rename them.
FIGURE 41.6 Create a new query from an existing column.
FIGURE 41.7 Use the Global Data Load options to set a default load behavior.
FIGURE 41.8 Power Query automatically adds a step to change data types when dat...
FIGURE 41.9 Disabling the Type Detection feature
FIGURE 41.10 Disabling the privacy level settings
Chapter 42
FIGURE 42.1 The Developer tab
FIGURE 42.2 The Macro Settings section of the Trust Center dialog box
FIGURE 42.3 Excel displays a security warning if a workbook contains macros.
FIGURE 42.4 Excel warns you if your workbook contains macros and you attempt to...
FIGURE 42.5 A simple VBA procedure
FIGURE 42.6 This VBA function returns the cube root of its argument.
FIGURE 42.7 The Record Macro dialog box
FIGURE 42.8 The MyName procedure was generated by the Excel macro recorder.
FIGURE 42.9 The TimeStamp procedure was generated by the Excel macro recorder....
FIGURE 42.10 This TimeStamp macro works correctly.
FIGURE 42.11 Use the Macro Options dialog box to add or change a shortcut key f...
FIGURE 42.12 Adding a button to a worksheet so that it can be used to execute a...
FIGURE 42.13 The ListFormulas macro creates a list of all formulas in a workshe...
Chapter 43
FIGURE 43.1 A simple custom worksheet function
FIGURE 43.2 Creating a worksheet formula that uses a custom function
FIGURE 43.3 Entering a description for a custom function. This description appe...
FIGURE 43.4 Using the Function Arguments dialog box to insert a custom function...
Chapter 44
FIGURE 44.1 A UserForm that asks the user to select an option
FIGURE 44.2 This dialog box is displayed by the VBA
InputBox
function.
FIGURE 44.3 A simple message box, displayed with the VBA
MsgBox
function
FIGURE 44.4 The second argument of the
MsgBox
function determines what appears ...
FIGURE 44.5 A message box with a longer message and a title
FIGURE 44.6 An empty UserForm
FIGURE 44.7 The Properties window for a CommandButton control
FIGURE 44.8 A Label control, after changing its Font properties
FIGURE 44.9 The code module for the UserForm
FIGURE 44.10 The UserForm after adding controls and adjusting some properties
FIGURE 44.11 Displaying the UserForm
FIGURE 44.12 Adjusting the tab order in a UserForm
Chapter 45
FIGURE 45.1 This worksheet uses UserForm controls.
FIGURE 45.2 Excel's two sets of worksheet controls
FIGURE 45.3 Use the Properties window to adjust the properties of a control—in ...
FIGURE 45.4 Double-clicking a control in Design mode activates the VBE and ente...
FIGURE 45.5 A
ComboBox
control
FIGURE 45.6 This worksheet has three
ScrollBar
controls.
FIGURE 45.7 A
TextBox
control with a vertical scrollbar
Chapter 46
FIGURE 46.1 The best way to create an event procedure is to let the VBE do it f...
FIGURE 46.2 Selecting a cell causes the active cell's row and column to become ...
Chapter 47
FIGURE 47.1 This range can consist of any number of rows.
FIGURE 47.2 Using the VBA
InputBox
function to get a value from the user
FIGURE 47.3 You can instruct Excel not to display these types of alerts while a...
Chapter 48
FIGURE 48.1 The Add-Ins dialog box
FIGURE 48.2 This dialog box enables the user to change the case of text in the ...
FIGURE 48.3 The custom dialog box
FIGURE 48.4 Adding descriptive information about your add-in
FIGURE 48.5 The Protection tab of the Project Properties dialog box
Guide
Cover
Table of Contents
Begin Reading
Pages
i
ii
iii
v
vii
ix
xli
xlii
xliii
xliv
xlv
1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
