Table of Contents
- Surrealty: An Organic Case Study
- Working with Microsoft Word
Accelerating Your Knowledge of Excel
- Getting Started with Excel Worksheets
- Creating and Autofitting Cell Content
- Populating the Worksheet with Data
- Using AutoSum To Create Automatic Calculations
- Using Formulas
- Making Your Worksheet Look Nicer
- Charting the Data
- Completing the Financial Picture
- Getting Fancy With Xcelsius
- Say It With Charts!
- The Effect of Text Entries and Blank Cells on Calculations
- Filtering Your Outlook Contacts
- New Charting and Productivity Tools
- Cataloging Your Backups in Excel
- Using Excel as a Simple Database
- Painless Pivot Tables
- Creating Interactive Spreadsheets Online
- Moving an Excel Macro
- Working with Scenarios and Goals
- Using Excel's Solver
- Emphasizing Sales Data in Excel
- XspandXL for Spreadsheet Analysis
- New Crystal Xcelsius Light (Free)
- Excel Business Analysis Books
- Excel 2007 Sorting, Filtering and Table Enhancements
- Creating an Entrepreneurial Marketing Plan in Excel 2007
- Named Ranges in Excel 2007
- Maintaining a Positive Outlook
- "Where Are My Socks?" Accessing Your Important Information
- Presenting Professionally with PowerPoint
- Posting a Web Site with FrontPage
- Publish or Perish
- Get Visual with Visio
- Tools That Integrate Your Office Applications
- Getting Organized with OneNote
- Video Tutorials
- Additional Resources
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.