Microsoft Excel 2016 Bible. Wiley

John Walkenbach

The Comprehensive Tutorial Resource

Getting Started with Excel 
Working with Formulas and Functions
Creating Charts and Graphics
Using Advanced Excel Features
Analyzing Data with Excel
Programming Excel with VBA
Worksheet Function Reference
Excel Shortcut Keys

Microsoft Excel 2016 Bible

About the Author
John Walkenbach is a bestselling Excel author who has published more than 50 spreadsheet
books. He lives amid the saguaros, javelinas, rattlesnakes, bobcats, and Gila monsters
in Southern Arizona, but the critters are mostly scared away by his clawhammer banjo
playing. For more information, Google him.

About the Technical Editor
Niek Otten lives in the Netherlands. He started in data processing in 1967 in an insurance
company. He ran into Visicalc in 1980 and has been addicted to spreadsheets ever since.
His fi rst encounter with Excel (version 1!) was in 1985 on a Macintosh. Since 2005, Niek has
been self-employed—reviewing books, writing articles, and developing Excel-related software,
such as a high-performance actuarial add-in.

Acknowledgments
Thanks again to everyone who bought the previous editions of this book. Your
suggestions have helped make this edition the best one yet.

And a special thanks to behind-the-scenes folks who helped considerably: Karen Gill
(the copy editor who made it more readable), Niek Otten (the technical editor who made it

more accurate), and Adaobi Obi Tulton (the project editor who made it all come together).


Introduction

Thank you for purchasing Excel 2016 Bible. 

If you’re just starting with Excel, you’ll be glad to

know that Excel 2016 is the easiest version ever.

My goal in writing this book is to share with you some of what I know about Excel and, in the

process, make you more effi cient on the job. The book contains everything that you need to know to learn the basics of Excel and then move on to more advanced topics at your own pace. You’ll fi nd many useful examples and lots of tips and tricks that I’ve accumulated over the years.

Is This Book for You?

The Bible series from John Wiley & Sons, Inc., is designed for beginning, intermediate, and

advanced users. This book covers all the essential components of Excel and provides clear and

practical examples that you can adapt to your own needs.

In this book, I’ve tried to maintain a good balance between the basics that every Excel user needs

to know and the more complex topics that will appeal to power users. I’ve used Excel for more than 20 years, and I realize that almost everyone still has something to learn (including myself). My goal is to make that learning an enjoyable process.

Software Versions

This book was written for the desktop version of Excel 2016 for Windows. Much of the information also applies to Excel 2013 and Excel 2010, but if you’re using an older version of Excel, I suggest that you put down this book immediately and fi nd a book that’s appropriate for your version of Excel. The user interface changes introduced in Excel 2007 are so extensive that this book will be very confusing if you use an earlier version.

Also, please note that this book is not applicable to Excel for Mac.

Offi ce 2016 is available in several versions, including a web version and a version for tablets and

phones. This book covers only the standard desktop version of Excel 2016.

Conventions Used in This Book

Take a minute to scan this section to learn some of the typographical and organizational

conventions that this book uses.

Excel commands

Excel 2016 (like the three previous versions) features a “menu-less” user interface. In place

of a menu system, Excel uses a context-sensitive Ribbon system. The words along the top

(such as File, Insert, Page Layout, and so on) are known as tabs. Click a tab, and the Ribbon

displays the commands for the selected tab. Each command has a name, which is (usually)

displayed next to or below the icon. The commands are arranged in groups, and the group

name appears at the bottom of the Ribbon.

The convention I use is to indicate the tab name, followed by the group name, followed by

the command name. So, the command used to toggle word wrap within a cell is indicated as

Home ➪ Alignment ➪ Wrap Text

You’ll learn more about the Ribbon user interface in Chapter 1, “Introducing Excel.”

Typographical conventions

Anything you’re supposed to type using the keyboard appears in bold. Lengthy input usually

appears on a separate line. For example, I may instruct you to enter a formula such as

the following:

="Part Name: " &VLOOKUP(PartNumber,PartList,2)

Names of the keys on your keyboard appear in normal type. When two keys should be

pressed simultaneously, they’re connected with a plus sign, like this: “Press Ctrl+C to copy

the selected cells.”

The four “arrow” keys are collectively known as the navigation keys.

Excel built-in worksheet functions appear in uppercase monofont, like this: “Note the

SUMPRODUCT function used in cell C20.”

Mouse conventions

You’ll come across some of the following mouse-related terms, all standard fare:

■ Mouse pointer: The small graphic fi gure that moves onscreen when you move your

mouse. The mouse pointer is usually an arrow, but it changes shape when you move

to certain areas of the screen or when you’re performing certain actions.

■ Point: Move the mouse so that the mouse pointer is on a specifi c item; for example,

“Point to the Save button on the toolbar.”

■ Click: Press the left mouse button once and release it immediately.

■ Right-click: Press the right mouse button once and release it immediately. The

right mouse button is used in Excel to pop up shortcut menus that are appropriate

for whatever is currently selected.

■ Double-click: Press the left mouse button twice in rapid succession.

■ Drag: Press the left mouse button and keep it pressed while you move the mouse.

Dragging is often used to select a range of cells or to change the size of an object.

For Touchscreen Users

Excel 2016 also works with touchscreen devices. If you happen to be using one of these devices, you probably already know the basic touch gestures.

This book doesn’t cover specifi c touchscreen gestures, but these three guidelines should work most of the time:

■ When you read “click,” you should tap. Quickly touching and releasing your fi nger on a

button is the same as clicking it with a mouse.

■ When you read “double-click,” tap twice. Touching twice in rapid succession is equivalent

to double-clicking.

■ When you read “right-click,” press and hold your fi nger on the item until a menu appears.

Tap an item on the pop-up menu to execute the command.

Make sure you enable Touch mode from the Quick Access toolbar. Touch mode increases the spacing between the Ribbon commands, making it less likely that you’ll touch the wrong command. If the Touch mode command is not in your Quick Access toolbar, touch the rightmost control and select Touch/Mouse Mode. 

This command toggles between normal mode and Touch mode.

How This Book Is Organized

Notice that the book is divided into six main parts, followed by two appendixes.

■ Part I: Getting Started with Excel: This part consists of nine chapters that provide

background about Excel. These chapters are considered required reading for

Excel newcomers, but even experienced users will probably fi nd some new information here.

■ Part II: Working with Formulas and Functions: The chapters in Part II cover

everything that you need to know to become profi cient with performing calculations in Excel.

■ Part III: Creating Charts and Graphics: The chapters in Part III describe how to

create effective charts. In addition, you’ll fi nd chapters on the conditional formatting

visualization features, Sparkline graphics, and a chapter with lots of tips on

integrating graphics into your worksheet.

■ Part IV: Using Advanced Excel Features: This part consists of eight chapters that

deal with topics that are sometimes considered advanced. However, many beginning

and intermediate users may fi nd this information useful as well.

■ Part V: Analyzing Data with Excel: Data analysis is the focus of the chapters in

Part V. Users of all levels will fi nd some of these chapters of interest.

■ Part VI: Programming Excel with VBA: Part VI is for those who want to customize

Excel for their own use or who are designing workbooks or add-ins that are to be

used by others. It starts with an introduction to recording macros and VBA

programming and then provides coverage of UserForms, events, and add-ins.

■ Part VII: Appendixes: This book has two appendixes that cover Excel worksheet

functions and Excel shortcut keys.

How to Use This Book

Although you’re certainly free to do so, I didn’t write this book with the intention that you

would read it cover to cover. Instead, it’s a reference book that you can consult when

■ You’re stuck while trying to do something.

■ You need to do something that you’ve never done before.

