List of Figures

Chapter 1: Excel in a Nutshell

Figure 1-1: The original Excel 2 for Windows. Excel has come a long way since its original version. (Photo courtesy of Microsoft Corporation)

Figure 1-2: The Ribbon sizes dynamically, depending on the horizontal size of Excel's window.

Figure 1-3: Clicking the Office Button displays a menu, and some of the menu items have submenus.

Figure 1-4: The shortcut menu and Mini Toolbar appears when you right-click a range.

Figure 1-5: This Smart Tag appears when you paste a copied range.

Figure 1-6: The Clip Art task pane allows you to search for and insert an image.

Figure 1-7: Using a keyboard sequence like Alt+I+R (for Insert Row) can still be used to insert a row and will display this ScreenTip during the process.

Figure 1-8: The Excel Help system window.

Figure 1-9: Objects on a worksheet drawing layer. Excel makes a great doodle pad.

Figure 1-10: Excel enables you to add many controls directly to the drawing layer of a worksheet.

Figure 1-11: Excel's Filter feature makes it easy to view only the database records that meet your criteria.

Figure 1-12: Excel can automatically insert subtotal formulas and create outlines.

Figure 1-13: Choose which elements to protect in the Protect Sheet dialog box.

Figure 1-14: The Protect Structure and Windows dialog box.

Figure 1-15: Use the Encrypt Document dialog box to specify a password for a workbook.

Chapter 2: Basic Facts about Formulas

Figure 2-1: This formula contains spaces and line breaks.

Figure 2-2: Excel's Formula AutoCorrect feature often suggests a correction to an erroneous formula.

Figure 2-3: This worksheet demonstrates the use of an absolute reference.

Figure 2-4: An example of using mixed references in a formula.

Figure 2-5: A Smart Tag appears after pasting data.

Figure 2-6: Use the Format Cells dialog box to change the Hidden and Locked status of a cell or range.

Figure 2-7: Excel's way of telling you that your formula contains a circular reference.

Figure 2-8: This worksheet presents a simple demonstration of goal seeking.

Figure 2-9: The Goal Seek dialog box.

Chapter 3: Working with Names

Figure 3-1: The Excel Name Manager dialog box.

Figure 3-2: The New Name dialog box.

Figure 3-3: The Name box provides a quick way to activate a named cell or range.

Figure 3-4: Excel makes it easy to create names by using text in adjacent cells.

Figure 3-5: The Create Names From Selection dialog box.

Figure 3-6: Using the Create From Selection command to create names from the data in this table may produce unexpected results.

Figure 3-7: This worksheet, which tracks daily sales, uses a named range that consists of an entire column.

Figure 3-8: Create a multisheet name.

Figure 3-9: The Paste Name dialog box.

Figure 3-10: This worksheet contains named ranges that correspond to row and column labels.

Figure 3-11: Range B3:B8 in this worksheet is named MyData. Cell D5 demonstrates an implicit intersection.

Figure 3-12: The Apply Names dialog box.

Figure 3-13: Excel displays range names when you zoom a sheet to 39 percent or less.

Figure 3-14: Copying a worksheet creates duplicated names.

Figure 3-15: Deleting the sheet that contains the cell for MyRange causes an erroneous reference.

Figure 3-16: Technically, the name InterestRate is a named formula, not a named cell.

Figure 3-17: Defining a name that refers to a constant.

Figure 3-18: Defining a named formula that uses worksheet functions.

Figure 3-19: Defining a named formula that uses a cell reference.

Figure 3-20: Defining a named formula that uses a range reference.

Figure 3-21: The CellToRight named formula varies, depending on the active cell.

Figure 3-22: Using the INDIRECT function with a named range.

Figure 3-23: You can enter a named formula that contains a 12-item array into 12 adjacent cells.

Figure 3-24: You can use a dynamic named formula to represent the sales data in column B.

Chapter 4: Introducing Worksheet Functions

Figure 4-1: When you begin to enter a function, Excel lists available functions that begin with the typed letters.

Figure 4-2: The Insert Function dialog box.

Figure 4-3: The Function Arguments dialog box.

