- 자료제공 :
- 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 Excel’s 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 table’s 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 table’s 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 a workbook 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 Excel’s 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 Excel’s leap year bug 306 Pre-1900 dates 306 Inconsistent date entries 307 Using Excel’s 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 Excel’s 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 chart’s 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 don’t 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 Excel’s 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 workbook’s 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 Pivot–driven 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 can’t 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