■ You have some time on your hands, 

and you’re interested in learning something new about Excel.

The index is comprehensive, and each chapter typically focuses on a single broad topic. 

If you’re just starting out with Excel, I recommend that you read the fi rst few chapters to

gain a basic understanding of the product and then do some experimenting on your own.

After you become familiar with Excel’s environment, you can refer to the chapters that

interest you most. Some readers, however, may prefer to follow the chapters in order.

Don’t be discouraged if some of the material is over your head. Most users get by just fi ne

by using only a small subset of Excel’s total capabilities. In fact, the 80/20 rule applies

here: 80% of Excel users use only 20% of its features. However, using only 20% of Excel’s

features still gives you lots of power at your fi ngertips.

Screenshot

Purchase Now !
Just with Paypal

Product details

Price
File Size	65,844 KB
Pages	1155 p
File Type	PDF format
ISBN	978-1-119-06751-1  978-1-119-06749-8 (ebk)  978-1-119-06750-4 (ebk)
Copyright	2015 by John Wiley & Sons, Inc

Table of Contents

Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli

Part I: Getting Started with Excel 1

Chapter 1: Introducing Excel . . . .. . . . . . . . . . . . . . . . . . . 3

Identifying What Excel Is Good For ........................................................................ 3

Seeing What’s New in Excel 2016 ............................................................................ 4

Understanding Workbooks and Worksheets ............................................................. 4

Moving Around a Worksheet .................................................................................. 7

Navigating with your keyboard ...................................................................... 8

Navigating with your mouse .......................................................................... 9

Using the Ribbon ................................................................................................ 10

Ribbon tabs................................................................................................ 10

Contextual tabs .......................................................................................... 12

Types of commands on the Ribbon ............................................................... 12

Accessing the Ribbon by using your keyboard .............................................. 14

Using Shortcut Menus ......................................................................................... 16

Customizing Your Quick Access Toolbar ................................................................. 17

Working with Dialog Boxes .................................................................................. 19

Navigating dialog boxes .............................................................................. 20

Using tabbed dialog boxes ........................................................................... 20

Using Task Panes ................................................................................................ 21

Creating Your First Excel Workbook ...................................................................... 23

Getting started on your worksheet .............................................................. 23

Filling in the month names ......................................................................... 23

Entering the sales data ............................................................................... 24

Formatting the numbers ............................................................................. 25

Making your worksheet look a bit fancier ..................................................... 25

Summing the values ................................................................................... 26

Creating a chart ......................................................................................... 26

Printing your worksheet ............................................................................. 27

Saving your workbook ................................................................................ 28

Chapter 2: Entering and Editing Worksheet Data . .  . . . . . . . . . . . . 29

Exploring Data Types ........................................................................................... 29

Numeric values ........................................................................................... 30

Text entries ............................................................................................... 30

Formulas .................................................................................................... 31

Entering Text and Values into Your Worksheets ..................................................... 32

Entering Dates and Times into Your Worksheets..................................................... 33

Entering date values ................................................................................... 33

Entering time values ................................................................................... 34

Modifying Cell Contents ...................................................................................... 34

Deleting the contents of a cell ..................................................................... 34

Replacing the contents of a cell ................................................................... 35

Editing the contents of a cell ...................................................................... 35

Learning some handy data-entry techniques ................................................ 37

Automatically moving the cell pointer after entering data .................... 37

Using navigation keys instead of pressing Enter ................................... 38

Selecting a range of input cells before entering data ............................ 38

Using Ctrl+Enter to place information into multiple cells

simultaneously .............................................................................. 38

Entering decimal points automatically ................................................. 38

Using AutoFill to enter a series of values ............................................. 39

Using AutoComplete to automate data entry ........................................ 40

Forcing text to appear on a new line within a cell ................................ 40

Using AutoCorrect for shorthand data entry..........................................41

Entering numbers with fractions ......................................................... 42

Using a form for data entry ................................................................ 42

Entering the current date or time into a cell ........................................ 43

Applying Number Formatting ............................................................................... 44

Using automatic number formatting ............................................................ 45

Formatting numbers by using the Ribbon ..................................................... 46

Using shortcut keys to format numbers .........................................................47

Formatting numbers by using the Format Cells dialog box ..............................47

Adding your own custom number formats .................................................... 50

Chapter 3: Essential Worksheet Operations . . . . . . . . . . . . . . . . . . 51

Learning the Fundamentals of Excel Worksheets ....................................................51

Working with Excel windows ........................................................................51

Moving and resizing windows ............................................................. 52

Switching among windows .................................................................. 53

Closing windows ................................................................................ 54

Activating a worksheet ............................................................................... 54

Adding a new worksheet to your workbook ................................................... 55

Deleting a worksheet you no longer need ..................................................... 55

Changing the name of a worksheet .............................................................. 56

Changing a sheet tab color .......................................................................... 57

Rearranging your worksheets ...................................................................... 57

Hiding and unhiding a worksheet ................................................................ 58

Controlling the Worksheet View ........................................................................... 59

Zooming in or out for a better view.............................................................. 59

Viewing a worksheet in multiple windows .................................................... 60

Comparing sheets side by side ..................................................................... 62

Splitting the worksheet window into panes .................................................. 62

Keeping the titles in view by freezing panes ................................................ 63

Monitoring cells with a Watch Window ......................................................... 65

Working with Rows and Columns .......................................................................... 66

Inserting rows and columns ........................................................................ 66

Deleting rows and columns .......................................................................... 68

Changing column widths and row heights .................................................... 68

Changing column widths .................................................................... 68

Changing row heights ......................................................................... 69

Hiding rows and columns ............................................................................ 70

Chapter 4: Working with Cells and Ranges . . . .  . . . . . . . . . . . . . . 73

Understanding Cells and Ranges ........................................................................... 73

Selecting ranges ..........................................................................................74

Selecting complete rows and columns ...........................................................76

Selecting noncontiguous ranges ...................................................................76

Selecting multisheet ranges ........................................................................ 78

Selecting special types of cells .................................................................... 80

Selecting cells by searching ........................................................................ 82

Copying or Moving Ranges ................................................................................... 84

Copying by using Ribbon commands ............................................................ 85

Copying by using shortcut menu commands ................................................. 86

Copying by using shortcut keys ................................................................... 87

Copying or moving by using drag-and-drop .................................................. 88

Copying to adjacent cells ............................................................................ 89

Copying a range to other sheets .................................................................. 90

Using the Offi ce Clipboard to paste .............................................................. 90

Pasting in special ways ............................................................................... 92

Using the Paste Special dialog box ............................................................... 94

Performing mathematical operations without formulas ......................... 96

Skipping blanks when pasting ............................................................ 97

Transposing a range ........................................................................... 97

Using Names to Work with Ranges ........................................................................ 97

Creating range names in your workbooks ..................................................... 98

Using the Name box ........................................................................... 98

Using the New Name dialog box ........................................................... 99

Using the Create Names from Selection dialog box ................................ 99

Managing names .......................................................................................101

Adding Comments to Cells ..................................................................................102

Formatting comments ................................................................................103

Changing a comment’s shape ......................................................................105

Reading comments ....................................................................................106

Printing comments ....................................................................................106

Hiding and showing comments ...................................................................107

Selecting comments ...................................................................................107

Editing comments ......................................................................................107

Deleting comments ....................................................................................107

Chapter 5: Introducing Tables . . . . . . . . . . . .. . . . . . . . . . . . . 109

What Is a Table? .................................................................................................109

Creating a Table .................................................................................................112

Changing the Look of a Table ..............................................................................113

Working with Tables ...........................................................................................116

Navigating in a table .................................................................................116

Selecting parts of a table ...........................................................................117

