APPENDIX

What’s on the

CD-ROM

This appendix describes the contents of the CD that accompanies this book. For any last minute changes, please refer to the ReadMe file located at the root of the CD.

This appendix provides information on the following topics:

•System Requirements

•Using the CD

•Files and software on the CD

•Troubleshooting

System Requirements

Make sure that your computer meets these minimum requirements:

•A Windows PC with Microsoft Excel 2010 installed.

•A CD-ROM drive.

Using the CD

To install the items from the CD to your hard drive, follow these steps:

1.Insert the CD-ROM into your computer’s CD drive.

2.Read through the license agreement and then click the Accept button if you want to use the CD.

933

Part VII: Appendixes

Note

The interface won’t launch if you have autorun (or autoplay) disabled. In that case, choose Start Run. In the dialog box that appears, type D:\start.exe. (Replace D with the proper letter if your CD drive uses a different letter. If you don’t know the letter, see how your CD drive is listed under My Computer.) n

You can then view the directory structure on the CD.

What’s on the CD

The following sections provide more details about the software and other materials available on the CD.

Applications

Adobe Reader

Adobe Reader is a freeware application for viewing files in the Adobe Portable Document format.

Freeware programs are copyrighted games, applications, and utilities that are free for personal use. Unlike shareware, these programs do not require a fee or provide technical support.

eBook version of Excel 2010 Bible

The complete text of the book you hold in your hands is provided on the CD in Adobe’s Portable Document Format (PDF). You can read and quickly search the content of this PDF file by using Adobe’s Acrobat Reader.

Sample files for Excel 2010 Bible

The files discussed in the book are organized by chapter. With a few exceptions, the files are all Excel 2010 files that have either of the following extensions:

•XLSX: An Excel workbook file

•XLSM: An Excel workbook file that contains VBA macros

When you open an XLSM file, Excel may display a Security Warning and tell you that macros have been disabled. To enable macros, click the Enable Content button in the Security Warning panel.

Because the files on this CD are from a trusted source, you may want to copy the files to your hard drive and then designate the folder as a trusted location. To do so, follow these steps:

1.Open an Explorer window, and select the CD-ROM drive that contains the companion CD-ROM.

2.Right-click the folder that corresponds to the root folder for the samples files, and choose Copy from the shortcut menu.

3.Activate the folder on your hard drive where you’d like to copy the files. Right-click the directory, and choose Paste from the shortcut menu.

934

Appendix B: What’s on the CD-ROM

The CD-ROM files will be copied to a subfolder in the folder you specified in Step 3. To designate this new folder as a trusted location:

1.Start Excel and choose File Options to display the Excel Options dialog box.

2.In the Excel Options dialog box, click the Trust Center tab.

3.Click the Trust Center Settings button.

4.In the Trust Center dialog box, click the Trusted Locations tab.

5.Click the Add New Location button to display the Microsoft Office Trusted Location dialog box.

6.In the Microsoft Office Trusted Location dialog box, click the Browse button and locate the folder that contains the files you copied from the CD-ROM.

7.Make sure that you select the option labeled Subfolders Of This Location Are Also Trusted.

After performing these steps, when you open XLSM files from this location, the macros are enabled and you don’t see the security warning.

Following is a list of the sample files, along with a brief description.

Note

Some chapters don’t use any sample files. n

Chapter 01

•table and chart.xlsx: An introductory hands-on example that creates a table and a chart.

Chapter 02

•loan payment calculator.xlsx: A workbook that demonstrates the difference between unformatted cells and formatted cells.

•number formatting.xlsx: A workbook with some examples of number formatting.

Chapter 04

•budget.xlsx: A simple multisheet budget workbook to demonstrate formatting across worksheets.

Chapter 05

•real estate table.xlsx: A workbook that contains a table of real estate listings for practice working with tables.

Chapter 06

•background image.xlsx: A workbook that contains a background image for a worksheet.

•loan payments.xlsx: A workbook that contains a range of data and formulas to demonstrate the effect of applying simple formatting.

935

Part VII: Appendixes

•text alignment.xlsx: A workbook that demonstrates different types of horizontal text alignment.

•theme examples.xlsx: A workbook that contains a table, SmartArt, and a chart to demonstrate the effect of applying different document themes.

Chapter 10

•cell references.xlsx: A workbook used to demonstrate the effect of using different types of cell references when copying formulas.

•circular reference.xlsx: A workbook that demonstrates an intentional circular reference. When you open this workbook, you see a message informing you of the circular reference.

•table formulas.xlsx: A workbook used to demonstrate the use of formulas within a table.

Chapter 11

•character set.xlsm: A workbook that displays the characters available in any font installed on your system.

•extract names.xlsx: A workbook that contains formulas to extract first, middle, and last names.

•text formula examples.xlsx: A workbook that contains examples of advanced text formulas.

•text histogram.xlsx: A workbook that contains two examples of creating a histogram directly in a range.

