Share Your Spreadsheets on the Web

Excel 2000 makes it easier than ever to save a workbook in HTML format. After creating your workbook, use the File*Save as Web Page command. Specify what you want to save (a single worksheet or the entire workbook) and click the Save button. The result will be an HTML document and, possibly, a directory containing ancillary files; the number of such files varies with the complexity of your workbook. These files contain information, such as graphics and macros, that can't be stored in standard HTML format. When you post the HTML file on a Web server, you must include the files in the associated directory. You'll find that the HTML file survives "round-tripping."

In other words, if you reopen the HTML file in Excel 2000, every element will be intact.

Alternately, you may save your workbook (single sheet only) in HTML format with "interactivity." When you open the HTML file in a compatible browser (IE 4.01 or later, not Netscape Navigator), you can interact with the Web page: enter data, recalculate formulas, update charts and pivot tables, and so on. Before you get too excited, however, realize that this feature has some serious limitations. Many common formatting options are not retained, and features like array formulas, macros, and outlining aren't supported.

In a simple interactive HTML document I created, the worksheet calculates the dates for various U.S. holidays, using the year entered in cell C3. I used the File*Save as Web Page command to save the workbook in HTML format. In the Save As dialog box, I checked the box labeled Add interactivity.

To try out this example, point your browser to http://www.j-walk.com/holidays.htm. When the page loads, you'll be able to enter a different year in cell C3, and the formulas will display the calculated dates. The sheet isn't protected, so you can even examine the formulas. Because there's no formula bar, you will have to press to view the formula in the active cell.

To view an interactive Excel file, you need Internet Explorer 4.01 or later, and Microsoft Office Web Components (included with most versions of Office 2000) must be installed on your system.

Note: If you have Excel 2000 installed on your system and you receive an error message when attempting to view an interactive spreadsheet on the Web, check your version of the software. For reasons known only to Microsoft, the Office Web components aren't included with the Small Business Edition of Office 2000 or with the stand-alone version of Excel 2000. In other words, your copy of Excel 2000 may be lacking one of the key selling points of the product.

According to Microsoft, you can legally install the Office Web Components if you own Office 2000 Small Business Edition and if someone in your organization has a license for Microsoft Office 2000 Premium, Professional, or Standard, or Microsoft Access 2000. If you have only the stand-alone version of Excel, you cannot install the Web Components.

Thousands Without Zeros

"Is there a way to display thousands without zeros? For example, I would like 52,000 to appear as 52."

-- Cedric D'Souza, Mississauga, Ontario

First, select the cells to be formatted, then choose Format*Cells. Click the Number tab. Select Custom from the Category list, and in the Type box enter 0, (that's a zero followed by a comma).

When this number format is applied, the cells will retain the correct numerical values, but they will be displayed without the last three digits. To display values in millions, insert an additional comma at the end of the format string (0,,). Another possibility is to display one or more decimal places -- for example, 52,100 as 52.1. To do this, include a decimal point in your format string (0.0,). You can also use these number formats in charts. To do so, double-click the chart axis to display the Format Axis dialog box. Then click the Number tab and specify the desired format.

In Excel 2000, the Scale tab of the Format Axis dialog box lets you specify the unit scaling directly. Just choose Millions from the 'Display units' drop-down box.

Adjust Subtotal Formatting

Excel's Data*Subtotals command inserts subtotal formulas into a list. You may want to make the subtotal rows stand out by applying special formatting--but this can't be done by any of Excel's autoformats. Use the outline controls on the left side of the workbook to collapse the outline so only the subtotal rows are visible. Press , select Visible cells only, and click OK. Then apply formatting to the selected cells. When you expand the outline, only the subtotal rows will have the formatting you applied.

(Send your questions and tips to john@ j-walk.com. We pay US$50 for published items. Contributing Editor John Walkenbach is the author of Excel 2000 Bible (IDG Books, 1999) and maintains The Spreadsheet Page (http://www.j-walk.com).)

Join the newsletter!

Or

Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

More about f2Microsoft

Show Comments