Lots of Small Spreadsheets

Tables in org-mode

Let’s begin with a brief overview of tables in org-mode. If you are already familiar, skip ahead.

Tables are little-more than vertical pipes in a normal text file. However, org-mode will keep keep them nicely formatted. For instance, typing this:

| Date|Category|Amount
|-
|▯

And then hitting tab, will produce this:

| Date | Category | Amount |
|------+----------+--------|
| ▯    |          |        |

Enter data in your table and use TAB to jump from cell to cell, and don’t worry about the formatting, for as soon as you enter this:

| Date | Category | Amount |
|------+----------+--------|
| <2015-03-12 Thu>     |          |        |

The TAB key will reformat the table, and you’ll have:

| Date             | Category | Amount |
|------------------+----------+--------|
| <2015-03-12 Thu> |          |        |

You can convert existing data into a table. For instance, select a region covering an embedded CSV, and call org-table-convert-region, to go from:

a,b,c,d
1,2

| a | b | c | d |
| 1 | 2 |   |   |

Spaces instead of commas? No problem. If the function doesn’t find any commas in the region, it changes to spaces. If you have commas, but the separators are tabs, type C-u first, to go from:

a	x,y,z	c
1	2

| a | x,y,z | c |
| 1 |     2 |   |

Tables in org-mode isn’t just about formatting data, as it has a number of spreadsheet-like features. For instance, it you place the cursor on the third column in this table:

| Date       | Category         | Amount |
|------------+------------------+--------|
| 2014/01/14 | Supplies         |  43.97 |
| 2014/02/15 | Supplies         |  56.48 |
| 2014/02/11 | Book             |  17.99 |
| 2014/06/10 | Kinesis Keyboard | 289.16 |
| 2014/08/22 | Books            |  18.99 |
| 2014/08/23 | Printer          |  99.96 |
| 2014/08/25 | Books            |   7.50 |
| 2014/08/30 | Supplies         |  58.26 |
| 2014/09/15 | Books            |  21.49 |
|------------+------------------+--------|
|            | Total:           |        |

And hit C-c +, the following message appears:

Sum of 9 items: 613.8    (C-y will insert result into buffer)

This allows you to move to the last row in that row, and insert the result into that cell. The downside is that once you insert the sum into the bottom cell, but later change a value above, C-c + will add the old summed value! The work around is to remove the sum first.

A better approach is to specify a table formula, just like you probably would with regular spreadsheet application. Since org-mode is a text file, we have to put those formulas somewhere, so they are appended to the table, as in:

| Date       | Category         | Amount |
|------------+------------------+--------|
| 2014/01/14 | Supplies         |  43.97 |
| 2014/02/15 | Supplies         |  56.48 |
| 2014/02/11 | Book             |  17.99 |
| 2014/06/10 | Kinesis Keyboard | 289.16 |
| 2014/08/22 | Books            |  18.99 |
| 2014/08/23 | Printer          |  99.96 |
| 2014/08/25 | Books            |   7.50 |
| 2014/08/30 | Supplies         |  58.26 |
| 2014/09/15 | Books            |  21.49 |
|------------+------------------+--------|
|            | Total:           |  613.8 |
#+TBLFM: @>$3=vsum(@2..@-1)

Then you place the cursor on the #+TBLFM line, and hit C-c C-c to recalculate your spreadsheet. What does that alchemical incantation? First, the = sign specifies a cell on the left hand side and its value on the right hand side. Let’s break up the rest:

• @> The @ specifies a row, and this refers to the last row in a table.
• $3 The $ specifies a column, so this refers to the third column.
• vsum A vertical sum function with parameters given in parenthesis
• @2 The second row. Notice that it the Amount header is @1 and the dashes separating the header from the body is ignored.
• @-1 The next to the last row. Using these relative references mean that we can add rows to our table, and still have the sum formula work.

At this point, I guess I would recommend the online documentation.