Excel All-in-One For Dummies

Introduction 1

About This Book 1

Foolish Assumptions 2

Conventions Used in This Book 3

Icons Used in This Book 5

Beyond the Book 5

Where to Go from Here 5

Book 1: Excel Basics 7

Chapter 1: Excel: The 50-Cent Tour 9

A Bird's-Eye View of Excel 10

Checking Out Excel's Start Screen 10

Getting Comfy with the Excel User Interface 12

Going behind the scenes to Excel's Backstage view 13

Ripping through the Ribbon 17

Working with the Quick Access toolbar 22

Fooling around with the Formula bar 23

What's up with the Worksheet area? 25

Taking a tour of the Status bar 30

Getting Help 31

Show-and-tell help with the Search feature 31

Getting Help from the Help tab on the Ribbon 32

Launching and Quitting Excel 34

Starting Excel from the Windows Start menu 34

Starting Excel from the Windows Search text box 35

Telling Cortana to Start Excel for you 36

Starting Excel from the Windows Metro view in Tablet mode 36

When it's quitting time 37

Chapter 2: Customizing Excel 39

Tailoring the Quick Access Toolbar to Your Tastes 40

Adding Ribbon commands to the Quick Access toolbar 41

Adding non-Ribbon commands to the Quick Access toolbar 42

Exercising Your Options 43

Changing some of the more universal settings on the General tab 44

Changing common calculation options on the Formulas tab 47

Digging the options on the Data tab 50

Changing correction options on the Proofing tab 51

Changing save options on the Save tab 53

Changing the Office language preferences 56

Setting some accessibility options 57

Changing a whole lot of other common options on the Advanced tab 58

Customizing the Excel Ribbon 63

Using Office Add-ins 68

Using Excel's Own Add-ins 70

Managing the standard Excel Add-ins 72

Managing Excel COM add-ins 72

Purchasing third-party add-ins 73

Book 2: Worksheet Design 75

Chapter 1: Building Worksheets 77

Creating Fresh Workbooks 78

Take it from a template 78

Designing a workbook from scratch 84

Getting to Know Excel's Data Types 86

What's in a label? 87

What's the value? 89

Data Entry 101 93

Data entry keyboard style 94

Doing data entry with the Touch keyboard 96

You AutoComplete me 99

You AutoCorrect me 100

Constraining data entry to a cell range 102

Getting Excel to put in the decimal point 103

You AutoFill me 103

Flash Fill to the rescue 110

Linking to Other Documents 116

Hyperlinks: the basics 116

Inserting links 117

Follow that link! 120

Editing links 120

Using the HYPERLINK Function 121

Saving the Data 123

Saving workbooks in other commonly used file formats 126

Changing the default file location 127

Saving a new workbook in the old file format 128

Document Recovery to the Rescue 129

Chapter 2: Formatting Worksheets 131

Selecting Cells and Ranges 132

Selecting cells with the mouse 133

Selecting cells by touch 135

Selecting cells with the keyboard 135

Selecting cells with AutoSelect 136

Selecting cells with Go to 138

Name that range! 139

Adjusting Columns and Rows 141

Fitting the column to its contents automatically 141

Adjusting columns the old-fashioned way 142

Setting a new standard width 143

Hiding out a column or two 143

Rambling rows 144

Formatting Tables from the Ribbon 145

Formatting Tables with the Quick Analysis Tool 149

Formatting Cells from the Ribbon 150

Formatting Cell Ranges with the Mini-Toolbar 152

Using the Format Cells Dialog Box 154

Assigning number formats 154

Altering the alignment 162

Fancy fonts and colors 166

Basic borders, fills, and patterns 169

Hiring Out the Format Painter 173

Easier Formatting with Cell Styles 174

Using the Number Format cell styles 174

Defining a custom cell style by example 176

Creating a new cell style from scratch 176

Merging styles into other workbooks 177

Analyzing Data with Conditional Formatting 178

Graphical conditional formatting 179

Conditional formatting with the Quick Analysis tool 180

Identifying particular values or text entries in a cell range 181

