Converting Excel to HTML Without the Crap

Steven Dutch, Natural and Applied Sciences,University of Wisconsin - Green Bay
First-time Visitors: Please visitSite Map and Disclaimer. Use "Back" to return here.


So you have a simple table you'd like to export from Excel (or some other spreadsheet) to HTML. It looks like this:

  A B C D
1 State Capital Area Sq. Mi. Population 2000
2 Maine Augusta 35,385 1,274,923
3 New Hampshire Concord 9,350 1,235,786
4 Vermont Montpelier 9,614 608,827
5 Massachusetts Boston 10,555 6,349,097
Rhode Island Providence 1,545 1,048,319 
7 Connecticut Hartford 5,543 3,405,565 

So you create it in Excel, insert it into your Web page, and try to modify it. And it won't modify easily. So you decide to have a look at the HTML code, and you find:

<table border="0" cellpadding="0" cellspacing="0" width="357" style="border-collapse:
collapse;width:268pt">
<colgroup>
<col width="101" style="mso-width-source:userset;mso-width-alt:3693;width:76pt">
<col width="85" style="mso-width-source:userset;mso-width-alt:3108;width:64pt">
<col width="88" style="mso-width-source:userset;mso-width-alt:3218;width:66pt">
<col width="83" style="mso-width-source:userset;mso-width-alt:3035;width:62pt">
</colgroup>
<tr height="40">
<td height="40" width="101" style="height: 30.0pt; width: 76pt" class="style1">
State</td>
<td width="85" style="width: 64pt" class="style2">Capital</td>
<td width="88" style="width: 66pt" class="style2">Area Sq. Mi.</td>
<td width="83" style="width: 62pt" class="style3">Population 2000</td>
</tr>
<tr height="20">
<td height="20" width="101" style="height: 15.0pt; width: 76pt" class="style4">
Maine</td>
<td width="85" style="width: 64pt" class="style5">Augusta</td>
<td align="right" width="88" style="width: 66pt" class="style5">35,385</td>
<td width="83" style="width: 62pt" class="style6">1,274,923</td>
</tr>
<tr height="40">
<td height="40" width="101" style="height: 30.0pt; width: 76pt" class="style4">
New Hampshire</td>
<td width="85" style="width: 64pt" class="style5">Concord</td>
<td align="right" width="88" style="width: 66pt" class="style5">9,350</td>
<td width="83" style="width: 62pt" class="style6">1,235,786</td>
</tr>
<tr height="20">
<td height="20" width="101" style="height: 15.0pt; width: 76pt" class="style4">
Vermont</td>
<td width="85" style="width: 64pt" class="style5">Montpelier</td>
<td align="right" width="88" style="width: 66pt" class="style5">9,614</td>
<td width="83" style="width: 62pt" class="style6">608,827</td>
</tr>
<tr height="20">
<td height="20" width="101" style="height: 15.0pt; width: 76pt" class="style4">
Massachusetts</td>
<td width="85" style="width: 64pt" class="style5">Boston</td>
<td align="right" width="88" style="width: 66pt" class="style5">10,555</td>
<td width="83" style="width: 62pt" class="style6">6,349,097</td>
</tr>
<tr height="20">
<td height="20" width="101" style="height: 15.0pt; width: 76pt" class="style4">
Rhode Island</td>
<td width="85" style="width: 64pt" class="style5">Providence</td>
<td align="right" width="88" style="width: 66pt" class="style5">1,545</td>
<td width="83" style="width: 62pt" class="style6">1,048,319</td>
</tr>
<tr height="21">
<td height="21" width="101" style="height: 15.75pt; width: 76pt" class="style7">
Connecticut</td>
<td width="85" style="width: 64pt" class="style8">Hartford</td>
<td align="right" width="88" style="width: 66pt" class="style8">5,543</td>
<td width="83" style="width: 62pt" class="style9">3,405,565</td>
</tr>
</table>

Where did all this garbage come from? The fault here is not with the spreadsheet, it's with the Web authoring programs that insist on putting this rubbish into everything. Any software that possesses real flexibility is a threat to some software writers, so the people who created the web authoring software insist that every single element has to be explicitly formatted. And of course there is no option for simply creating free form HTML.

For one thing, the authors of this mess seem to have forgotten that the "c" in "css" means "cascading." That means a style, once set, remains set until overridden by a later style sheet. So there is no need to declare a style for every single cell. Even in standard HTML, once a column width is set, it doesn't need to be set in every cell.

Is there some way to get a nice, simple table with simple, bare-bones HTML? Yes, but it will take a little work. The good news is that once the work is done, you can re-use it indefinitely. here's your original table:

  A B C D
