This tutorial is designed to show you step-by-step how to transform an Excel or similar spreadsheet full of data into a webpage without having to manually type in every single tag for each cell in the table. For my webpages, I don't use a WYSIWYG HTML editor but do all my coding more or less from scratch, and this Excel trick is a great timesaver. I'm pretty sure you can copy and paste the finished Excel table into a WYSIWYG program and get the same results. This method may seem complicated at first, but after you do it a few times it's a breeze!
Here are a few examples that I've used this method for: Census, Cemetery readings and Biography Indexes. You can use Excel for any type of page that you want aligned into nice, straight rows and columns
Type your census, cemetery or other table type data in Excel, using one column in Excel for each individual column on the original document. To save time formatting, I prefer to type an entire township or division all on one worksheet rather than putting each page of the census on separate worksheets. I usually put the census page numbers in the left-most column so it's easier to keep track of the page divisions.
Step 1: Open your Excel document.
Step 2: Click on the top-most and left-most cell of the data you want to include on your webpage. In this example, I don't want the column with the Page Number, nor do I want the row with the headers, so I will click on cell B3. Left-click on your starting cell and highlight all the cells you want to include either by dragging the mouse while holding the left button down, or by holding down the Shift key and using the arrow keys. Make sure you get all the columns across the width of the database.
Step 3: Click on "Edit", then "Replace…". Leave the "Find what" box blank, then in the "Replace with" box type " " (type without the "") Then click the "Replace All" button. This will put a non-breaking space in all the blank cells, and keep the table properly formatted in the webpage.
Step 4: Locate the left-most column that you want to include, in this example the "Dwelling #" column. Click on the top cell in that column, then click on "Insert" then "Columns". This will add an empty column to the left of your first data column. Click on the second data column (Family #) and repeat to add another empty column. Repeat, adding an empty column between each data column across the entire worksheet.
Step 5: Click in the top-most cell of the left-most empty column you just added and type your table row opening tags: <TR><TD>. Click in this same cell and hold down the left button and drag the mouse until all the cells in that column are highlighted.
Click on "Edit", then "Fill" then "Down". This will fill the entire column with your opening tags so you only have to type it once
Step 6: Repeat step 5 in all of the remaining empty columns EXCEPT you will need to type </TD><TD> in all of the columns except the last one, which will be </TD></TR>. If you are familiar with HTML coding you will see that each individual data cell is now between a table cell tag. If you want to make the text in each cell bold you can use <TR><TD><B> in the first column and </B></TD><TD><B> in the other columns
Step 7: The final step is to highlight all of the columns and rows of data and formatting that you wish to include on your webpage. Click on the top-most <TR><TD> cell and then drag until all are highlighted. Click on "Edit" then "Copy", and then go to your HTML document and paste it in. Your HTML document will need to have your table opening and closing tags added such as <TABLE> just before the first <TR><TD> and </TABLE> after the last </TD></TR> tag, plus headers for each column in the census. You probably won't want to put an entire township on one webpage, so I usually just copy one or two census pages at a time from the Excel file and then paste bewteen the table tags, then copy and paste one or two more into the next HTML page
Now you can either repeat this process for each worksheet in the Excel file, or you can use this method to code all of the worksheets in a single document at once. This will only work well if all of your worksheets contain an identical number of columns, and each column contains the same information (as a census would). The worksheets must all be in the same workbook (document).
Hold down the "Ctrl" key on your keyboard and then left-click on each tab of the worksheet that you want to include. The tabs that are included will turn the same color as the first sheet you were viewing. After you have grouped the desired worksheets, begin formatting on your first page starting with Step 2 above (the example showing the Grouping has already been formatted, so please ignore the formatting). Whatever action you take in this worksheet will be carried out indentically on all the other selected sheets. This is important to remember in case you see a typo or change something in a cell, whatever you type will be typed on all the other sheets. When you are done formatting, right-click on a tab and select "Ungroup Sheets", now the sheets are all separate again. If some of the sheets have fewer rows of data you may want to delete the extra formatting rows as you don't need to copy and paste them into the HTML page. Likewise, if some sheets have more data rows and the columns with the tags don't go down far enough, use the "Fill --> Down" command to fill the empty cells in the tag columns.
If this tutorial doesn't make sense or you still need help, please feel free to Email me