Highlighting duplicate values in a cell range 184

Creating your own conditional formatting rules 184

Managing conditional formatting rules 186

Chapter 3: Editing and Proofing Worksheets 187

Opening a Workbook 188

Using the Open screen in the backstage view 188

Opening more than one workbook at a time 192

Finding misplaced workbooks 192

Using the other Open options 193

Editing a Cell 194

Changing your mind with Undo and Redo 195

Clearing cells 197

Inserting cells 199

A Worksheet with a View 201

Zooming in and zooming back out again 201

Freezing window panes 204

Saving custom views 206

Copying and Moving Stuff Around 208

Copying and moving with drag-and-drop 209

Copying and moving with copy, cut, and paste 210

Finding and Replacing Worksheet Data 218

Finding data 218

Finding and replacing data 221

Getting It Right: Spell Checking Your Worksheets 223

Changing the spelling options 225

Adding words to the custom dictionary 226

Looking Up and Translating Stuff 227

Marking Invalid Data 227

Chapter 4: Managing Worksheets 229

Reorganizing the Worksheet 230

Being cautious when deleting and inserting columns and rows 230

Deleting columns and rows 231

Adding new columns and rows 232

Splitting the worksheet into panes 233

Outlining worksheets 235

Reorganizing the Workbook 244

Renaming a worksheet 245

Designer sheets 245

Adding worksheets 247

Deleting worksheets 248

Changing the sheets 248

Editing and formatting multiple worksheets as a group 249

Hiding worksheets 250

Opening windows on different sheets 251

Viewing workbook stats 254

Working with Multiple Workbooks 255

Comparing windows on different workbooks 255

Transferring data between open windows 256

Moving or copying sheets from one workbook to another 256

Consolidating Worksheets 258

Consolidating by position 259

Consolidating by category 262

Linking consolidated data 263

Chapter 5: Printing Worksheets 265

Printing from the Backstage View 266

Selecting the printer to use 268

Previewing the printout 269

Quick Printing the Worksheet 272

Working with the Page Setup Options 273

Using the buttons in the Page Setup group 273

Using the buttons in the Scale to Fit group 280

Using the Print options on the Sheet tab of the Page Setup dialog box 281

Headers and Footers 282

Adding a ready-made header or footer 282

Creating a custom header or footer 284

Solving Page Break Problems 288

Printing the Formulas in a Report 290

Book 3: Formulas and Functions 291

Chapter 1: Building Basic Formulas 293

Getting Started with Formulas 294

Pointing at formula cells 296

Editing formulas 297

Using AutoSum to add numbers 298

Using Quick Analysis to add numbers 300

Building formulas with computational operators 301

Augmenting Formulas with Worksheet Functions 305

Inserting a function 305

Entering function arguments 308

Copying Formulas 310

Copying with relative cell references 311

Copying with absolute references 312

Copying with mixed cell references 315

Adding Array Formulas 316

Creating an array formula 318

Editing a dynamic array formula 320

Building old-fashioned array formulas 320

Editing an old-fashioned array formula 321

Naming Cells and Ranges 322

Defining cell and range names 323

Naming constants and formulas 325

Using names in building formulas 326

Creating names from column and row headings 327

Managing names 329

Applying names to existing formulas 330

Adding Linking Formulas 332

Controlling Formula Recalculation 333

Circular References 335

Chapter 2: Logical Functions and Error Trapping 337

Understanding Error Values 338

Using Logical Functions 340

Getting to know the powerful IF function 341

Evaluating the many talents of the IFS function 342

Learning about the SWITCH function 344

Error-Trapping Formulas 345

Error-trapping with IF 346

Upping your error-trapping game with ISERROR 347

Whiting-Out Errors with Conditional Formatting 348

Checking It Twice: Auditing Your Formulas 349

Tracing precedents 351

Tracing dependents 354

Error checking 355

Changing the Error Checking options 357

Error tracing 358

Evaluating a formula 360

Removing Errors from the Printout 361

Chapter 3: Date and Time Formulas 363

Understanding Dates and Times 363

Changing the date locale 364

Building formulas that calculate elapsed dates 364

