A couple of weeks ago I talked about the options for preparing data for presentation using the tools available in most spreadsheets, but that’s great for using in a word processor or a slide presentation, but we live in an online world. You can save a chart or graph as a picture and post it online, but if you try to do that with a table it will be much less helpful than actually having the numbers online where they can be copied.
There are a couple of ways to easily post the numbers online, and which one is best depends on whether you expect to be updating it regularly, and the accessibility of scripting languages such as PHP. They’re both pretty involved, though, so I’ll talk about one this week, and the other one in another post.
If you aren’t expecting the data to be updated frequently, or if you don’t have access to someone who can program in a scripting language, or if your server won’t allow/support scripting languages, then your best option is creating hard-coded HTML using your spreadsheet.
If you’re using a modern program, you may be able to save as HTML. The result may not look exactly like you intend it to, and the back-end code will make a hand-coded HTML purist cry, but it will be possible to post it online.
If you’re using an older program or want more control over the appearance and/or back-end code, you can just use concatenate() to create a hard-coded HTML table. (By the way, for anyone who’s worried that “hard-coded HTML” means that it’s hard to do, it’s more like “hard” in the concrete sense. It starts as a fluid, but once it turns hard, it’s not changing anymore.) The basic concept is simple, and you can follow along in the “Present_HardCoded” worksheet of the example.
Step 0: Decide where to put the code
I usually place the code to the side of the original table, but in the example I’ve placed it below it, and that’s honestly probably better. You can also place it in an entirely separate worksheet. Anything’s fine, but it slightly affects how you follow these instructions.
Step 1: Add any necessary rows or columns, naming them where appropriate.
Once you’ve chosen your space, there’s a few things to do to prepare it. First, you’re going to want two or more rows above the code for the table itself, and I usually consider an extra one for blank space to be good. So if you’re working to the side of the original table, insert the appropriate number of rows.
I say “two or more” additional rows because you will want, at minimum, one row for the starting HTML code, and one row for the ending HTML code. You may want more than one row for the starting HTML code if your rows have more than one format, since all of the formatting information for a table cell goes in the starting tag. You should name these rows whatever your want – in my example I’m using “start_header”, for the row with the code for the header row, “start1” for the rows with no coloring, “start2” for the rows with light shading, start_footer for the bottom row, and “end” for the row with the closing html tags.
Step 2: Enter the HTML code in the starting and ending rows.
In the rows you just named, enter the tags for a table cell, with whatever formatting you desire. The simplest example, with no formatting, would be <td> in the “start” row and </td> in the “end” row. If you have multiple columns with the same formatting you can either copy & paste the code from the first one into the later ones, or you can set the later ones equal to the first one. I recommend this second approach, especially if not all of the columns are the same — it means that if you make any changed you make them in only one place, and don’t have to risk over-writing different code by pasting in the wrong column.
Step 3: Enter the HTML code in the starting column.
This is the column you inserted before your table actually starts. In most cases the first cell of the first row of what you intend to be your table will be: <table><tr> (That’s saying “start a table” then “start a row”.)
The first cell of all the later rows will be: <tr> (This is just saying “start a row”)
Step 4: Enter the concatenate formula in the second column (which is the 1st column of your table).
The goal of this formula is to concatenate together the results of the previous cell in the row, the starting HTML code, the data, and the ending HTML code. So if this formula is being entered in cell B21, and the table with the source data starts in B1, the formula would be: =concatenate(A21,Start,B1,End)
If all of your rows have the same formatting, just copy this down through the same number of rows as your original table. So if your original table was 10 rows, 1 through 10, you’d copy the formula down from row 21 through row 30.
If different rows have different formatting, then just enter a modified formula on the relevant line — for example, in B22 you might enter =concatenate(A22,Start2,B2,End)
Step 5: Enter the concatenate formula in the rest of the columns (one for each column in your table)
If all of the cells in your rows are the same as the first one in that row, you can just copy the formulas you just entered and paste them to the right. Otherwise you would construct the formula the same way you did in step 4.
Step 6: Enter the concatenate formula in the last column
The last column, which is one column to the left of the the original table if you’ve positioned the formulas beneath the table, is where you enter the formula to finish off each row. In most of the rows it should just be a concatenate() of the previous column, and “</tr>”. In the bottom row it should be “</tr></table>”.
Step 6: Copy the last column of formulas
Because all of your concatenates included the text from the previous column, the last column contains all of it. If adding the bit that refers to the previous column is confusing then you can skip it, but then you’d need to copy the whole set of formulas, not just the last column.
Step 7: Paste it into your website in whatever manner you usually add content.
Here’s the one I did:
| 1/12 | 2/12 | 3/12 | 4/12 | 5/12 | 6/12 | 7/12 | 8/12 | 9/12 | 10/12 | 11/12 | 12/12 | 1/13 | 2/13 | 3/13 | 4/13 | Total | |
| Lib 1 | 159 | 182 | 153 | 167 | 46 | 44 | 36 | 59 | 72 | 80 | 68 | 37 | 160 | 184 | 158 | 168 | 1773 |
| Lib 2 | 291 | 333 | 281 | 307 | 82 | 81 | 66 | 108 | 132 | 147 | 126 | 67 | 294 | 336 | 291 | 308 | 3250 |
| Lib 3 | 200 | 228 | 193 | 210 | 57 | 56 | 45 | 74 | 90 | 101 | 87 | 45 | 203 | 230 | 200 | 211 | 2230 |
| Lib 4 | 209 | 240 | 203 | 220 | 60 | 58 | 48 | 78 | 95 | 106 | 90 | 48 | 216 | 241 | 210 | 222 | 2344 |
| Lib 5 | 328 | 375 | 317 | 346 | 93 | 91 | 75 | 121 | 149 | 166 | 141 | 75 | 329 | 379 | 327 | 347 | 3659 |
| Lib 6 | 236 | 270 | 228 | 249 | 67 | 66 | 54 | 87 | 107 | 120 | 101 | 55 | 239 | 272 | 236 | 250 | 2637 |
| Lib 7 | 379 | 436 | 366 | 400 | 109 | 105 | 86 | 141 | 172 | 192 | 164 | 88 | 387 | 438 | 380 | 402 | 4245 |
| Lib 8 | 384 | 441 | 371 | 405 | 110 | 106 | 88 | 143 | 174 | 194 | 166 | 89 | 392 | 444 | 384 | 407 | 4298 |
| Lib 9 | 240 | 274 | 232 | 253 | 68 | 67 | 54 | 89 | 109 | 121 | 104 | 55 | 243 | 277 | 240 | 253 | 2679 |
| Lib 10 | 67 | 78 | 65 | 71 | 19 | 19 | 15 | 25 | 31 | 34 | 29 | 15 | 68 | 78 | 67 | 71 | 752 |
| Total | 4986 | 8571 | 7227 | 7884 | 2133 | 2079 | 1701 | 2775 | 3393 | 3783 | 3228 | 1722 | 7676 | 8637 | 7479 | 7917 |
This trick is especially useful when you’re in an environment like the free WordPress.com host which won’t let you set up your own style sheets or embedded CSS, so you have to repeat the same CSS for every single cell you want formatted. It allows you to set it up once, and have it automatically be inserted everywhere it needs to be. If you want to change something about the formatting this will also let you change it in one place, instead of having to make the change in every single occurrence.
Even if you only have a little bit of coding to do, this can be a useful trick, especially if you’re doing any formatting. Not only does it lower the chances that you’ll accidentally forget something, or insert it in the wrong place, or mis-type the code — it can also vastly simplify your formulas. For example, here’s how you’d produce the following effect, with and without this trick:
| This is a blue background |
With this trick:
A1: <td style=”background-color: Blue;”>
A2: </td>
A3: =concatenate(A1,”This is a blue background”,A2)
Without this trick:
A3: =concatenate(<td style=”,””””,”background-color: Blue;”,””””,”>This is a blue background</td>”)
(Remember, to get a quotation mark to show up in the results of a concatenate() formula, you need to actually enter four quotation marks.)
I don’t know about you, but I know which one I’d rather write, read, and edit!
You can also extend this trick to another level, if you want. For example:
| Color | Start | End | Formula | Result |
| Red | =concatenate(“<td style=”, “”””, “background-color: “, Color, “;”, “”””, “>”) | </td> | =concatenate(Start, “This cell is “, Color, “.”, End) | This cell is Red. |
| Yellow | =concatenate(“<td style=”, “”””, “background-color: “, Color, “;”, “”””, “>”) | </td> | =concatenate(Start, “This cell is “, Color, “.”, End) | This cell is Yellow. |
| Blue | =concatenate(“<td style=”, “”””, “background-color: “, Color, “;”, “”””, “>”) | </td> | =concatenate(Start, “This cell is “, Color, “.”, End) | This cell is Blue. |
That’s it for this week’s post! Next week I’ll be back with generating CSV files for use as online database.