Adding new rows or columns ......................................................................117

Deleting rows or columns ...........................................................................118

Moving a table ..........................................................................................118

Working with the Total Row .......................................................................119

Removing duplicate rows from a table .........................................................120

Sorting and fi ltering a table .......................................................................121

Sorting a table ..................................................................................122

Filtering a table ................................................................................123

Filtering a table with slicers ..............................................................125

Converting a table back to a range ..............................................................128

Chapter 6: Worksheet Formatting . . . . . . . . . . . . . . . . . . . . . . . 129

Getting to Know the Formatting Tools .................................................................129

Using the formatting tools on the Home tab ................................................131

Using the Mini toolbar ...............................................................................131

Using the Format Cells dialog box ...............................................................132

Using Different Fonts to Format Your Worksheet ...................................................133

Changing Text Alignment ...................................................................................136

Choosing horizontal alignment options .......................................................137

Choosing vertical alignment options ...........................................................139

Wrapping or shrinking text to fi t the cell ....................................................139

Merging worksheet cells to create additional text space ...............................140

Displaying text at an angle ........................................................................141

Controlling the text direction ....................................................................142

Using Colors and Shading ...................................................................................142

Adding Borders and Lines ...................................................................................143

Adding a Background Image to a Worksheet .........................................................146

Using Named Styles for Easier Formatting ............................................................147

Applying styles .........................................................................................148

Modifying an existing style .......................................................................149

Creating new styles ...................................................................................150

Merging styles from other workbooks ..........................................................151

Controlling styles with templates ...............................................................151

Understanding Document Themes........................................................................151

Applying a theme ......................................................................................153

Customizing a theme .................................................................................155

Chapter 7: Understanding Excel Files . . . . . . . . . . . . . . . . 157

Creating a New Workbook ....................................................................................157

Opening an Existing Workbook............................................................................158

Filtering fi lenames ....................................................................................160

Choosing your fi le display preferences ........................................................160

Saving a Workbook .............................................................................................161

Using AutoRecover .............................................................................................163

Recovering versions of the current workbook ..............................................163

Recovering unsaved work ...........................................................................163

Confi guring AutoRecover ...........................................................................164

Password-Protecting a Workbook .........................................................................164

Organizing Your Files .........................................................................................165

Other Workbook Info Options ..............................................................................166

Protect Workbook options ..........................................................................166

Check for Issues options ............................................................................166

Manage Versions options ............................................................................167

Browser View options .................................................................................167

Compatibility Mode section ........................................................................167

Closing Workbooks .............................................................................................167

Safeguarding Your Work .....................................................................................168

Excel File Compatibility ......................................................................................168

Checking compatibility ..............................................................................168

Recognizing the Excel 2016 fi le formats ......................................................170

Saving a fi le for use with an older version of Excel .......................................170

Chapter 8: Using and Creating Templates . . . . . . . . . . . . . . . . . 171

Exploring Excel Templates ..................................................................................171

Viewing templates .....................................................................................171

Creating a workbook from a template ..........................................................172

Modifying a template................................................................................. 174

Understanding Custom Excel Templates ............................................................... 174

Working with the default templates ............................................................175

Using the workbook template to change workbook defaults ..................175

Creating a worksheet template ...........................................................176

Editing your template .......................................................................177

Resetting the default workbook .........................................................177

Creating custom templates .........................................................................177

Saving your custom templates ...........................................................178

Using custom templates .....................................................................178

Getting ideas for creating templates ...................................................179

Chapter 9: Printing Your Work . . . . . . . . . . . . . . . . . . . . . . 181

Basic Printing ....................................................................................................181

Changing Your Page View ....................................................................................183

Normal view ..............................................................................................183

Page Layout view ......................................................................................184

Page Break Preview ....................................................................................186

Adjusting Common Page Setup Settings ...............................................................187

Choosing your printer ................................................................................188

Specifying what you want to print .............................................................189

Changing page orientation .........................................................................190

Specifying paper size .................................................................................190

Printing multiple copies of a report ............................................................190

Adjusting the page margins .......................................................................190

Understanding page breaks ........................................................................192

Inserting a page break ......................................................................192

Removing manual page breaks ...........................................................193

Printing row and column titles ...................................................................193

Scaling printed output ...............................................................................194

Printing cell gridlines ................................................................................195

Printing row and column headers ...............................................................195

Using a background image ..........................................................................195

Adding a Header or a Footer to Your Reports ........................................................197

Selecting a predefi ned header or footer .......................................................197

Understanding header and footer element codes ..........................................198

Other header and footer options .................................................................200

Other Print-Related Topics ..................................................................................200

Copying Page Setup settings across Sheets ..................................................200

Preventing certain cells from being printed ................................................201

Preventing objects from being printed ........................................................202

Creating custom views of your worksheet ....................................................203

Creating PDF fi les ......................................................................................204

Part II: Working with Formulas and Functions 205

Chapter 10: Introducing Formulas and Functions . . . . . . . . . . . . 207

Understanding Formula Basics ............................................................................207

Using operators in formulas........................................................................208

Understanding operator precedence in formulas ..........................................210

Using functions in your formulas ...............................................................212

Examples of formulas that use functions ............................................212

Function arguments ..........................................................................213

More about functions ........................................................................214

Entering Formulas into Your Worksheets ..............................................................214

Entering formulas manually .......................................................................217

Entering formulas by pointing ....................................................................217

Pasting range names into formulas .............................................................218

Inserting functions into formulas ...............................................................219

Function entry tips ...................................................................................221

Editing Formulas ................................................................................................221

Using Cell References in Formulas .......................................................................222

Using relative, absolute, and mixed references .............................................223

Changing the types of your references ........................................................225

Referencing cells outside the worksheet ......................................................225

Referencing cells in other worksheets ................................................226

Referencing cells in other workbooks .................................................226

Using Formulas in Tables ....................................................................................227

Summarizing data in a table ......................................................................227

Using formulas within a table ....................................................................229

Referencing data in a table ........................................................................230

Correcting Common Formula Errors ......................................................................232

Handling circular references ......................................................................233

Specifying when formulas are calculated .....................................................234

Using Advanced Naming Techniques ....................................................................236

Using names for constants .........................................................................236

Using names for formulas ...........................................................................237

Using range intersections...........................................................................238

Applying names to existing references ........................................................240

Working with Formulas .......................................................................................241

Not hard-coding values ..............................................................................241

Using the Formula bar as a calculator ..........................................................241

Making an exact copy of a formula .............................................................241

Converting formulas to values ....................................................................242

Chapter 11: Creating Formulas That Manipulate Text . . . . . . . . . . . . . . 243

A Few Words About Text .....................................................................................243

Text Functions ...................................................................................................244

Working with character codes .....................................................................245

The CODE function ............................................................................245

The CHAR function ...........................................................................246

Determining whether two strings are identical ............................................248

Joining two or more cells ...........................................................................248

Displaying formatted values as text ............................................................249

Displaying formatted currency values as text ..............................................251

Repeating a character or string ..................................................................251

Creating a text histogram ..........................................................................251

Padding a number .....................................................................................252

Removing excess spaces and nonprinting characters ....................................253

Counting characters in a string ..................................................................254

Changing the case of text ..........................................................................254

Extracting characters from a string ............................................................255

Replacing text with other text ...................................................................256

Finding and searching within a string ........................................................257

Searching and replacing within a string ......................................................257

Advanced Text Formulas .....................................................................................258

Counting specifi c characters in a cell ..........................................................258

Counting the occurrences of a substring in a cell .........................................258

Extracting the fi rst word of a string ...........................................................259

Extracting the last word of a string ............................................................259

Extracting all but the fi rst word of a string .................................................260

