Home > Guides > Home & Office Computing > Microsoft Applications

Microsoft Office Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Creating Interactive Spreadsheets Online

Last updated Mar 26, 2004.

There was a time when saving an Excel spreadsheet as HTML meant that it represented static data. That's no longer the case. You can now post a set of data, and even a chart representing that data, and allow a user to dynamically change it in a Web page.

Let's take a look at a typical chart and dataset in Excel. If we select the chart, we also highlight the dataset on which it is based.

When you click File > Save As Webpage in Excel, you get some interesting options.

First you can click to add Interactivity.

You can add a title to the final page.

Next, you can change the name of the page from the generic "Page.htm" that Excel attempts to use.

Finally, you can (obviously) determine the folder in which the file should be saved, which should be where the rest of your Web site is stored.

When you click Publish, you get another screen, with another chance to change what you will be publishing – but why would you?

Hint: To avoid the confusion in this drop down menu, forget it – just use the selection feature to tell Excel which element of which worksheet you want published. The one item you do want to check in this screen is to enable Open published page in Web browser.

I recommend this so that you don't need to navigate to that folder to see your preview. To check the actual file in the actual folder, just double-click it when publishing is done, and it should open in your default browser.

You can also select the AutoPublish feature, which will always publish the selected items (or actually re-publish them with changes over the original page) every time you close Excel. I don't like this kind of automatic stuff, but if it helps you, go for it.

When it's published, the Web page has the title you added as well as the chart and dataset. What's cool, as we can see here with the new data in the chart, is that the user can play with the chart data and manipulate it by changing the underlying data.

The one item that the interactive Web chart is not capable of using is a feature of Excel that I do like: conditional formatting.

If we return to the original worksheet and select a cell, we can choose Format > Conditional Formatting. In this case, I want to highlight this value any time it's greater than 200, by changing the color of the font to red and making it bold.

Sure enough, when I change the value to be greater than 200, the value in the cell takes on the property that I've chosen. Again, unfortunately the Web version does not yet have this feature – perhaps in 2006?

Finally, as a hint, you can use the Format Painter to repeat the same conditional formatting in other cells. Select the cell you've added it to, click the Format Painter, and click to apply it to any other cell.

One last thing to consider: if you read our recent update about Pivot Tables, you can select pivot table in the worksheet, and publish the pivot table only with its functionality as an interactive Web page. It doesn't have drag and drop capabilities, but you can use the little + and buttons to expand and hide various layers of the data.

Clicking the + under Product in the third quarter, for example, breaks down the product sales for that quarter by numbers within regions, and by product. This might be more information than you really want to have on the page, but it's nice to know it's there when you need it.

I see the advantage of this tool to creating specific views ("Sales by Quarter") and naming the page accordingly.

So those are some ways to add interactivity to your spreadsheet, by putting it online as an interactive Web object, changing some formats to reflect conditions in the spreadsheet itself, or publishing a pivot table version of the data as an interactive Web page.