Building formulas that calculate elapsed times 365

Using Date Functions 366

TODAY 366

DATE and DATEVALUE .367

DAY, WEEKDAY, MONTH, and YEAR 368

DAYS360 369

Other useful Date functions 370

Using Time Functions 374

NOW 374

TIME and TIMEVALUE 375

HOUR, MINUTE, and SECOND 376

Chapter 4: Finagling Financial Formulas 377

Some Financial Function Basics Before You Begin 378

The PV, NPV, and FV Functions 379

Calculating the Present Value 379

Calculating the Net Present Value 380

Calculating the Future Value 381

The PMT Function 382

Depreciation Functions 385

Excel's Advanced Financial Functions 388

Chapter 5: Math and Statistical Formulas 393

Math & Trig Functions 394

Rounding off numbers 394

POWER and SQRT 398

The SUM of the parts 400

Conditional summing 400

Statistical Functions 403

AVERAGE, MAX, MIN, and MEDIAN 404

Counting cells 405

Using specialized statistical functions 410

Chapter 6: Lookup, Information, and Text Formulas 411

Looking Up Stuff with the Lookup Functions 412

Modern lookups with XLOOKUP 412

Old-fashioned lookups with VLOOKUP and HLOOKUP 417

Performing an old-fashioned two-way lookup with MATCH and INDEX 421

Getting Cell Reference Info with the Reference Functions 424

Get the skinny on columns and rows 425

Transposing cell ranges 426

Interrogating Excel 427

Getting information about a cell 427

Are you my TYPE? 430

Using the IS functions 431

Working with Text Functions and Formulas 432

Using text functions 432

The T function 435

TEXTJOIN function 435

Concatenating text 437

Book 4: Worksheet Collaboration and Review 439

Chapter 1: Protecting Workbooks and Worksheet Data 441

Password-Protecting the Workbook 442

Protecting the workbook when saving the file 442

Assigning a password to open from the Info screen 444

Entering the password to gain access 445

Entering the password to make changes 445

Changing or deleting a password 446

Setting Up Worksheet Data Protection 447

Changing a cell's Locked and Hidden Protection formatting 448

Protecting the worksheet 449

Enabling cell range editing by certain users 452

Doing data entry in the unlocked cells of a protected Worksheet 456

Protecting the Workbook Structure 458

Chapter 2: Preparing a Workbook for Distribution 461

Getting Your Workbook Ready for Review 462

Adding properties to a workbook 463

Digitally signing a document 463

Annotating Workbooks 468

Adding notes 469

Displaying and hiding notes 469

Marking up a worksheet with digital ink 471

Chapter 3: Sharing Workbooks and Worksheet Data 475

Sharing Your Workbooks Online 476

Sharing a workbook saved on your OneDrive 476

Copying a sharing link 479

Sharing Your Workbooks via Email 480

Emailing a copy of a workbook 480

Emailing a workbook as a PDF file 481

Sharing Excel Data with Office Programs 482

Sharing data between Excel and Word 483

Sharing data between Excel and PowerPoint 488

Making Shared Workbooks Accessible 489

Some useful accessibility guidelines and tweaks 490

Adding alternative text to graphics 491

Running the Accessibility Checker 492

Exporting Workbooks to Other Usable File Formats 493

Saving and exporting worksheets as PDF files 494

Saving worksheets as XPS files 495

Saving worksheets as ODS files 496

Saving worksheets as HTML files 496

Collaborating Conversationally with Comments 498

Inserting a comment 498

Displaying and hiding comments 499

Editing a comment 499

Replying to a comment 500

Deleting a comment 500

Book 5: Charts and Graphics 501

Chapter 1: Charting Worksheet Data 503

Worksheet Charting 101 504

Embedded charts versus separate chart sheets 505

Inserting recommended charts 506

Inserting specific chart types from the Ribbon 507

Inserting charts with the Quick Analysis tool 509

Creating a chart on a separate chart sheet 510

Refining the chart from the Chart Design tab 511

Customizing chart elements from the Format tab 515

Customizing the elements of a chart 515

