Excel Bible

EXCEL 2007 BIBLE
------------------------------------------------------------------------

This readme provides information on the following topics:

* System Requirements

* Using the CD

* What's on the CD

* Troubleshooting

========================================================================
System Requirements
------------------------------------------------------------------------

Make sure that your computer meets the minimum system requirements
listed in this section. If your computer doesn't match up to most of
these requirements, you may have a problem using the contents of the CD.

* A Windows PC with Microsoft Excel 2007.

* 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 into your computer's CD drive. The license agreement
appears.

<> The interface won't launch if you have autorun
disabled. In that case, click 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.) Click OK.

2. Read through the license agreement, and then click the Accept
button if you want to use the CD. After you click Accept, the
License Agreement window won't appear again.

The CD interface appears. The interface allows you to install the
programs and run the demos with just a click of a button (or two).

========================================================================
What's on the CD
------------------------------------------------------------------------

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

eBook version of Excel 2007 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,
also included on the CD.

Shareware programs are fully functional, trial versions of copyrighted
programs. If you like particular programs, register with their authors
for a nominal fee and receive licenses, enhanced versions, and technical
support.

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.

GNU software is governed by its own license, which is included inside
the folder of the GNU product. See the GNU license for more details.

Trial, demo, or evaluation versions are usually limited either by time
or functionality (such as being unable to save projects). Some trial
versions are very sensitive to system date changes. If you alter your
computer's date, the programs will "time out" and will no longer be
functional.

Sample files for the Excel 2007 Bible

The files discussed in the book are organized by chapter. With a few
exceptions, the files are all Excel 2007 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
tells you that macros have been disabled. To enable macros, click the
Options button in the Security Warning panel and then select the option
labeled Enable This Content.

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.

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 Office-->Excel 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 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.

<> Some chapters don't use any sample file.

Chapter 01

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

Chapter 03

* loan payment calculator.xlsx: A workbook that demonstrates some
formulas.

* number formatting.xlsx: A workbook that contains a few examples of
built-in number formats.

Chapter 05

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

Chapter 06

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

Chapter 07

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

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

Chapter 11

* 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.

* worksheet functions.xlsx: A workbook that contains a table that
lists and describes every worksheet function.

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

Chapter 12

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

* 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 13

* 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).

Chapter 14

* 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.

* 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 15

* 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 16

* 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 18

* 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.

Chapter 19

* 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 20

* 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.

Chapter 21

* color sets examples.xlsx: A workbook that demonstrates a
conditional formatting color scale.

* conditional formatting examples.xlsx

* 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 sets.

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

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

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

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

Chapter 28

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

Chapter 33

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

Chapter 34

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

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 non-numeric data in a pivot table.

* exports by state.xlsx:

* 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 item by date.

* sales by region.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 data table.xlsx: A workbook that demonstrate how to
create a data 1-way data table for what-if analysis.

* mortgage loan.xlsx: A workbook with input cells and formula cells
to demonstrate 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