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

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