Formatting elements of a chart 521

Saving a customized chart as a template 526

Adding Sparkline Graphics to a Worksheet 527

Adding Infographics to a Worksheet 529

Printing Charts 532

Chapter 2: Adding Graphic Objects 533

Going Graphical: The Basics of Working with Graphic Objects 534

Manipulating graphics 535

Moving graphic objects to new layers 536

Aligning graphic objects 538

Grouping graphic objects 539

Managing graphic objects in the Selection task pane 541

Inserting Different Types of Graphics 542

Inserting 2-D online images 542

Inserting 3-D online images 544

Inserting local pictures 544

Inserting icons, stock images, and other ready-made graphics 545

Making Changes to Graphics 546

Editing pictures 546

Formatting photos and line art pictures 547

Formatting 3-D model images 548

Drawing Graphics 549

Drawing predefined shapes 549

Adding text boxes 550

Inserting WordArt 554

Inserting SmartArt graphics 555

Adding Screenshots of the Windows Desktop 558

Using Themes 558

Book 6: Data Management 561

Chapter 1: Building and Maintaining Tables 563

Table Basics 564

Designing the basic table 564

Add new records to a table 568

Eliminating duplicate records 573

Sorting Table Data 574

Sorting records on a single field 575

Sorting records on multiple fields 575

Sorting a table on font and fill colors and cell icons 578

Subtotaling Table Data 580

Chapter 2: Filtering and Querying a Table 583

Filtering Data 584

Using AutoFilter 584

Using the Advanced Filter 593

Using the Database Functions 600

Querying External Data 602

Retrieving data from Access database tables 605

Retrieving data from the web 608

Retrieving data from text files 609

Querying data from other data sources 613

Transforming a data query in the Power Query Editor 614

Book 7: Data Analysis 617

Chapter 1: Performing What-If Analysis 619

Using Data Tables 620

Creating a one-variable data table 621

Creating a two-variable data table 624

Exploring Different Scenarios 626

Creating new scenarios 626

Producing a summary report 630

Hide and Goal Seeking 631

Using Solver 633

Setting up and defining the problem 634

Solving the problem 636

Changing Solver options 637

Saving and loading a model problem 639

Creating Solver reports 640

Chapter 2: Performing Large-Scale Data Analysis 641

Creating PivotTables 642

Creating PivotTables with the Quick Analysis tool 643

Creating recommended PivotTables 645

Creating PivotTables manually 646

Formatting a PivotTable 651

Refining the PivotTable layout and style 652

Formatting the parts of the PivotTable 653

Sorting and Filtering the PivotTable Data 656

Filtering the report 656

Filtering individual Column and Row fields 657

Slicing the PivotTable data 658

Using timeline filters 659

Sorting the PivotTable 661

Modifying the PivotTable 661

Changing the summary functions 662

Adding Calculated Fields 664

Changing the PivotTable options 665

Creating PivotCharts 666

Moving a PivotChart to its own sheet 667

Filtering a PivotChart 667

Formatting a PivotChart 668

Using the Power Pivot Add-in 669

Data modeling with Power Pivot 670

Switching between the Data View and Diagram View 672

Adding calculated columns courtesy of DAX 674

Using the 3D Maps Feature 676

Creating Forecast Worksheets 681

Book 8: Macros and VBA 685

Chapter 1: Recording and Running Macros 687

Macro Basics 688

Getting ready to record 688

Recording macros 690

Running a macro 693

Saving your recorded macros in a macro-enabled workbook 694

Assigning Macros to the Ribbon and the Quick Access Toolbar 695

Adding your macros to a custom tab on the Ribbon 695

Adding your macros to custom buttons on the Quick Access toolbar 697

Macro Security 698

Chapter 2: VBA Programming 701

Using the Visual Basic Editor 702

Editing recorded macros 704

Writing new macros in the Visual Basic Editor 713

Running macros in the Visual Basic Editor 714

Creating Custom Excel Functions 715

Adding a description to a user-defined function 717

Using a custom function in your worksheet 718

Saving custom functions in add-in files 719

Index 725