Figure 0-4: You can add, remove, and arrange commands on the Quick Access Toolbar.
Figure 0-5: The Mini toolbar
Figure 0-6: Formula AutoComplete feature
Figure 0-7: Theme choices
Figure 0-8: SmartArt choices
Figure 0-9: SmartArt example
Figure 0-10: Zoom slider
Figure 0-11: Workbook view options
Figure 0-12: Compatibility Checker
Chapter 1: Range Names
Figure 1-1: You can create a range name by selecting the cell range you want to name and then typing the range name in the Name Box.
Figure 1-2: Naming cell F3 east and F4 west
Figure 1-3: Naming range A1:B4 Data
Figure 1-4: Naming a noncontiguous range of cells
Figure 1-5: By naming the cells that contain state sales with state abbreviations, you can use the abbreviation when you refer to the cell rather than the cell’s column letter and row number.
Figure 1-6: Select Create From Selection
Figure 1-7: Select the Left Column check box
Figure 1-8: The New Name dialog box before creating any range names
Figure 1-9: New Name dialog box after creating a range name
Figure 1-10: Name Manager dialog box for States.xlsx
Figure 1-11: Historical investment data
Figure 1-12: You can add a range name to a formula by using the Paste Name dialog box.
Figure 1-13: Example of AutoComplete feature
Figure 1-14: Name Manager dialog box with worksheet and workbook names
Figure 1-15: How to apply range names to formulas
Chapter 2: Lookup Functions
Figure 2-1: Using a lookup function to compute a tax rate. The numbers in the first column of the table range are sorted in ascending order.
Figure 2-2: Looking up prices from product ID codes. When the table range isn’t sorted in ascending order, enter False as the last argument in the lookup function formula.
Figure 2-3: Using an HLOOKUP function to determine a price that changes depending on the date it’s sold.
Chapter 3: The INDEX Function
Figure 3-1: You can use the INDEX function to calculate the distance between cities.
Figure 3-2: Distances for a Seattle Sonics road trip
Chapter 4: The MATCH Function
Figure 4-1: Using the MATCH function to locate the position of a value in a range.
Figure 4-2: The MATCH function can be used in combination with functions such as INDEX and VLOOKUP.
Figure 4-3: This example uses the MAX, MATCH, and VLOOKUP functions to find and display the highest value in a list.
Figure 4-4: Using the MATCH function to calculate an investment’s payback period.
Chapter 5: Text Functions
Figure 5-1: Examples of text functions
Figure 5-2: Using the TRIM function to trim away excess spaces.
Figure 5-3: Using text functions to extract the product ID, price, and product description from a text string.
Figure 5-4: Extracting East, North, and South sales with a combination of the FIND, LEFT, LEN, and MID functions.
Figure 5-5: Text To Columns Wizard dialog box
Figure 5-6: Result of Text To Columns Wizard
Figure 5-7: Using the REPT function to create a frequency graph.
Chapter 6: Dates and Date Functions
Figure 6-1: Use the Format Cells command to change dates from serial number format to month-day-year format.
Figure 6-2: Reformatting a serial number to month-day-year format.
Figure 6-3: You can use the DATEVALUE function to format a date in serial format.
Figure 6-4: Using the DATEDIF function.
Chapter 7: Evaluating Investments by Using Net Present Value Criteria
Figure 7-1: To determine which investment is better, we need to calculate net present value.
Figure 7-2: Using the XNPV function
Figure 7-3: NPV converted to today’s dollars.
Chapter 8: Internal Rate of Return
Figure 8-1: Example of the IRR function
Figure 8-2: Project with more than one IRR
Figure 8-3: Project with no IRR
Figure 8-4: IRR can lead to an incorrect choice of which project to pursue.
Figure 8-5: Example of the XIRR function
Figure 8-6: Example of the MIRR function
Chapter 9: More Excel Financial Functions
Figure 9-1: Example of PV function
Figure 9-2: Example of FV function
Figure 9-3: Examples of PMT, PPMT, CUMPRINC, CUMIPMT, and IPMT functions
Figure 9-4: Example of RATE function
Figure 9-5: Example of NPER function
Chapter 10: Circular References
Figure 10-1: A loop causing a circular reference
Figure 10-2: A circular reference can occur when you’re calculating taxes.
Figure 10-3: Use the Enable Iterative Calculation option to resolve a circular reference.
Figure 10-4: Excel runs the calculations to resolve the circular reference.
Chapter 11: IF Statements
Figure 11-1: You can use an IF formula to model quantity discounts.
Figure 11-2: Hedging example that uses IF statements
Figure 11-3: Freeze Panes options
Figure 11-4: Moving-average trading rule beats buy and hold!
Figure 11-5: Using IF statements to model the first roll in craps
Figure 11-6: Pro forma assumptions and balance sheet
Figure 11-7: Pro forma income statement
Figure 11-8: Error trapping formulas
Figure 11-9: Examples of Excel error values
Chapter 12: Time and Time Functions
Figure 12-1: Examples of time formats
Figure 12-2: Excel time formats
Figure 12-3: Determining time needed to complete jobs
Figure 12-4: Using the Now() and Today() functions
Figure 12-5: Computing length of time worked by employees
Figure 12-6: Determining total hours worked during the week
Figure 12-7: Entering a sequence of times
Chapter 13: The Paste Special Command
Figure 13-1: Using the Paste Special command to paste only values
Figure 13-2: The Paste Special dialog box with Values selected. Selecting Values pastes only values and not any formulas.
Figure 13-3: Use the Transpose option in the Paste Special dialog box to transpose a row of data into a column or a column of data into a row.
Figure 13-4: Data for using the Divide option in the Paste Special dialog box to divide a data range by a constant.
Figure 13-5: You can apply an option in the Operation area of the Paste Special dialog box to a range of cells.
Figure 13-6: Results of using the Divide option in the Paste Special dialog box
Chapter 14: The Auditing Tool
Figure 14-1: The Formula Auditing toolbar
Figure 14-2: You can use the auditing tool to trace formulas in complex spreadsheets.
Figure 14-3: Tracing dependent cells
Figure 14-4: Clicking Trace Dependents repeatedly shows all the dependents of the price growth assumption.
Figure 14-5: Direct precedents for Year 1 before-tax profit
Figure 14-6: Click Trace Precedents repeatedly to show all precedents of Year 1 before-tax profit.
Figure 14-7: Data for using the auditing tool with data on multiple worksheets
Figure 14-8: Results of tracing precedents with data on multiple worksheets
Figure 14-9: With the Go To dialog box, you can audit data in multiple worksheets.
Chapter 15: Sensitivity Analysis with Data Tables
Figure 15-1: The inputs that change the profitability of a lemonade store
Figure 15-2: One-way data table with varying prices
Figure 15-4: One-way data table with varying prices
Figure 15-3: Creating a data table
Figure 15-5: A two-way data table showing profit as a function of price and unit variable cost
Figure 15-6: You can use a data table to determine how mortgage payments vary as the amount borrowed and the interest rate change.
Figure 15-7: You can use a data table to calculate how many years it will take to break even.
Figure 15-8: A two-way data table
Chapter 16: The Goal Seek Command
Figure 16-1: We’ll use this data to set up the Goal Seek feature to perform a breakeven analysis.
Figure 16-2: The Goal Seek dialog box filled in with entries for a breakeven analysis
Figure 16-3: You can use data such as this with the Goal Seek feature to determine the amount you can borrow based on a set monthly payment.
Figure 16-4: The Goal Seek dialog box set up to calculate the mortgage example
Figure 16-5: Goal Seek can help you solve story problems.
Figure 16-6: The Goal Seek dialog box filled in to solve an algebra story problem
Chapter 17: Using the Scenario Manager for Sensitivity Analysis
Figure 17-1: The data on which the scenarios are based
Figure 17-2: The scenario summary report
Figure 17-3: Data inputs for the best-case scenario
Figure 17-4: Defining the input values for the best-case scenario
Figure 17-5: The Scenario Manager dialog box displays each scenario I defined.
Figure 17-6: Use the Scenario Summary dialog box to select the result cells for the summary report.
Chapter 18: The COUNTIF, COUNTIFS, COUNT, COUNTA, and COUNTBLANK Functions
Figure 18-1: The song database we use for the COUNTIF examples
Figure 18-2: Using COUNTIF to determine how many songs were sung by each singer.
Figure 18-3: You can combine the COUNTIF function with the not-equal-to operator (<>).
Chapter 19: The SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS Functions
Figure 19-1: Data we’ll use for SUMIF examples
Figure 19-2: Results of SUMIF computations
Chapter 20: The OFFSET Function
Figure 20-1: Using the OFFSET function
Figure 20-2: You can do a left-hand lookup by using the MATCH and OFFSET functions.
Figure 20-3: You can use the OFFSET function in calculations when you’re working with data that isn’t always in the same location in a worksheet.
Figure 20-4: Using the OFFSET function to compute development costs for Phases 1–3.
Figure 20-5: Movie example using the OFFSET function
Figure 20-6: Evaluate Formula dialog box
Figure 20-7: Finding the last number in a column
Figure 20-8: Example of a dynamic range
Figure 20-9: Creating a dynamic range
Figure 20-10: We can use the OFFSET function to update this chart dynamically.
Figure 20-11: Creating a dynamic range name for the units sold
Figure 20-12: The formula used to define a dynamic range named Month
Chapter 21: The INDIRECT Function
Figure 21-1: A simple example of the INDIRECT function
Figure 21-2: You can use the INDIRECT function to change cell references in formulas without changing the formulas themselves.
Figure 21-3: Monthly sales (months 1–7) of a product listed by using the INDIRECT function
Figure 21-4: Several ways to sum the values in the cell range A5:A10
Figure 21-5: Results of SUM formulas after inserting a blank row in the original range
Figure 21-6: Use the INDIRECT function to create reference to range name within a formula
Chapter 22: Conditional Formatting
Figure 22-1: Conditional formatting commands
Figure 22-2: Conditional formatting options
Figure 22-3: Highlighting the ten highest temperatures in red
Figure 22-4: Conditional formatting using Top/Bottom rules