Extracting fi rst names, middle names, and last names .................................260

Removing titles from names .......................................................................262

Creating an ordinal number ........................................................................262

Counting the number of words in a cell .......................................................262

Chapter 12: Working with Dates and Times . . . . . . . . . . . . . . 263

How Excel Handles Dates and Times .....................................................................263

Understanding date serial numbers .............................................................263

Entering dates ...........................................................................................264

Understanding time serial numbers ............................................................266

Entering times ..........................................................................................267

Formatting dates and times........................................................................268

Problems with dates ..................................................................................269

Excel’s leap year bug .........................................................................269

Pre-1900 dates ..................................................................................270

Inconsistent date entries ...................................................................270

Date-Related Worksheet Functions ......................................................................271

Displaying the current date .......................................................................272

Displaying any date ...................................................................................273

Generating a series of dates .......................................................................274

Converting a nondate string to a date .........................................................275

Calculating the number of days between two dates ......................................276

Calculating the number of workdays between two dates ...............................276

Offsetting a date using only workdays ........................................................278

Calculating the number of years between two dates .....................................278

Calculating a person’s age...........................................................................279

Determining the day of the year .................................................................280

Determining the day of the week ................................................................281

Determining the week of the year ..............................................................281

Determining the date of the most recent Sunday .........................................281

Determining the fi rst day of the week after a date .......................................282

Determining the nth occurrence of a day of the week in a month .................282

Calculating dates of holidays ......................................................................282

New Year’s Day ..................................................................................283

Martin Luther King, Jr., Day ..............................................................283

Presidents’ Day .................................................................................284

Easter ..............................................................................................284

Memorial Day ....................................................................................284

Independence Day .............................................................................284

Labor Day .........................................................................................284

Columbus Day ...................................................................................284

Veterans Day ....................................................................................285

Thanksgiving Day .............................................................................285

Christmas Day ..................................................................................285

Determining the last day of a month ..........................................................285

Determining whether a year is a leap year ...................................................285

Determining a date’s quarter ......................................................................286

Time-Related Worksheet Functions ......................................................................286

Displaying the current time .......................................................................287

Displaying any time ..................................................................................287

Calculating the difference between two times .............................................288

Summing times that exceed 24 hours ..........................................................289

Converting from military time ....................................................................291

Converting decimal hours, minutes, or seconds to a time ..............................292

Adding hours, minutes, or seconds to a time ...............................................292

Rounding time values ................................................................................293

Working with non-time-of-day values .........................................................294

Chapter 13: Creating Formulas That Count and Sum . . . . . . . . . . . . . . . 297

Counting and Summing Worksheet Cells...............................................................297

Basic Counting Formulas.....................................................................................299

Counting the total number of cells ..............................................................301

Counting blank cells ..................................................................................301

Counting nonblank cells ............................................................................301

Counting numeric cells ..............................................................................302

Counting text cells ....................................................................................302

Counting nontext cells ...............................................................................302

Counting logical values ..............................................................................302

Counting error values in a range .................................................................302

Advanced Counting Formulas ..............................................................................303

Counting cells by using the COUNTIF function .............................................303

Counting cells based on multiple criteria .....................................................304

Using And criteria.............................................................................306

Using Or criteria ...............................................................................307

Combining And and Or criteria ...........................................................308

Counting the most frequently occurring entry .............................................308

Counting the occurrences of specifi c text ....................................................309

Entire cell contents ...........................................................................309

Partial cell contents ..........................................................................310

Total occurrences in a range ..............................................................310

Counting the number of unique values ........................................................310

Creating a frequency distribution ...............................................................311

The FREQUENCY function ...................................................................311

Using formulas to create a frequency distribution ...............................313

Using the Analysis ToolPak to create a frequency distribution .............315

Using a pivot table to create a frequency distribution .........................317

Summing Formulas .............................................................................................318

Summing all cells in a range .......................................................................318

Computing a cumulative sum ......................................................................319

Ignoring errors when summing ...................................................................320

Summing the “top n” values .......................................................................321

Conditional Sums Using a Single Criterion ............................................................322

Summing only negative values ...................................................................323

Summing values based on a different range .................................................323

Summing values based on a text comparison ...............................................324

Summing values based on a date comparison ...............................................324

Conditional Sums Using Multiple Criteria .............................................................324

Using And criteria .....................................................................................325

Using Or criteria ........................................................................................326

Using And and Or criteria ...........................................................................326

Chapter 14: Creating Formulas That Look Up Values . . . . . . . . . . . 327

Introducing Lookup Formulas .............................................................................327

Functions Relevant to Lookups ...........................................................................329

Basic Lookup Formulas .......................................................................................330

The VLOOKUP function ..............................................................................330

The HLOOKUP function ..............................................................................332

The LOOKUP function ................................................................................333

Combining the MATCH and INDEX functions ................................................334

Specialized Lookup Formulas ..............................................................................336

Looking up an exact value .........................................................................337

Looking up a value to the left ....................................................................338

Performing a case-sensitive lookup .............................................................339

Looking up a value from multiple lookup tables ...........................................340

Determining letter grades for test scores .....................................................341

Calculating a grade-point average ...............................................................342

Performing a two-way lookup .....................................................................343

Performing a two-column lookup ................................................................344

Determining the cell address of a value within a range.................................345

Looking up a value by using the closest match ............................................346

Chapter 15: Creating Formulas for Financial Applications . . . . .. . . . . 349

The Time Value of Money ....................................................................................349

Loan Calculations ...............................................................................................351

Worksheet functions for calculating loan information ..................................351

PMT .................................................................................................352

PPMT................................................................................................352

IPMT ................................................................................................352

RATE ................................................................................................353

NPER ................................................................................................353

PV ...................................................................................................354

A loan calculation example ........................................................................354

Credit card payments .................................................................................356

Creating a loan amortization schedule ........................................................358

Summarizing loan options by using a data table ..........................................359

Creating a one-way data table ............................................................360

Creating a two-way data table ............................................................361

Calculating a loan with irregular payments .................................................363

Investment Calculations .....................................................................................365

Future value of a single deposit ..................................................................365

Calculating simple interest ................................................................365

Calculating compound interest ...........................................................366

Calculating interest with continuous compounding .............................369

Future value of a series of deposits .............................................................371

Depreciation Calculations ...................................................................................373

Financial Forecasting .........................................................................................376

Chapter 16: Miscellaneous Calculations . . . . . . . . . . . . . . . . . 381

Unit Conversions ................................................................................................381

Solving Right Triangles ......................................................................................384

Area, Surface, Circumference, and Volume Calculations .........................................385

Calculating the area and perimeter of a square ............................................386

Calculating the area and perimeter of a rectangle ........................................386

Calculating the area and perimeter of a circle ..............................................386

Calculating the area of a trapezoid .............................................................386

Calculating the area of a triangle ................................................................387

Calculating the surface and volume of a sphere ...........................................387

Calculating the surface and volume of a cube ..............................................387

Calculating the surface and volume of a rectangular solid ............................387

Calculating the surface and volume of a cone ..............................................387

Calculating the volume of a cylinder ...........................................................388

Calculating the volume of a pyramid ...........................................................388

Rounding Numbers .............................................................................................388

Basic rounding formulas ............................................................................389

Rounding to the nearest multiple ...............................................................390

Rounding currency values ..........................................................................390

Working with fractional dollars ..................................................................391

Using the INT and TRUNC functions ............................................................392

Rounding to an even or odd integer ............................................................392

Rounding to n signifi cant digits .................................................................393

Chapter 17: Introducing Array Formulas . . . . .. . . . . . . . . . . . . . 395

Understanding Array Formulas ............................................................................395