1 State Capital Area Sq. Mi. Population 2000
2 Maine Augusta 35,385 1,274,923
3 New Hampshire Concord 9,350 1,235,786
4 Vermont Montpelier 9,614 608,827
5 Massachusetts Boston 10,555 6,349,097
Rhode Island Providence 1,545 1,048,319 
7 Connecticut Hartford 5,543 3,405,565 

Now, go to Sheet 2 of your spreadsheet. In columns A,C,E, etc., you put your HTML code

  A B C D E F G H I
1 <tr><td>   </td><td>   </td><td>   </td><td>   </td></tr>
2 <tr><td>   </td><td>   </td><td>   </td><td>   </td></tr>
3 <tr><td>   </td><td>   </td><td>   </td><td>   </td></tr>
4 <tr><td>   </td><td>   </td><td>   </td><td>   </td></tr>
5 <tr><td>   </td><td>   </td><td>   </td><td>   </td></tr>
<tr><td>   </td><td>   </td><td>   </td><td>   </td></tr>
7 <tr><td>   </td><td>   </td><td>   </td><td>   </td></tr>

In columns B,D,F, etc., you put references to the data:

  A B C D E F G H I
1 <tr><td> =Sheet1!A1 </td><td> =Sheet1!B1 </td><td> =Sheet1!C1 </td><td> =Sheet1!D1 </td></tr>
2 <tr><td> =Sheet1!A2 </td><td> =Sheet1!B2 </td><td> =Sheet1!C2 </td><td> =Sheet1!D2 </td></tr>
3 <tr><td> =Sheet1!A3 </td><td> =Sheet1!B3 </td><td> =Sheet1!C3 </td><td> =Sheet1!D3 </td></tr>
4 <tr><td> =Sheet1!A4 </td><td> =Sheet1!B4 </td><td> =Sheet1!C4 </td><td> =Sheet1!D4 </td></tr>
5 <tr><td> =Sheet1!A5 </td><td> =Sheet1!B5 </td><td> =Sheet1!C5 </td><td> =Sheet1!D5 </td></tr>
<tr><td> =Sheet1!A6 </td><td> =Sheet1!B6 </td><td> =Sheet1!C6 </td><td> =Sheet1!D6 </td></tr>
7 <tr><td> =Sheet1!A7 </td><td> =Sheet1!B7 </td><td> =Sheet1!C7 </td><td> =Sheet1!D7 </td></tr>

The good news is you only need to do this for the first row, then you can copy it into all the remaining rows. Make as many columns and rows as you are likely to need even for a large table. Once you copy the formulas, your sheet will look like this:

  A B C D E F G H I
1 <tr><td> State </td><td> Capital </td><td> Area Sq. Mi. </td><td> Population 2000 </td></tr>
2 <tr><td> Maine </td><td> Augusta </td><td> 35,385 </td><td> 1,274,923 </td></tr>
3 <tr><td> New Hampshire </td><td> Concord </td><td> 9,350 </td><td> 1,235,786 </td></tr>
4 <tr><td> Vermont </td><td> Montpelier </td><td> 9,614 </td><td> 608,827 </td></tr>
5 <tr><td> Massachusetts </td><td> Boston </td><td> 10,555 </td><td> 6,349,097 </td></tr>
<tr><td> Rhode Island </td><td> Providence </td><td> 1,545 </td><td> 1,048,319  </td></tr>
7 <tr><td> Connecticut </td><td> Hartford </td><td> 5,543 </td><td> 3,405,565  </td></tr>

Last step, copy the table into your web page. Not into Design View because that's how all the formatting garbage gets inserted. Copy it into Code View so it goes into the HTML code exactly like it looks. You should have no problems even if all the cells aren't fully visible, but if cell contents do get truncated, go back and format the column widths in your spreadsheet. Add <table> at the beginning and </table> at the end. Bettwr yet, define those tags first and copy the spreadsheet contents between them.

One small problem is that blank cells on the original spreadsheet come across as zeros. You can usually edit these out of the web page. If you have numerical data with zeros that can't simply be deleted, go to the Code View and delete zeros between tags. Actually, replace </td>  0  <td> with </td>    <td>.

You have to copy the full width of the spreadsheet to get the </tr> tags. However, any excess columns can be easily deleted in the Design View. You can limit the copied text to actual data but if you copy excess rows, they can also easily be deleted.

Now, save your spreadsheet. Next time you have data to put into an HTML table, just insert it into Sheet 1 (erase the existing data first) and copy Sheet 2 to your Web Page.


Return to Computer Tips Index
Return to Professor Dutch's Home Page

Created 18 January 2002, Last Update 12 May 2010

Not an official UW Green Bay site