Figure 4-4: Don't forget about Excel's Help system. It's the most comprehensive function reference source available.

Figure 4-5: Press Ctrl+Shift+A to instruct Excel to display descriptive placeholders for a function.

Chapter 5: Manipulating Text

Figure 5-1: The ANSI character set (for the Calibri font).

Figure 5-2: The formula in D1 doesn't display the formatted number.

Figure 5-3: Using the REPT function to create a histogram in a worksheet range.

Figure 5-4: Using a formula to pad a number with asterisks.

Figure 5-5: Using a formula to express a number as an ordinal.

Figure 5-6: This worksheet uses formulas to extract the first name, middle name (or initial), and last name from a list of names in column A.

Chapter 6: Working with Dates and Times

Figure 6-1: Use the Number Format drop-down list to change the appearance of dates and times.

Figure 6-2: Using Excel's AutoFill feature to create a series of dates.

Figure 6-3: Using the NETWORKDAYS function to calculate the number of working days between two dates.

Figure 6-4: Calculating the number of each weekday in each month of a year.

Figure 6-5: Using formulas to determine the date for various holidays.

Figure 6-6: Incorrect cell formatting makes the total appear incorrectly.

Figure 6-7: An employee time sheet workbook.

Figure 6-8: Calculating the number of hours worked returns an error if the shift spans midnight.

Figure 6-9: Using a formula to create a series of incremental times.

Figure 6-10: This worksheet converts dates and times between time zones.

Figure 6-11: This worksheet uses times not associated with a time of day.

Chapter 7: Counting and Summing Techniques

Figure 7-1: Formulas provide various counts of the data in A1:B10.

Figure 7-2: This worksheet demonstrates various counting techniques that use multiple criteria.

Figure 7-3: The MODE function returns the most frequently occurring value in a range.

Figure 7-4: This worksheet demonstrates various ways to count characters in a range.

Figure 7-5: Using an array formula to count the number of unique values in a range.

Figure 7-6: Creating a frequency distribution for the data in A1:E20.

Figure 7-7: Frequency distributions created using the FREQUENCY function.

Figure 7-8: Creating a frequency distribution of test scores.

Figure 7-9: The Analysis ToolPak's Histogram dialog box.

Figure 7-10: A frequency distribution and chart generated by the Analysis ToolPak's Histogram option.

Figure 7-11: The chart displays a histogram; the contents of cell E1 determine the number of categories.

Figure 7-12: Simple formulas in column C display a cumulative sum of the values in column B.

Figure 7-13: Using an IF function to hide cumulative sums for missing data.

Figure 7-14: A negative value in column F indicates a past-due payment.

Figure 7-15: This worksheet demonstrates summing based on multiple criteria.

Chapter 8: Using Lookup Functions

Figure 8-1: Lookup formulas in row 2 look up the information for the employee name in cell B2.

Figure 8-2: Using VLOOKUP to look up a tax rate.

Figure 8-3: Using HLOOKUP to look up a tax rate.

Figure 8-4: Using LOOKUP to look up a tax rate.

Figure 8-5: Using the INDEX and MATCH functions to perform a lookup.

Figure 8-6: This lookup table requires an exact match.

Figure 8-7: The VLOOKUP function can't look up a value in column B, based on a value in column C.

Figure 8-8: Using an array formula to perform a case-sensitive lookup.

Figure 8-9: This worksheet demonstrates the use of multiple lookup tables.

Figure 8-10: Looking up letter grades for test scores.

Figure 8-11: Using multiple formulas to calculate a GPA.

Figure 8-12: This table demonstrates a two-way lookup.

Figure 8-13: The Lookup Wizard add-in can create a formula that performs a two-way lookup.

Figure 8-14: This workbook performs a lookup by using information in two columns (D and E).

Figure 8-15: The formula in cell B2 returns the address in the Data range for the value in cell B1.

Figure 8-16: This workbook demonstrates how to perform a lookup by using the closest match.

Figure 8-17: This workbook demonstrates a table lookup using linear interpolation.

Figure 8-18: Column B contains formulas that perform a lookup using linear interpolation.

