Using concatenate() to write HTML code (presenting data online)

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 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.