Thanks to the 19 people who responded to my request for help. Here's
a summary of suggestions they provided for how to generate a text
file of a spreadsheet that displays the formulas instead of calculated values.
1) Use CTRL-tilde as a toggle to display the formulas in cells of an
Excel spreadsheet. Then copy and paste the spreadsheet into
Notepad. Convert the equals signs to blanks, and paste the result
back into a spreadsheet. This has the disadvantage of converting
dates to numbers (I think day one is 12/31/1899), but it doesn't take
long to copy the dates displayed as you want them (from the original
spreadsheet) into the new spreadsheet. I had to clean up a few other
calculated fields in a similar fashion.
2) To display the formulas all the time, change the global program
setting. For Excel 2007 (PC version), click on the office logo
(upper left corner), then Excel Options (bottom of the window that
popped up), Advanced, Display options for this workbook. Then
proceed as above to copy and paste into Notepad. You can also do
this via Formulas, Formula Auditing, which has a Show Formulas toggle.
3) Display the formulas as described above, then save the file as
unicode text. This file can be pasted back into a spreadsheet, but
also has the same problem with converting dates to a number, etc.
Two other options for sharing data that would avoid a proprietary
format or make the spreadsheet widely accessible:
1) Open Office
(<http://www.openoffice.org/>http://www.openoffice.org/) was
recommended as a non-proprietary (open source) format without having
to resort to a plain text file.
2) try importing it into Google Spreadsheets and then providing a
link and share it with the world.
I've concluded that perhaps the best way to maintain compatibility
with future programs, and facilitate long-term access (many decades,
or more), would be to archive the spreadsheet in multiple formats,
including its current proprietary format (for short-term
compatibility), as a PDF of the current format (showing calculated
values, and maybe the formula version too), and (for the longest-term
access) a text file that includes the formulas and could potentially
be imported into a future spreadsheet program. Accompanying metadata
would of course be important. I've already had the experience of
having a program I used for data storage go extinct (SuperCalc)
during this ongoing 37-year study, so I'm sensitive to the issue of
long-term data storage/compatibility.
David Inouye
Dept. of Biology
University of Maryland
Rocky Mtn. Biological Laboratory
