Custom Formats of Numeric Display in a Worksheet or a Graph

Table of Contents:
0. Introduction
1. Column Properties dialog and Standard Display Formats
2. Varieties of Custom Display Formats
・  2.1  Digits Control
・  2.2  %, π and Other Units as Suffix/Prefix
・  2.3  Fraction Expression
・  2.4  Degree(°) Angle with Minutes(‘) and Seconds(“)
・  2.5  Hexadecimal Expression
3. Other Places for Custom Display Formats
・  3.1  In a Worksheet Cell
・  3.2  In a Graph

0. Introduction

In Origin, numerical values can be displayed in a worksheet or a graph in various formats. Though the internal values (implemented as “double”) may be the same, showing it is just a matter of “display”, for example either in decimal, or in scientific notation with an exponent. In Origin 2019, the repertoire of the numeric display formats has been significantly expanded to enhance the visual effectiveness and convenience. In this blog, we introduce these varieties of formats in the worksheet column properties with simple examples. Then, at the end of this article, we will touch the custom formats in other places like in a graph.

Note 1:The examples in this article are stored in this sample file.

Note 2: The custom formats, introduced newly in the coming Origin 2019 version are: 1) Suffix with π and other units, 2) Fraction of π, 3) Improved DMS – Degree angle with minutes(‘) and Seconds(“), and 4) Hexadecimal numbers.

1. Column Properties dialog and Standard Display Formats

To see or change the current display format of a column, you can double-click the column header (like A(X), B(Y), etc.) to show the “Column Properties” dialog box (above). Here, in the dropdown of “Display“, you see 4 standard display formats: “Decimal: 1000“, “Scientific: 1E3“, “Engineering: 1K“, and “Decimal: 1,000“.

The first (default) format, “Decimal: 1000” is the decimal format simply with a decimal point. “Scientific: 1E3” is a notation for the floating number with mantissa (before E) and exponent (after E). “Engineering: 1K” is an expression with a “metric prefix” such as kilo(K), mega(M), milli(m), micro(μ). The last “Decimal: 1,000” is same to “1000”, but with thousand separators(,) (in the US style) for thousands, millions, billions, etc.

Note 1: In the above example, the value 0.00003456 is expressed in the scientific notation, 3.456E-5 because it goes below the threshold which was set in the “Numeric Format” tab in Options dialog (“Tools> Options” menu).

Note 2: In the graph, for the tick labels, there is an additional standard format, “Scientific: 10^3“, a number with an exponent as a superscript like 1.234×10³  – another scientific format in addition to “1E3” to display 1.234E+03.

2. Varieties of Custom Display Formats

In the “Column Properties” dialog box, if you choose “Custom” at the dropdown of “Display“, you see many samples of custom display formats. These samples of custom formats are divided into the following four four categories:
1) Digits Control
2) %, π and Other Units as Suffix/Prefix
3) Fraction
4) Degree Angle with Minutes and Seconds
5) Hexadecimal Numbers

We explain each format category below.

Note: The list of custom formats and their formal descriptions can be found in this page.

2.1 Digits Control

You can control the digits in terms of number of digits as well as having the leading zeros.

Format Sample	Meaning	Example of Data and its Display
.2	Fixed number of decimal places	12.123456789 ⇒ 12.12 (Variation:  Adding * at the end of format eliminates the trailing 0’s. For example, with .2 , 12.1 ⇒ 12.10 , and with .2* , 12.1 ⇒ 12.1)
*3	Specific number of significant digits	23.45678 ⇒ 23.4
S.3	Scientific notation (in E±<Exponent>) format with number of fraction digits	23.456789 ⇒ 2.346E+01
P*3	Scientific notation (in x10exponent format) with number of significant digits	23.456789 ⇒ 2.35×101 NOTE: In the worksheet, the cell must be set as “Rich Text” to show the superscripted form. Otherwise, it shows the raw string like 2.35×10∖+(1) .
E.3	Engineering notation with number of fraction digits	234567.12345678 ⇒ 2.346M
#5	Integer with minimum number of digits (may introduce leading 0’s)	23.456789 ⇒ 00012

2.2 %, π and Other Units as Suffix/Prefix

Not only having a unit in “Unit” header row of the column, each cell can also show the unit by the display format as a suffix or a prefix. In addition, “%” and “π” are special suffixes which show the scaled values.