Figure 8-19: This worksheet uses a formula that uses the LOOKUP function and the TREND function.

Chapter 9: Tables and Worksheet Databases

Figure 9-1: A typical worksheet database.

Figure 9-2: A worksheet database, converted to a table.

Figure 9-3: When you select a cell in a table, you can use the commands on the Table Tools contextual menu.

Figure 9-4: Excel offers many different table styles.

Figure 9-5: Removing duplicate rows from a table is easy.

Figure 9-6: Each column in a table contains sorting and filtering options.

Figure 9-7: A table, after performing a three-column sort.

Figure 9-8: Using the Sort dialog box to specify a three-column sort.

Figure 9-9: This table is filtered to show only the information for N. County.

Figure 9-10: Several types of summary functions are available for the Totals Row.

Figure 9-11: Adding a calculated column to this table is easy.

Figure 9-12: The Difference column contains a formula.

Figure 9-13: This table shows sales by month and by region.

Figure 9-14: The formula AutoComplete feature is useful when creating a formula that refers to data in a table.

Figure 9-15: This real estate listing database is used to demonstrate advanced filtering.

Figure 9-16: A criteria range for advanced filtering.

Figure 9-17: The Advanced Filter dialog box.

Figure 9-18: The result of applying an advanced filter.

Figure 9-19: The criteria range (A1:A2) selects records that describe homes with four bedrooms.

Figure 9-20: This criteria range uses multiple columns that select records using a logical AND operation.

Figure 9-21: This criteria range selects records that describe properties that were listed in the month of March.

Figure 9-22: This criteria range has two sets of criteria, each of which is in a separate row.

Figure 9-23: Repeating values in the criteria range applies the OR operator to only those criteria that aren't repeated.

Figure 9-24: Using computed criteria with advanced filtering.

Figure 9-25: Using the DSUM function to sum a table using a criteria range.

Figure 9-26: This database is a good candidate for subtotals, which are inserted at each change of the month and at each change of the region.

Figure 9-27: The Subtotal dialog box automatically inserts subtotal formulas into a sorted table.

Figure 9-28: Excel adds the subtotal formulas automatically and creates an outline.

Figure 9-29: Use the outline controls to hide the detail and display only the summary rows.

Chapter 10: Miscellaneous Calculations

Figure 10-1: A right triangle's components.

Figure 10-2: This workbook is useful for working with right triangles.

Figure 10-3: Using formulas to solve simultaneous equations.

Chapter 11: Borrowing and Investing Formulas

Figure 11-1: Some present value calculations.

Figure 11-2: Calculating the present value of a lump sum.

Figure 11-3: Calculating a present value of an annuity with a lump sum.

Figure 11-4: Calculating the future value of payments.

Figure 11-5: Calculating the future value a lump sum.

Figure 11-6: Calculating the future value of payments and a lump sum.

Figure 11-7: Calculating a loan payment.

Figure 11-8: Calculating retirement payments.

Figure 11-9: Calculating the interest rate on a short-term loan.

Figure 11-10: Calculating a growth rate.

Figure 11-11: An amortization schedule to verify the results of the RATE function.

Figure 11-12: Using the NPER function for retirement calculations.

Figure 11-13: Calculating the effect of an early loan payoff.

Figure 11-14: Calculating the principal and interest component of selected payments.

Figure 11-15: Using the CUMIPMT and CUMPRINC functions.

Figure 11-16: Calculating the present value of regular payments with a deferred start is a two-step process.

Figure 11-17: Using the PRICE function.

Figure 11-18: When the price is higher than face value, the yield is lower than the coupon.

Chapter 12: Discounting and Depreciation Formulas

Figure 12-1: Three methods of computing net present value.

Figure 12-2: An initial investment returns positive future cash flows.

Figure 12-3: The NPV function can be used to determine the initial investment required.

Figure 12-4: Some NPV calculations include an initial cash inflow.

Figure 12-5: The initial investment may still have value at the end of the cash flows.

Figure 12-6: The NPV function can include an initial value and a terminal value.

Figure 12-7: The NPV function can accept multiple positive and negative cash flows.