A multicell array formula ...........................................................................396

A single-cell array formula .........................................................................398

Creating an Array Constant .................................................................................399

Understanding the Dimensions of an Array ..........................................................400

One-dimensional horizontal arrays .............................................................400

One-dimensional vertical arrays .................................................................401

Two-dimensional arrays .............................................................................401

Naming Array Constants .....................................................................................403

Working with Array Formulas ..............................................................................404

Entering an array formula ..........................................................................404

Selecting an array formula range ................................................................405

Editing an array formula ............................................................................405

Expanding or contracting a multicell array formula .....................................406

Using Multicell Array Formulas ...........................................................................407

Creating an array from values in a range .....................................................407

Creating an array constant from values in a range .......................................408

Performing operations on an array..............................................................408

Using functions with an array ....................................................................410

Transposing an array .................................................................................410

Generating an array of consecutive integers ................................................ 411

Using Single-Cell Array Formulas .........................................................................413

Counting characters in a range ...................................................................413

Summing the three smallest values in a range .............................................414

Counting text cells in a range ....................................................................415

Eliminating intermediate formulas .............................................................416

Using an array in lieu of a range reference ..................................................418

Chapter 18: Performing Magic with Array Formulas . . .. . . . . . . . . . . . 421

Working with Single-Cell Array Formulas .............................................................421

Summing a range that contains errors.........................................................421

Counting the number of error values in a range ...........................................423

Summing the n largest values in a range .....................................................423

Computing an average that excludes zeros ...................................................424

Determining whether a particular value appears in a range ..........................425

Counting the number of differences in two ranges .......................................426

Returning the location of the maximum value in a range .............................427

Finding the row of a value’s nth occurrence in a range .................................428

Returning the longest text in a range .........................................................428

Determining whether a range contains valid values .....................................429

Summing the digits of an integer ...............................................................430

Summing rounded values ...........................................................................431

Summing every nth value in a range ...........................................................432

Removing nonnumeric characters from a string ...........................................433

Determining the closest value in a range .....................................................434

Returning the last value in a column ..........................................................435

Returning the last value in a row ...............................................................436

Working with Multicell Array Formulas ................................................................437

Returning only positive values from a range ................................................437

Returning nonblank cells from a range .......................................................439

Reversing the order of cells in a range ........................................................439

Sorting a range of values dynamically .........................................................440

Returning a list of unique items in a range .................................................441

Displaying a calendar in a range .................................................................442

Part III: Creating Charts and Graphics 445

Chapter 19: Getting Started Making Charts . . .. . . . . . . . . . . . . 447

What Is a Chart? ................................................................................................447

Understanding How Excel Handles Charts ............................................................448

Embedded charts .......................................................................................449

Chart sheets..............................................................................................450

Creating a Chart .................................................................................................452

Hands On: Creating and Customizing a Chart........................................................453

Selecting the data .....................................................................................454

Choosing a chart type ................................................................................454

Experimenting with different styles ...........................................................454

Experimenting with different layouts .........................................................456

Trying another view of the data .................................................................457

Trying other chart types ............................................................................458

Working with Charts ..........................................................................................460

Resizing a chart ........................................................................................460

Moving a chart ..........................................................................................460

Copying a chart .........................................................................................461

Deleting a chart ........................................................................................461

Adding chart elements ...............................................................................461

Moving and deleting chart elements ...........................................................461

Formatting chart elements .........................................................................462

Printing charts .........................................................................................464

Understanding Chart Types .................................................................................464

Choosing a chart type ................................................................................465

Column charts ...........................................................................................467

Bar charts .................................................................................................470

Pie charts ................................................................................................. 474

XY (scatter) charts ....................................................................................475

Area charts ...............................................................................................477

Radar charts .............................................................................................478

Surface charts ...........................................................................................481

Bubble charts ............................................................................................482

Stock charts..............................................................................................483

New Chart Types for Excel 2016 ...........................................................................485

Histogram charts ......................................................................................485

Pareto charts ............................................................................................485

Waterfall charts ........................................................................................487

Box & whisker charts .................................................................................487

Sunburst charts ........................................................................................488

Treemap charts .........................................................................................488

Learning More ...................................................................................................490

Chapter 20: Learning Advanced Charting . . . . . . . . . . . . . . . . . . . . . 491

Selecting Chart Elements ....................................................................................491

Selecting with the mouse ...........................................................................492

Selecting with the keyboard.......................................................................493

Selecting with the Chart Elements control...................................................493

User Interface Choices for Modifying Chart Elements ............................................494

Using the Format task pane ........................................................................495

Using the chart customization buttons .......................................................496

Using the Ribbon .......................................................................................496

Using the Mini toolbar ...............................................................................497

Modifying the Chart Area ...................................................................................497

Modifying the Plot Area .....................................................................................498

Working with Titles in a Chart ............................................................................499

Working with a Legend .......................................................................................500

Working with Gridlines .......................................................................................503

Modifying the Axes ............................................................................................503

Value axis .................................................................................................503

Category axis ............................................................................................508

Working with Data Series ....................................................................................512

Deleting or hiding a data series ..................................................................513

Adding a new data series to a chart ............................................................513

Changing data used by a series ...................................................................514

Changing the data range by dragging the range outline .......................514

Using the Edit Series dialog box .........................................................515

Editing the Series formula .................................................................515

Displaying data labels in a chart ................................................................ 517

Handling missing data ...............................................................................519

Adding error bars ......................................................................................520

Adding a trendline ....................................................................................521

Modifying 3-D charts .................................................................................522

Creating combination charts ......................................................................523

Displaying a data table ..............................................................................526

Creating Chart Templates ....................................................................................527

Learning Some Chart-Making Tricks ....................................................................528

Creating picture charts ..............................................................................528

Creating a thermometer chart ....................................................................530

Creating a gauge chart ...............................................................................532

Creating a comparative histogram ...............................................................533

Creating a Gantt chart ...............................................................................534

Plotting mathematical functions with one variable ......................................536

Plotting mathematical functions with two variables ....................................537

Chapter 21: Visualizing Data Using Conditional F ormatting . . . . . . . . . . . . 539

About Conditional Formatting .............................................................................539

Specifying Conditional Formatting ......................................................................541

Formatting types you can apply .................................................................542

Making your own rules ..............................................................................542

Conditional Formats That Use Graphics ................................................................544

Using data bars .........................................................................................544

A simple data bar ..............................................................................544

Using data bars in lieu of a chart .......................................................545

Using color scales ......................................................................................546

A color scale example ........................................................................546

An extreme color scale example .........................................................548

Using icon sets ..........................................................................................549

An icon set example ..........................................................................549

Another icon set example ..................................................................551

Creating Formula-Based Rules .............................................................................552

Understanding relative and absolute references ...........................................553

Conditional formatting formula examples ....................................................554

Identifying weekend days .................................................................555

Highlighting a row based on a value ...................................................555

Displaying alternate-row shading .......................................................556

Creating checkerboard shading ..........................................................556

Shading groups of rows .....................................................................557

Displaying a total only when all values are entered .............................557

Creating a Gantt Chart ......................................................................558

Working with Conditional Formats.......................................................................559

Managing rules .........................................................................................560

Copying cells that contain conditional formatting .......................................560

Deleting conditional formatting .................................................................560

Locating cells that contain conditional formatting ......................................560

Chapter 22: Creating Sparkline Graphics . . . . .  . . . . . . . . . . . . 563

Sparkline Types .................................................................................................564

Creating Sparklines ............................................................................................565

Customizing Sparklines ......................................................................................568

Sizing Sparkline cells .................................................................................568

Handling hidden or missing data ................................................................568

Changing the Sparkline type ......................................................................569

