Escape from Excel Hell
Publication date: March 13, 2006
The CD-ROM that accompanies Escape from Excel Hell contains many files and information. This portal page will help to guide you to the various resources on the CD. Most people who use the CD-ROM files for the book prefer to have direct access to the spreadsheets on the CD. This works well but there's a slight disadvantage; the spreadsheet files open in read-only mode. Even when you drag a copy of the spreadsheets folder to your hard drive, the files come over to your hard drive in read-only mode. This read-only mode protects the original file from changes, but I want you to be be able to make changes and introduce enhancements of your own. I want you to experiment, learn, and develop insights. If you make a mistake, you can always go back to the CD-ROM and get a fresh copy.
To avoid the issue of of opening read-only files, I have created a spreadsheets.zip file. When you copy this file to your hard disk and expand it, all the spreadsheets in this folder will permit you to write to them.
The official Web Site for Escape from Excel Hell is:
There is one file you definitely want to know about. It is the ReadMe.xls file located inside the spreadsheets directory of the CD. This one spreadsheet file is your gateway to effectively all the spreadsheets in Escape from Excel Hell.
The ReadMe.xls does three things. It provides a link to each of the spreadsheets, which you can directly click on to open. It outlines important dependencies such as whether the spreadsheet requires other spreadsheets to be simultaneously open, if it requires an Excel Add-In to be loaded (such as the Analysis ToolPak), or if it uses macros. For each of the spreadsheets there is a description that outlines what the spreadsheet does. In some cases there are additional notes or comments.
There are no direct hyperlinks to some of the spreadsheet files and you will need to open the spreadsheet from the Excel File menu. This is because opening using a hyperlink would interfere with the spreadsheet's behavior.
Please note that, there are two worksheet tabs: one for Excel users on the Windows platform and one for Excel users on the Macintosh platform.
On the Windows platform there in an Install Escape Excel Hell Utility.xla button. Clicking it should load an Utility Pak that should help you to do things like sort worksheet tabs in alphabetical order, or by date. There are also some navigation routines that will help you to jump from worksheet to worksheet, keeping the same range of cells selected as you move across worksheets. The keystroke sequences for this are: Ctrl+Shift+J and Ctrl+Shift+L.
To get maximum enjoyment from the simulation in the ch07_02ForestFireSimulation.xls file, you need to have the FireFont2 font installed in your system. This particular font makes the value 1 look like a "tree" and 9 look like a "flame".
Though you can run the simulation without the use of this font, you will likely find it more satisfying if you have the font installed. There is also another reason I made the font a part of this simulation. I want you to walk through the steps of installing a font. This may come in handy at a later stage.
To load a font on your computer, open the Fonts Control Panel in your Windows operating system. On the File menu, click Install New Font. Locate the font on your CD-ROM within the font directory. Remember to select Copy Fonts to Fonts Folder.
I want you to know that the ch09_LargeSpreadsheet_05.xls and the ch09_LargeSpreadsheet_06.xls files are based on data containing 120,000 rows of information (33 columns), and contain information meteorological information from 152 weather stations in 63 countries collected over a five-year period. In spreadsheet terms, this is VERY LARGE (the maximum number of rows in an Excel worksheet is 65,536). It is almost 14MB in size. Open this spreadsheet and go to the worksheet tab called PresentationLayer. When you click the country name in cell B5, a pull-down list of countries should be displayed. The same thing goes for cell B9. When you click on it, you should see the different kinds of data collected. The data is relatively complete for precipitation and min, max, and mean temperatures. After you select these, click the Spinner control (the up/down arrow keys) to move the timeline forward and backward.
The only difference between the two files is that the "06" file is stripped of the document recording macros. I want to prove the point that the raw computation speed is a result of spreadsheet design and layout. Achieving the speed, given the sheer size of the file, quantity of data it contains, and the complexity of where the data is located, is a significant accomplishment. It raises the bar on spreadsheet size and performance, and sets new standards.
http://www.ncdc.noaa.gov/oa/climate/online/ww-ii-data.html
The trial version of Crystal Xcelsius Professional is included on the CD-ROM for Escape from Excel Hell. Neither the trial software nor the dashboards generated ever time out! This allows you to experiment to your heart's content. The only catch is that you cannot save the "XLF" source files, and the generated dashboards display the words "Trial Version" on them.
After you have installed the software, you can follow the examples in "Spreadsheets and Dashboards" of Chapter 10. You can get additional information at:
 ISBN: 076454120X |