4.5.1 Controlling How Numeric Data Display in the Worksheet Column

In addition to naming and setting the Plot Designation of a column, you can specify the Format (Text & Numeric, Numeric, etc.) and Data Type (i.e. Numeric data are stored as "double", "real", etc.). By default, the column's Format is set to handle both Text & Numeric data, but when system resources are limited, this can be restricted to Numeric only. Additionally, by specifying Data Type = Numeric , you can further reduce the amount of memory that is allocated for numeric data storage (see Caution, below).

Numeric vs. Text & Numeric

The Text & Numeric format uses approximately 20% more memory than type Numeric Double(8). In most cases this difference will not cause problems with machine performance. If you are concerned about maximizing system resources, you can configure Origin to use Numeric Double(8) for all columns in new worksheets:

  1. From the menu, select Tools: Options.
  2. Click the Miscellaneous tab.
  3. Select Use Numeric as Preferred Column Type.

In addition, if you are applying custom formats to a column of numeric data, you should change the column Format from Text & Numeric to Numeric. While Text & Numeric generally supports Origin's custom display formatting, there are quirks and setting Format to Numeric eliminates some issues.

Numeric Data Types and memory allocation

When you are working with numeric data, you can adjust the amount of storage allocated to each data value by changing the Data Type setting in the Column Properties dialog box. For instance, if you needed to save even more memory (upwards of an additional 50%), you could change columns to Format = Numeric with a Data Type of Real(4) (see the note of caution, below):

To open the Column Properties dialog box:

  1. Double-click on the column heading.

or

  1. Right-click on a selected column and choose Properties.

or

  1. With the column selected, choose Format: Column from the Origin menu.
    then
  2. In the Column Properties dialog, choose Format = Numeric.
  3. Select from the Data Type drop-down list.
Workbook Bytes Range of Values

double

8

±1.7E±308 (15 digits)

real

4

±3.4E±38 (7 digits)

short

2

32,768 to 32,767

long

4

2,147,483,648 to 2,147,483,647

char

1

-128 to 127

byte

1

0 to 255

ushort

2

0 to 65,535

ulong

4

0 to 4,294,967,295

complex

16

±1.7E±308 (15 digits), each 8 bytes

CAUTION: Data Types of less than Double(8) may cause problems with various math operations. For the same reason, you should verify the column Data Type before you enter or import data into a worksheet column. If the Data Type is not appropriate to the data, you risk losing precision in your numbers.

Format and Numeric Display

When the worksheet column is set to Format = Numeric or Text & Numeric, the number of displayed digits is determined by a combination of these settings in the Column Properties dialog box:

  • Display. Specify decimal, scientific, or engineering formats or a custom format.
  • Digits. This is the number of decimal places or significant digits to display in the column (dataset).

If not specified (i.e. Numeric Display is set to Default Decimal Digits), Origin uses the global Number of Digits setting on the Numeric Format tab of the Options dialog box.

Note that the Digits settings in the Column Properties dialog box take precedence over the global setting in the Options dialog box.


To set the number of default decimal digits (global except as specified in the next procedure):

  1. From the menu, choose Tools: Options.
  2. Click the Numeric Format tab.
  3. Set the number of Number of Digits.


To set the number of significant digits or decimal places for one or more datasets in a worksheet:

  1. With the worksheet active, select Format: Column, or double-click on the worksheet column heading. This opens the Column Properties dialog box.
  2. Change Digits from Default Decimal Digits to Set Decimal Places = or Significant Digits =, and enter a number in the text box below (max Decimal Places = 12; max Significant Digits = 15).
  3. Optionally, select Apply to All Columns to the Right.

A Note about Displayed vs. Actual Values

When you import or type your data into a worksheet, Origin uses the number of digits/decimal places settings to display the data in each cell. Note that this is merely a visual representation of the data set. The actual data values are stored, and when you perform calculations, it is the actual data values -- consistent with the limits of the Data Type and amount of allocated memory -- that are used, not the values displayed in the cell.

Decimal, Scientific and Engineering Notation

Origin supports display of worksheet data in Decimal:1000, Scientific:1E3, Engineering:1k, and Decimal:1,000 formats. If you select Decimal:1000, Engineering:1k, or Decimal:1,000, Origin uses additional controls (the Upper Threshold Power and the Lower Threshold Power settings on the Numeric Format tab of the Options dialog box) for numeric display.

By default, the Upper Threshold Power and the Lower Threshold Power settings are 6 and -3, respectively. This means that for values in the range of 1x10-3 to 1x106, data will display using the format specified in the Display drop-down list (Options group) in the Column Properties dialog box. For values that exceed either the lower or upper conversion thresholds (less than 1x10-3 or greater than 1x106), data will display in scientific notation, regardless of the setting in the Display drop-down list.


To change the default threshold power controls:

  1. Select Tools: Options.
  2. Click the Numeric Format tab.
  3. In the Convert to Scientific Notation group, set the Upper Threshold Power and Lower Threshold Power.