Changing Sparkline colors and line width ....................................................569

Highlighting certain data points ................................................................570

Adjusting Sparkline axis scaling .................................................................570

Faking a reference line ..............................................................................571

Specifying a Date Axis ....................................................................................... 574

Auto-Updating Sparklines ...................................................................................575

Displaying a Sparkline for a Dynamic Range ........................................................576

Chapter 23: Enhancing Your Work with Pictures and Drawings . . . . . . . . . . 579

Using Shapes .....................................................................................................579

Inserting a Shape ......................................................................................580

Adding text to a Shape ..............................................................................583

Formatting Shapes.....................................................................................584

Stacking Shapes ........................................................................................585

Grouping objects .......................................................................................586

Aligning and spacing objects ......................................................................586

Reshaping Shapes ......................................................................................587

Printing objects ........................................................................................588

Using SmartArt ..................................................................................................590

Inserting SmartArt ....................................................................................590

Customizing SmartArt ...............................................................................591

Changing the layout and style ....................................................................592

Learning more about SmartArt ...................................................................593

Using WordArt ...................................................................................................593

Working with Other Graphics Types .....................................................................594

About graphics fi les ...................................................................................594

Inserting screenshots ................................................................................598

Displaying a worksheet background image ..................................................598

Using the Equation Editor ...................................................................................598

Part IV: Using Advanced Excel Features 601

Chapter 24: Customizing the Excel User Interface . . . .. . . . . . . 603

Customizing the Quick Access Toolbar .................................................................603

About the Quick Access toolbar ..................................................................604

Adding new commands to the Quick Access toolbar......................................605

Other Quick Access toolbar actions .............................................................608

Customizing the Ribbon .....................................................................................609

Why you may want to customize the Ribbon ...............................................609

What can be customized ............................................................................609

How to customize the Ribbon .....................................................................610

Creating a new tab ............................................................................610

Creating a new group ........................................................................ 611

Adding commands to a new group ...................................................... 611

Resetting the Ribbon .................................................................................612

Chapter 25: Using Custom Number Formats . . . . . . . . . . . . . . . 613

About Number Formatting ..................................................................................613

Automatic number formatting ....................................................................614

Formatting numbers by using the Ribbon ....................................................615

Using shortcut keys to format numbers .......................................................615

Using the Format Cells dialog box to format numbers ...................................616

Creating a Custom Number Format ....................................................................... 617

Parts of a number format string .................................................................620

Custom number format codes ......................................................................621

Custom Number Format Examples ........................................................................623

Scaling values ...........................................................................................623

Displaying values in thousands ..........................................................623

Displaying values in hundreds ...........................................................624

Displaying values in millions .............................................................624

Appending zeros to a value ................................................................626

Displaying leading zeros ............................................................................626

Specifying conditions ................................................................................627

Displaying fractions ..................................................................................627

Displaying a negative sign on the right .......................................................628

Formatting dates and times........................................................................628

Displaying text with numbers ....................................................................629

Suppressing certain types of entries ...........................................................630

Filling a cell with a repeating character ......................................................631

Chapter 26: Using Data Validation . . . . . . . . . . . . . . . 633

About Data Validation ........................................................................................633

Specifying Validation Criteria .............................................................................634

Types of Validation Criteria You Can Apply ...........................................................635

Creating a Drop-Down List ..................................................................................637

Using Formulas for Data Validation Rules .............................................................638

Understanding Cell References ............................................................................639

Data Validation Formula Examples .......................................................................641

Accepting text only ...................................................................................641

Accepting a larger value than the previous cell ...........................................641

Accepting nonduplicate entries only ...........................................................642

Accepting text that begins with a specifi c character ....................................643

Accepting dates by the day of the week ......................................................643

Accepting only values that don’t exceed a total ...........................................643

Creating a dependent list ...........................................................................644

Chapter 27: Creating and Using Worksheet Outlines . . . . . . . . . . . . . . 647

Introducing Worksheet Outlines ..........................................................................647

Creating an Outline ............................................................................................651

Preparing the data ....................................................................................651

Creating an outline automatically ...............................................................652

Creating an outline manually .....................................................................653

Working with Outlines ........................................................................................654

Displaying levels .......................................................................................654

Adding data to an outline ..........................................................................655

Removing an outline .................................................................................655

Adjusting the outline symbols ....................................................................655

Hiding the outline symbols ........................................................................656

Chapter 28: Linking and Consolidating Worksheets . . . . . .. . . . . . . . . . 657

Linking Workbooks ............................................................................................657

Creating External Reference Formulas ..................................................................658

Understanding link formula syntax ............................................................658

Creating a link formula by pointing ............................................................659

Pasting links.............................................................................................660

Working with External Reference Formulas ..........................................................660

Creating links to unsaved workbooks ..........................................................660

Opening a workbook with external reference formulas .................................661

Changing the startup prompt .....................................................................662

Updating links ..........................................................................................663

Changing the link source ...........................................................................663

Severing links ...........................................................................................663

Avoiding Potential Problems with External Reference Formulas .............................664

Renaming or moving a source workbook ......................................................664

Using the Save As command .......................................................................664

Modifying a source workbook .....................................................................665

Intermediary links ....................................................................................665

Consolidating Worksheets ...................................................................................666

Consolidating worksheets by using formulas ...............................................667

Consolidating worksheets by using Paste Special .........................................668

Consolidating worksheets by using the Consolidate dialog box .....................669

A workbook consolidation example .............................................................671

Refreshing a consolidation .........................................................................674

More about consolidation ...........................................................................675

Chapter 29: Excel and the Internet . . . . . . . . . . . . . . . 677

Saving a Workbook on the Internet .....................................................................677

Saving Workbooks in HTML Format ......................................................................679

Creating an HTML fi le ................................................................................680

Creating a single-fi le web page ...................................................................681

Opening an HTML File ........................................................................................683

Working with Hyperlinks ....................................................................................684

Inserting a hyperlink ................................................................................684

Using hyperlinks .......................................................................................686

E-Mail Features ..................................................................................................686

Discovering Offi ce Add-Ins ..................................................................................687

Chapter 30: Protecting Your Work . . . . . . . . . . . . . . . . 689

Types of Protection ............................................................................................689

Protecting a Worksheet ......................................................................................690

Unlocking cells..........................................................................................690

Sheet protection options ............................................................................692

Assigning user permissions ........................................................................693

Protecting a Workbook .......................................................................................694

Requiring a password to open a workbook ...................................................694

Protecting a workbook’s structure ..............................................................695

VBA Project Protection .......................................................................................697

Related Topics....................................................................................................697

Saving a worksheet as a PDF fi le .................................................................698

Marking a workbook fi nal ...........................................................................698

Inspecting a workbook ...............................................................................698

Using a digital signature ............................................................................700

Getting a digital ID ...........................................................................700

Signing a workbook ...........................................................................700

Chapter 31: Making Your Worksheets Error Free . . . . . . . . . . . . . 701

Finding and Correcting Formula Errors .................................................................701

Mismatched parentheses ............................................................................702

Cells are fi lled with hash marks ..................................................................704

Blank cells are not blank ...........................................................................704

Extra space characters ...............................................................................704

Formulas returning an error .......................................................................705

#DIV/0! errors ...................................................................................706

#N/A errors .......................................................................................707

#NAME? errors ..................................................................................708

#NULL! errors ...................................................................................708

#NUM! errors ....................................................................................709

#REF! errors ......................................................................................709

#VALUE! errors..................................................................................710

Absolute/relative reference problems ..........................................................710

Operator precedence problems ....................................................................711

Formulas are not calculated ........................................................................712

Actual versus displayed values ...................................................................712