Format Sample	Meaning	Example of Data and its Display
#.0%	“%” is a special keyword unlike other units (like km). The display is scaled by 1/100. If no decimal places, use “#%“, if there are two decimal places, use “#.00%” .	0.123 ⇒ 12.3%
#/# “pi”	“pi” is a special keyword unlike other units (like km). The display is scaled by π, and #/# expresses in fraction.	1.0472 (=π*60/180) ⇒ π/3 (Note: If you double-click the cell, it shows 0.33333 for editing.)
#/4 “pi”	“pi” is a special keyword unlike other units. The display is scaled by π/4, and #/4 expresses as <n>π/4 with rounding.	2.18166 (=π*135/180) ⇒ 3π/4 (Note: If you double-click the cell, it shows 0.75 for editing.)
* “pi”	“pi” is a special keyword unlike other units. The display is scaled by π.	0.78540 (=π*45/180) ⇒ 0.25π (Note: If you double-click the cell, it shows 0.25 for editing.)
.3″km”	Ordinary unit like km in this example can be simply attached as a suffix. No scaling occurs.	123.4567 ⇒ 123.457km
“$” .2	Ordinary unit like $ in this example can be simply attached as a prefix. No scaling occurs.	3.45 ⇒ $ 3.40

2.3 Fraction Expression

Format Sample	Meaning	Example of Data and its Display
##/##	Fraction format which allows “improper fraction” form (i.e., the numerator may be larger than the denominator ).	0.5 ⇒ 1/2 123.456 ⇒ 7037/57
# ##/##	Fraction format in “mixed fraction” (with an integer and a “true fraction”) format	0.5 ⇒ 1/2 123.456 ⇒ 123 26/57
# #/12	Fraction format in “mixed fraction” format scaled by 1/12 Note: This expression is useful for the number in an Imperial System. For example, the 123.456 feet in length is 123 feet 5 inches.	0.5 ⇒ 6/12 123.456 ⇒ 123 5/12

2.4 Degree(°) Angle with Minutes(‘) and Seconds(“)

Format Sample	Meaning	Example of Data and its Display
DMS	Angle in Degrees(°) Minutes(‘) and Seconds(“)	36.34255 ⇒ 37° 20’33”
DM	Angle in Degrees(°) and Minutes(‘)	36.34255 ⇒ 37° 21′
DMS2	Angle in Degrees(°) Minutes(‘) and Seconds(“). Seconds have 2 decimal places.	36.34255 ⇒ 37° 20’33.18″
DM3	Angle in Degrees(°) and Minutes(‘). Minutes have 3 decimal places.	36.34255 ⇒ 37° 20.553′
D MS	DMS, but degrees and the rest (minutes and seconds) are separated by a space.	36.34255 ⇒ 37°  20′  33″
DMF	The last character “F” displays the angle value without symbols ( °  ‘  ” ), but inserting spaces as separators.	36.34255 ⇒ 37  20 33
DM NS	Indication of positive or negative value by the directional “N“(for North) or “S“(for South) affixes, respectively.  NS is a shorthand of NSA where “A” means “After” for a suffix. Use NSB, to attach as a prefix (“B” means “Before”).	36.34255  ⇒ 36°  21′ N －36.34255 ⇒ 36°  21′ S
DM EWB	Indication of positive or negative value by the directional “E”(for East) or “W”(for West) affixes, respectively. EW is a shorthand of EWA where “A” means “After” for a suffix. Use ESB, to attach as a prefix (“B” means “Before”).	36.34255  ⇒  E36°  21′ －36.34255 ⇒ W36°  21′

2.5 Hexadecimal Expression

Following the notation in C language, Origin now supports the hexadecimal formats of numbers, which let the user operate bitwise directly without converting to a string. (In older versions,  the string denoted as a hexadecimal number must be converted to numeric by Hex() function, then convert back to hexadecimal by e.g., Dec2Hex()$ function like in Excel.)

Format Sample	Meaning	Example of Display (when data is 16565705703691)
%x	32-bit storage (max 8 hexdigits) with no leading 0’s. The “x” displays hex characters in lower case, a-f.	101010b
%#x	32-bit storage (max 8 hexdigits). The “#” indicates to attach “0x” prefix.	0x101010b
%I64X	“I64X” indicates to use 64-bit storage (max 13 hexdigits) with no leading 0’s, and “X” indicates hex characters in upper case, A-F.	F110101010B
%08X	32-bit storage (max 8 hexdigits) with leading 0’s. “08” indicates 8 total width.	0101010B
%#015I64x	64-bit storage (max 13 hexdigits) with leading 0’s. “015” indicates the 15 total width (including 0X part), with “0X” prefix.	0x00f110101010b

