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

e-books shop
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

e-books shop

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
  ●▬▬▬▬▬❂❂❂▬▬▬▬▬●
●▬▬❂❂▬▬●
●▬❂▬●

═════ ═════

Loading...
DMCA.com Protection Status