Floating point number errors ......................................................................713

“Phantom link” errors ................................................................................714

Using Excel Auditing Tools..................................................................................715

Identifying cells of a particular type ..........................................................715

Viewing formulas ......................................................................................716

Tracing cell relationships ...........................................................................718

Identifying precedents ......................................................................718

Identifying dependents .....................................................................719

Tracing error values ...................................................................................720

Fixing circular reference errors ...................................................................720

Using the background error-checking feature ..............................................720

Using Formula Evaluator ............................................................................722

Searching and Replacing ....................................................................................723

Searching for information ..........................................................................724

Replacing information ...............................................................................725

Searching for formatting ...........................................................................725

Spell-Checking Your Worksheets ..........................................................................726

Using AutoCorrect ..............................................................................................728

Part V: Analyzing Data with Excel 731

Chapter 32: Importing and Cleaning Data . . . . . .. . . . . . . 733

Importing Data ..................................................................................................733

Importing from a fi le .................................................................................734

Spreadsheet fi le formats ....................................................................734

Database fi le formats .........................................................................735

Text fi le formats ...............................................................................735

Importing HTML fi les ........................................................................736

Importing XML fi les ..........................................................................736

Importing a text fi le into a specifi ed range..................................................736

Copying and pasting data ..........................................................................738

Data Cleanup Techniques ....................................................................................738

Removing duplicate rows ...........................................................................738

Identifying duplicate rows .........................................................................740

Splitting text ............................................................................................ 741

Using Text to Columns .......................................................................742

Using Flash Fill .................................................................................743

Changing the case of text ..........................................................................746

Removing extra spaces............................................................................... 747

Removing strange characters .....................................................................748

Converting values ......................................................................................748

Classifying values...................................................................................... 749

Joining columns ........................................................................................750

Rearranging columns .................................................................................751

Randomizing the rows ...............................................................................751

Extracting a fi lename from a URL ...............................................................751

Matching text in a list ...............................................................................752

Changing vertical data to horizontal data ...................................................753

Filling gaps in an imported report ..............................................................755

Checking spelling ......................................................................................757

Replacing or removing text in cells .............................................................757

Adding text to cells ...................................................................................759

Fixing trailing minus signs ........................................................................760

A Data Cleaning Checklist ...................................................................................760

Exporting Data .................................................................................................. 761

Exporting to a text fi le .............................................................................. 761

CSV fi les ........................................................................................... 761

TXT fi les ........................................................................................... 761

PRN fi les ..........................................................................................762

Exporting to other fi le formats ...................................................................762

Chapter 33: Introducing Pivot Tables . . .  . . . . . . . . . . . . . . . 763

About Pivot Tables .............................................................................................763

A pivot table example ................................................................................764

Data appropriate for a pivot table ...............................................................767

Creating a Pivot Table Automatically ...................................................................769

Creating a Pivot Table Manually ..........................................................................771

Specifying the data ...................................................................................771

Specifying the location for the pivot table ..................................................772

Laying out the pivot table..........................................................................773

Formatting the pivot table .........................................................................775

Modifying the pivot table ..........................................................................777

More Pivot Table Examples ..................................................................................779

What is the daily total new deposit amount for each branch? ........................779

Which day of the week accounts for the most deposits? ................................780

How many accounts were opened at each branch, broken down

by account type? ...................................................................................781

What’s the dollar distribution of the different account types? ......................781

What types of accounts do tellers open most often? .....................................782

In which branch do tellers open the most checking accounts for

new customers? .....................................................................................783

Learning More ...................................................................................................784

Chapter 34: Analyzing Data with Pivot Tables . . . . . . . . . . . . . . . . 785

Working with Nonnumeric Data ...........................................................................785

Grouping Pivot Table Items .................................................................................787

A manual grouping example .......................................................................787

Automatic grouping examples.....................................................................788

Grouping by date ..............................................................................788

Grouping by time ..............................................................................793

Creating a Frequency Distribution .......................................................................794

Creating a Calculated Field or Calculated Item ......................................................796

Creating a calculated fi eld ..........................................................................798

Inserting a calculated item ........................................................................800

Filtering Pivot Tables with Slicers .......................................................................804

Filtering Pivot Tables with a Timeline ..................................................................806

Referencing Cells Within a Pivot Table .................................................................807

Creating Pivot Charts .........................................................................................809

A pivot chart example ...............................................................................809

More about pivot charts .............................................................................812

Another Pivot Table Example ..............................................................................813

Using the Data Model .........................................................................................817

Learning More About Pivot Tables .......................................................................821

Chapter 35: Performing Spreadsheet What-If Analysis . . . . . . . . . . . . . . 823

A What-If Example .............................................................................................823

Types of What-If Analyses ...................................................................................825

Performing manual what-if analysis ............................................................825

Creating data tables ...................................................................................826

Creating a one-input data table ..........................................................826

Creating a two-input data table .........................................................829

Using Scenario Manager .............................................................................833

Defi ning scenarios ............................................................................833

Displaying scenarios .........................................................................836

Moodifying scenarios ........................................................................837

Merging scenarios .............................................................................837

Generating a scenario report ..............................................................838

Chapter 36: Analyzing Data Using Goal Seeking and Solver .  . . . . . . . 841

What-If Analysis, in Reverse ...............................................................................841

Single-Cell Goal Seeking .....................................................................................842

A goal-seeking example .............................................................................842

More about goal seeking ............................................................................844

Introducing Solver .............................................................................................845

Appropriate problems for Solver ..................................................................845

A simple Solver example.............................................................................846

More about Solver ......................................................................................851

Solver Examples .................................................................................................852

Solving simultaneous linear equations ........................................................853

Minimizing shipping costs .........................................................................855

Allocating resources ..................................................................................858

Optimizing an investment portfolio ............................................................859

Chapter 37: Analyzing Data with the Analysis ToolPak . . . . . . . . . . . 863

The Analysis ToolPak: An Overview .....................................................................863

Installing the Analysis ToolPak Add-In ................................................................864

Using the Analysis Tools .....................................................................................864

Introducing the Analysis ToolPak Tools ...............................................................865

Analysis of Variance ..................................................................................866

Correlation ................................................................................................866

Covariance ................................................................................................867

Descriptive Statistics .................................................................................867

Exponential Smoothing ..............................................................................868

F-test (two-sample test for variance) ..........................................................868

Fourier Analysis ........................................................................................869

Histogram .................................................................................................869

Moving Average .........................................................................................870

Random Number Generation .......................................................................871

Rank and Percentile ..................................................................................873

Regression ................................................................................................873

Sampling ..................................................................................................874

T-Test .......................................................................................................874

Z-Test (two-sample test for means) ..............................................................875

Chapter 38: Working with Get & Transform . . . .  . . . . . . . . 877

Get & Transform: An Overview ............................................................................877

Data Sources for Get & Transform ........................................................................878

Example: A Simple Query ....................................................................................879

Choosing the data source ...........................................................................879

Removing extraneous columns ...................................................................880

Importing the data ....................................................................................882

Modifying the query ..................................................................................882

Refreshing the query .................................................................................883

How Your Actions Are Recorded...........................................................................884

Example: Returning Summarized Data .................................................................885

Example: Transforming Data from a Web Query .....................................................888

Separating the year from the movie title .....................................................888

Fixing the dollar values .............................................................................890

Adding an Index column ............................................................................892

Importing the data ....................................................................................892

Example: Merging Two Web Queries ......................................................................893

Performing the fi rst web query ...................................................................893

Performing the second web query ...............................................................894

Merging the two queries ............................................................................896

Example: Getting a List of Files ...........................................................................898

Example: Choosing a Random Sample ...................................................................901