Figure 12-8: Calculating the NPV using quarterly cash flows.

Figure 12-9: Calculating the NPV by annualizing quarterly cash flows.

Figure 12-10: Calculating FV using the NPV function.

Figure 12-11: Calculating equivalent payments with NPV.

Figure 12-12: The IRR returns the rate based on the cash flow frequency and should be converted into an annual rate.

Figure 12-13: Using the IRR function to calculate geometric average growth.

Figure 12-14: Checking IRR and NPV using sum of PV approach.

Figure 12-15: The same cash flows can have multiple IRRs.

Figure 12-16: Multiple internal rate of return.

Figure 12-17: Accumulating balance approach for multiple IRRs.

Figure 12-18: The XNPV function works with irregular cash flows.

Figure 12-19: The XIRR function works with irregular cash flows.

Figure 12-20: Monthly returns for a mutual fund.

Figure 12-21: A comparison of four depreciation functions.

Figure 12-22: This chart shows an asset's value over time, using four depreciation functions.

Figure 12-23: Using the VDB function to calculate accumulated depreciation.

Chapter 13: Financial Schedules

Figure 13-1: A simple amortization schedule.

Figure 13-2: A dynamic amortization schedule.

Figure 13-3: Keeping the user input isolated in its own area.

Figure 13-4: Calculating a credit card payment schedule.

Figure 13-5: The structure for a one-way data table.

Figure 13-6: Using a one-way data table to display three loan calculations for various interest rates.

Figure 13-7: The Excel Data Table dialog box.

Figure 13-8: The structure for a two-way data table.

Figure 13-9: Using a two-way data table to display payment amounts for various loan amounts and interest rates.

Figure 13-10: A trial balance lists all accounts and balances.

Figure 13-11: A balance sheet summarizes certain accounts.

Figure 13-12: The income statement can include a statement of retained earnings.

Figure 13-13: Entries on a common size income statement are shown relative to revenue.

Figure 13-14: Various financial ratio calculations.

Figure 13-15: Creating an index from growth data.

Chapter 14: Introducing Arrays

Figure 14-1: Column D contains formulas to calculate the total for each product.

Figure 14-2: A 3 x 4 array, entered into a range of cells.

Figure 14-3: A 3 x 4 array, entered into a 10 x 5 cell range.

Figure 14-4: Creating a named array constant.

Figure 14-5: Using a named array in an array formula.

Figure 14-6: Excel's warning message reminds you that you can't edit just one cell of a multicell array formula.

Figure 14-7: Creating an array from a range.

Figure 14-8: After you press F9, the formula bar displays the array constant.

Figure 14-9: Performing a mathematical operation on an array.

Figure 14-10: Multiplying each array element by itself.

Figure 14-11: Using the TRANSPOSE function to transpose a rectangular array.

Figure 14-12: Using an array formula to generate consecutive integers.

Figure 14-13: The goal is to count the number of characters in a range of text.

Figure 14-14: An array formula returns the sum of the three smallest values in A1:A10.

Figure 14-15: An array formula returns the number of text cells in the range.

Figure 14-16: Without an array formula, calculating the average change requires intermediate formulas in column D.

Figure 14-17: You can replace the lookup table in D1:E10 with an array constant.

Chapter 15: Performing Magic with Array Formulas

Figure 15-1: An array formula can sum a range of values, even if the range contains errors.

Figure 15-2: Calculating an average excluding cells that contain a 0.

Figure 15-3: Using an array formula to determine whether a range contains a particular value.

Figure 15-4: An array formula calculates the sum of the digits in an integer.

Figure 15-5: Using an array formula to correct rounding errors.

Figure 15-6: An array formula returns the sum of every nth value in the range.

Figure 15-7: An array formula returns the closest match.

Figure 15-8: Ranking data with Excel's RANK function and with array formulas.

Figure 15-9: You can use array formulas to summarize data such as this in a dynamic crosstab table.

Figure 15-10: Using an array formula to return only the positive values in a range.

Figure 15-11: A multicell array formula reverses the order of the values in the range.

Figure 15-12: A multicell array formula in column C sorts the values entered into column A.

