Tables
Org comes with a fast and intuitive table editor. Spreadsheet-like calculations are supported using the Emacs Calc package (see GNU Emacs Calculator Manual).
Built-in Table Editor
Org makes it easy to format tables in plain ASCII. Any line with |
as the first non-whitespace character is considered part of a table.
| is also the column separator(note: To insert a vertical bar into a
table field, use \vert or, inside a word abc\vert{}def.).
Moreover, a line starting with |- is a horizontal rule. It
separates rows explicitly. Rows before the first horizontal rule are
header lines. A table might look like this:
| Name | Phone | Age | |-------+-------+-----| | Peter | 1234 | 17 | | Anna | 4321 | 25 |
A table is re-aligned automatically each time you press
TAB, RET or C-c C-c inside the table.
TAB also moves to the next field—RET to the
next row—and creates new table rows at the end of the table or
before horizontal lines. The indentation of the table is set by the
first line. Horizontal rules are automatically expanded on every
re-align to span the whole table width. So, to create the above
table, you would only type
|Name|Phone|Age| |-
and then press TAB to align the table and start filling in
fields. Even faster would be to type |Name|Phone|Age followed by
C-c RET.
When typing text into a field, Org treats DEL,
Backspace, and all character keys in a special way, so that
inserting and deleting avoids shifting other fields. Also, when
typing immediately after point was moved into a new field with
TAB, S-TAB or RET, the field is
automatically made blank. If this behavior is too unpredictable for
you, configure the option org-table-auto-blank-field.
Creation and conversion
-
C-c |(org-table-create-or-convert-from-region)
Convert the active region to table. If every line contains at least
one TAB character, the function assumes that the material
is tab separated. If every line contains a comma, comma-separated
values (CSV) are assumed. If not, lines are split at whitespace
into fields. You can use a prefix argument to force a specific
separator: C-u forces CSV, C-u C-u forces
TAB, C-u C-u C-u prompts for a regular
expression to match the separator, and a numeric argument
N indicates that at least N consecutive spaces, or alternatively
a TAB will be the separator.
If there is no active region, this command creates an empty Org
table. But it is easier just to start typing, like | N a m e | P h o n e | A g e RET | - TAB.
Re-aligning and field motion
-
C-c C-c(org-table-align)
Re-align the table without moving point.
-
TAB(org-table-next-field)
Re-align the table, move to the next field. Creates a new row if necessary.
-
M-x org-table-blank-field
Blank the current table field or active region.
-
S-TAB(org-table-previous-field)
Re-align, move to previous field.
-
RET(org-table-next-row)
Re-align the table and move down to next row. Creates a new row if
necessary. At the beginning or end of a line, RET still
inserts a new line, so it can be used to split a table.
-
M-a(org-table-beginning-of-field)
Move to beginning of the current table field, or on to the previous field.
-
M-e(org-table-end-of-field)
Move to end of the current table field, or on to the next field.
Column and row editing
-
M-LEFT(org-table-move-column-left)
Move the current column left.
-
M-RIGHT(org-table-move-column-right)
Move the current column right.
-
M-S-LEFT(org-table-delete-column)
Kill the current column.
-
M-S-RIGHT(org-table-insert-column)
Insert a new column at point position. Move the recent column and all cells to the right of this column to the right.
-
M-UP(org-table-move-row-up)
Move the current row up.
-
M-DOWN(org-table-move-row-down)
Move the current row down.
-
M-S-UP(org-table-kill-row)
Kill the current row or horizontal line.
-
S-UP(org-table-move-cell-up)
Move cell up by swapping with adjacent cell.
-
S-DOWN(org-table-move-cell-down)
Move cell down by swapping with adjacent cell.
-
S-LEFT(org-table-move-cell-left)
Move cell left by swapping with adjacent cell.
-
S-RIGHT(org-table-move-cell-right)
Move cell right by swapping with adjacent cell.
-
M-S-DOWN(org-table-insert-row)
Insert a new row above the current row. With a prefix argument, the line is created below the current one.
-
C-c -(org-table-insert-hline)
Insert a horizontal line below current row. With a prefix argument, the line is created above the current line.
-
C-c RET(org-table-hline-and-move)
Insert a horizontal line below current row, and move point into the row below that line.
-
C-c ^(org-table-sort-lines)
Sort the table lines in the region. The position of point indicates the column to be used for sorting, and the range of lines is the range between the nearest horizontal separator lines, or the entire table. If point is before the first column, you are prompted for the sorting column. If there is an active region, the mark specifies the first line and the sorting column, while point should be in the last line to be included into the sorting. The command prompts for the sorting type, alphabetically, numerically, or by time. You can sort in normal or reverse order. You can also supply your own key extraction and comparison functions. When called with a prefix argument, alphabetic sorting is case-sensitive.
Regions
-
C-c C-x M-w(org-table-copy-region)
Copy a rectangular region from a table to a special clipboard. Point and mark determine edge fields of the rectangle. If there is no active region, copy just the current field. The process ignores horizontal separator lines.
-
C-c C-x C-w(org-table-cut-region)
Copy a rectangular region from a table to a special clipboard, and blank all fields in the rectangle. So this is the "cut" operation.
-
C-c C-x C-y(org-table-paste-rectangle)
Paste a rectangular region into a table. The upper left corner ends up in the current field. All involved fields are overwritten. If the rectangle does not fit into the present table, the table is enlarged as needed. The process ignores horizontal separator lines.
-
M-RET(org-table-wrap-region)
Split the current field at point position and move the rest to the line below. If there is an active region, and both point and mark are in the same column, the text in the column is wrapped to minimum width for the given number of lines. A numeric prefix argument may be used to change the number of desired lines. If there is no region, but you specify a prefix argument, the current field is made blank, and the content is appended to the field above.
Calculations
-
C-c +(org-table-sum)
Sum the numbers in the current column, or in the rectangle defined
by the active region. The result is shown in the echo area and can
be inserted with C-y.
-
S-RET(org-table-copy-down)
When current field is empty, copy from first non-empty field above. When not empty, copy current field down to next row and move point along with it.
Depending on the variable org-table-copy-increment, integer and
time stamp field values, and fields prefixed or suffixed with
a whole number, can be incremented during copy. Also, a 0 prefix
argument temporarily disables the increment.
This key is also used by shift-selection and related modes (see Packages that conflict with Org mode).
Miscellaneous
-
C-c `(org-table-edit-field)
Edit the current field in a separate window. This is useful for
fields that are not fully visible (see Column Width and Alignment).
When called with a C-u prefix, just make the full field
visible, so that it can be edited in place. When called with two
C-u prefixes, make the editor window follow point through
the table and always show the current field. The follow mode exits
automatically when point leaves the table, or when you repeat this
command with C-u C-u C-c `.
-
M-x org-table-import
Import a file as a table. The table should be TAB or whitespace separated. Use, for example, to import a spreadsheet table or data from a database, because these programs generally can write TAB-separated text files. This command works by inserting the file into the buffer and then converting the region to a table. Any prefix argument is passed on to the converter, which uses it to determine the separator.
-
C-c |(org-table-create-or-convert-from-region)
Tables can also be imported by pasting tabular text into the Org
buffer, selecting the pasted text with C-x C-x and then
using the C-c | command (see Creation and conversion).
-
M-x org-table-export
Export the table, by default as a TAB-separated file. Use for data
exchange with, for example, spreadsheet or database programs. The
format used to export the file can be configured in the variable
org-table-export-default-format. You may also use properties
TABLE_EXPORT_FILE and TABLE_EXPORT_FORMAT to specify the file
name and the format for table export in a subtree. Org supports
quite general formats for exported tables. The exporter format is
the same as the format used by Orgtbl radio tables, see Translator functions, for a detailed description.
-
M-x org-table-header-line-mode
Turn on the display of the first data row of the table at point in
the window header line when this first row is not visible anymore in
the buffer. You can activate this minor mode by default by setting
the option org-table-header-line-p to t.
-
M-x org-table-transpose-table-at-point
Transpose the table at point and eliminate hlines.
Column Width and Alignment
The width of columns is automatically determined by the table editor. The alignment of a column is determined automatically from the fraction of number-like versus non-number fields in the column.
Editing a field may modify alignment of the table. Moving
a contiguous row or column—i.e., using TAB or
RET—automatically re-aligns it. If you want to disable
this behavior, set org-table-automatic-realign to nil. In any
case, you can always align manually a table:
-
C-c C-c(org-table-align)
Align the current table.
Setting the option org-startup-align-all-tables re-aligns all tables
in a file upon visiting it. You can also set this option on
a per-file basis with:
#+STARTUP: align #+STARTUP: noalign
Sometimes a single field or a few fields need to carry more text, leading to inconveniently wide columns. Maybe you want to hide away several columns or display them with a fixed width, regardless of content, as shown in the following example.
|---+---------------------+--------| |---+-------…+…| | | <6> | | | | <6> …|…| | 1 | one | some | ----\ | 1 | one …|…| | 2 | two | boring | ----/ | 2 | two …|…| | 3 | This is a long text | column | | 3 | This i…|…| |---+---------------------+--------| |---+-------…+…|
To set the width of a column, one field anywhere in the column may
contain just the string <N> where N specifies the width
as a number of characters. You control displayed width of columns
with the following tools:
-
C-c TAB(org-table-toggle-column-width)
Shrink or expand current column.
If a width cookie specifies a width W for the column, shrinking it displays the first W visible characters only. Otherwise, the column is shrunk to a single character.
When called before the first column or after the last one, ask for a list of column ranges to operate on.
-
C-u C-c TAB(org-table-shrink)
Shrink all columns with a column width. Expand the others.
-
C-u C-u C-c TAB(org-table-expand)
Expand all columns.
To see the full text of a shrunk field, hold the mouse over it:
a tool-tip window then shows the full contents of the field.
Alternatively, C-h . (display-local-help) reveals them,
too. For convenience, any change near the shrunk part of a column
expands it.
Setting the option org-startup-shrink-all-tables shrinks all columns
containing a width cookie in a file the moment it is visited. You can
also set this option on a per-file basis with:
#+STARTUP: shrink
If you would like to overrule the automatic alignment of number-rich
columns to the right and of string-rich columns to the left, you can
use <r>, <c> or <l> in a similar fashion. You may also combine
alignment and field width like this: <r10>.
Lines which only contain these formatting cookies are removed automatically upon exporting the document.
Column Groups
When Org exports tables, it does so by default without vertical lines
because that is visually more satisfying in general. Occasionally
however, vertical lines can be useful to structure a table into groups
of columns, much like horizontal lines can do for groups of rows. In
order to specify column groups, you can use a special row where the
first field contains only /. The further fields can either contain
< to indicate that this column should start a group, > to indicate
the end of a column, or <> (no space between < and >) to make
a column a group of its own. Upon export, boundaries between column
groups are marked with vertical lines. Here is an example:
| N | N^2 | N^3 | N^4 | sqrt(n) | sqrt[4](N) | |---+-----+-----+-----+---------+------------| | / | < | | > | < | > | | 1 | 1 | 1 | 1 | 1 | 1 | | 2 | 4 | 8 | 16 | 1.4142 | 1.1892 | | 3 | 9 | 27 | 81 | 1.7321 | 1.3161 | |---+-----+-----+-----+---------+------------| #+TBLFM: $2=$1^2::$3=$1^3::$4=$1^4::$5=sqrt($1)::$6=sqrt(sqrt(($1)))
It is also sufficient to just insert the column group starters after every vertical line you would like to have:
| N | N^2 | N^3 | N^4 | sqrt(n) | sqrt[4](N) | |---+-----+-----+-----+---------+------------| | / | < | | | < | |
The Orgtbl Minor Mode
If you like the intuitive way the Org table editor works, you might
also want to use it in other modes like Text mode or Mail mode. The
minor mode Orgtbl mode makes this possible. You can always toggle the
mode with M-x orgtbl-mode. To turn it on by default, for
example in Message mode, use
(add-hook 'message-mode-hook #'turn-on-orgtbl)Furthermore, with some special setup, it is possible to maintain tables in arbitrary syntax with Orgtbl mode. For example, it is possible to construct LaTeX tables with the underlying ease and power of Orgtbl mode, including spreadsheet capabilities. For details, see Tables in Arbitrary Syntax.
The Spreadsheet
The table editor makes use of the Emacs Calc package to implement spreadsheet-like capabilities. It can also evaluate Emacs Lisp forms to derive fields from other fields. While fully featured, Org's implementation is not identical to other spreadsheets. For example, Org knows the concept of a column formula that will be applied to all non-header fields in a column without having to copy the formula to each relevant field. There is also a formula debugger, and a formula editor with features for highlighting fields in the table corresponding to the references at point in the formula, moving these references by arrow keys.
References
To compute fields in the table from other fields, formulas must
reference other fields or ranges. In Org, fields can be referenced by
name, by absolute coordinates, and by relative coordinates. To find
out what the coordinates of a field are, press C-c ? in
that field, or press C-c } to toggle the display of a grid.
Field references
Formulas can reference the value of another field in two ways. Like
in any other spreadsheet, you may reference fields with
a letter/number combination like B3, meaning the second field in the
third row. However, Org prefers to use another, more general
representation that looks like this:7
@ROW$COLUMN
Column specifications can be absolute like $1, $2, …, $N, or
relative to the current column, i.e., the column of the field which is
being computed, like $+1 or $-2. $< and $> are immutable
references to the first and last column, respectively, and you can use
$>>> to indicate the third column from the right.
The row specification only counts data lines and ignores horizontal
separator lines, or "hlines". Like with columns, you can use absolute
row numbers @1, @2, …, @N, and row numbers relative to the
current row like @+3 or @-1. @< and @> are immutable
references the first and last row in the table, respectively. You may
also specify the row relative to one of the hlines: @I refers to the
first hline, @II to the second, etc. @-I refers to the first such
line above the current line, @+I to the first such line below the
current line. You can also write @III+2 which is the second data
line after the third hline in the table.
@0 and $0 refer to the current row and column, respectively, i.e.,
to the row/column for the field being computed. Also, if you omit
either the column or the row part of the reference, the current
row/column is implied.
Org's references with unsigned numbers are fixed references in the sense that if you use the same reference in the formula for two different fields, the same field is referenced each time. Org's references with signed numbers are floating references because the same reference operator can reference different fields depending on the field being calculated by the formula.
Here are a few examples:
@2$3 |
2nd row, 3rd column (same as C2) |
$5 |
column 5 in the current row (same as E&) |
@2 |
current column, row 2 |
@-1$-3 |
field one row up, three columns to the left |
@-I$2 |
field just under hline above current row, column 2 |
@>$5 |
field in the last row, in column 5 |
Range references
You may reference a rectangular range of fields by specifying two
field references connected by two dots ... The ends are included in
the range. If both fields are in the current row, you may simply use
$2..$7, but if at least one field is in a different row, you need to
use the general @ROW$COLUMN format at least for the first field,
i.e., the reference must start with @ in order to be interpreted
correctly. Examples:
$1..$3 |
first three fields in the current row |
$P..$Q |
range, using column names (see Advanced features) |
$<<<..$>> |
start in third column, continue to the last but one |
@2$1..@4$3 |
nine fields between these two fields (same as A2..C4) |
@-1$-2..@-1 |
3 fields in the row above, starting from 2 columns on the left |
@I..II |
between first and second hline, short for @I..@II |
Range references return a vector of values that can be fed into Calc
vector functions. Empty fields in ranges are normally suppressed, so
that the vector contains only the non-empty fields. For other options
with the mode switches E, N and examples, see Formula syntax for Calc.
Field coordinates in formulas
One of the very first actions during evaluation of Calc formulas and
Lisp formulas is to substitute @# and $# in the formula with the
row or column number of the field where the current result will go to.
The traditional Lisp formula equivalents are org-table-current-dline
and org-table-current-column. Examples:
-
if(@# % 2, $#, string("")) - Insert column number on odd rows, set field to empty on even rows.
-
$2 = '(identity remote(FOO, @@#$1)) - Copy text or values of each row of column 1 of the table named FOO into column 2 of the current table.
-
@3 = 2 * remote(FOO, @1$$#) - Insert the doubled value of each column of row 1 of the table named FOO into row 3 of the current table.
For the second and third examples, table FOO must have at least as many rows or columns as the current table. Note that this is inefficient(note: The computation time scales as O(N^2) because table FOO is parsed for each field to be copied.) for large number of rows.
Named references
$name is interpreted as the name of a column, parameter or constant.
Constants are defined globally through the variable
org-table-formula-constants, and locally—for the file—through
a line like this example:
#+CONSTANTS: c=299792458. pi=3.14 eps=2.4e-6
Also, properties (see Properties and Columns) can be used as
constants in table formulas: for a property Xyz use the name
$PROP_Xyz, and the property will be searched in the current outline
entry and in the hierarchy above it. If you have the constants.el
package, it will also be used to resolve constants, including natural
constants like $h for Planck's constant, and units like $km for
kilometers8. Column names and parameters can be specified in special table lines. These are described below, see Advanced features. All names must start with a letter, and further consist of letters and numbers.
Remote references
You may also reference constants, fields and ranges from a different table, either in the current file or even in a different file. The syntax is
remote(NAME,REF)
where NAME can be the name of a table in the current file
as set by a #+NAME: line before the table. It can also be the ID of
an entry, even in a different file, and the reference then refers to
the first table in that entry. REF is an absolute field or
range reference as described above for example @3$3 or $somename,
valid in the referenced table.
When NAME has the format @ROW$COLUMN, it is substituted
with the name or ID found in this field of the current table. For
example remote($1, @@>$2) ⇒ remote(year_2013, @@>$1). The format
B3 is not supported because it can not be distinguished from a plain
table name or ID.
Formula syntax for Calc
A formula can be any algebraic expression understood by the Emacs Calc
package. Note that Calc has the non-standard convention that / has
lower precedence than *, so that a/b*c is interpreted as
(a/(b*c)). Before evaluation by calc-eval (see Calling Calc from Your Lisp Programs), variable substitution takes place according to
the rules described above.
The range vectors can be directly fed into the Calc vector functions
like vmean and vsum.
A formula can contain an optional mode string after a semicolon. This
string consists of flags to influence Calc and other modes during
execution. By default, Org uses the standard Calc modes (precision
12, angular units degrees, fraction and symbolic modes off). The
display format, however, has been changed to (float 8) to keep
tables compact. The default settings can be configured using the
variable org-calc-default-modes.
-
p20 - Set the internal Calc calculation precision to 20 digits.
-
n3,s3,e2,f4 - Normal, scientific, engineering or fixed format of the result of Calc passed back to Org. Calc formatting is unlimited in precision as long as the Calc calculation precision is greater.
-
D,R - Degree and radian angle modes of Calc.
-
F,S - Fraction and symbolic modes of Calc.
-
u - Units simplification mode of Calc. Calc is also a symbolic calculator and is capable of working with values having a unit, represented with numerals followed by a unit string in Org table cells. This mode instructs Calc to simplify the units in the computed expression before returning the result.
-
T,t,U - Duration computations in Calc or Lisp, Durations and time values.
-
E -
If and how to consider empty fields. Without
Eempty fields in range references are suppressed so that the Calc vector or Lisp list contains only the non-empty fields. WithEthe empty fields are kept. For empty fields in ranges or empty field references the valuenan(not a number) is used in Calc formulas and the empty string is used for Lisp formulas. AddNto use 0 instead for both formula types. For the value of a field the modeNhas higher precedence thanE. -
N -
Interpret all fields as numbers, use 0 for non-numbers. See the
next section to see how this is essential for computations with Lisp
formulas. In Calc formulas it is used only occasionally because
there number strings are already interpreted as numbers without
N. -
L - Literal, for Lisp formulas only. See the next section.
Unless you use large integer numbers or high-precision calculation and
display for floating point numbers you may alternatively provide
a printf format specifier to reformat the Calc result after it has
been passed back to Org instead of letting Calc already do the
formatting9. A few examples:
$1+$2 |
Sum of first and second field |
$1+$2;%.2f |
Same, format result to two decimals |
exp($2)+exp($1) |
Math functions can be used |
$0;%.1f |
Reformat current cell to 1 decimal |
($3-32)*5/9 |
Degrees F → C conversion |
$c/$1/$cm |
Hz → cm conversion, using constants.el |
tan($1);Dp3s1 |
Compute in degrees, precision 3, display SCI 1 |
sin($1);Dp3%.1e |
Same, but use printf specifier for display |
vmean($2..$7) |
Compute column range mean, using vector function |
vmean($2..$7);EN |
Same, but treat empty fields as 0 |
taylor($3,x=7,2) |
Taylor series of $3, at x=7, second degree |
Calc also contains a complete set of logical operations (see Logical Operations). For example
-
if($1 < 20, teen, string("")) -
"teen"if age$1is less than 20, else the Org table result field is set to empty with the empty string. -
if("$1" ="nan" || "$2" == "nan", string(""), $1 + $2); E f-1= -
Sum of the first two columns. When at least one of the input fields
is empty the Org table result field is set to empty.
Eis required to not convert empty fields to 0.f-1is an optional Calc format string similar to%.1fbut leaves empty results empty. -
if(typeof(vmean($1..$7)) =12, string(""), vmean($1..$7)); E= -
Mean value of a range unless there is any empty field. Every field
in the range that is empty is replaced by
nanwhich letsvmeanresult innan. Thentypeof =12= detects thenanfromvmeanand the Org table result field is set to empty. Use this when the sample set is expected to never have missing values. -
if("$1..$7" ="[]", string(""), vmean($1..$7))= - Mean value of a range with empty fields skipped. Every field in the range that is empty is skipped. When all fields in the range are empty the mean value is not defined and the Org table result field is set to empty. Use this when the sample set can have a variable size.
-
vmean($1..$7); EN - To complete the example before: Mean value of a range with empty fields counting as samples with value 0. Use this only when incomplete sample sets should be padded with 0 to the full size.
You can add your own Calc functions defined in Emacs Lisp with
defmath and use them in formula syntax for Calc.
Emacs Lisp forms as formulas
It is also possible to write a formula in Emacs Lisp. This can be useful for string manipulation and control structures, if Calc's functionality is not enough.
A formula is evaluated as a Lisp form when it starts with a
single-quote followed by an opening parenthesis. Cell table
references are interpolated into the Lisp form before execution. The
evaluation should return either a string or a number. Evaluation
modes and a printf format used to render the returned values can be
specified after a semicolon.
By default, references are interpolated as literal Lisp strings: the field content is replaced in the Lisp form stripped of leading and trailing white space and surrounded in double-quotes. For example:
'(concat $1 $2)
concatenates the content of columns 1 and column 2.
When the N flag is used, all referenced elements are parsed as
numbers and interpolated as Lisp numbers, without quotes. Fields that
cannot be parsed as numbers are interpolated as zeros. For example:
'(+ $1 $2);N
adds columns 1 and 2, equivalent to Calc's $1+$2. Ranges are
inserted as space-separated fields, so they can be embedded in list or
vector syntax. For example:
'(apply '+ '($1..$4));N
computes the sum of columns 1 to 4, like Calc's vsum($1..$4).
When the L flag is used, all fields are interpolated literally: the
cell content is replaced in the Lisp form stripped of leading and
trailing white space and without quotes. If a reference is intended
to be interpreted as a string by the Lisp form, the reference operator
itself should be enclosed in double-quotes, like "$3". The L flag
is useful when strings and numbers are used in the same Lisp form. For
example:
'(substring "$1" $2 $3);L
extracts the part of the string in column 1 between the character positions specified in the integers in column 2 and 3 and it is easier to read than the equivalent:
'(substring $1 (string-to-number $2) (string-to-number $3))
When the formula itself contains ; symbol, Org mode may incorrectly
interpret everything past ; as format specifier:
'(concat $1 ";")
You can put an extra tailing ; to indicate that all the earlier
instances of ; belong to the formula itself:
'(concat $1 ";");
Durations and time values
If you want to compute time values use the T, t, or U flag,
either in Calc formulas or Elisp formulas:
| Task 1 | Task 2 | Total | |---------+----------+----------| | 2:12 | 1:47 | 03:59:00 | | 2:12 | 1:47 | 03:59 | | 3:02:20 | -2:07:00 | 0.92 | #+TBLFM: @2$3=$1+$2;T::@3$3=$1+$2;U::@4$3=$1+$2;t
Input duration values must be of the form HH:MM[:SS], where seconds
are optional. With the T flag, computed durations are displayed as
HH:MM:SS (see the first formula above). With the U flag, seconds
are omitted so that the result is only HH:MM (see second formula
above). Zero-padding of the hours field depends upon the value of the
variable org-table-duration-hour-zero-padding.
With the t flag, computed durations are displayed according to the
value of the option org-table-duration-custom-format, which defaults
to hours and displays the result as a fraction of hours (see the
third formula in the example above).
Negative duration values can be manipulated as well, and integers are considered as seconds in addition and subtraction.
Field and range formulas
To assign a formula to a particular field, type it directly into the
field, preceded by :=, for example vsum(@II..III). When you press
TAB or RET or C-c C-c with point
still in the field, the formula is stored as the formula for this
field, evaluated, and the current field is replaced with the result.
Formulas are stored in a special TBLFM keyword located directly
below the table. If you type the equation in the fourth field of the
third data line in the table, the formula looks like @3$4=$1+$2.
When inserting/deleting/swapping column and rows with the appropriate
commands, absolute references (but not relative ones) in stored
formulas are modified in order to still reference the same field. To
avoid this from happening, in particular in range references, anchor
ranges at the table borders (using @<, @>, $<, $>), or at
hlines using the @I notation. Automatic adaptation of field
references does not happen if you edit the table structure with normal
editing commands—you must fix the formulas yourself.
Instead of typing an equation into the field, you may also use the following command
-
C-u C-c =(org-table-eval-formula)
Install a new formula for the current field. The command prompts
for a formula with default taken from the TBLFM keyword,
applies it to the current field, and stores it.
The left-hand side of a formula can also be a special expression in
order to assign the formula to a number of different fields. There is
no keyboard shortcut to enter such range formulas. To add them, use
the formula editor (see Editing and debugging formulas) or edit
the TBLFM keyword directly.
-
$2= - Column formula, valid for the entire column. This is so common that Org treats these formulas in a special way, see Column formulas.
-
@3= -
Row formula, applies to all fields in the specified row.
@>=means the last row. -
@1$2..@4$3= - Range formula, applies to all fields in the given rectangular range. This can also be used to assign a formula to some but not all fields in a row.
-
$NAME= - Named field, see Advanced features.
Column formulas
When you assign a formula to a simple column reference like $3=, the
same formula is used in all fields of that column, with the following
very convenient exceptions: (i) If the table contains horizontal
separator hlines with rows above and below, everything before the
first such hline is considered part of the table header and is not
modified by column formulas. Therefore a header is mandatory when you
use column formulas and want to add hlines to group rows, like for
example to separate a total row at the bottom from the summand rows
above. (ii) Fields that already get a value from a field/range
formula are left alone by column formulas. These conditions make
column formulas very easy to use.
To assign a formula to a column, type it directly into any field in
the column, preceded by an equal sign, like =$1+$2. When you press
TAB or RET or C-c C-c with point
still in the field, the formula is stored as the formula for the
current column, evaluated and the current field replaced with the
result. If the field contains only =, the previously stored formula
for this column is used. For each column, Org only remembers the most
recently used formula. In the TBLFM keyword, column formulas look
like $4=$1+$2. The left-hand side of a column formula can not be
the name of column, it must be the numeric column reference or $>.
Instead of typing an equation into the field, you may also use the following command:
-
C-c =(org-table-eval-formula)
Install a new formula for the current column and replace current
field with the result of the formula. The command prompts for
a formula, with default taken from the TBLFM keyword, applies it
to the current field and stores it. With a numeric prefix argument,
e.g., C-5 C-c =, the command applies it to that many
consecutive fields in the current column.
Lookup functions
Org has three predefined Emacs Lisp functions for lookups in tables.
-
(org-lookup-first VAL S-LIST R-LIST &optional PREDICATE)
Searches for the first element S in list S-LIST for which
(PREDICATE VAL S)is non-nil; returns the value from the corresponding position in
list R-LIST. The default PREDICATE is
equal. Note that the parameters VAL and S
are passed to PREDICATE in the same order as the
corresponding parameters are in the call to org-lookup-first,
where VAL precedes S-LIST. If
R-LIST is nil, the matching element S of
S-LIST is returned.
-
(org-lookup-last VAL S-LIST R-LIST &optional PREDICATE)
Similar to org-lookup-first above, but searches for the last
element for which PREDICATE is non-nil.
-
(org-lookup-all VAL S-LIST R-LIST &optional PREDICATE)
Similar to org-lookup-first, but searches for all elements for
which PREDICATE is non-nil, and returns all
corresponding values. This function can not be used by itself in
a formula, because it returns a list of values. However, powerful
lookups can be built when this function is combined with other Emacs
Lisp functions.
If the ranges used in these functions contain empty fields, the E
mode for the formula should usually be specified: otherwise empty
fields are not included in S-LIST and/or R-LIST
which can, for example, result in an incorrect mapping from an element
of S-LIST to the corresponding element of
R-LIST.
These three functions can be used to implement associative arrays, count matching cells, rank results, group data, etc. For practical examples see this tutorial on Worg.
Editing and debugging formulas
You can edit individual formulas in the minibuffer or directly in the
field. Org can also prepare a special buffer with all active formulas
of a table. When offering a formula for editing, Org converts
references to the standard format (like B3 or D&) if possible. If
you prefer to only work with the internal format (like @3$2 or
$4), configure the variable org-table-use-standard-references.
-
C-c =orC-u C-c =(org-table-eval-formula)
Edit the formula associated with the current column/field in the minibuffer. See Column formulas, and Field and range formulas.
-
C-u C-u C-c =(org-table-eval-formula)
Re-insert the active formula (either a field formula, or a column
formula) into the current field, so that you can edit it directly in
the field. The advantage over editing in the minibuffer is that you
can use the command C-c ?.
-
C-c ?(org-table-field-info)
While editing a formula in a table field, highlight the field(s) referenced by the reference at point position in the formula.
-
C-c }(org-table-toggle-coordinate-overlays)
Toggle the display of row and column numbers for a table, using
overlays. These are updated each time the table is aligned; you can
force it with C-c C-c.
-
C-c {(org-table-toggle-formula-debugger)
Toggle the formula debugger on and off. See below.
-
C-c '(org-table-edit-formulas)
Edit all formulas for the current table in a special buffer, where the formulas are displayed one per line. If the current field has an active formula, point in the formula editor marks it. While inside the special buffer, Org automatically highlights any field or range reference at point position. You may edit, remove and add formulas, and use the following commands:
-
C-c C-corC-x C-s(org-table-fedit-finish)
Exit the formula editor and store the modified formulas. With
C-u prefix, also apply the new formulas to the
entire table.
-
C-c C-q(org-table-fedit-abort)
Exit the formula editor without installing changes.
-
C-c C-r(org-table-fedit-toggle-ref-type)
Toggle all references in the formula editor between standard (like
B3) and internal (like @3$2).
-
TAB(org-table-fedit-lisp-indent)
Pretty-print or indent Lisp formula at point. When in a line
containing a Lisp formula, format the formula according to Emacs
Lisp rules. Another TAB collapses the formula back
again. In the open formula, TAB re-indents just like
in Emacs Lisp mode.
-
M-TAB(lisp-complete-symbol)
Complete Lisp symbols, just like in Emacs Lisp mode.
-
S-UP,S-DOWN,S-LEFT,S-RIGHT
Shift the reference at point. For example, if the reference is
B3 and you press S-RIGHT, it becomes C3. This also
works for relative references and for hline references.
-
M-S-UP(org-table-fedit-line-up)
Move the test line for column formulas up in the Org buffer.
-
M-S-DOWN(org-table-fedit-line-down)
Move the test line for column formulas down in the Org buffer.
-
M-UP(org-table-fedit-scroll-up)
Scroll up the window displaying the table.
-
M-DOWN(org-table-fedit-scroll-down)
Scroll down the window displaying the table.
-
C-c }
Turn the coordinate grid in the table on and off.
Making a table field blank does not remove the formula associated with
the field, because that is stored in a different line—the TBLFM
keyword line. During the next recalculation, the field will be filled
again. To remove a formula from a field, you have to give an empty
reply when prompted for the formula, or to edit the TBLFM keyword.
You may edit the TBLFM keyword directly and re-apply the changed
equations with C-c C-c in that line or with the normal
recalculation commands in the table.
Using multiple TBLFM lines
You may apply the formula temporarily. This is useful when you want
to switch the formula applied to the table. Place multiple TBLFM
keywords right after the table, and then press C-c C-c on
the formula to apply. Here is an example:
| x | y | |---+---| | 1 | | | 2 | | #+TBLFM: $2=$1*1 #+TBLFM: $2=$1*2
Pressing C-c C-c in the line of #+TBLFM: $2=$1*2 yields:
| x | y | |---+---| | 1 | 2 | | 2 | 4 | #+TBLFM: $2=$1*1 #+TBLFM: $2=$1*2
If you recalculate this table, with C-u C-c *, for example,
you get the following result from applying only the first TBLFM
keyword.
| x | y | |---+---| | 1 | 1 | | 2 | 2 | #+TBLFM: $2=$1*1 #+TBLFM: $2=$1*2
Debugging formulas
When the evaluation of a formula leads to an error, the field content
becomes the string #ERROR. If you want to see what is going on
during variable substitution and calculation in order to find a bug,
turn on formula debugging in the Tbl menu and repeat the calculation,
for example by pressing C-u C-u C-c = RET in a field.
Detailed information are displayed.
Updating the table
Recalculation of a table is normally not automatic, but needs to be triggered by a command. To make recalculation at least semi-automatic, see Advanced features.
In order to recalculate a line of a table or the entire table, use the following commands:
-
C-c *(org-table-recalculate)
Recalculate the current row by first applying the stored column formulas from left to right, and all field/range formulas in the current row.
-
C-u C-c *orC-u C-c C-c
Recompute the entire table, line by line. Any lines before the first hline are left alone, assuming that these are part of the table header.
-
C-u C-u C-c *orC-u C-u C-c C-c(org-table-iterate)
Iterate the table by recomputing it until no further changes occur. This may be necessary if some computed fields use the value of other fields that are computed later in the calculation sequence.
-
M-x org-table-recalculate-buffer-tables
Recompute all tables in the current buffer.
-
M-x org-table-iterate-buffer-tables
Iterate all tables in the current buffer, in order to converge table-to-table dependencies.
Advanced features
If you want the recalculation of fields to happen automatically, or if you want to be able to assign /names/(note: Such names must start with an alphabetic character and use only alphanumeric/underscore characters.) to fields and columns, you need to reserve the first column of the table for special marking characters.
-
C-#(org-table-rotate-recalc-marks)
Rotate the calculation mark in first column through the states #,
*, !, $. When there is an active region, change all marks in
the region.
Here is an example of a table that collects exam results of students and makes use of these features:
|---+---------+--------+--------+--------+-------+------| | | Student | Prob 1 | Prob 2 | Prob 3 | Total | Note | |---+---------+--------+--------+--------+-------+------| | ! | | P1 | P2 | P3 | Tot | | | # | Maximum | 10 | 15 | 25 | 50 | 10.0 | | ^ | | m1 | m2 | m3 | mt | | |---+---------+--------+--------+--------+-------+------| | # | Peter | 10 | 8 | 23 | 41 | 8.2 | | # | Sam | 2 | 4 | 3 | 9 | 1.8 | |---+---------+--------+--------+--------+-------+------| | | Average | | | | 25.0 | | | ^ | | | | | at | | | $ | max=50 | | | | | | |---+---------+--------+--------+--------+-------+------| #+TBLFM: $6=vsum($P1..$P3)::$7=10*$Tot/$max;%.1f::$at=vmean(@-II..@-I);%.1f
Please note that for these special tables, recalculating the table with
C-u C-c *only affects rows that are marked#or*, and fields that have a formula assigned to the field itself. The column formulas are not applied in rows with empty first field.
The marking characters have the following meaning:
-
! -
The fields in this line define names for the columns, so that you
may refer to a column as
$Totinstead of$6. -
^ -
This row defines names for the fields above the row. With such
a definition, any formula in the table may use
$m1to refer to the value10. Also, if you assign a formula to a names field, it is stored as$name = .... -
_ -
Similar to
^, but defines names for the fields in the row below. -
$ -
Fields in this row can define parameters for formulas. For
example, if a field in a
$row containsmax=50, then formulas in this table can refer to the value 50 using$max. Parameters work exactly like constants, only that they can be defined on a per-table basis. -
# -
Fields in this row are automatically recalculated when pressing
TABorRETorS-TABin this row. Also, this row is selected for a global recalculation withC-u C-c *. Unmarked lines are left alone by this command. -
* -
Selects this line for global recalculation with
C-u C-c *, but not for automatic recalculation. Use this when automatic recalculation slows down editing too much. -
/ -
Do not export this line. Useful for lines that contain the
narrowing
<N>markers or column group markers.
Finally, just to whet your appetite for what can be done with the fantastic Calc package, here is a table that computes the Taylor series of degree n at location x for a couple of functions.
|---+-------------+---+-----+--------------------------------------| | | Func | n | x | Result | |---+-------------+---+-----+--------------------------------------| | # | exp(x) | 1 | x | 1 + x | | # | exp(x) | 2 | x | 1 + x + x^2 / 2 | | # | exp(x) | 3 | x | 1 + x + x^2 / 2 + x^3 / 6 | | # | x^2+sqrt(x) | 2 | x=0 | x*(0.5 / 0) + x^2 (2 - 0.25 / 0) / 2 | | # | x^2+sqrt(x) | 2 | x=1 | 2 + 2.5 x - 2.5 + 0.875 (x - 1)^2 | | * | tan(x) | 3 | x | 0.0175 x + 1.77e-6 x^3 | |---+-------------+---+-----+--------------------------------------| #+TBLFM: $5=taylor($2,$4,$3);n3
Org Plot
Org Plot can produce graphs of information stored in Org tables, either graphically or in ASCII art.
Graphical plots using Gnuplot
Org Plot can produce 2D and 3D graphs of information stored in Org
tables using Gnuplot and Gnuplot mode. To see this in action, ensure
that you have both Gnuplot and Gnuplot mode installed on your system,
then call C-c \quot g or M-x org-plot/gnuplot on the
following table.
#+PLOT: title:"Citas" ind:1 deps:(3) type:2d with:histograms set:"yrange [0:]" | Sede | Max cites | H-index | |-----------+-----------+---------| | Chile | 257.72 | 21.39 | | Leeds | 165.77 | 19.68 | | Sao Paolo | 71.00 | 11.50 | | Stockholm | 134.19 | 14.33 | | Morelia | 257.56 | 17.67 |
Org Plot supports a range of plot types, and provides the ability to add more. For example, a radar plot can be generated like so:
#+PLOT: title:"An evaluation of plaintext document formats" transpose:yes type:radar min:0 max:4 | Format | Fine-grained-control | Initial Effort | Syntax simplicity | Editor Support | Integrations | Ease-of-referencing | Versatility | |-------------------+----------------------+----------------+-------------------+----------------+--------------+---------------------+-------------| | Word | 2 | 4 | 4 | 2 | 3 | 2 | 2 | | LaTeX | 4 | 1 | 1 | 3 | 2 | 4 | 3 | | Org Mode | 4 | 2 | 3.5 | 1 | 4 | 4 | 4 | | Markdown | 1 | 3 | 3 | 4 | 3 | 3 | 1 | | Markdown + Pandoc | 2.5 | 2.5 | 2.5 | 3 | 3 | 3 | 2 |
Notice that Org Plot is smart enough to apply the table's headers as
labels. Further control over the labels, type, content, and
appearance of plots can be exercised through the PLOT keyword
preceding a table. See below for a complete list of Org Plot options.
For more information and examples see the Org Plot tutorial.
Plot options
-
set - Specify any Gnuplot option to be set when graphing.
-
title - Specify the title of the plot.
-
ind -
Specify which column of the table to use as the
xaxis. -
timeind -
Specify which column of the table to use as the
xaxis as a time value. -
deps -
Specify the columns to graph as a Lisp style list, surrounded by
parentheses and separated by spaces for example
dep:(3 4)to graph the third and fourth columns. Defaults to graphing all other columns aside from theindcolumn. -
transpose -
When
y,yes, ortattempt to transpose the table data before plotting. Also recognizes the shorthand optiontrans. -
type -
Specify the type of the plot, by default one of
2d,3d,radar, orgrid. Available types can be customized withorg-plot/preset-plot-types. -
with -
Specify a
withoption to be inserted for every column being plotted, e.g.,lines,points,boxes,impulses. Defaults tolines. -
file -
If you want to plot to a file, specify
"path/to/desired/output-file". -
labels -
List of labels to be used for the
deps. Defaults to the column headers if they exist. -
line - Specify an entire line to be inserted in the Gnuplot script.
-
map -
When plotting
3dorgridtypes, set this totto graph a flat mapping rather than a3dslope. -
min -
Provides a minimum axis value that may be used by a plot type.
Implicitly assumes the
yaxis is being referred to. Can explicitly provide a value for a either thexoryaxis withxminandymin. -
max -
Provides a maximum axis value that may be used by a plot type.
Implicitly assumes the
yaxis is being referred to. Can explicitly provide a value for a either thexoryaxis withxmaxandymax. -
ticks -
Provides a desired number of axis ticks to display, that may be used
by a plot type. If none is given a plot type that requires ticks
will use
org--plot/sensible-tick-numto try to determine a good value. -
timefmt -
Specify format of Org mode timestamps as they will be parsed by
Gnuplot. Defaults to
%Y-%m-%d-%H:%M:%S. -
script -
If you want total control, you can specify a script file—place the
file name between double-quotes—which will be used to plot.
Before plotting, every instance of
$datafilein the specified script will be replaced with the path to the generated data file. Note: even if you set this option, you may still want to specify the plot type, as that can impact the content of the data file.
ASCII bar plots
While point is on a column, typing C-c " a or M-x orgtbl-ascii-plot create a new column containing an ASCII-art bars
plot. The plot is implemented through a regular column formula. When
the source column changes, the bar plot may be updated by refreshing
the table, for example typing C-u C-c *.
| Sede | Max cites | | |---------------+-----------+--------------| | Chile | 257.72 | WWWWWWWWWWWW | | Leeds | 165.77 | WWWWWWWh | | Sao Paolo | 71.00 | WWW; | | Stockholm | 134.19 | WWWWWW: | | Morelia | 257.56 | WWWWWWWWWWWH | | Rochefourchat | 0.00 | | #+TBLFM: $3='(orgtbl-ascii-draw $2 0.0 257.72 12)
The formula is an Elisp call.
Draw an ASCII bar in a table.
VALUE is the value to plot.
MIN is the value displayed as an empty bar. MAX
is the value filling all the WIDTH. Sources values outside
this range are displayed as too small or too large.
WIDTH is the number of characters of the bar plot. It
defaults to 12.
Footnotes
-
7
Org understands references typed by the user as
B4, but it does not use this syntax when offering a formula for editing. You can customize this behavior using the variableorg-table-use-standard-references.Backrefs: 1
-
8
The file
constants.elcan supply the values of constants in two different unit systems,SIandcgs. Which one is used depends on the value of the variableconstants-unit-system. You can use theSTARTUPoptionsconstSIandconstcgsto set this value for the current buffer.Backrefs: 1
-
9
The printf reformatting is limited in precision because the value passed to it is converted into an "integer" or "double". The "integer" is limited in size by truncating the signed value to 32 bits. The "double" is limited in precision to 64 bits overall which leaves approximately 16 significant decimal digits.
Backrefs: 1