Example: Unpivoting a Table ...............................................................................904

Tips for Using Get & Transform ............................................................................905

Learning More ...................................................................................................906

Part VI: Programming Excel with VBA 907

Chapter 39: Introducing Visual Basic for Applications .. . . . . . . . 909

Introducing VBA Macros .....................................................................................909

Displaying the Developer Tab ..............................................................................910

About Macro Security .........................................................................................911

Saving Workbooks That Contain Macros ...............................................................912

Two Types of VBA Macros ....................................................................................913

VBA Sub procedures ...................................................................................913

VBA functions ...........................................................................................915

Creating VBA Macros ..........................................................................................917

Recording VBA macros ...............................................................................917

Recording your actions to create VBA code: The basics .........................917

Recording a macro: A simple example .................................................918

Examining the macro ........................................................................919

Testing the macro .............................................................................920

Editing the macro .............................................................................920

Another example ..............................................................................921

Running the macro ...........................................................................921

Examining the macro ........................................................................922

Rerecording the macro ......................................................................923

Testing the macro .............................................................................923

More about recording VBA macros ...............................................................923

Absolute versus relative recording ......................................................924

Storing macros in your Personal Macro Workbook ................................925

Assigning a macro to a shortcut key ...................................................925

Assigning a macro to a button ...........................................................926

Adding a macro to your Quick Access toolbar ......................................927

Writing VBA code ......................................................................................927

The basics: Entering and editing code ................................................927

How VBA works .................................................................................928

Objects and collections ......................................................................930

Properties ........................................................................................931

Methods ...........................................................................................933

Variables ..........................................................................................933

Controlling execution ........................................................................934

A macro that can’t be recorded ...........................................................936

Learning More ...................................................................................................937

Chapter 40: Creating Custom Worksheet Functions . . . . . . . . 939

Overview of VBA Functions .................................................................................939

An Introductory Example....................................................................................940

A custom function .....................................................................................940

Using the function in a worksheet ..............................................................941

Analyzing the custom function ..................................................................941

About Function Procedures .................................................................................942

Executing Function Procedures ...........................................................................944

Calling custom functions from a procedure ..................................................944

Using custom functions in a worksheet formula ...........................................944

Function Procedure Arguments ...........................................................................945

A function with no argument .....................................................................945

A function with one argument ...................................................................946

Another function with one argument ..........................................................946

A function with two arguments ..................................................................948

A function with a range argument ..............................................................949

A simple but useful function ......................................................................950

Debugging Custom Functions ..............................................................................950

Inserting Custom Functions ................................................................................951

Learning More ...................................................................................................953

Chapter 41: Creating UserForms . . . . . . . .. . . . . . . . 955

Why Create UserForms? .......................................................................................955

UserForm Alternatives ........................................................................................956

The InputBox function ..............................................................................956

The MsgBox function .................................................................................957

Creating UserForms: An Overview ........................................................................960

Working with UserForms ............................................................................961

Adding controls .........................................................................................962

Changing the properties of a control ...........................................................963

Handling events ........................................................................................964

Displaying a UserForm ...............................................................................964

A UserForm Example ...........................................................................................964

Creating the UserForm ...............................................................................965

Testing the UserForm .................................................................................966

Creating an event handler procedure ...........................................................967

Another UserForm Example .................................................................................968

Creating the UserForm ...............................................................................968

Testing the UserForm .................................................................................969

Creating event handler procedures ..............................................................971

Testing the UserForm .................................................................................972

Making the macro available from a worksheet button ...................................973

Making the macro available on your Quick Access toolbar ............................973

More on Creating UserForms ................................................................................974

Adding accelerator keys .............................................................................974

Controlling tab order .................................................................................974

Learning More ...................................................................................................975

Chapter 42: Using UserForm Controls in a Worksheet . . . . . . . . . 977

Why Use Controls on a Worksheet? ......................................................................977

Using Controls ...................................................................................................980

Adding a control .......................................................................................980

About Design mode ....................................................................................980

Adjusting properties ..................................................................................981

Common properties ....................................................................................982

Linking controls to cells ............................................................................983

Creating macros for controls .......................................................................983

Reviewing the Available ActiveX Controls ............................................................985

CheckBox ..................................................................................................985

ComboBox .................................................................................................985

CommandButton ........................................................................................986

Image .......................................................................................................987

Label ........................................................................................................987

ListBox .....................................................................................................987

OptionButton ............................................................................................988

ScrollBar ...................................................................................................988

SpinButton ...............................................................................................989

TextBox ....................................................................................................990

ToggleButton ............................................................................................991

Chapter 43: Working with Excel Events . . . . . . . . . . . 993

Understanding Events ........................................................................................993

Entering Event-Handler VBA Code........................................................................994

Using Workbook-Level Events ..............................................................................996

Using the Open event ................................................................................996

Using the SheetActivate event ...................................................................997

Using the NewSheet event ..........................................................................998

Using the BeforeSave event ........................................................................998

Using the BeforeClose event .......................................................................999

Working with Worksheet Events ..........................................................................999

Using the Change event ........................................................................... 1000

Monitoring a specifi c range for changes .................................................... 1000

Using the SelectionChange event .............................................................. 1001

Using the BeforeRightClick event ............................................................. 1002

Using Nonobject Events .................................................................................... 1002

Using the OnTime event ........................................................................... 1003

Using the OnKey event ............................................................................ 1003

Chapter 44: VBA Examples . . . . . . . . . . . . . . 1005

Working with Ranges ....................................................................................... 1005

Copying a range ...................................................................................... 1006

Copying a variable-size range ................................................................... 1006

Selecting to the end of a row or column .................................................... 1007

Selecting a row or column ........................................................................ 1008

Moving a range ....................................................................................... 1008

Looping through a range effi ciently .......................................................... 1009

Prompting for a cell value ........................................................................ 1010

Determining the type of selection ............................................................ 1012

Identifying a multiple selection ............................................................... 1013

Counting selected cells ............................................................................ 1013

Working with Workbooks .................................................................................. 1014

Saving all workbooks ............................................................................... 1014

Saving and closing all workbooks .............................................................. 1015

Working with Charts ........................................................................................ 1015

Modifying the chart type ......................................................................... 1016

Modifying chart properties ...................................................................... 1016

Applying chart formatting ....................................................................... 1017

VBA Speed Tips ................................................................................................ 1017

Turning off screen updating ..................................................................... 1017

Preventing alert messages ........................................................................ 1018

Simplifying object references ................................................................... 1018

Declaring variable types .......................................................................... 1019

Chapter 45: Creating Custom Excel Add-Ins . . . . . . . . . . . . 1021

What Is an Add-In? .......................................................................................... 1021

Working with Add-Ins ...................................................................................... 1022

Why Create Add-Ins? ........................................................................................ 1023

Creating Add-Ins .............................................................................................. 1024

An Add-In Example .......................................................................................... 1025

About Module1 ........................................................................................ 1026

About the UserForm ................................................................................. 1026

Testing the workbook .............................................................................. 1027

Adding descriptive information ................................................................ 1027

Creating the user interface for your add-in macro ...................................... 1027

Protecting the project .............................................................................. 1028

Creating the add-in ................................................................................. 1029

Installing the add-in ............................................................................... 1029

Part VII: Appendixes 1031

Appendix A: Worksheet Function Reference . . .. . . . . . . . . . . 1033

Appendix B: Excel Shortcut Keys . . . . . .  . . . . . . . 1053

Index . . . . . . . . . . . . . . . . . . . . . . . . . 1061

  ●▬▬▬▬▬❂❂❂▬▬▬▬▬●

●▬▬❂❂▬▬●

●▬❂▬●

●❂●

═════● ●═════