Figure 15-13: Using an array formula to return unique items from a list.

Figure 15-14: Displaying a calendar using a single array formula.

Figure 15-15: A simpler version of the formula also displays dates from the preceding and subsequent month.

Chapter 16: Intentional Circular References

Figure 16-1: Excel's way of telling you that your formula contains a circular reference.

Figure 16-2: The Circular Reference command displays a list of cells involved in a circular reference.

Figure 16-3: To calculate a circular reference, you must check the Enable Iterative Calculation check box.

Figure 16-4: The company also deducts the 5 percent contribution of net profits as an expense (shown in cell B3), creating an intentional circular reference.

Figure 16-5: Using circular reference formulas to generate unique random integers in column A.

Figure 16-6: This workbook uses circular references to calculate several recursive equations.

Figure 16-7: This worksheet solves two simultaneous equations.

Figure 16-8: This uses a single-point data series that's calculated with a circular reference formulas.

Chapter 17: Charting Techniques

Figure 17-1: The formula bar displays the SERIES formula for the selected data series in a chart.

Figure 17-2: A SERIES formula that uses arrays rather than ranges.

Figure 17-3: The chart title is linked to cell A1.

Figure 17-4: This chart displays progress toward a goal.

Figure 17-5: This chart resembles a speedometer gauge and displays a value between 0 and 100 percent.

Figure 17-6: The color of the column depends varies with the value.

Figure 17-7: A comparative histogram.

Figure 17-8: You can create a simple Gantt chart from a bar chart.

Figure 17-9: This box plot summarizes the data in columns A through D.

Figure 17-10: This chart plots every nth data point (specified in A1) by ignoring data in the rows hidden by filtering.

Figure 17-11: This chart displays the six most recent data points.

Figure 17-12: Selecting data to plot using a Combo Box.

Figure 17-13: This chart plots the SIN(x).

Figure 17-14: A general-purpose, single-variable plotting workbook.

Figure 17-15: Using a surface chart to plot a function with two variables.

Figure 17-16: A general-purpose, two-variable plotting workbook.

Figure 17-17: Creating a circle using an XY chart.

Figure 17-18: A general circle plotting application.

Figure 17-19: This fully functional clock is actually an XY chart in disguise.

Figure 17-20: Displaying a digital clock in a worksheet is much easier but not as fun to create.

Figure 17-21: A hypocycloid curve.

Figure 17-22: The Format Trendline dialog box offers several types of automatic trendlines.

Figure 17-23: Before (left chart) and after (right chart) adding a linear trendline to a chart.

Figure 17-24: Using the LINEST function to calculate slope and y-intercept.

Figure 17-25: Column E contains formulas that calculate the predicted values for y.

Figure 17-26: Using a trendline to forecast values for two additional periods of time.

Figure 17-27: A chart displaying a logarithmic trendline.

Figure 17-28: A chart displaying a power trendline.

Figure 17-29: A chart displaying an exponential trendline.

Figure 17-30: A chart displaying a polynomial trendline.

Chapter 18: Pivot Tables

Figure 18-1: This table is used to create a pivot table.

Figure 18-2: A simple pivot table.

Figure 18-3: A pivot table that uses a report filter.

Figure 18-4: This range is not appropriate for a pivot table.

Figure 18-5: In the Create PivotTable dialog box, you tell Excel where the data is and then specify a location for the pivot table.

Figure 18-6: Use the PivotTable Field List to build the pivot table.

Figure 18-7: After a few simple steps, the pivot table shows a summary of the data.

Figure 18-8: The PivotTable Options dialog box.

Figure 18-9: Two fields are used for row labels.

Figure 18-10: The pivot table is filtered by date.

Figure 18-11: This pivot table shows daily totals for each branch.

Figure 18-12: This pivot table uses the Count function to summarize the data.

Figure 18-13: This pivot table counts the number of accounts that fall into each value range.

Figure 18-14: This pivot table uses a Report Filter to show only the Teller data.

Figure 18-15: This pivot table (and pivot chart) compares the Central branch with the other two branches combined.

