Loading...
Error

John Walkenbach - Excel 2013 Bible+CD [Справочник, 2013, PDF][ENG]

Ответить на тему

 | 

 
Автор Сообщение

alexdesign

Excel 2013 Bible

Библия Excel 2013

Год выпуска: 2013
Автор: John Walkenbach
Наличие иллюстраций: Ч/б иллюстрации
Жанр: Справочник
Язык: Английский
Издательство: Wiley
ISBN: 978-1118490365
Формат: PDF
Качество: Электронное издание: оригинал-макет
Количество страниц: 1060
Описание: John Walkenbach's name is synonymous with excellence in computer books that decipher the complexities of Microsoft Excel. Known as "Mr. Spreadsheet," Walkenbach shows you how to maximize the power of Excel 2013 while bringing you up to speed on the latest features. This perennial bestseller is fully updated to cover all the new features of Excel 2013, including how to navigate the user interface, take advantage of various file formats, master formulas, analyze data with PivotTables, and more.

Whether you're an Excel beginner who is looking to get more savvy or an advanced user looking to become a power user, this latest edition provides you with comprehensive coverage as well as helpful tips, tricks, and techniques that you won't find anywhere else.

Shares the invaluable insight of Excel guru and bestselling author "Mr. Spreadsheet" John Walkenbach as he guides you through every aspect of Excel 2013
Provides essential coverage of all the newest features of Excel 2013
Presents material in a clear, concise, logical format that is ideal for all levels of Excel experience
Features a website that includes downloadable templates and worksheets from the book
Chart your path to fantastic formulas and stellar spreadsheets with Excel 2013 Bible!
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 2013........................................................................... 4
Understanding Workbooks and Worksheets............................................................ 5
Moving Around a Worksheet................................................................................. 8
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.............................................................. 13
Accessing the Ribbon by using your keyboard.............................................. 14
Using Shortcut Menus.......................................................................................... 15
Customizing Your Quick Access Toolbar............................................................... 16
Working with Dialog Boxes.................................................................................. 18
Navigating dialog boxes.............................................................................. 19
Using tabbed dialog boxes........................................................................... 19
Using Task Panes................................................................................................ 20
Contents
Contents
Creating Your First Excel Workbook..................................................................... 21
Getting started on your worksheet............................................................... 22
Filling in the month names.......................................................................... 22
Entering the sales data................................................................................ 23
Formatting the numbers.............................................................................. 23
Making your worksheet look a bit fancier..................................................... 24
Summing the values.................................................................................... 25
Creating a chart.......................................................................................... 25
Printing your worksheet.............................................................................. 26
Saving your workbook................................................................................ 26
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................................................. 36
Automatically moving the cell pointer after entering data..................... 36
Using navigation keys instead of pressing Enter................................... 37
Selecting a range of input cells before entering data............................. 37
Using Ctrl+Enter to place information into multiple
cells simultaneously....................................................................... 38
Entering decimal points automatically................................................. 38
Using AutoFill to enter a series of values............................................. 38
Using AutoComplete to automate data entry........................................ 38
Forcing text to appear on a new line within a cell................................ 40
Using AutoCorrect for shorthand data entry......................................... 40
Entering numbers with fractions......................................................... 40
Using a form for data entry................................................................. 41
Entering the current date or time into a cell......................................... 42
Applying Number Formatting............................................................................... 43
Using automatic number formatting............................................................. 44
Formatting numbers by using the Ribbon..................................................... 44
Using shortcut keys to format numbers........................................................ 45
Formatting numbers using the Format Cells dialog box................................. 45
Adding your own custom number formats................................................... 48
xii
Contents
Chapter 3: Essential Worksheet Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Learning the Fundamentals of Excel Worksheets................................................... 49
Working with Excel windows...................................................................... 49
Moving and resizing windows............................................................. 50
Switching among windows................................................................. 51
Closing windows................................................................................ 52
Activating a worksheet................................................................................ 52
Adding a new worksheet to your workbook................................................. 53
Deleting a worksheet you no longer need..................................................... 53
Changing the name of a worksheet.............................................................. 54
Changing a sheet tab color.......................................................................... 54
Rearranging your worksheets...................................................................... 55
Hiding and unhiding a worksheet................................................................ 57
Controlling the Worksheet View........................................................................... 57
Zooming in or out for a better view............................................................. 57
Viewing a worksheet in multiple windows................................................... 59
Comparing sheets side by side..................................................................... 60
Splitting the worksheet window into panes.................................................. 60
Keeping the titles in view by freezing panes................................................. 61
Monitoring cells with a Watch Window....................................................... 62
Working with Rows and Columns........................................................................ 64
Inserting rows and columns........................................................................ 64
Deleting rows and columns......................................................................... 65
Hiding rows and columns............................................................................ 66
Changing column widths and row heights.................................................... 67
Changing column widths.................................................................... 67
Changing row heights......................................................................... 68
Chapter 4: Working with Cells and Ranges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Understanding Cells and Ranges........................................................................... 69
Selecting ranges.......................................................................................... 70
Selecting complete rows and columns.......................................................... 71
Selecting noncontiguous ranges................................................................... 72
Selecting multisheet ranges......................................................................... 73
Selecting special types of cells..................................................................... 75
Selecting cells by searching......................................................................... 77
Copying or Moving Ranges.................................................................................. 79
Copying by using Ribbon commands........................................................... 80
Copying by using shortcut menu commands................................................ 81
Copying by using shortcut keys................................................................... 82
Copying or moving by using drag-and-drop.................................................. 83
Copying to adjacent cells............................................................................. 84
Copying a range to other sheets................................................................... 85
Using the Office Clipboard to paste.............................................................. 85
Pasting in special ways............................................................................... 87
xiii
xiv
Contents
Using the Paste Special dialog box............................................................... 88
Performing mathematical operations without formulas......................... 90
Skipping blanks when pasting............................................................. 90
Transposing a range........................................................................... 90
Using Names to Work with Ranges....................................................................... 91
Creating range names in your workbooks..................................................... 92
Using the Name box........................................................................... 92
Using the New Name dialog box......................................................... 92
Using the Create Names from Selection dialog box............................... 93
Managing names......................................................................................... 94
Adding Comments to Cells................................................................................... 95
Formatting comments................................................................................. 97
Changing a comment’s shape...................................................................... 98
Reading comments...................................................................................... 99
Printing comments...................................................................................... 99
Hiding and showing comments...................................................................100
Selecting comments...................................................................................100
Editing comments......................................................................................100
Deleting comments....................................................................................100
Chapter 5: Introducing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
What Is a Table?................................................................................................101
Creating a Table.................................................................................................104
Changing the Look of a Table..............................................................................105
Working with Tables..........................................................................................108
Navigating in a table..................................................................................108
Selecting parts of a table............................................................................108
Adding new rows or columns.....................................................................108
Deleting rows or columns...........................................................................109
Moving a table...........................................................................................109
Working with the Total Row......................................................................110
Removing duplicate rows from a table........................................................111
Sorting and filtering a table........................................................................112
Sorting a table...................................................................................113
Filtering a table.................................................................................115
Filtering a table with Slicers...............................................................116
Converting a table back to a range..............................................................118
Chapter 6: Worksheet Formatting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Getting to Know the Formatting Tools.................................................................119
Using the formatting tools on the Home tab................................................120
Using the Mini toolbar...............................................................................121
Using the Format Cells dialog box...............................................................122
Using Different Fonts to Format Your Worksheet.................................................123
Changing Text Alignment...................................................................................126
Choosing horizontal alignment options.......................................................126
Choosing vertical alignment options...........................................................128
Wrapping or shrinking text to fit the cell.....................................................129
xv
Contents
Merging worksheet cells to create additional text space................................129
Displaying text at an angle.........................................................................130
Controlling the text direction......................................................................131
Using Colors and Shading...................................................................................131
Adding Borders and Lines...................................................................................132
Adding a Background Image to a Worksheet........................................................134
Using Named Styles for Easier Formatting............................................................136
Applying styles..........................................................................................136
Modifying an existing style.........................................................................137
Creating new styles....................................................................................138
Merging styles from other workbooks.........................................................139
Controlling styles with templates................................................................139
Understanding Document Themes.......................................................................140
Applying a theme......................................................................................141
Customizing a theme.................................................................................142
Chapter 7: Understanding Excel Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Creating a New Workbook..................................................................................145
Opening an Existing Workbook...........................................................................146
Filtering filenames.....................................................................................147
Choosing your file display preferences........................................................148
Saving a Workbook............................................................................................149
Using AutoRecover.............................................................................................150
Recovering versions of the current workbook..............................................151
Recovering unsaved work..........................................................................151
Configuring AutoRecover...........................................................................151
Password-Protecting a Workbook........................................................................152
Organizing Your Files.........................................................................................153
Other Workbook Info Options.............................................................................155
Protect Workbook options..........................................................................155
Check for Issues options.............................................................................156
Compatibility Mode section........................................................................156
Closing Workbooks............................................................................................157
Safeguarding Your Work.....................................................................................157
Excel File Compatibility......................................................................................158
Checking compatibility...............................................................................158
Recognizing the Excel 2013 file formats......................................................159
Saving a file for use with an older version of Excel......................................160
Chapter 8: Using and Creating Templates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Exploring Excel Templates..................................................................................161
Viewing templates.....................................................................................161
Creating a workbook from a template.........................................................162
Modifying a template.................................................................................164
Understanding Custom Excel Templates..............................................................164
Working with the default templates............................................................165
Using the workbook template to change workbook defaults.................165
Creating a worksheet template...........................................................166
xvi
Contents
Editing your template........................................................................166
Resetting the default workbook..........................................................167
Creating custom templates.........................................................................167
Saving your custom templates............................................................168
Using custom templates.....................................................................168
Getting ideas for creating templates....................................................169
Chapter 9: Printing Your Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Basic Printing.....................................................................................................171
Changing Your Page View...................................................................................173
Normal view.............................................................................................173
Page Layout view......................................................................................174
Page Break Preview...................................................................................175
Adjusting Common Page Setup Settings...............................................................177
Choosing your printer................................................................................178
Specifying what you want to print..............................................................178
Changing page orientation..........................................................................179
Specifying paper size.................................................................................180
Printing multiple copies of a report.............................................................180
Adjusting the page margins........................................................................180
Understanding page breaks........................................................................181
Inserting a page break.......................................................................182
Removing manual page breaks...........................................................182
Printing row and column titles....................................................................182
Scaling printed output................................................................................183
Printing cell gridlines.................................................................................184
Printing row and column headers...............................................................184
Using a background image.........................................................................184
Adding a Header or Footer to Your Reports..........................................................186
Selecting a predefined header or footer.......................................................186
Understanding header and footer element codes..........................................187
Other header and footer options.................................................................188
Copying Page Setup Settings across Sheets...........................................................189
Preventing Certain Cells from Being Printed.........................................................189
Preventing Objects from Being Printed.................................................................190
Creating Custom Views of Your Worksheet..........................................................191
Creating PDF files......................................................................................192
Part II: Working with Formulas and Functions 193
Chapter 10: Introducing Formulas and Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Understanding Formula Basics............................................................................195
Using operators in formulas........................................................................196
Understanding operator precedence in formulas..........................................197
Using functions in your formulas................................................................199
Examples of formulas that use functions.............................................200
Function arguments...........................................................................201
More about functions........................................................................202
xvii
Contents
Entering Formulas into Your Worksheets.............................................................202
Entering formulas manually.......................................................................205
Entering formulas by pointing....................................................................205
Pasting range names into formulas.............................................................206
Inserting functions into formulas................................................................207
Function entry tips.....................................................................................209
Editing Formulas................................................................................................210
Using Cell References in Formulas.......................................................................211
Using relative, absolute, and mixed references............................................211
Changing the types of your references.........................................................213
Referencing cells outside the worksheet......................................................214
Referencing cells in other worksheets.................................................214
Referencing cells in other workbooks.................................................214
Using Formulas in Tables....................................................................................215
Summarizing data in a table.......................................................................215
Using formulas within a table.....................................................................217
Referencing data in a table.........................................................................219
Correcting Common Formula Errors....................................................................220
Handling circular references.......................................................................221
Specifying when formulas are calculated.....................................................222
Using Advanced Naming Techniques...................................................................223
Using names for constants..........................................................................224
Using names for formulas...........................................................................224
Using range intersections...........................................................................225
Applying names to existing references........................................................227
Working with Formulas......................................................................................228
Not hard-coding values..............................................................................228
Using the Formula bar as a calculator.........................................................229
Making an exact copy of a formula.............................................................229
Converting formulas to values....................................................................230
Chapter 11: Creating Formulas That Manipulate Text. . . . . . . . . . . . . . . . . . . . . . . . . . . 231
A Few Words about Text....................................................................................231
Text Functions...................................................................................................232
Working with character codes....................................................................233
The CODE function...........................................................................234
The CHAR function...........................................................................234
Determining whether two strings are identical.............................................236
Joining two or more cells...........................................................................236
Displaying formatted values as text.............................................................237
Displaying formatted currency values as text...............................................239
Repeating a character or string...................................................................239
Creating a text histogram...........................................................................239
Padding a number.....................................................................................240
Removing excess spaces and nonprinting characters....................................241
Counting characters in a string...................................................................242
Changing the case of text...........................................................................242
Extracting characters from a string..............................................................243
xviii
Contents
Replacing text with other text.....................................................................244
Finding and searching within a string.........................................................244
Searching and replacing within a string.......................................................245
Advanced Text Formulas....................................................................................246
Counting specific characters in a cell...........................................................246
Counting the occurrences of a substring in a cell.........................................246
Extracting the first word of a string.............................................................247
Extracting the last word of a string.............................................................247
Extracting all but the first word of a string..................................................248
Extracting first names, middle names, and last names..................................248
Removing titles from names.......................................................................250
Creating an ordinal number........................................................................250
Counting the number of words in a cell......................................................250
Chapter 12: Working with Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
How Excel Handles Dates and Times...................................................................251
Understanding date serial numbers.............................................................251
Entering dates............................................................................................252
Understanding time serial numbers.............................................................254
Entering times...........................................................................................255
Formatting dates and times........................................................................256
Problems with dates...................................................................................257
Excel’s leap year bug.........................................................................257
Pre-1900 dates...................................................................................258
Inconsistent date entries....................................................................258
Date-Related Worksheet Functions......................................................................259
Displaying the current date.........................................................................260
Displaying any date...................................................................................261
Generating a series of dates........................................................................262
Converting a nondate string to a date..........................................................263
Calculating the number of days between two dates......................................264
Calculating the number of workdays between two dates..............................264
Offsetting a date using only workdays.........................................................266
Calculating the number of years between two dates.....................................266
Calculating a person’s age..........................................................................267
Determining the day of the year..................................................................268
Determining the day of the week................................................................269
Determining the week of the year...............................................................269
Determining the date of the most recent Sunday..........................................270
Determining the first day of the week after a date........................................270
Determining the nth occurrence of a day of the week in a month..................270
Calculating dates of holidays......................................................................271
New Year’s Day................................................................................271
Martin Luther King, Jr., Day..............................................................271
Presidents’ Day.................................................................................272
Easter...............................................................................................272
xix
Contents
Memorial Day...................................................................................272
Independence Day.............................................................................272
Labor Day.........................................................................................272
Columbus Day..................................................................................272
Veterans Day....................................................................................273
Thanksgiving Day.............................................................................273
Christmas Day...................................................................................273
Determining the last day of a month...........................................................273
Determining whether a year is a leap year...................................................273
Determining a date’s quarter......................................................................274
Time-Related Functions......................................................................................274
Displaying the current time........................................................................275
Displaying any time...................................................................................275
Calculating the difference between two times..............................................276
Summing times that exceed 24 hours..........................................................277
Converting from military time....................................................................280
Converting decimal hours, minutes, or seconds to a time.............................280
Adding hours, minutes, or seconds to a time...............................................280
Rounding time values................................................................................281
Working with non-time-of-day values.........................................................282
Chapter 13: Creating Formulas That Count and Sum . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Counting and Summing Worksheet Cells.............................................................285
Basic Counting Formulas....................................................................................287
Counting the total number of cells..............................................................288
Counting blank cells..................................................................................289
Counting nonblank cells.............................................................................289
Counting numeric cells...............................................................................290
Counting text cells.....................................................................................290
Counting nontext cells...............................................................................290
Counting logical values..............................................................................290
Counting error values in a range.................................................................290
Advanced Counting Formulas.............................................................................291
Counting cells by using the COUNTIF function............................................291
Counting cells based on multiple criteria.....................................................292
Using And criteria.............................................................................293
Using Or criteria................................................................................295
Combining And and Or criteria..........................................................295
Counting the most frequently occurring entry..............................................296
Counting the occurrences of specific text.....................................................297
Entire cell contents............................................................................297
Partial cell contents...........................................................................298
Total occurrences in a range..............................................................298
Counting the number of unique values.......................................................298
Creating a frequency distribution................................................................299
The FREQUENCY function.................................................................299
Using formulas to create a frequency distribution................................301
xx
Contents
Using the Analysis ToolPak to create a frequency distribution..............302
Using a pivot table to create a frequency distribution..........................304
Summing Formulas............................................................................................304
Summing all cells in a range.......................................................................304
Computing a cumulative sum.....................................................................305
Ignoring errors when summing...................................................................307
Summing the “top n” values.......................................................................307
Conditional Sums Using a Single Criterion...........................................................309
Summing only negative values...................................................................310
Summing values based on a different range.................................................310
Summing values based on a text comparison...............................................310
Summing values based on a date comparison..............................................310
Conditional Sums Using Multiple Criteria.............................................................311
Using And criteria......................................................................................312
Using Or criteria........................................................................................312
Using And and Or criteria...........................................................................312
Chapter 14: Creating Formulas That Look Up Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Introducing Lookup Formulas.............................................................................313
Functions Relevant to Lookups...........................................................................314
Basic Lookup Formulas.......................................................................................316
The VLOOKUP function.............................................................................316
The HLOOKUP function.............................................................................317
The LOOKUP function................................................................................318
Combining the MATCH and INDEX functions..............................................319
Specialized Lookup Formulas..............................................................................321
Looking up an exact value..........................................................................322
Looking up a value to the left.....................................................................323
Performing a case-sensitive lookup.............................................................324
Looking up a value from multiple lookup tables..........................................325
Determining letter grades for test scores......................................................325
Calculating a grade-point average...............................................................326
Performing a two-way lookup.....................................................................327
Performing a two-column lookup................................................................329
Determining the cell address of a value within a range.................................330
Looking up a value by using the closest match............................................331
Chapter 15: Creating Formulas for Financial Applications. . . . . . . . . . . . . . . . . . . . . . . . 333
The Time Value of Money...................................................................................333
Loan Calculations...............................................................................................334
Worksheet functions for calculating loan information..................................335
PMT.................................................................................................335
PPMT...............................................................................................336
IPMT................................................................................................336
RATE................................................................................................336
NPER................................................................................................337
PV ....................................................................................................337
xxi
Contents
A loan calculation example.........................................................................337
Credit card payments.................................................................................339
Creating a loan amortization schedule.........................................................340
Summarizing loan options by using a data table..........................................342
Creating a one-way data table............................................................343
Creating a two-way data table............................................................344
Calculating a loan with irregular payments..................................................346
Investment Calculations......................................................................................347
Future value of a single deposit..................................................................347
Calculating simple interest.................................................................347
Calculating compound interest...........................................................348
Calculating interest with continuous compounding.............................351
Future value of a series of deposits.............................................................352
Depreciation Calculations...................................................................................354
Chapter 16: Miscellaneous Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Unit Conversions................................................................................................359
Solving Right Triangles.......................................................................................362
Area, Surface, Circumference, and Volume Calculations.......................................363
Calculating the area and perimeter of a square............................................364
Calculating the area and perimeter of a rectangle.........................................364
Calculating the area and perimeter of a circle..............................................364
Calculating the area of a trapezoid..............................................................364
Calculating the area of a triangle.................................................................365
Calculating the surface and volume of a sphere...........................................365
Calculating the surface and volume of a cube..............................................365
Calculating the surface and volume of a rectangular solid............................365
Calculating the surface and volume of a cone..............................................366
Calculating the volume of a cylinder...........................................................366
Calculating the volume of a pyramid...........................................................366
Rounding Numbers............................................................................................366
Basic rounding formulas.............................................................................367
Rounding to the nearest multiple................................................................368
Rounding currency values..........................................................................368
Working with fractional dollars..................................................................369
Using the INT and TRUNC functions...........................................................370
Rounding to an even or odd integer............................................................370
Rounding to n significant digits..................................................................371
Chapter 17: Introducing Array Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
Understanding Array Formulas............................................................................373
A multicell array formula...........................................................................374
A single-cell array formula.........................................................................376
Creating an Array Constant.................................................................................377
Understanding the Dimensions of an Array..........................................................378
One-dimensional horizontal arrays.............................................................378
One-dimensional vertical arrays..................................................................379
Two-dimensional arrays.............................................................................379
xxii
Contents
Naming Array Constants.....................................................................................381
Working with Array Formulas.............................................................................382
Entering an array formula..........................................................................382
Selecting an array formula range.................................................................382
Editing an array formula............................................................................383
Expanding or contracting a multicell array formula......................................384
Using Multicell Array Formulas...........................................................................384
Creating an array from values in a range.....................................................385
Creating an array constant from values in a range.......................................385
Performing operations on an array..............................................................386
Using functions with an array.....................................................................387
Transposing an array.................................................................................387
Generating an array of consecutive integers.................................................388
Using Single-Cell Array Formulas........................................................................390
Counting characters in a range...................................................................390
Summing the three smallest values in a range.............................................391
Counting text cells in a range.....................................................................392
Eliminating intermediate formulas..............................................................393
Using an array in lieu of a range reference..................................................395
Chapter 18: Performing Magic with Array Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Working with Single-Cell Array Formulas............................................................397
Summing a range that contains errors.........................................................397
Counting the number of error values in a range...........................................398
Summing the n largest values in a range......................................................399
Computing an average that excludes zeros..................................................400
Determining whether a particular value appears in a range..........................401
Counting the number of differences in two ranges.......................................402
Returning the location of the maximum value in a range..............................403
Finding the row of a value’s nth occurrence in a range.................................404
Returning the longest text in a range...........................................................404
Determining whether a range contains valid values.....................................404
Summing the digits of an integer................................................................406
Summing rounded values...........................................................................407
Summing every nth value in a range...........................................................408
Removing nonnumeric characters from a string...........................................409
Determining the closest value in a range.....................................................410
Returning the last value in a column...........................................................410
Returning the last value in a row................................................................412
Working with Multicell Array Formulas...............................................................413
Returning only positive values from a range................................................413
Returning nonblank cells from a range........................................................414
Reversing the order of cells in a range.........................................................414
Sorting a range of values dynamically.........................................................415
Returning a list of unique items in a range..................................................416
Displaying a calendar in a range.................................................................417
xxiii
Contents
Part III: Creating Charts and Graphics 419
Chapter 19: Getting Started Making Charts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
What Is a Chart?.................................................................................................421
Understanding How Excel Handles Charts...........................................................422
Embedded charts.......................................................................................423
Chart sheets..............................................................................................424
Creating a Chart.................................................................................................426
Hands On: Creating and Customizing a Chart......................................................426
Selecting the data.......................................................................................427
Choosing a chart type................................................................................427
Experimenting with different styles.............................................................429
Experimenting with different layouts..........................................................429
Trying another view of the data..................................................................430
Trying other chart types.............................................................................431
Working with Charts..........................................................................................433
Resizing a chart.........................................................................................433
Moving a chart..........................................................................................433
Copying a chart.........................................................................................434
Deleting a chart.........................................................................................434
Adding chart elements...............................................................................434
Moving and deleting chart elements............................................................434
Formatting chart elements..........................................................................436
Printing charts...........................................................................................436
Understanding Chart Types.................................................................................437
Choosing a chart type................................................................................437
Column charts...........................................................................................439
Bar charts..................................................................................................442
Line charts................................................................................................443
Pie charts..................................................................................................445
XY (scatter) charts.....................................................................................446
Area charts................................................................................................448
Radar charts..............................................................................................449
Surface charts............................................................................................450
Bubble charts............................................................................................452
Stock charts...............................................................................................453
Learning More....................................................................................................455
Chapter 20: Learning Advanced Charting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
Selecting Chart Elements.....................................................................................457
Selecting with the mouse............................................................................458
Selecting with the keyboard........................................................................459
Selecting with the Chart Element control.....................................................459
User Interface Choices for Modifying Chart Elements............................................460
Using the Format task pane........................................................................461
Using the chart customization buttons........................................................462
xxiv
Contents
Using the Ribbon.......................................................................................462
Using the Mini toolbar...............................................................................462
Modifying the Chart Area....................................................................................463
Modifying the Plot Area......................................................................................464
Working with Titles in a Chart............................................................................465
Working with a Legend......................................................................................466
Working with Gridlines.......................................................................................468
Modifying the Axes............................................................................................469
Value axis.................................................................................................469
Category axis.............................................................................................474
Working with Data Series...................................................................................478
Deleting or hiding a data series...................................................................478
Adding a new data series to a chart............................................................478
Changing data used by a series...................................................................479
Changing the data range by dragging the range outline........................480
Using the Edit Series dialog box.........................................................480
Editing the Series formula..................................................................481
Displaying data labels in a chart.................................................................482
Handling missing data................................................................................484
Adding error bars.......................................................................................485
Adding a trendline.....................................................................................486
Modifying 3-D charts..................................................................................487
Creating combination charts.......................................................................488
Displaying a data table...............................................................................491
Creating Chart Templates....................................................................................491
Learning Some Chart-Making Tricks....................................................................492
Creating picture charts...............................................................................492
Creating a thermometer chart.....................................................................494
Creating a gauge chart................................................................................495
Displaying conditional colors in a column chart...........................................496
Creating a comparative histogram...............................................................498
Creating a Gantt chart................................................................................499
Plotting mathematical functions with one variable.......................................500
Plotting mathematical functions with two variables.....................................501
Chapter 21: Visualizing Data Using Conditional Formatting. . . . . . . . . . . . . . . . . . . . . . . 503
About Conditional Formatting.............................................................................503
Specifying Conditional Formatting.......................................................................505
Formatting types you can apply..................................................................505
Making your own rules..............................................................................506
Conditional Formats That Use Graphics...............................................................507
Using data bars..........................................................................................507
A simple data bar..............................................................................507
Using data bars in lieu of a chart........................................................509
Using color scales......................................................................................509
A color scale example........................................................................510
An extreme color scale example.........................................................511
xxv
Contents
Using icon sets..........................................................................................512
An icon set example..........................................................................512
Another icon set example..................................................................514
Creating Formula-Based Rules.............................................................................515
Understanding relative and absolute references...........................................516
Conditional formatting formula examples....................................................517
Identifying weekend days..................................................................518
Highlighting a row based on a value...................................................518
Displaying alternate-row shading.......................................................519
Creating checkerboard shading..........................................................519
Shading groups of rows.....................................................................520
Displaying a total only when all values are entered.............................520
Working with Conditional Formats......................................................................521
Managing rules..........................................................................................521
Copying cells that contain conditional formatting........................................522
Deleting conditional formatting...................................................................522
Locating cells that contain conditional formatting........................................523
Chapter 22: Creating Sparkline Graphics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525
Sparkline Types..................................................................................................526
Creating Sparklines.............................................................................................527
Customizing Sparklines......................................................................................529
Sizing Sparkline cells.................................................................................529
Handling hidden or missing data................................................................529
Changing the Sparkline type.......................................................................530
Changing Sparkline colors and line width....................................................531
Highlighting certain data points..................................................................531
Adjusting Sparkline axis scaling..................................................................532
Faking a reference line...............................................................................533
Specifying a Date Axis........................................................................................535
Auto-Updating Sparklines...................................................................................536
Displaying a Sparkline for a Dynamic Range........................................................536
Chapter 23: Enhancing Your Work with Pictures and Drawings . . . . . . . . . . . . . . . . . . . . 539
Using Shapes......................................................................................................539
Inserting a Shape.......................................................................................540
Adding text to a Shape...............................................................................543
Formatting Shapes.....................................................................................543
Stacking Shapes.........................................................................................544
Grouping objects........................................................................................545
Aligning and spacing objects......................................................................545
Reshaping Shapes......................................................................................546
Printing objects..........................................................................................549
Using SmartArt...................................................................................................549
Inserting SmartArt.....................................................................................549
Customizing SmartArt................................................................................550
Changing the layout...................................................................................552
xxvi
Contents
Changing the style.....................................................................................552
Learning more about SmartArt...................................................................553
Using WordArt...................................................................................................553
Working with Other Graphic Types.....................................................................554
About graphics files...................................................................................555
Inserting screenshots..................................................................................558
Displaying a worksheet background image..................................................558
Using the Equation Editor...................................................................................558
Part IV: Using Advanced Excel Features 561
Chapter 24: Customizing the Excel User Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563
Customizing the Quick Access Toolbar................................................................563
About the Quick Access toolbar..................................................................564
Adding new commands to the Quick Access toolbar....................................565
Other Quick Access toolbar actions.............................................................567
Customizing the Ribbon......................................................................................568
Why you may want to customize the Ribbon..............................................569
What can be customized............................................................................569
How to customize the Ribbon.....................................................................569
Creating a new tab............................................................................570
Creating a new group........................................................................570
Adding commands to a new group.....................................................570
Resetting the Ribbon..................................................................................571
Chapter 25: Using Custom Number Formats. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
About Number Formatting..................................................................................573
Automatic number formatting....................................................................574
Formatting numbers by using the Ribbon....................................................574
Using shortcut keys to format numbers.......................................................575
Using the Format Cells dialog box to format numbers..................................576
Creating a Custom Number Format.....................................................................577
Parts of a number format string..................................................................578
Custom number format codes.....................................................................580
Custom Number Format Examples......................................................................582
Scaling values............................................................................................582
Displaying values in thousands..........................................................582
Displaying values in hundreds...........................................................583
Displaying values in millions.............................................................583
Appending zeros to a value................................................................585
Displaying leading zeros.............................................................................585
Specifying conditions.................................................................................586
Displaying fractions...................................................................................586
Displaying a negative sign on the right........................................................587
Formatting dates and times........................................................................587
Displaying text with numbers.....................................................................588
xxvii
Contents
Suppressing certain types of entries............................................................589
Filling a cell with a repeating character.......................................................590
Chapter 26: Using Data Validation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591
About Data Validation........................................................................................591
Specifying Validation Criteria..............................................................................592
Types of Validation Criteria You Can Apply.........................................................593
Creating a Drop-Down List..................................................................................595
Using Formulas for Data Validation Rules............................................................596
Understanding Cell References............................................................................596
Data Validation Formula Examples......................................................................598
Accepting text only....................................................................................598
Accepting a larger value than the previous cell............................................598
Accepting nonduplicate entries only............................................................598
Accepting text that begins with a specific character.....................................599
Accepting dates by the day of the week.......................................................600
Accepting only values that don’t exceed a total...........................................600
Creating a dependent list............................................................................600
Chapter 27: Creating and Using Worksheet Outlines. . . . . . . . . . . . . . . . . . . . . . . . . . . . 603
Introducing Worksheet Outlines..........................................................................603
Creating an Outline............................................................................................607
Preparing the data......................................................................................607
Creating an outline automatically................................................................608
Creating an outline manually......................................................................608
Working with Outlines........................................................................................610
Displaying levels........................................................................................610
Adding data to an outline...........................................................................611
Removing an outline..................................................................................611
Adjusting the outline symbols....................................................................611
Hiding the outline symbols.........................................................................612
Chapter 28: Linking and Consolidating Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613
Linking Workbooks............................................................................................613
Creating External Reference Formulas.................................................................614
Understanding link formula syntax.............................................................614
Creating a link formula by pointing.............................................................615
Pasting links..............................................................................................616
Working with External Reference Formulas.........................................................616
Creating links to unsaved workbooks..........................................................616
Opening a workbook with external reference formulas.................................617
Changing the startup prompt......................................................................618
Updating links...........................................................................................619
Changing the link source............................................................................619
Severing links............................................................................................619
Avoiding Potential Problems with External Reference Formulas............................620
Renaming or moving a source workbook....................................................620
Using the Save As command......................................................................620
xxviii
Contents
Modifying a source workbook....................................................................620
Intermediary links......................................................................................621
Consolidating Worksheets...................................................................................622
Consolidating worksheets by using formulas...............................................623
Consolidating worksheets by using Paste Special.........................................624
Consolidating worksheets by using the Consolidate dialog box.....................625
A workbook consolidation example............................................................626
Refreshing a consolidation..........................................................................628
More about consolidation...........................................................................629
Chapter 29: Excel and the Internet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
Saving a Workbook on the Internet.....................................................................631
Saving Workbooks in HTML Format....................................................................632
Creating an HTML file................................................................................634
Creating a single-file web page...................................................................635
Opening an HTML File........................................................................................636
Working with Hyperlinks....................................................................................637
Inserting a hyperlink..................................................................................637
Using hyperlinks........................................................................................639
Using Web Queries.............................................................................................639
E-Mail Features..................................................................................................642
Chapter 30: Protecting Your Work. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 643
Types of Protection.............................................................................................643
Protecting a Worksheet.......................................................................................644
Unlocking cells..........................................................................................645
Sheet protection options.............................................................................646
Assigning user permissions........................................................................647
Protecting a Workbook.......................................................................................647
Requiring a password to open a workbook..................................................647
Protecting a workbook’s structure...............................................................649
VB Project Protection..........................................................................................650
Related Topics....................................................................................................651
Saving a worksheet as a PDF file................................................................651
Marking a workbook final..........................................................................651
Inspecting a workbook...............................................................................652
Using a digital signature.............................................................................653
Getting a digital ID............................................................................653
Signing a workbook...........................................................................654
Chapter 31: Making Your Worksheets Error Free. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655
Finding and Correcting Formula Errors................................................................655
Mismatched parentheses............................................................................656
Cells are filled with hash marks..................................................................657
Blank cells are not blank............................................................................658
Extra space characters................................................................................658
Formulas returning an error.......................................................................660
xxix
Contents
#DIV/0! errors...................................................................................660
#N/A errors......................................................................................661
#NAME? errors..................................................................................662
#NULL! errors...................................................................................662
#NUM! errors....................................................................................662
#REF! errors......................................................................................663
#VALUE! errors.................................................................................663
Absolute/relative reference problems..........................................................664
Operator precedence problems...................................................................664
Formulas are not calculated........................................................................665
Actual versus displayed values...................................................................665
Floating point number errors......................................................................666
“Phantom link” errors................................................................................667
Using Excel Auditing Tools.................................................................................668
Identifying cells of a particular type............................................................668
Viewing formulas.......................................................................................669
Tracing cell relationships............................................................................671
Identifying precedents.......................................................................672
Identifying dependents......................................................................672
Tracing error values...................................................................................673
Fixing circular reference errors...................................................................673
Using the background error-checking feature...............................................673
Using Formula Evaluator............................................................................675
Searching and Replacing.....................................................................................676
Searching for information...........................................................................677
Replacing information................................................................................678
Searching for formatting.............................................................................678
Spell-Checking Your Worksheets.........................................................................680
Using AutoCorrect..............................................................................................681
Part V: Analyzing Data with Excel 683
Chapter 32: Importing and Cleaning Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685
Importing Data...................................................................................................686
Importing from a file..................................................................................686
Spreadsheet file formats.....................................................................686
Database file formats.........................................................................687
Text file formats................................................................................687
Importing HTML files........................................................................688
Importing XML files...........................................................................688
Importing a text file into a specified range...................................................688
Copying and pasting data...........................................................................690
Data Clean-up Techniques..................................................................................690
Removing duplicate rows...........................................................................690
Identifying duplicate rows..........................................................................692
Splitting text..............................................................................................692
xxx
Contents
Using Text to Columns......................................................................694
Using Flash Fill.................................................................................695
Changing the case of text...........................................................................698
Removing extra spaces...............................................................................698
Removing strange characters......................................................................699
Converting values......................................................................................700
Classifying values......................................................................................701
Joining columns........................................................................................702
Rearranging columns.................................................................................703
Randomizing the rows...............................................................................703
Extracting a filename from a URL...............................................................703
Matching text in a list................................................................................704
Changing vertical data to horizontal data....................................................705
Filling gaps in an imported report...............................................................707
Checking spelling.......................................................................................708
Replacing or removing text in cells.............................................................709
Adding text to cells....................................................................................710
Fixing trailing minus signs..........................................................................711
A Data Cleaning Checklist...................................................................................712
Exporting Data...................................................................................................712
Exporting to a text file................................................................................712
CSV files...........................................................................................712
TXT files...........................................................................................713
PRN files...........................................................................................713
Exporting to other file formats....................................................................713
Chapter 33: Introducing Pivot Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715
About Pivot Tables.............................................................................................715
A pivot table example................................................................................716
Data appropriate for a pivot table...............................................................719
Creating a Pivot Table Automatically...................................................................722
Creating a Pivot Table Manually..........................................................................723
Specifying the data.....................................................................................723
Specifying the location for the pivot table....................................................724
Laying out the pivot table...........................................................................725
Formatting the pivot table..........................................................................727
Modifying the pivot table...........................................................................729
More Pivot Table Examples.................................................................................731
What is the daily total new deposit amount for each branch?.......................731
Which day of the week accounts for the most deposits?...............................732
How many accounts were opened at each branch, broken
down by account type?..........................................................................733
What’s the dollar distribution of the different account types?.......................733
What types of accounts do tellers open most often?.....................................734
How does the Central branch compare with the other two branches?............735
In which branch do tellers open the most checking
accounts for new customers?..................................................................736
Learning More....................................................................................................737
xxxi
Contents
Chapter 34: Analyzing Data with Pivot Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739
Working with Non-Numeric Data........................................................................739
Grouping Pivot Table Items.................................................................................741
A manual grouping example.......................................................................741
Automatic grouping examples....................................................................742
Grouping by date..............................................................................742
Grouping by time..............................................................................746
Creating a Frequency Distribution.......................................................................748
Creating a Calculated Field or Calculated Item.....................................................750
Creating a calculated field..........................................................................752
Inserting a calculated item..........................................................................754
Filtering Pivot Tables with Slicers........................................................................756
Filtering Pivot Tables with a Timeline..................................................................758
Referencing Cells within a Pivot Table.................................................................759
Creating Pivot Charts..........................................................................................761
A pivot chart example................................................................................762
More about pivot charts.............................................................................765
Another Pivot Table Example..............................................................................765
Producing a Report with a Pivot Table.................................................................769
Using the Data Model.........................................................................................771
Learning More about Pivot Tables.......................................................................775
Chapter 35: Performing Spreadsheet What-If Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . 777
A What-If Example.............................................................................................777
Types of What-If Analyses..................................................................................778
Performing manual what-if analysis............................................................779
Creating data tables...................................................................................779
Creating a one-input data table..........................................................780
Creating a two-input data table..........................................................782
Using Scenario Manager.............................................................................786
Defining scenarios.............................................................................786
Displaying scenarios..........................................................................789
Modifying scenarios..........................................................................790
Merging scenarios.............................................................................790
Generating a scenario report..............................................................791
Chapter 36: Analyzing Data Using Goal Seeking and Solver . . . . . . . . . . . . . . . . . . . . . . 793
What-If Analysis, in Reverse...............................................................................793
Single-Cell Goal Seeking.....................................................................................794
A goal-seeking example..............................................................................794
More about goal seeking............................................................................796
Introducing Solver..............................................................................................797
Appropriate problems for Solver.................................................................797
A simple Solver example............................................................................798
More about Solver.....................................................................................802
Solver Examples.................................................................................................804
Solving simultaneous linear equations........................................................804
Minimizing shipping costs..........................................................................806
xxxii
Contents
Allocating resources...................................................................................809
Optimizing an investment portfolio.............................................................810
Chapter 37: Analyzing Data with the Analysis ToolPak . . . . . . . . . . . . . . . . . . . . . . . . . . 813
The Analysis ToolPak: An Overview....................................................................813
Installing the Analysis ToolPak Add-in................................................................814
Using the Analysis Tools.....................................................................................814
Introducing the Analysis ToolPak Tools...............................................................815
Analysis of Variance..................................................................................815
Correlation................................................................................................816
Covariance................................................................................................817
Descriptive Statistics..................................................................................817
Exponential Smoothing..............................................................................818
F-test (two-sample test for variance)...........................................................818
Fourier Analysis.........................................................................................819
Histogram.................................................................................................819
Moving Average........................................................................................820
Random Number Generation......................................................................821
Rank and Percentile...................................................................................822
Regression.................................................................................................822
Sampling...................................................................................................823
T-Test.......................................................................................................824
Z-Test (two-sample test for means).............................................................824
Part VI: Programming Excel with VBA 825
Chapter 38: Introducing Visual Basic for Applications. . . . . . . . . . . . . . . . . . . . . . . . . . . 827
Introducing VBA Macros.....................................................................................827
Displaying the Developer Tab.............................................................................828
About Macro Security.........................................................................................829
Saving Workbooks That Contain Macros.............................................................830
Two Types of VBA Macros..................................................................................831
VBA Sub procedures..................................................................................831
VBA functions...........................................................................................832
Creating VBA Macros..........................................................................................834
Recording VBA macros...............................................................................834
Recording your actions to create VBA code: The basics.......................834
Recording a macro: A simple example................................................835
Examining the macro.........................................................................836
Testing the macro..............................................................................837
Editing the macro..............................................................................837
Another example...............................................................................838
Running the macro............................................................................838
Examining the macro.........................................................................839
Rerecording the macro.......................................................................840
Testing the macro..............................................................................840
xxxiii
Contents
More about recording VBA macros.............................................................840
Absolute versus relative recording......................................................841
Storing macros in your Personal Macro Workbook..............................842
Assigning a macro to a shortcut key...................................................842
Assigning a macro to a button............................................................843
Adding a macro to your Quick Access toolbar.....................................844
Writing VBA code......................................................................................844
The basics: Entering and editing code.................................................844
How VBA works................................................................................845
Objects and collections......................................................................847
Properties.........................................................................................848
Methods...........................................................................................850
Variables..........................................................................................850
Controlling execution........................................................................850
A macro that can’t be recorded..........................................................852
Learning More....................................................................................................854
Chapter 39: Creating Custom Worksheet Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 855
Overview of VBA Functions................................................................................855
An Introductory Example....................................................................................856
A custom function.....................................................................................856
Using the function in a worksheet...............................................................857
Analyzing the custom function...................................................................857
About Function Procedures.................................................................................859
Executing Function Procedures...........................................................................859
Calling custom functions from a procedure.................................................860
Using custom functions in a worksheet formula...........................................860
Function Procedure Arguments...........................................................................861
A function with no argument......................................................................861
A function with one argument....................................................................861
Another function with one argument..........................................................862
A function with two arguments..................................................................863
A function with a range argument..............................................................864
A simple but useful function.......................................................................865
Debugging Custom Functions..............................................................................866
Inserting Custom Functions.................................................................................866
Learning More....................................................................................................868
Chapter 40: Creating UserForms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 869
Why Create UserForms?......................................................................................869
UserForm Alternatives........................................................................................870
The InputBox function...............................................................................870
The MsgBox function.................................................................................871
Creating UserForms: An Overview.......................................................................874
Working with UserForms...........................................................................875
Adding controls.........................................................................................875
Changing the properties of a control...........................................................876
xxxiv
Contents
Handling events.........................................................................................877
Displaying a UserForm...............................................................................878
A UserForm Example..........................................................................................878
Creating the UserForm...............................................................................878
Testing the UserForm.................................................................................880
Creating an event handler procedure...........................................................880
Another UserForm Example................................................................................881
Creating the UserForm...............................................................................882
Testing the UserForm.................................................................................883
Creating event handler procedures..............................................................884
Testing the UserForm.................................................................................886
Making the macro available from a worksheet button..................................886
Making the macro available on your Quick Access toolbar...........................887
More on Creating UserForms...............................................................................887
Adding accelerator keys.............................................................................887
Controlling tab order..................................................................................888
Learning More....................................................................................................888
Chapter 41: Using UserForm Controls in a Worksheet. . . . . . . . . . . . . . . . . . . . . . . . . . . 889
Why Use Controls on a Worksheet?.....................................................................889
Using Controls...................................................................................................891
Adding a control........................................................................................892
About Design mode...................................................................................892
Adjusting properties...................................................................................892
Common properties...................................................................................893
Linking controls to cells.............................................................................894
Creating macros for controls.......................................................................894
Reviewing the Available ActiveX Controls............................................................896
CheckBox..................................................................................................896
ComboBox................................................................................................897
CommandButton........................................................................................898
Image........................................................................................................898
Label.........................................................................................................898
ListBox......................................................................................................898
OptionButton.............................................................................................899
ScrollBar...................................................................................................900
SpinButton................................................................................................901
TextBox....................................................................................................901
ToggleButton.............................................................................................902
Chapter 42: Working with Excel Events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 903
Understanding Events.........................................................................................903
Entering Event-Handler VBA Code.......................................................................904
Using Workbook-Level Events.............................................................................906
Using the Open event.................................................................................906
Using the SheetActivate event.....................................................................907
Using the NewSheet event..........................................................................908
xxxv
Contents
Using the BeforeSave event........................................................................908
Using the BeforeClose event.......................................................................909
Working with Worksheet Events.........................................................................909
Using the Change event..............................................................................909
Monitoring a specific range for changes......................................................910
Using the SelectionChange event................................................................911
Using the BeforeRightClick event................................................................912
Using Non-Object Events....................................................................................912
Using the OnTime event.............................................................................913
Using the OnKey event...............................................................................913
Chapter 43: VBA Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 915
Working with Ranges.........................................................................................915
Copying a range.........................................................................................916
Copying a variable-size range.....................................................................916
Selecting to the end of a row or column......................................................917
Selecting a row or column..........................................................................918
Moving a range..........................................................................................918
Looping through a range efficiently.............................................................919
Prompting for a cell value..........................................................................920
Determining the type of selection................................................................922
Identifying a multiple selection...................................................................923
Counting selected cells...............................................................................923
Working with Workbooks...................................................................................924
Saving all workbooks.................................................................................924
Saving and closing all workbooks...............................................................925
Working with Charts..........................................................................................925
Modifying the chart type............................................................................926
Modifying chart properties.........................................................................926
Applying chart formatting..........................................................................927
VBA Speed Tips.................................................................................................927
Turning off screen updating.......................................................................927
Preventing alert messages..........................................................................928
Simplifying object references......................................................................928
Declaring variable types.............................................................................929
Chapter 44: Creating Custom Excel Add-Ins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 931
What Is an Add-In?.............................................................................................931
Working with Add-Ins........................................................................................932
Why Create Add-Ins?..........................................................................................933
Creating Add-Ins................................................................................................934
An Add-In Example............................................................................................935
About Module1..........................................................................................936
About the UserForm...................................................................................936
Testing the workbook................................................................................937
Adding descriptive information...................................................................937
Creating the user interface for your add-in macro........................................937
xxxvi
Contents
Protecting the project.................................................................................938
Creating the add-in....................................................................................939
Installing the add-in...................................................................................939
Part VII: Appendixes 941
Appendix A: Worksheet Function Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 943
Appendix B: Excel Shortcut Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 961
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 967
Download
Для скачивания .torrent файлов необходима регистрация
Сайт не распространяет и не хранит электронные версии произведений, а лишь предоставляет доступ к создаваемому пользователями каталогу ссылок на торрент-файлы, которые содержат только списки хеш-сумм
Показать сообщения:    
Ответить на тему