Introduction xxxix
Part I: Getting Started with Excel 1
Chapter 1: Introducing Excel 3
Understanding What Excel Is Used For 3
Understanding Workbooks and Worksheets 4
Moving around a Worksheet 4
Navigating with your keyboard 7
Navigating with your mouse 8
Using the Ribbon 9
Ribbon tabs 9
Contextual tabs 11
Types of commands on the Ribbon 12
Accessing the Ribbon by using your keyboard 12
Using Shortcut Menus 15
Customizing Your Quick Access Toolbar 16
Working with Dialog Boxes 18
Navigating dialog boxes 18
Using tabbed dialog boxes 19
Using Task Panes 20
Creating Your First Excel Workbook 20
Getting started on your worksheet 20
Filling in the month names 21
Entering the sales data 22
Formatting the numbers 23
Making your worksheet look a bit fancier 23
Summing the values 24
Creating a chart 25
Printing your worksheet 25
Saving your workbook 26
Chapter 2: Entering and Editing Worksheet Data 27
Exploring Data Types 27
Numeric values 27
Text entries 28
Formulas 29
Error values 29
Entering Text and Values into Your Worksheets 30
Entering numbers 30
Entering text 30
Using Enter mode 31
Entering Dates and Times into Your Worksheets 31
Entering date values 31
Entering time values 32
Modifying Cell Contents 32
Deleting the contents of a cell 32
Replacing the contents of a cell 33
Editing the contents of a cell 33
Learning some handy data-entry techniques 35
Automatically moving the selection after entering data 35
Selecting a range of input cells before entering data 36
Using Ctrl+Enter to place information into multiple cells simultaneously 36
Changing modes 36
Entering decimal points automatically 36
Using AutoFill to enter a series of values 37
Using AutoComplete to automate data entry 37
Forcing text to appear on a new line within a cell 38
Using AutoCorrect for shorthand data entry 39
Entering numbers with fractions 40
Using a form for data entry 40
Entering the current date or time into a cell 41
Applying Number Formatting 42
Using automatic number formatting 43
Formatting numbers by using the Ribbon 43
Using shortcut keys to format numbers 43
Formatting numbers by using the Format Cells dialog box 45
Adding your own custom number formats 47
Using Excel on a Tablet 47
Exploring Excels tablet interface 48
Entering formulas on a tablet 49
Introducing the Draw Ribbon 49
Chapter 3: Performing Basic Worksheet Operations 53
Learning the Fundamentals of Excel Worksheets 53
Working with Excel windows 53
Moving and resizing windows 54
Switching among windows 55
Closing windows 55
Activating a worksheet 56
Adding a new worksheet to your workbook 56
Deleting a worksheet you no longer need 57
Changing the name of a worksheet 57
Changing a sheet tab color 58
Rearranging your worksheets 58
Hiding and unhiding a worksheet 60
Controlling the Worksheet View 60
Zooming in or out for a better view 60
Viewing a worksheet in multiple windows 61
Comparing sheets side by side 62
Splitting the worksheet window into panes 63
Keeping the titles in view by freezing panes 63
Monitoring cells with a Watch Window 65
Working with Rows and Columns 66
Selecting rows and columns 66
Inserting rows and columns 66
Deleting rows and columns 68
Changing column widths and row heights 68
Changing column widths 69
Changing row heights 69
Hiding rows and columns 70
Chapter 4: Working with Excel Ranges and Tables 73
Understanding Cells and Ranges 73
Selecting ranges 74
Selecting complete rows and columns 75
Selecting noncontiguous ranges 75
Selecting multi-sheet ranges 76
Selecting special types of cells 79
Selecting cells by searching 81
Copying or Moving Ranges 83
Copying by using Ribbon commands 84
Copying by using shortcut menu commands 85
Copying by using shortcut keys 85
Copying or moving by using drag-and-drop 87
Copying to adjacent cells 88
Copying a range to other sheets 89
Using the Office Clipboard to paste 89
Pasting in special ways 91
Using the Paste Special dialog box 92
Performing mathematical operations without formulas 94
Skipping blanks when pasting 94
Transposing a range 94
Using Names to Work with Ranges 95
Creating range names in your workbooks 96
Using the Name box 96
Using the New Name dialog box 96
Using the Create Names from Selection dialog box 97
Managing names 99
Adding Comments to Cells 100
Showing comments 101
Replying to comments 102
Editing comments and replies 102
Deleting comments and replies 103
Resolving comment threads 103
Adding Notes to Cells 104
Showing notes 105
Formatting notes 106
Editing notes 108
Deleting notes 108
Working with Tables 108
Understanding a tables structure 108
The header row 109
The data body 109
The total row 109
The resizing handle 110
Creating a table 110
Adding data to a table 111
Sorting and filtering table data 111
Sorting a table 112
Filtering a table 114
Filtering a table with slicers 116
Changing the tables appearance 117
Chapter 5: Formatting Worksheets 121
Getting to Know the Formatting Tools 121
Using the formatting tools on the Home tab 122
Using the Mini toolbar 123
Using the Format Cells dialog box 124
Formatting Your Worksheet 124
Using fonts to format your worksheet 124
Changing text alignment 127
Choosing horizontal alignment options 127
Choosing vertical alignment options 129
Wrapping or shrinking text to fit the cell 129
Merging worksheet cells to create additional text space 129
Displaying text at an angle 131
Using colors and shading 131
Adding borders and lines 132
Using Conditional Formatting 135
Specifying conditional formatting 135
Using graphical conditional formats 135
Using data bars 135
Using color scales 137
Using icon sets 138
Creating formula-based rules 139
Understanding relative and absolute references 141
Conditional formatting formula examples 142
Identifying weekend days 142
Highlighting a row based on a value 142
Displaying alternate-row shading 143
Creating checkerboard shading 144
Shading groups of rows 144
Working with conditional formats 144
Managing rules 145
Copying cells that contain conditional formatting 145
Deleting conditional formatting 146
Locating cells that contain conditional formatting 146
Using Named Styles for Easier Formatting 146
Applying styles 147
Modifying an existing style 147
Creating new styles 149
Merging styles from other workbooks 150
Controlling styles with templates 150
Understanding Document Themes 150
Applying a theme 152
Customizing a theme 153
Chapter 6: Understanding Excel Files and Templates 157
Creating a New Workbook 157
Opening an Existing Workbook 158
Filtering filenames 160
Choosing your file display preferences 161
Saving a Workbook 161
Using AutoRecover 163
Recovering versions of the current workbook 164
Recovering unsaved work 164
Configuring AutoRecover 165
Password-Protecting a Workbook 165
Organizing Your Files 166
Other Workbook Info Options 166
Protect Workbook options 166
Check for Issues options 167
Version History 167
Manage Workbook options 167
Browser View options 168
Compatibility Mode section 168
Closing Workbooks 168
Safeguarding Your Work 168
Working with Templates 169
Exploring Excel templates 169
Viewing templates 169
Creating aworkbook from a template 170
Modifying a template 172
Using default templates 172
Using the workbook template to change workbook defaults 173
Creating a worksheet template 174
Editing your template 174
Resetting the default workbook 174
Using custom workbook templates 174
Creating custom templates 174
Saving your custom templates 175
Using custom templates 176
Chapter 7: Printing Your Work 177
Doing Basic Printing 177
Changing Your Page View 179
Normal view 179
Page Layout view 180
Page Break Preview 181
Adjusting Common Page Setup Settings 183
Choosing your printer 184
Specifying what you want to print 184
Changing page orientation 185
Specifying paper size 185
Printing multiple copies of a report 186
Adjusting the page margins 186
Understanding page breaks 187
Inserting a page break 187
Removing manual page breaks 188
Printing row and column titles 188
Scaling printed output 189
Printing cell gridlines 189
Printing row and column headers 190
Using a background image 190
Adding a Header or a Footer to Your Reports 192
Selecting a predefined header or footer 192
Understanding header and footer element codes 192
Exploring other header and footer options 194
Exploring Other Print-Related Topics 194
Copying Page Setup settings across sheets 195
Preventing certain cells from being printed 195
Preventing objects from being printed 196
Creating custom views of your worksheet 197
Creating PDF files 198
Chapter 8: Customizing the Excel User Interface 199
Customizing the Quick Access Toolbar 199
About the Quick Access Toolbar 200
Adding new commands to the Quick Access Toolbar 201
Other Quick Access Toolbar actions 203
Customizing the Ribbon 204
Why you may want to customize the Ribbon 205
What can be customized 205
How to customize the Ribbon 205
Creating a new tab 206
Creating a new group 206
Adding commands to a new group 207
Resetting the Ribbon 208
Part II: Working with Formulas and Functions 209
Chapter 9: Introducing Formulas and Functions 211
Understanding Formula Basics 211
Using operators in formulas 213
Understanding operator precedence in formulas 214
Using functions in your formulas 216
Examples of formulas that use functions 216
Function arguments 217
More about functions 218
Entering Formulas into Your Worksheets 218
Entering formulas by pointing 220
Pasting range names into formulas 220
Inserting functions into formulas 221
Function entry tips 223
Editing Formulas 224
Using Cell References in Formulas 225
Using relative, absolute, and mixed references 225
Changing the types of your references 227
Referencing cells outside the worksheet 228
Referencing cells in other worksheets 228
Referencing cells in other workbooks 228
Introducing Formula Variables 229
Understanding the LET function 230
Formula variables in action 231
Using Formulas in Tables 232
Summarizing data in a table 232
Using formulas within a table 234
Referencing data in a table 235
Correcting Common Formula Errors 237
Handling circular references 238
Specifying when formulas are calculated 238
Using Advanced Naming Techniques 239
Using names for constants 240
Using names for formulas 240
Using range intersections 241
Applying names to existing references 243
Working with Formulas 244
Not hard-coding values 244
Using the Formula bar as a calculator 244
Making an exact copy of a formula 244
Converting formulas to values 245
Chapter 10: Understanding and Using Array Formulas 247
Understanding Legacy Array Formulas 248
Example of a legacy array formula 248
Editing legacy array formulas 249
Introducing Dynamic Arrays 250
Understanding spill ranges 252
Referencing spill ranges 254
Exploring Dynamic Array Functions 255
The SORT function 256
The SORTBY function 257
The UNIQUE function 258
The RANDARRAY function 259
The SEQUENCE function 260
The FILTER function 262
Using multiple conditions with the FILTER function 263
Filtering records that contain a search term 264
The XLOOKUP function 265
XLOOKUP with wildcards 268
Chapter 11: Using Formulas for Common Mathematical Operations 271
Calculating Percentages 271
Calculating percent of goal 271
Calculating percent variance 272
Calculating percent variance with negative values 273
Calculating a percent distribution 274
Calculating a running total 275
Applying a percent increase or decrease to values 276
Dealing with divide-by-zero errors 277
Rounding Numbers 278
Rounding numbers using formulas 279
Rounding to the nearest penny 279
Rounding to significant digits 280
Counting Values in a Range 282
Using Excels Conversion Functions 283
Chapter 12: Using Formulas to Manipulate Text 285
Working with Text 285
Using Text Functions 286
Joining text strings 286
Setting text to sentence case 288
Removing spaces from a text string 289
Extracting parts of a text string 290
Finding a particular character in a text string 291
Finding the second instance of a character 292
Substituting text strings 293
Counting specific characters in a cell 294
Adding a line break within a formula 295
Cleaning strange characters from text fields 296
Padding numbers with zeros 297
Formatting the numbers in a text string 297
Using the DOLLAR function 299
Chapter 13: Using Formulas with Dates and Times 301
Understanding How Excel Handles Dates and Times 301
Understanding date serial numbers 301
Entering dates 302
Understanding time serial numbers 303
Entering times 304
Formatting dates and times 305
Problems with dates 306
Excels leap year bug 306
Pre-1900
dates 306
Inconsistent date entries 307
Using Excels Date and Time Functions 307
Getting the current date and time 308
Calculating age 308
Calculating the number of days between two dates 309
Calculating the number of workdays between two dates 310
Using NETWORKDAYS.INTL 310
Generating a list of business days excluding holidays 311
Extracting parts of a date 313
Calculating number of years and months between dates 314
Converting dates to Julian date formats 315
Calculating the percent of year completed and remaining 316
Returning the last date of a given month 317
Using the EOMONTH function 318
Calculating the calendar quarter for a date 318
Calculating the fiscal quarter for a date 319
Returning a fiscal month from a date 320
Calculating the date of the Nth weekday of the month 321
Calculating the date of the last weekday of the month 322
Extracting parts of a time 323
Calculating elapsed time 324
Rounding time values 325
Converting decimal hours, minutes, or seconds to a time 326
Adding hours, minutes, or seconds to a time 326
Chapter 14: Using Formulas for Conditional Analysis 329
Understanding Conditional Analysis 329
Checking if a simple condition is met 329
Checking for multiple conditions 330
Validating conditional data 331
Looking up values 332
Checking if Condition1 AND Condition2 are met 333
Referring to logical conditions in cells 334
Checking if Condition1 OR Condition2 are met 335
Performing Conditional Calculations 336
Summing all values that meet a certain condition 336
Summing greater than zero 338
Summing all values that meet two or more conditions 339
Summing if values fall between a given date range 340
Using SUMIFS 341
Getting a count of values that meet a certain condition 341
Getting a count of values that meet two or more conditions 342
Finding nonstandard characters 343
Getting the average of all numbers that meet a certain condition 344
Getting the average of all numbers that meet two or more conditions 344
Chapter 15: Using Formulas for Matching and Lookups 347
Introducing Lookup Formulas 347
Leveraging Excels Lookup Functions 348
Looking up an exact value based on a left lookup column 348
Looking up an exact value based on any lookup column 351
Looking up values horizontally 352
Hiding errors returned by lookup functions 353
Finding the closest match from a list of banded values 354
Finding the closest match with the INDEX and MATCH functions 356
Looking up values from multiple tables 357
Looking up a value based on a two-way matrix 359
Using default values for match 360
Finding a value based on multiple criteria 361
Returning text with SUMPRODUCT 362
Finding the last value in a column 362
Finding the last number using LOOKUP 363
Chapter 16: Using Formulas with Tables and Conditional Formatting 365
Highlighting Cells That Meet Certain Criteria 365
Highlighting cells based on the value of another cell 367
Highlighting Values That Exist in List1 but Not List2 369
Highlighting Values That Exist in List1 and List2 371
Highlighting Based on Dates 372
Highlighting days between two dates 374
Highlighting dates based on a due date 376
Chapter 17: Making Your Formulas Error-Free 379
Finding and Correcting Formula Errors 379
Mismatched parentheses 380
Cells are filled with hash marks 381
Blank cells are not blank 381
Extra space characters 382
Formulas returning an error 382
#DIV/0! errors 383
#N/A errors 383
#NAME? errors 384
#NULL! errors 384
#NUM! errors 384
#REF! errors 385
#SPILL! errors 385
#VALUE! errors 386
Operator precedence problems 386
Formulas are not calculated 387
Problems with decimal precision 387
Phantom link errors 388
Using Excel Auditing Tools 388
Identifying cells of a particular type 388
Viewing formulas 389
Tracing cell relationships 389
Identifying precedents 390
Identifying dependents 390
Tracing error values 391
Fixing circular reference errors 391
Using the background error-checking feature 391
Using Formula Evaluator 392
Searching and Replacing 393
Searching for information 393
Replacing information 394
Searching for formatting 395
Spell-checking
your worksheets 396
Using AutoCorrect 396
Part III: Creating Charts and Other Visualizations 399
Chapter 18: Getting Started with Excel Charts 401
What Is a Chart? 401
How Excel handles charts 402
Embedded charts 403
Chart sheets 404
Parts of a chart 405
Chart limitations 408
Basic Steps for Creating a Chart 408
Creating the chart 408
Switching the row and column orientation 410
Changing the chart type 410
Applying a chart layout 412
Applying a chart style 413
Adding and deleting chart elements 413
Formatting chart elements 413
Modifying and Customizing Charts 414
Moving and resizing a chart 414
Converting an embedded chart to a chart sheet 415
Copying a chart 416
Deleting a chart 416
Adding chart elements 416
Moving and deleting chart elements 416
Formatting chart elements 416
Copying a charts formatting 417
Renaming a chart 418
Printing charts 418
Understanding Chart Types 419
Choosing a chart type 419
Column charts 421
Bar charts 423
Line charts 424
Pie charts 426
XY (scatter) charts 427
Area charts 428
Radar charts 429
Surface charts 430
Bubble charts 430
Stock charts 431
Newer Chart Types for Excel 431
Histogram charts 431
Pareto charts 433
Waterfall charts 434
Box& whisker charts 434
Sunburst charts 436
Treemap charts 437
Funnel charts 437
Map charts 438
Chapter 19: Using Advanced Charting Techniques 441
Selecting Chart Elements 441
Selecting with the mouse 442
Selecting with the keyboard 443
Selecting with the Chart Elements control 443
Exploring the User Interface Choices for Modifying Chart Elements 444
Using the Format task pane 444
Using the chart customization buttons 445
Using the Ribbon 446
Using the Mini toolbar 446
Modifying the Chart Area 447
Modifying the Plot Area 448
Working with Titles in a Chart 449
Working with a Legend 450
Working with Gridlines 452
Modifying the Axes 452
Modifying the value axis 452
Modifying the category axis 456
Working with Data Series 460
Deleting or hiding a data series 461
Adding a new data series to a chart 462
Changing data used by a series 462
Changing the data range by dragging the range outline 463
Using the Edit Series dialog box 463
Editing the Series formula 464
Displaying data labels in a chart 465
Handling missing data 467
Adding error bars 468
Adding a trendline 468
Creating combination charts 470
Displaying a data table 472
Creating Chart Templates 473
Chapter 20: Creating Sparkline Graphics 475
Sparkline Types 475
Creating Sparklines 477
Customizing Sparklines 480
Sizing Sparkline cells 480
Handling hidden or missing data 480
Changing the Sparkline type 481
Changing Sparkline colors and line width 481
Highlighting certain data points 481
Adjusting Sparkline axis scaling 482
Faking a reference line 483
Specifying a Date Axis 484
Auto-Updating Sparklines 486
Displaying a Sparkline for a Dynamic Range 486
Chapter 21: Visualizing with Custom Number Formats and Shapes 489
Visualizing with Number Formatting 489
Doing basic number formatting 489
Using shortcut keys to format numbers 490
Using the Format Cells dialog box to format numbers 491
Getting fancy with custom number formatting 492
Formatting numbers in thousands and millions 494
Hiding and suppressing zeros 495
Applying custom format colors 496
Formatting dates and times 497
Using symbols to enhance reporting 498
Using Shapes and Icons as Visual Elements 502
Inserting a shape 502
Inserting SVG icon graphics 504
Inserting 3D models 504
Formatting shapes and icons 506
Enhancing Excel reports with shapes 507
Creating visually appealing containers with shapes 507
Layering shapes to save space 509
Constructing your own infographic widgets with shapes 509
Creating dynamic labels 510
Creating linked pictures 510
Using SmartArt and WordArt 513
SmartArt basics 513
WordArt basics 514
Working with Other Graphics Types 515
About graphics files 515
Inserting screenshots 516
Displaying a worksheet background image 516
Using the Equation Editor 516
Part IV: Managing and Analyzing Data 519
Chapter 22: Importing and Cleaning Data 521
Importing Data 521
Importing from a file 522
Spreadsheet file formats 522
Database file formats 522
Text file formats 523
HTML files 523
XML files 524
Importing vs opening 524
Importing a text file 525
Copying and pasting data 528
Cleaning Up Data 529
Removing duplicate rows 529
Identifying duplicate rows 530
Splitting text 531
Using Text to Columns 532
Using Flash Fill 533
Changing the case of text 536
Removing extra spaces 537
Removing strange characters 538
Converting values 538
Classifying values 538
Joining columns 540
Rearranging columns 541
Randomizing the rows 541
Extracting a filename from a URL 541
Matching text in a list 542
Changing vertical data to horizontal data 543
Filling gaps in an imported report 545
Checking spelling 547
Replacing or removing text in cells 547
Adding text to cells 548
Fixing trailing minus signs 549
Following a data cleaning checklist 549
Exporting Data 550
Exporting to a text file 550
CSV files 550
TXT files 550
PRN files 551
Exporting to other file formats 551
Chapter 23: Using Data Validation 553
About Data Validation 553
Specifying Validation Criteria 554
Types of Validation Criteria You Can Apply 555
Creating a Drop-Down List 557
Using Formulas for Data Validation Rules 558
Understanding Cell References 559
Data Validation Formula Examples 560
Accepting text only 561
Accepting a larger value than the previous cell 561
Accepting nonduplicate entries only 561
Accepting text that begins with a specific character 561
Accepting dates by the day of the week 562
Accepting only values that dont exceed a total 563
Creating a dependent list 563
Using Data Validation without Restricting Entry 564
Showing an input message 564
Making suggested entries 564
Chapter 24: Creating and Using Worksheet Outlines 567
Introducing Worksheet Outlines 567
Creating an Outline 570
Preparing the data 571
Creating an outline automatically 572
Creating an outline manually 572
Working with Outlines 574
Displaying levels 574
Adding data to an outline 575
Removing an outline 575
Adjusting the outline symbols 575
Hiding the outline symbols 575
Chapter 25: Linking and Consolidating Worksheets 577
Linking Workbooks 577
Creating External Reference Formulas 578
Understanding link formula syntax 578
Creating a link formula by pointing 579
Pasting links 580
Working with External Reference Formulas 580
Creating links to unsaved workbooks 580
Opening a workbook with external reference formulas 581
Changing the startup prompt 582
Updating links 582
Changing the link source 583
Severing links 583
Avoiding Potential Problems with External Reference Formulas 583
Renaming or moving a source workbook 584
Using the Save As command 584
Modifying a source workbook 584
Using Intermediary links 585
Consolidating Worksheets 585
Consolidating worksheets by using formulas 587
Consolidating worksheets by using Paste Special 587
Consolidating worksheets by using the Consolidate dialog box 588
Viewing a workbook consolidation example 590
Refreshing a consolidation 592
Learning more about consolidation 593
Chapter 26: Introducing PivotTables 595
About PivotTables 595
A PivotTable example 596
Data appropriate for a PivotTable 598
Creating a PivotTable Automatically 600
Creating a PivotTable Manually 602
Specifying the data 602
Specifying the location for the PivotTable 603
Laying out the PivotTable 603
Formatting the PivotTable 607
Modifying the PivotTable 609
Seeing More PivotTable Examples 611
What is the daily total new deposit amount for each branch? 611
Which day of the week accounts for the most deposits? 611
How many accounts were opened at each branch, broken down by account type? 613
How much money was used to open the accounts? 613
What types of accounts do tellers open most often? 614
In which branch do tellers open the most checking accounts for new customers? 615
Learning More 616
Chapter 27: Analyzing Data with PivotTables 617
Working with Non-Numeric Data 617
Grouping PivotTable Items 619
Grouping items manually 619
Grouping items automatically 621
Grouping by date 621
Grouping by time 625
Using a PivotTable to Create a Frequency Distribution 626
Creating a Calculated Field or Calculated Item 628
Creating a calculated field 630
Inserting a calculated item 632
Filtering PivotTables with Slicers 635
Filtering PivotTables with a Timeline 637
Referencing Cells within a PivotTable 638
Creating PivotCharts 640
A PivotChart example 640
More about PivotCharts 643
Using the Data Model 644
Chapter 28: Performing Spreadsheet What-If Analysis 651
Looking at a What-If Example 651
Exploring Types of What-If Analyses 653
Performing manual what-if analysis 653
Creating data tables 653
Creating a one-input data table 654
Creating a two-input data table 657
Using Scenario Manager 661
Defining scenarios 662
Displaying scenarios 664
Modifying scenarios 666
Merging scenarios 666
Generating a scenario report 666
Analyzing Data with Artificial Intelligence 668
Using Excels suggestions 668
Querying analyzed data 671
Chapter 29: Analyzing Data Using Goal Seeking and Solver 675
Exploring What-If Analysis, in Reverse 675
Using Single-Cell
Goal Seeking 675
Looking at a goal-seeking example 676
Learning more about goal seeking 678
Introducing Solver 678
Looking at appropriate problems for Solver 679
Seeing a simple Solver example 679
Exploring Solver options 685
Seeing Some Solver Examples 686
Solving simultaneous linear equations 686
Minimizing shipping costs 688
Allocating resources 691
Optimizing an investment portfolio 693
Chapter 30: Analyzing Data with the Analysis ToolPak 697
The Analysis ToolPak: An Overview 697
Installing the Analysis ToolPak Add-In 698
Using the Analysis Tools 698
Introducing the Analysis ToolPak Tools 699
Analysis of variance 699
Correlation 700
Covariance 701
Descriptive statistics 701
Exponential smoothing 701
F-Test (two-sample test for variance) 701
Fourier analysis 702
Histogram 703
Moving average 704
Random number generation 705
Rank and percentile 706
Regression 706
Sampling 707
t-Test 707
z-Test (two-sample test for means) 708
Chapter 31: Protecting Your Work 709
Types of Protection 709
Protecting a Worksheet 710
Unlocking cells 710
Sheet protection options 712
Assigning user permissions 713
Protecting a Workbook 714
Requiring a password to open a workbook 714
Protecting a workbooks structure 715
Protecting a VBA Project 716
Related Topics 717
Saving a worksheet as a PDF file 717
Marking a workbook as final 717
Inspecting a workbook 718
Using a digital signature 719
Getting a digital ID 719
Signing a workbook 719
Part V: Understanding Power Pivot and Power Query 721
Chapter 32: Introducing Power Pivot 723
Understanding the Power Pivot Internal Data Model 723
The Power Pivot Ribbon 724
Linking Excel tables to Power Pivot 725
Preparing your Excel tables 726
Adding your Excel tables to the data model 727
Creating relationships between your PowerPivot tables 728
Managing existing relationships 730
Using Power Pivot data in reporting 732
Loading Data from Other Data Sources 733
Loading data from relational databases 733
Loading data from SQL Server 733
Loading data from other relational database systems 738
Loading data from flat files 738
Loading data from external Excel files 739
Loading data from text files 741
Loading data from the Clipboard 742
Refreshing and managing external data connections 742
Manually refreshing your Power Pivot data 743
Setting up automatic refreshing 743
Editing your data connection 745
Chapter 33: Working Directly with the Internal Data Model 747
Directly Feeding the Internal Data Model 747
Managing Relationships in the Internal Data Model 754
Managing Queries& Connections 755
Chapter 34: Adding Formulas to Power Pivot 757
Enhancing Power Pivot Data with Calculated Columns 757
Creating your first calculated column 758
Formatting your calculated columns 759
Referencing calculated columns in other calculations 760
Hiding calculated columns from end users 760
Utilizing DAX to Create Calculated Columns 762
Identifying DAX functions safe for calculated columns 762
Building DAX-driven calculated columns 764
Month sorting in Power Pivotdriven PivotTables 765
Referencing fields from other tables 768
Nesting functions 770
Understanding Calculated Measures 770
Editing and deleting calculated measures 773
Using Cube Functions to Free Your Data 773
Chapter 35: Introducing Power Query 777
Understanding Power Query Basics 777
Understanding query steps 784
Viewing the Advanced Query Editor 785
Refreshing Power Query data 786
Managing existing queries 787
Understanding column-level actions 788
Understanding table actions 790
Getting Data from External Sources 792
Importing data from files 793
Getting data from Excel workbooks 794
Getting data from CSV and text files 795
Getting data from PDF files 795
Importing data from database systems 796
Importing data from relational and OLAP databases 796
Importing data from Azure databases 797
Importing data using ODBC connections to nonstandard databases 797
Getting Data from Other Data Systems 797
Managing Data Source Settings 798
Editing data source settings 798
Data Profiling with Power Query 800
Data profiling options 800
Data profiling quick actions 801
Chapter 36: Transforming Data with Power Query 805
Performing Common Transformation Tasks 805
Removing duplicate records 805
Filling in blank fields 808
Filling in empty strings 808
Concatenating columns 809
Changing case 811
Finding and replacing specific text 811
Trimming and cleaning text 812
Extracting the left, right, and middle values 814
Extracting first and last characters 815
Extracting middle characters 816
Splitting columns using character markers 816
Unpivoting columns 819
Unpivoting other columns 820
Pivoting columns 821
Creating Custom Columns 823
Concatenating with a custom column 824
Understanding data type conversions 826
Spicing up custom columns with functions 826
Adding conditional logic to custom columns 829
Grouping and Aggregating Data 830
Working with Custom Data Types 832
Chapter 37: Making Queries Work Together 837
Reusing Query Steps 837
Understanding the Append Feature 841
Creating the needed base queries 841
Appending the data 842
Understanding the Merge Feature 845
Understanding Power Query joins 845
Merging queries 846
Understanding fuzzy matching 851
Chapter 38: Enhancing Power Query Productivity 855
Implementing Some Power Query Productivity Tips 855
Getting quick information about your queries 855
Organizing queries in groups 856
Selecting columns in your queries faster 857
Renaming query steps 857
Quickly creating reference tables 859
Copying queries to save time 859
Viewing query dependencies 860
Setting a default load behavior 860
Preventing automatic data type changes 861
Avoiding Power Query Performance Issues 862
Using views instead of tables 862
Letting your back-end database servers do some crunching 863
Upgrading to 64-bit Excel 863
Disabling privacy settings to improve performance 864
Disabling relationship detection 864
Part VI: Automating Excel 867
Chapter 39: Introducing Visual Basic for Applications 869
Introducing VBA Macros 869
Displaying the Developer Tab 870
Learning about Macro Security 871
Saving Workbooks That Contain Macros 872
Looking at Two Types of VBA Macros 873
VBA Sub procedures 873
VBA functions 874
Creating VBA Macros 876
Recording VBA macros 876
Recording your actions to create VBA code: the basics 876
Recording a macro: a simple example 877
Examining the macro 878
Testing the macro 879
Editing the macro 879
Relative versus absolute recording 880
Another example 881
Running the macro 881
Examining the macro 882
Rerecording the macro 883
Testing the macro 883
More about recording VBA macros 884
Storing macros in your Personal Macro Workbook 884
Assigning a macro to a shortcut key 885
Assigning a macro to a button 885
Adding a macro to your Quick Access Toolbar 886
Writing VBA code 887
The basics: entering and editing code 887
The Excel object model 888
Objects and collections 889
Properties 889
Methods 891
The Range object 892
Variables 892
Controlling execution 893
A macro that cant be recorded 895
Learning More 896
Chapter 40: Creating Custom Worksheet Functions 899
Introducing VBA Functions 899
Seeing a Simple Example 900
Creating a custom function 900
Using the function in a worksheet 901
Analyzing the custom function 901
Learning about Function Procedures 902
Executing Function Procedures 904
Calling custom functions from a procedure 904
Using custom functions in a worksheet formula 904
Using Function Procedure Arguments 905
Creating a function with no arguments 905
Creating a function with one argument 906
Creating another function with one argument 906
Creating a function with two arguments 907
Creating a function with a range argument 908
Creating a simple but useful function 909
Debugging Custom Functions 910
Inserting Custom Functions 910
Learning More 912
Chapter 41: Creating UserForms 913
Understanding Why to Create UserForms 913
Exploring UserForm Alternatives 914
Using the InputBox function 914
Using the MsgBox function 915
Creating UserForms: An Overview 918
Working with UserForms 919
Adding controls 919
Changing the properties of a control 920
Handling events 921
Displaying a UserForm 922
Looking at a UserForm Example 923
Creating the UserForm 923
Testing the UserForm 924
Creating an event handler procedure 925
Looking at Another UserForm Example 926
Creating the UserForm 926
Creating event handler procedures 928
Showing the UserForm 930
Testing the UserForm 931
Making the macro available from a worksheet button 931
Making the macro available on your Quick Access Toolbar 932
Enhancing UserForms 932
Adding accelerator keys 932
Controlling tab order 933
Learning More 933
Chapter 42: Using UserForm Controls in a Worksheet 935
Understanding Why to Use Controls on a Worksheet 935
Using Controls 938
Adding a control 938
Learning about Design mode 938
Adjusting properties 938
Using common properties 939
Linking controls to cells 940
Creating macros for controls 941
Reviewing the Available ActiveX Controls 942
CheckBox 942
ComboBox 942
CommandButton 943
Image 944
Label 944
ListBox 944
OptionButton 945
ScrollBar 945
SpinButton 946
TextBox 946
ToggleButton 947
Chapter 43: Working with Excel Events 949
Understanding Events 949
Entering Event-Handler VBA Code 950
Using Workbook-Level Events 951
Using the Open event 952
Using the SheetActivate event 953
Using the NewSheet event 954
Using the BeforeSave event 954
Using the BeforeClose event 954
Working with Worksheet Events 955
Using the Change event 956
Monitoring a specific range for changes 956
Using the SelectionChange event 958
Using the BeforeRightClick event 959
Using Special Application Events 959
Using the OnTime event 960
Using the OnKey event 961
Chapter 44: Seeing Some VBA Examples 963
Working with Ranges 963
Copying a range 964
Copying a variable-size range 965
Selecting to the end of a row or column 966
Selecting a row or column 966
Moving a range 967
Looping through a range efficiently 967
Prompting for a cell value 968
Determining the type of selection 970
Identifying a multiple selection 970
Counting selected cells 971
Working with Workbooks 972
Saving all workbooks 972
Saving and closing all workbooks 972
Creating a workbook 972
Working with Charts 973
Modifying the chart type 973
Modifying chart properties 974
Applying chart formatting 974
VBA Speed Tips 975
Turning off screen updating 975
Preventing alert messages 975
Simplifying object references 976
Declaring variable types 976
Chapter 45: Creating Custom Excel Add-Ins 979
Understanding Add-Ins 979
Working with Add-Ins 980
Understanding When to Create Add-Ins 982
Creating Add-Ins 982
Looking at an Add-In Example 983
Learning about Module1 984
Learning about the UserForm 984
Testing the workbook 985
Adding descriptive information 985
Creating the user interface for your add-in macro 986
Protecting the project 986
Creating the add-in 987
Installing the add-in 987
Index 989