Figure 18-16: This pivot table uses three report filters.

Figure 18-17: A pivot table before creating groups of states.

Figure 18-18: A pivot table with two groups and subtotals for the groups.

Figure 18-19: Pivot tables with options for subtotals and grand totals.

Figure 18-20: You can use a pivot table to summarize the sales data by month.

Figure 18-21: The pivot table, before grouping by month.

Figure 18-22: Use the Grouping dialog box to group pivot table items by dates.

Figure 18-23: The pivot table, after grouping by month and year.

Figure 18-24: This pivot table shows sales by quarter and by year.

Figure 18-25: This pivot table is grouped by Hours.

Figure 18-26: Creating a frequency distribution for these test scores is simple.

Figure 18-27: The pivot table and pivot chart shows the frequency distribution for the test scores.

Figure 18-28: This data demonstrates calculated fields and calculated items.

Figure 18-29: This pivot table was created from the sales data.

Figure 18-30: The Insert Calculated Field dialog box.

Figure 18-31: This pivot table uses a calculated field.

Figure 18-32: The Insert Calculated Item dialog box.

Figure 18-33: This pivot table uses calculated items for quarterly totals.

Figure 18-34: The pivot table, after creating two groups and adding subtotals.

Figure 18-35: The formulas in column F reference cells in the pivot table.

Figure 18-36: After expanding the pivot table, formulas that use the GETPIVOTDATA function continue to display the correct result.

Figure 18-37: This table contains data for each county in the United States.

Figure 18-38: This pivot table was created from the county data.

Figure 18-39: This worksheet lists calculated fields and items for the pivot table.

Figure 18-40: This custom list ensures that the Region names are sorted correctly.

Figure 18-41: A 119-page pivot table report.

Chapter 19: Conditional Formatting and Data Validation

Figure 19-1: This worksheet demonstrates a few conditional formatting rules.

Figure 19-2: One of several different conditional formatting dialog boxes.

Figure 19-3: Use the New Formatting Rule dialog box to create your own conditional formatting rules.

Figure 19-4: The length of the data bars is proportional to the value in the cell.

Figure 19-5: This table uses data bars conditional formatting.

Figure 19-6: A real Excel bar chart (not conditional formatting data bars).

Figure 19-7: A range that uses color scale conditional formatting.

Figure 19-8: Use the New Formatting Rule dialog box to customize a color scale.

Figure 19-9: This worksheet, which uses color scale conditional formatting, is zoomed to 20%.

Figure 19-10: Using an icon set to indicate the status of projects.

Figure 19-11: Changing the icon assignment rule.

Figure 19-12: Using a customized icon set to indicate the status of projects.

Figure 19-13: The arrows depict the trend from Test 1 to Test 2.

Figure 19-14: Displaying only one icon from an icon set.

Figure 19-15: The Conditional Formatting Rules Manager dialog box.

Figure 19-16: This ranges uses data bars, color scales, and icon sets.

Figure 19-17: Creating a conditional formatting rule based on a formula.

Figure 19-18: Using conditional formatting to apply formatting to alternate rows.

Figure 19-19: Conditional formatting produces these groups of alternate shaded rows.

Figure 19-20: The sum is displayed only when all four values have been entered.

Figure 19-21: A missing value causes the sum to be hidden.

Figure 19-22: Names that begin with the letter entered in cell A1 are highlighted.

Figure 19-23: Cells that meet the criteria entered in cell A1 are highlighted.

Figure 19-24: Using a custom VBA function to apply conditional formatting to cells that contain a formula.

Figure 19-25: Using conditional formatting to highlight cells with invalid entries.

Figure 19-26: Displaying a message when the user makes an invalid entry.

Figure 19-27: The Settings tab of the Data Validation dialog box.

Figure 19-28: Excel can draw circles around invalid entries (in this case, cells that contain values greater than 100).

Figure 19-29: This drop-down list was created using data validation.

Figure 19-30: Using data validation to prevent duplicate entries in a range.

Figure 19-31: Using data validation to ensure that the sum of a range does not exceed a certain value.

Chapter 20: Creating Megaformulas

