How to use tables for collecting data and calculating using Org-Mode

Creating tables in org-mode

In org-mode, you have many features for organising your text. You can even execute code from inside the document. When you want to collect data, your expenses for example, you would want a table. Most users will use a spreadsheet program but that is a bit of overkill, especially since you are usually in Emacs! Consider an expense list, you want a few items with price and date.
To create this table you can start with a ‘|’ character and ‘Item’ then ‘|’ and so on until the end of the line. Finish the line with hitting the TAB key and your table will be created for you. After that you just hit TAB for each item you want to fill in.

Filling in data

You may also have a table already. In this case you can create from a region. If you take data from a spreadsheet and paste it into your org document it shows up as a list. It is usually separated by spaces. To make this a table, make sure it is marked and then convert it to a table, using ‘C-c |’.

Cabbage €0.84 06/06/2019 Sweet Potatoes €3.90 06/06/2019 Lettuce €0.43 06/06/2019 Broccoli €1.09 06/06/2019
If you mark the text above and convert it, the result is this:
The conversion looks for tabs first, second commas and last spaces on every line. Many times you will have extra fields due to spaces inside text. If this happens, experiment with adding more spaces and using ‘C-u C-u C-u 3’. Here 3 is an arbitrary number that sets how many spaces should mean ‘end of field’.

Moving data, formatting tables

The example above contains only the absolute basics, you may want to add a column in the table. This is easily achieved by being in the table and typing ‘M-S-<right>’. Once you start practising, you will notice the connection to the move column command ‘M-<right>’.
Continue with the same table and add a heading. Start by adding a new line with ‘M-S-<down>’, create a line across the table ‘C-c -‘. Continue to fill in the top with text.
This example is not possible to import into gnucash so you need to add a few columns. It is also useful to re-arrange them in the common order that gnucash expects. Try creating the below table yourself.

Adding formulas

When you have a table, many times you want to calculate the sum of columns, or specific fields. Org-mode uses the calc module that is usually part of Emacs. The simplest calculation you want to do is to summarise the current column, you do this with ‘C-c +’. The result will show up in the mini-buffer. If you want to put them in the column, use ‘C-y’ and it shows up in the current place of the cursor. Most likely you will have a table where you update the values on a regular basis and want to see their sum or average immediately in the table. To add a formula, you have two methods, type it in a cell or use a key chord. When you use the key chord ‘C-c =’, you can place your cursor anywhere in the column you want and the result will be placed in that cell. You can also fill in the formula manually, the format is simpler than it looks at first sight. Any formula starts with the cell where the data goes and then an equals sign and continues with the function choice, ‘vsum’ is the most common.

#+TBLFM: $6=vsum(@I..@II)

This example puts the result in the 6th Column ($6=), the values are calculated from all values in the column between the first (I) and the second separator (II). You can also point to the cells themselves. You do this by using the the row (@) and the column ($) and then the number. Since the table will change as you add data you should usually point to relative positions though. Relative positions are denoted with ‘>’s. A single one means the one closest to the end. The other direction ‘<‘ means the beginning so in the case of columns ‘$’ the leftmost. In the example table ‘$6’ equals ‘$>’. When you use the relative value the value will be found in the last column even when you add a new column. If you use ‘$6’, you need to change the number to match how many columns you have.

#+TBLFM: $>=vsum(@I..@II)

Exporting to other documents

You can export the table to a csv file if you want to use it in other applications. It is very simple, just put your cursor inside the table and hit ‘M-x org-table-export’.

Conclusion

The table mode is very easy to get started with for simple tables but if you look further you can find many spreadsheet features. This article has only scratched the surface of what is possible. There are many more mathematical features and you can even produce graphs from the data.

About the author

Mats Tage Axelsson

I am a freelance writer for Linux magazines. I enjoy finding out what is possible under Linux and how we can all chip in to improve it. I also cover renewable energy and the new way the grid operates. You can find more of my writing on my blog.