4.5.4 Custom Numeric Formats

All data stored in the data area of the worksheet is treated as either text or numeric. Display of text strings is fairly straightforward. However, as the following image illustrates, the same numeric data can be made to display in any number of ways, depending upon your needs. This page covers much of what you need to know about custom display of numeric data.


ColProperties Custom Formats.png

When the column Format = Numeric, and Display = Custom, you can use the Custom Display notations described below, to apply a custom format to numeric data in the worksheet column.


To open the '''Column Properties''' dialog box:

  • Double-click on the worksheet column heading.
  • Select the column, then right-click choose Properties.
  • Select a column and from the menu, choose Format: Column.


For more information, see worksheet column Properties; or press F1 when the Properties dialog is open.

Significant Digits, Decimal Places, Scientific and Engineering Formats

There are global settings (the Options dialog box) and column level settings (Column Properties) for display of Decimal Digits, Significant Digits, and Scientific and Engineering Notation. For information, see this topic.

When further customization of these display formats is needed, Origin supports Custom Display formatting using the Column Properties dialog box.

Some examples display an exponent. Display of exponents requires that Rich Text be turned on in the worksheet cell(s) (e.g. 1x2\+(32) ⇒ 1 X 232).

Format Description Example
*n Display n significant digits 71.46378651763725 using *571.464
.n Display n decimal places 71.46378651763725 using .571.46379
*n* Display n significant digits, truncating trailing zeros 30.95010 using *5*30.95
.n, Display thousand separator for the integer portion and specify n decimal places 12345.67890 using .2,12,345.68
P*n Display n significant digits in scientific notation of the form 10^3 12345.67890 using P*51.2346x104
S*n Display n significant digits in scientific notation of the form 1E3 12345.67890 using S*51.2346E+04
S.n[#] Display n decimal places, in scientific notation of the form 1E3. The optional pound symbol "#" (square brackets indicate an optional flag) modifies the notation to display only the coefficient. 12345.67890 using S.51.23457E+04
E*n Display n significant digits, in engineering format (e.g. 1.000k) 12345.67890 using E*512.346k
E.n Display n decimal places, in engineering format 12345.67890 using E.512.34568k
##+## Display a number as two or more digits, a "+" separator, then two digits (e.g. surveying stations). 12345.67890 using ##+##123+46
#+##M Display a number as number, a "+" separator, then two digits, plus suffix "M". 12345.67890 using #+##M123+46M
2.n Display n decimal places, in power of 2 format 4294967296 using 2.32 ⇒ 1x232
L.n Display n decimal places, in power of e format 1096.6331584282 using L.21.00xe7

Fractions

Format Description Example
# ##/## Display a number as proper fraction. The first # indicates the integer part of the mixed number and the rest part after space indicates the remainder. In the reminder part, a forward slash / separates numerator and denominator. The numbers of # in both can be varied, indicating the number of digits of remainder. For example, # #/# displays remainder with up to 1 digit, # ##/## displays remainder with up to 2 digits, and so on. 71.4637865 using # ##/##71 32/69
# #/n Round and display a number as a fraction with specified n as denominator. The numerator and denominator are separated by a forward slash /. The number of digits of numerator is adjusted accordingly. 71.4637865 using # #/871 4/8

Percentages

Format Description Example
#% Display a number as a percentage. 0.714637865176373 using #%71%
#[.00]% Display a number as percentage of 100 followed by the percent sign "%". #% displays percentage as integer, while #.0% displays percentage as 1 decimal place and #.00% displays 2 decimal places. Note that the square brackets in the format indicate an optional argument; they are not entered in your expression. 0.714637865176373 using #.0%71.5%

pi

The quantity pi (π) is treated specially in the Origin worksheet. Once you have set the Column Properties Custom Display list to one of the pi options, you can enter multipliers directly (e.g. 1,2,3, etc.) and Origin will convert the display to pi units (e.g. π, 2π, 3π, etc.).

Format Description Example
* "pi"

Display a number as a decimal, followed by the symbol π.

Note that converting this value back to a simple Text & Numeric or Numeric format, displays a scaled decimal quantity (e.g. "2π" becomes "6.28319...").

1 using * "pi"0.31831π
#/4 "pi"

Display a number as π divided by "4", with rounding.

Note: If you click into the cell containing "3π/4", the cell displays the a decimal in π units ("0.75") in edit mode; but converting this column back to a simple Text & Numeric or Numeric format displays a scaled decimal quantity ("2.35619...").

1 using #/4 "pi"π/4

#/# "pi"

Display a number as a fraction of π. See the note in the cell above.

1 using #/# "pi"π/3

Other Prefix and Suffix Units

Format Description Example
"$"* Display a number with a dollar sign ($) prefixed. 71.4637865176 using "$".2$71.46
*n "km" Display a number to n significant digits, with the "km" (kilometers) suffixed. 71.4637865176 using *3 "km"71.5 km

Origin's syntax for custom formats can sometimes be generalized. For instance, to display a number as dollars + cents (e.g. $2.50), you combine a "$" prefix with the ".n" notation from the first table to limit decimal display to 2 (i.e. "$.2").

Degrees, Minutes, Seconds

Common expressions of geographic coordinates are supported, such as degrees-minutes-seconds, degrees + decimal minutes, and decimal degrees. Some experimentation can be useful for fine-tuning data display. For instance, you can display data as DMS or D<space>MS.

Format Description Example
DMS Display a number in Degrees° Minutes' Seconds", where 1 degree = 60 minutes, and 1 minute = 60 seconds. 42.3251 using DMS42°19'30"
DMF Display a number in Degrees Minutes Seconds, without unit symbols. 42.3251 using DMF42 19 30
D MDn EW (longitude)
D MDn NS (latitude)
Display a number in Degrees and Decimal Minutes. Parameter n specifies decimal places. Positive values will have "E" or "N" appended, Negative values will have "W" or "S" appended. To preserve negative values do not append "EW" or "NS".

42.3251 using D MD3 NS42° 19.506 N
-72.6412 using D MD3 EW72° 38.472 W
-72.6412 using D MD3-72° 38.472

D MDn EWB (longitude)
D MDn NSB (latitude)
Display a number in Degrees and Decimal Minutes. Parameter n specifies decimal places. Letter "B" ("before") specifies that positive values should have "E" or "N" prefixed, negative values will have "W" or "S" prefixed. To preserve negative values do not append "EWB" or "NSB".

42.3251 using D MD3 NSBN 42° 19.506
-72.6412 using D MD3 EWBW 72° 38.472
-72.6412 using D MD3-72° 38.472

Dn EW (longitude)
Dn NS(latitude)
Display a number in Decimal Degrees. Parameter n specifies decimal places. Positive values will have "E" or "N" appended, Negative values will have "W" or "S" appended.

42.3251 using D3 NS42.325° N
-72.6412 using D3 EW72.641° W


Note: To display simply "42.3251" or "-72.6412", set
Format = Numeric
Display = Decimal:1000.

Date and Time

For Custom Display of date-time data, see Formatting Date and Time Data.

Hexadecimal Numbers

Beginning with Origin 2019, the worksheet supports display of hexadecimal numbers using Custom Display formatting. For more information, see Hexadecimal Numbers in Origin.