Chapter 12

•holidays.xlsx: A workbook that demonstrates how to calculate the date for 10 U.S. holidays.

•jogging log.xlsx: A workbook that tracks time and distanced jogged. Demonstrates the use of times not associated with a time of day.

•time sheet.xlsm: A workbook that tracks hours worked during a week.

•work days.xlsx: A workbook that demonstrates how to calculate the number of work days between two dates (excluding weekends and holidays).

•XDATE demo.xlsm: A workbook that contains custom functions that allow you to work with dates prior to 1900.

Chapter 13

•basic counting.xlsx: A workbook that demonstrates basic counting formulas.

•conditional sum.xlsx: A workbook that demonstrates how to calculate conditional sums, using single and multiple criteria.

•count unique.xlsx: A workbook that demonstrates how to count unique (nonduplicated) items in a range.

936

Appendix B: What’s on the CD-ROM

•counting text in a range.xlsx: A workbook that demonstrates various ways to count text in a range.

•cumulative sum.xlsx: A workbook that demonstrates how to display a cumulative sum.

•frequency distribution.xlsx: A workbook that demonstrates four methods to create a frequency distribution.

•multiple criteria counting.xlsx: A workbook that demonstrates counting formulas that use multiple criteria.

Chapter 14

•basic lookup examples.xlsx: A workbook that demonstrates a variety of common lookup techniques.

•specialized lookup examples.xlsx: A workbook that demonstrates a variety of specialized lookup techniques.

Chapter 15

•annuity calculator.xlsx: A workbook that calculates interest on annuities.

•credit card payments.xlsx: A workbook that contains formulas to calculate credit card payments.

•depreciation calculations.xlsx: A workbook that demonstrates methods to calculate depreciation of an asset.

•investment calculations.xlsx: A workbook that demonstrates formulas to calculate interest on investments.

•irregular payments.xlsx: A workbook that calculates a loan with irregular payments.

•loan amortization schedule.xlsx: A workbook that calculates a loan amortization schedule.

•loan data tables.xlsx: A workbook that demonstrates how to use 1-way and 2-way data tables to calculate loan information.

•loan payment.xlsx: A workbook that contains formulas to calculate loan payments.

Chapter 16

•array examples.xlsx: A workbook that contains examples of array formulas.

Chapter 17

•array formula calendar.xlsx: A workbook that displays a calendar for any month by using a complex array formula.

•multi-cell array formulas.xlsx: A workbook that demonstrates a variety of multicell array formulas.

•single-cell array formulas.xlsx: A workbook that demonstrates a variety of single-cell array formulas.

•yearly calendar.xlsx: A workbook that displays monthly calendars for any year by using a complex array formula.

937

Part VII: Appendixes

Chapter 18

•area charts.xlsx: A workbook that contains an area chart example.

•bar charts.xlsx: A workbook that contains bar chart examples.

•bubble charts.xlsx: A workbook that contains bubble chart examples.

•column charts.xlsx: A workbook that contains various types of column charts.

•doughnut charts.xlsx: A workbook that contains a doughnut chart example.

•hands-on example.xlsx: A workbook that contains data used in the hands-on charting example.

•line charts.xlsx: A workbook that contains bar chart examples.

•pie charts.xlsx: A workbook that contains pie chart examples.

•radar charts.xlsx: A workbook that contains radar chart examples.

•six chart types.xlsx: A workbook that shows a single range, displayed using six chart types.

•stock charts.xlsx: A workbook that contains stock chart examples.

•surface charts.xlsx: A workbook that contains a surface chart example.

•xy charts.xlsx: A workbook that contains XY chart examples.

Chapter 19

•comparative histogram.xlsx: A workbook that demonstrates how to create a comparative histogram chart.

•conditional colors.xlsx: A workbook that contains a chart that displays different color columns, based on the value.

•function plot 2D.xlsx: A workbook that demonstrates how to plot a function that uses one variable.

•function plot 3D.xlsm: A workbook that demonstrates how to plot a function that uses two variables.

•gantt chart.xlsx: A workbook that demonstrates how to create a simple Gantt chart for project planning.

•gauge chart.xlsx: A workbook that contains a chart that plots a single data point as a gauge.

•log scale.xlsx: A workbook that demonstrates a logarithmic scale on a chart.

•picture charts.xlsx: A workbook that demonstrates the use of pictures in charts.

•thermometer chart.xlsx: A workbook that contains a chart that plots a single data point as a thermometer.

•weather combination chart.xlsx: A workbook that demonstrates a combination chart with two value axes.

938

Appendix B: What’s on the CD-ROM

Chapter 20

•color scale example.xlsx: A workbook that demonstrates a conditional formatting color scale.

•conditional formatting examples.xlsx: A workbook that contains examples of various types of conditional formatting.

•conditional formatting formulas.xlsx: A workbook that demonstrates a variety of conditional formatting formulas.

