Microsoft Excel 365 Bible

eBook - Bible

35,99 €
(inkl. MwSt.)


E-Book Download
Bibliografische Daten
ISBN/EAN: 9781119835233
Sprache: Englisch
Umfang: 1072 S., 81.06 MB
Auflage: 1. Auflage 2022
Format: EPUB
DRM: Adobe DRM


Your personal, hands-on guide to the latest and most useful features in Microsoft Excel 365

Excel 365 is Microsofts latest cloud-based version of its world-famous spreadsheet app. Powerful and user-friendly, its an ideal solution for businesses and people looking to make sense ofand draw intelligence fromtheir data.

TheExcel 365 Bible carries over the best content from the best-sellingExcel 2019 Bible while reflecting how a new generation uses Excel in Excel 365. The authoring team with their decades of Excel and business intelligence experience and recognition from the Excel community as Excel MVPs delivers an accessible and authoritative roadmap to Excel 365. Interested in the basics? Youll learn to create spreadsheets and workbooks and navigate the user interface. If youre ready for more advanced topics you can skip right to the material on creating visualizations, crafting custom functions, and using Visual Basic for Applications to script automations.

Youll also get:

Over 900 pages of powerful tips, tricks, and strategies to unlock the full potential of Microsoft Excel 365Guidance on how to import, manage, and analyze large amounts of dataAdvice on how to craft predictions and What-If Analyses based on data you already have

Perfect for anyone new to Excel, as well as experts and advanced users, theExcel 365 Bible is your comprehensive, go-to guide for everything you need to know about the worlds most popular, easy-to-use spreadsheet software.


Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

Dick Kusleika has been working with Microsoft Office for more than 20 years. He was formerly a Microsoft MVP, having been awarded 12 consecutive years. Dick has written several books about Excel and Access.


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


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


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


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

Informationen zu E-Books

Alle hier erworbenen E-Books können Sie in Ihrem Kundenkonto in die kostenlose PocketBook Cloud laden. Dadurch haben Sie den Vorteil, dass Sie von Ihrem PocketBook E-Reader, Ihrem Smartphone, Tablet und PC jederzeit auf Ihre gekauften und bereits vorhandenen E-Books Zugriff haben.

Um die PocketBook Cloud zu aktivieren, loggen Sie sich bitte in Ihrem Kundenkonto ein und gehen dort in den Bereich „E-Books“. Setzen Sie hier einen Haken bei „Neue E-Book-Käufe automatisch zu meiner Cloud hinzufügen.“. Dadurch wird ein PocketBook Cloud Konto für Sie angelegt. Die Zugangsdaten sind dabei dieselben wie die Ihres Kundenkontos in diesem Webshop.

Weitere Informationen zur PocketBook Cloud finden Sie unter www.meinpocketbook.de.

Allgemeine E-Book-Informationen

E-Books in diesem Webshop können in den Dateiformaten EPUB und PDF vorliegen und können ggf. mit einem Kopierschutz versehen sein. Sie finden die entsprechenden Informationen in der Detailansicht des jeweiligen Titels.

E-Books ohne Kopierschutz oder mit einem digitalen Wasserzeichen können Sie problemlos auf Ihr Gerät übertragen. Sie müssen lediglich die Kompatibilität mit Ihrem Gerät prüfen.

Um E-Books, die mit Adobe DRM geschützt sind, auf Ihr Lesegerät zu übertragen, benötigen Sie zusätzlich eine Adobe ID und die kostenlose Software Adobe® Digital Editions, wo Sie Ihre Adobe ID hinterlegen müssen. Beim Herunterladen eines mit Adobe DRM geschützten E-Books erhalten Sie zunächst eine .acsm-Datei, die Sie in Adobe® Digital Editions öffnen müssen. Durch diesen Prozess wird das E-Book mit Ihrer Adobe-ID verknüpft und in Adobe® Digital Editions geöffnet.