Illustrations of How to Publish an Interactive Excel Workbooks and Charts in HTML
Bob Jensen at Trinity University

• Illustration 1:  To run the interactive spreadsheet, the browser you are using now must have Internet Explorer 4.01 or higher according to Microsoft Corporation.  However, some people around the world are telling me that they cannot run these illustrations unless they have upgraded to Office 2000 with at least the Internet Explorer 5.00 or higher that comes with the Office 2000 upgrade.  A few people with IE 5.00 and 5.01 stand alones with having the Office 2000 upgrades reported problems in having the spreadsheets appear in Illustrations 1-4.   If you see a spreadsheet in Illustration 1, you can test for interaction in by entering new data in Cells B11, B12, and/or B13.  Click here to view the Illustration 1 interactive spreadsheet.
  
  
• Illustration 2:  To run an interactive chart, you must save the chart separately as an HTML file following instructions given in Illustration 2.  Illustration 2 also notes that macros will not run in the HTML version.  Click here to view the Illustration 2  interactive chart.
  
  
• Illustration 3:  To show a multiple chart problem that I accidentally resolved.  Click here to view the Illustration 3 interactive chart.
  
  
  
• Microsoft Corporation Illustrations
  
  

Illustration 1 on Saving an Excel Workbook as a Dynamic (Interactive) HTML File Example 

Note the chart below and then scroll down to the table beneath the chart.  Change the amount of the load to $300,000,000 and then note how both the table and the chart change interactively even though you are still in your browser rather than Excel.

Test for interaction by entering new data in Cells B11, B12, and/or B13.

The above loan amortization table is for a real world loan from ten New York banks to a copper mining company in Mexico.  See the 133spans.xls file at http://www.cs.trinity.edu/~rjensen/

Illustration 2 on Saving an Excel Chart as an Interactive HTML file

• You must save (publish) each chart as a separate HTML file, and when you save a workbook it does not automatically save any chart.  You can, however, create a new (master)  HTML file and then copy in the Excel workbook and copy in each chart to the master HTML file.  
  
  
• When you save a chart, it will add a spreadsheet, but the spreadsheet will only include the data range that pertains to the chart.  It will not include cells that are outside the chart's data range.
  
  
• If you have some cells that you would like included in the chart's spreadsheet, you should add rows and columns within the chart's data range.  For example, in Illustration 2 below, the column for "Gender" was not originally in the chart that I created.  When I discovered that this column did not appear in the chart's spreadsheet, I added a blank column between the listing of the student name column and the examination score column.  Then I copied the "Gender" column between the student names and their examination scores.  When I saved the chart below, it then included all three columns in the chart's spreadsheet even though the "Gender" column data was never declared in the chart's data range.
  
  
• If you create macros, I cannot find any way to get them to run in the HTML file.  For example, the macro hot keys do not work.  If you attach a macro to a button, the button will not appear in the HTML file.  For example, in this simple illustration, the Excel workbook has two macros called the "Female" and "Male."  I then created the macros and attached them to buttons on the Excel spreadsheet such that if you clicked on the "Male" button, the macro filled the blue data into the spreadsheet shown below.  It also created the blue bar chart shown below and created the blue text box in the chart.   When I clicked in the "Female" button, the "Female" macro replaced all the male names, gender, and examination scores with female data that appeared in red instead of blue.  The text box also changed to red and the contents of the text box in the chart then read "Female Gender" in red.  Unfortunately, the macro buttons in the Excel chart will not get saved in the HTML version and the macros will not run.  I cannot change the data below into female data with the Female macro.
  
  
• A text box that reads "Male Gender" in blue  that was attached to the original Excel chart did not get transferred into the HTML version of the chart below.   Hence, any text that you read must be part of the charts Title, axes labels, or legend box.  You cannot transfer text from a text box that you added to the Excel chart.
  
  
• I cannot get any attached objects such as buttons, arrows, and text boxes to transfer to the HTML version.  Also recall that no macros will run in the DHTML version.
  
  
• Note that you can change the names and examination score data below.  The chart will change accordingly on-the-fly as you change the name and examination scores.  The Gender data was never part of the chart itself. 

Note that you can change the names, gender, and examination score data above.  Try it by typing in new names.  The chart will change accordingly on-the-fly as you change the name and examination scores.  The Gender data was never part of the chart itself.

Illustration 3 Showing a Multiple Chart

This is another illustration of an interactive chart.  The original table in the Excel file was as follows:

When I created a chart, I only used the second column (Names) and the fourth column (Grades) to create the Excel chart.  Then I selected the chart and saved the Excel XLS file as the following HTM file.   Note that the Gender column does not appear in the table or the chart below.  If I wanted the Gender column to appear in the table, I have to go back in, move the Gender column to the third column,  and re-save the HTM file.  Since I did not do this in this illustration, I did not get the Gender column anywhere in the table below.

Note that you can change the table values and watch the graph change.  Only now this interaction is being done in your browser that reads DHTML code rather than Excel that only reads Visual Basic colde.

Pivot Table and Microsoft Corporation Illustrations

Excel Tips and Videos (Pivot Tables and Charts)
Did you know that Microsoft Corporation presents some of its financial history in Excel pivot tables?  You can download the Excel Workbooks containing pivot tables from http://www.microsoft.com/msft/tools.htm 

I prepared two videos on how to download and use the Microsoft pivot tables.  These can be downloaded from  http://www.cs.trinity.edu/~rjensen/MicrosoftInvestorRelationPivots/

Excel Tips (Goal Seek)
This is a nice feature used by Dr. Hubbard and me when we derived the yield curves consistent with interest rate swap values in Example 5 in Appendix B of FAS 133.  Both an narrative in 133ex05.htm and an Excel workbook 133ex05a.xls  illustrating the derivation of yield curves can be found at  http://www.cs.trinity.edu/~rjensen/ 

I prepared a video illustrating the use of the Goal Seek utility in Excel at http://www.cs.trinity.edu/~rjensen/video/acct5342/ 
The goal seek video is named ExcelGoalSeek.rm 

Excel Tips (Conditional Formatting)
This is a nice article discussing a feature of Excel that I never tried before reading this article.  

"Vigilant Spreadsheets, by Charles Kelliher and Lois S. Mahoney (both authors are faculty members at the University of Central Florida), Journal of Accountancy, November 2001, pp. 41-45 --- http://www.aicpa.org/pubs/jofa/nov2001/kelliher.htm 

Would you like to be able to scan your company’s financial operations spreadsheet and instantly see which departments are over budget or behind schedule or which accounts receivable are past due? There’s an easy way to do that in Excel, which can automatically flag cells that meet most any condition you establish. You can set the cells to display different formatting flags—colors, font styles, shading, patterns, underlining—with each custom format identifying a specific financial condition. For example, you can program Excel to flag costs that are over budget by displaying them as red; under-budget costs may appear blue.

The Excel function that does this job is conditional formatting. What makes the function especially handy is that it’s not static—that is, when the data in the worksheet change, the cells instantly reflect that by taking on the appropriate formatting.

To set up the function, first highlight the cells you want to include. Then click on Format, Conditional Formatting, which brings up the dialog box shown in exhibit 1 (of the article).

I prepared a video illustrating the use of the Conditional Format utility in Excel at http://www.cs.trinity.edu/~rjensen/video/acct5342/ 
The conditional formatting video is named ExcelConditionalFormat.rm 

Bob Jensen's other video tutorials  --- http://www.cs.trinity.edu/~rjensen/video/ 

Return to top of page.