Figure 20-1: This spreadsheet uses multiple formulas to calculate mortgage loan information.

Figure 20-2: The goal is to remove the middle name or middle initial from each name.

Figure 20-3: Removing the middle names and initials requires six intermediate formulas.

Figure 20-4: These intermediate formulas will eventually be converted to a single megaformula.

Figure 20-5: Column B contains a megaformula that returns the character position of the last space of the name in column A.

Figure 20-6: The formulas in this worksheet determine the validity of a credit card number.

Figure 20-7: This workbook uses a megaformula to generate realistic random names.

Chapter 21: Tools and Methods for Debugging Formulas

Figure 21-1: #DIV/0! errors occur when the data in column C is missing.

Figure 21-2: Formulas in the range C4:C6 use an absolute reference to cell C1.

Figure 21-3: A simple demonstration of numbers that appear to add up incorrectly.

Figure 21-4: Excel's way of asking whether you want to update links in a workbook.

Figure 21-5: This worksheet demonstrates an inconsistency when summing logical values.

Figure 21-6: The Go To Special dialog box.

Figure 21-7: Zooming out and selecting all formula cells can give you a good overview of how the worksheet is designed.

Figure 21-8: Displaying formulas (bottom window) and their results (top window).

Figure 21-9: This worksheet displays lines that indicate cell precedents for the formula in cell C13.

Figure 21-10: Excel can check your formulas for potential errors.

Figure 21-11: Clicking an error Smart Tag gives you a list of options.

Figure 21-12: Using the Error Checking dialog box to cycle through potential errors identified by Excel.

Figure 21-13: Excel's Formula Evaluator shows a formula being calculated one step at a time.

Chapter 22: Introducing VBA

Figure 22-1: The Developer tab, which is not displayed by default, contains commands that are useful for VBA users.

Figure 22-2: The Macro Settings section of the Trust Center dialog box.

Figure 22-3: Excel's Security Warning that the file contains macros.

Figure 22-4: You see this warning when the VBA window is open and a workbook contains macros.

Figure 22-5: Excel warns you if your workbook contains macros and you attempt to save it in a non-macro file format.

Figure 22-6: The Visual Basic Editor window.

Figure 22-7: A Project window with two projects listed.

Figure 22-8: Use the Properties window to change the name of a VBA module.

Figure 22-9: Code window overload.

Chapter 23: Function Procedure Basics

Figure 23-1: A simple VBA function displayed in a code window.

Figure 23-2: Use the References dialog box to create a reference to a project that contains a custom VBA function.

Figure 23-3: Excel's Insert Function dialog box displays a brief description of the selected function.

Figure 23-4: Provide a function description in the Macro Options dialog box.

Figure 23-5: Executing a VBA statement that assigns a function to a particular function category.

Figure 23-6: Use a MsgBox statement to monitor the value of a variable as a Function procedure executes.

Figure 23-7: Using the VB Editor Immediate window to display results while a function is running.

Figure 23-8: A runtime error identified by VBA.

Figure 23-9: The highlighted statement has generated a runtime error.

Figure 23-10: The highlighted statement contains a breakpoint.

Figure 23-11: Saving a workbook as an add-in.

Chapter 24: VBA Programming Concepts

Figure 24-1: Displaying a list of VBA functions in the VB Editor.

Figure 24-2: Use the Intersect function to work with the intersection of two ranges.

Chapter 25: VBA Custom Function Examples

Figure 25-1: The CELLTYPE function returns a string that describes the contents of a cell.

Figure 25-2: Selecting an operation from the list displays the result in cell C14.

Figure 25-3: Calculating sales commissions based on sales amount and years employed.

Figure 25-4: Examples of the SPELLDOLLARS function.

Figure 25-5: Examples of the extended date function.

Figure 25-6: The MONTHNAMES function entered as an array formula.

Figure 25-7: An array formula generates nonduplicated consecutive integers, arranged randomly.

Figure 25-8: The RANGERANDOMIZE function returns the contents of a range, but in a randomized order.

Appendix B: Using Custom Number Formats

Figure B-1: The Number tab of the Format Cells dialog box.