•data bars examples.xlsx: A workbook that demonstrates conditional formatting data bars.

•extreme color scale.xlsx: A workbook that demonstrates a conditional formatting color scale in a large range.

•icon set examples.xlsx: A workbook that demonstrates a conditional formatting icon set.

Chapter 21

•sparkline examples.xlsx: A workbook that contains examples of Sparklines.

Chapter 22

•photo styles.xlsx: A workbook that contains examples of styles applied to a photo.

•shape examples.xlsx: A workbook that contains examples of shapes.

•smartart demo xlsx: A workbook that contains SmartArt.

•smartart org chart.xlsx: A workbook that contains an organizational chart created using SmartArt.

•wordart examples.xlsx: A workbook that contains examples of WordArt.

Chapter 24

•number formats.xlsx: A workbook that contains examples of many custom number formats.

Chapter 25

•data validation examples.xlsx: A workbook that contains examples that demonstrate data validation.

Chapter 26

•outline example.xlsx: A workbook that contains data suitable for an outline.

Chapter 27

•dependent.xlsx: A workbook used to demonstrate linking (uses source.xlsx).

•region1.xlsx: One of three workbooks used to demonstrate data consolidation.

•region2.xlsx: One of three workbooks used to demonstrate data consolidation.

939

Part VII: Appendixes

•region3.xlsx: One of three workbooks used to demonstrate data consolidation.

•source.xlsx: A workbook used to demonstrate linking (uses dependent.xlsx).

Chapter 28

•webpage.xlsx: A workbook used to demonstrate HTML exporting.

Chapter 33

•budget data.accdb: A single-table Microsoft Access database file, used to demonstrate Microsoft Query.

Chapter 34

•bank accounts.xlsx: A workbook that contains data suitable for a pivot table.

•normalized data.xlsx: A workbook that demonstrates normalized data.

Chapter 35

•calculated fields and items.xlsx: A workbook that contains a pivot table that demonstrates calculated fields and calculated items.

•county data.xlsx: A workbook that contains a large table and a pivot table created from the table.

•employee list.xlsx: A workbook that contains a table to demonstrate the use of nonnumeric data in a pivot table.

•hourly readings.xlsx: A workbook that contains time-based information to demonstrate grouping item by time.

•income and expense.xlsx: A workbook that contains a pivot table to demonstrate how to reference cells within a pivot table.

•music list.xlsx: A workbook that demonstrates how to create and format a pivot table report.

•reverse pivot.xlsm: A workbook that contains a VBA macro that converts a summary table into a 3-column database table.

•sales by date.xlsx: A workbook that contains time-based information to demonstrate grouping items by date.

•sales by region pivot chart.xlsx: A workbook that demonstrates how to create a pivot chart from a pivot table.

•test scores.xlsx: A workbook that demonstrates how to use a pivot table to create a frequency distribution.

Chapter 36

•direct mail.xlsx: A workbook that contains a profit model to demonstrate how to create a 2-way data table for what-if analysis.

•mortgage loan.xlsx: A workbook with input cells and formula cells to demonstrate what-if analysis.

940

Appendix B: What’s on the CD-ROM

•mortgage loan data table.xlsx: A workbook that demonstrate how to create a data 1-way data table for what-if analysis.

•production model.xlsx: A workbook to demonstrate the Scenario Manager.

Chapter 37

•allocating resources.xlsx: A workbook that contains a model to demonstrate how to allocate resources and maximize profit using Solver.

•investment portfolio.xlsx: A workbook that contains a model to demonstrate how to maximize the return on an investment portfolio using Solver.

•linear equations.xlsx: A workbook that demonstrates how to use Solver to solve a set of linear equations.

•mortgage loan.xlsx: A workbook with input cells and formula cells to demonstrate goal seeking.

•shipping costs.xlsx: A workbook that contains a model to demonstrate how to minimize shipping costs using Solver.

•three products.xlsx: A workbook that contains a simple profit model to demonstrate Solver.

Chapter 38

•atp examples.xlsx: A workbook that demonstrates the tools in the Analysis ToolPak add-in.

Chapter 39

•cube root.xlsm: A workbook that contains a VBA function to calculate the cube root.

•current date.xlsm: A workbook that contains a simple VBA macro to insert the current date into the active cell.

•list formulas.xlsm: A workbook that contains a VBA macro to generate a list of all formulas in a worksheet.

Chapter 40

•vba functions.xlsm: A workbook that contains examples of VBA functions used in worksheet formulas.

Chapter 41

•change case.xlsm: A VBA macro that uses a UserForm to enable the user to make a choice.

•show message.xlsm: A workbook that contains a UserForm that displays a message.

Chapter 42

•mortgage loan.xlsx: A workbook that demonstrates how to use ActiveX controls on a worksheet, with no macros.

•worksheet controls.xlsm: A workbook that contains examples of all ActiveX controls that can be used on a worksheet.

941