Note: Origin now supports big integers which at most 52 bits (13 hexdigits, the fraction part of 64-bit double), and their bitwise operations by the Bitwise operators(&(AND) 、 |(OR)) and the Bitwise functions: BitAnd(), BitOr(), BitXor(), BitLShift(), and BitRShift()
Example:
For two 44-bit binaries, i=hex(F110101010B);  and   j=hex(F2010101012);
Dec2hex(bitAnd(i,j),12)$=;     //returns => 0F0000000002
Dec2hex(bitOr(i,j),12)$=;        //returns => 0F311111111B
Dec2hex(bitXor(i,j),12)$=;      //returns => 003111111119
Dec2hex(bitLShift(i,3),12)$=; //returns => 788808080858
Dec2hex(bitRShift(i,3),12)$=; //returns => 01E220202021

You can download a sample Origin file to demonstrate these bitwise formats and operations from this link.

3. Other Places for Custom Display Formats

The custom formats described in the previous section can be used not only for the worksheet column, but also in a specific worksheet cell, or in a graph:

3.1 In a Worksheet Cell

You can set the display format of a specific cell by right-clicking the cell, and choosing “Format Cells…” flyout. In the example below, the cell C1 has the custom display format, “.2″m+(2)” to attach  an ordinary unit suffix, square meter(m²).

Notice that to accommodate the superscript of “2”, the cell is specified as Rich Text, and the part is specified with the escape sequence of the superscript ( +(….) ). (For the details of the escape sequences, see here.)

3.2 In a Graph

There are various places to put the custom display format in a graph. For example, please look at the graph below:

This sample graph contains a scatter plot overlaid on a contour plot, and there are four kinds of labels with custom formats: a) Annotation labels, b) Data Info display, c) Axis tick labels, d) Text labels, and e) Labels on color scale. The notations of formatting elements of these labels are essentially the same for the worksheet column. Here are brief explanations where you can enter the formats.
(This sample project can be downloaded from here.)

a) Annotation Labels:

One data point in the scatter plot has an annotation label (“42.2 ft”) specified the number of decimals and unit suffix. When you select the Annotation tool, and double-click the point, you get the default annotation label with the coordinates. Double-click the label to show the Annotation dialog, and you can edit the label notation with the format – in this example, the Z value of the point is shown with 2 decimal places and the unit(“ft”) by the format: $(z,.1 “ft”) .

b) Data Point Tooltip/Data Info. window:

Data point tooltip/Data Info is interactively displayed by simply mousing over the data point, or using the Data Reader/Cursor tool. In either case, the display is highly customizable, and you can use the Custom Formart. In this sample, X and Y are formatted by DM EW 、 DM NS, respectively. The Z(Height) has the format, .1 “ft” .

c) Axis tick labels:

Here, both X and Y axes have tick labels with “D M EW” and “D M NS” custom format, respectively. To specify, you can double-click an axis to show the Axis dialog, choose “Tick Labels” tab, choose “Custom” at the Display option, then you can enter “D M EW” for X axis, and “D M NS” for Y axis at the Custom Format field.

d) Text Labels

The above sample has a text label “Factor = 68.3%”, and this percent value is a reference to a worksheet cell with “#.0%” format. To make such a label, first copy the worksheet cell (CTRL-C), and during the in-place editing of the text label, you can right-click, and choose “Paste Link” flyout. You can change the default format (“W”) in the %-substitution to any custom format. In this example, the default label (which means the 1st cell in the column D in Sheet1 in Book1 with standard format W):
Factor = %([Book1]Sheet1,@WL,D[1], W)
was modified to the following:
Factor = %([Book1]Sheet1,@WL,D[1], #.0%)

Note: The details of the %-substitution is explained here.

e) Labels on Color Scale

If you double-click the color scale on the graph, it opens the Color Scale Control dialog, then choose “Labels” section in the left panel, you see the “Custom Format” option when you choose “Custom” at Display. In this example, 3″ft” was entered in the Custom Format option to control the significant digits (3) and the attachment of the unit(ft).