Previous Topic
Data Format
The Data Format dialog helps you to specify the data format for a chart in the Inspector panel. This dialog appears when you select 
and options in the dialog vary according to different sources it is opened from.
When you open the dialog to specify the data format for the category axis (the X axis) in a chart, it consists of two tabs: Major Label and Minor Label.
OK
Applies the settings and closes the dialog.
Cancel
Cancels the settings and closes the dialog.
Displays the help document about this feature.
Ignores the setting and closes this dialog.
Major Label
Specifies the data format of the major tick mark labels on the axis.
To specify a format, first select a category from the Category box, then select the required format of this category from the Format box. The selected format will be displayed in the Properties text box. You can further edit the format if you want. Then, select the Add button to add the format to the Stack box. Repeat the steps to specify formats for other categories. Only one format can be defined for each category.
Lists the category types and formats available for each category.
| Category | Format | Description (Sample) |
|---|---|---|
| Scale | Divides the value by hundreds, thousands, and so on. | |
| Logarithm | Calculates ten to the power of the value. | |
| Hundreds | Divides the value by one hundred. | |
| Thousands | Divides the value by one thousand. | |
| Millions | Divides the value by one million. | |
| Billions | Divides the value by one billion. | |
| Trillions | Divides the value by one trillion. | |
| Number | Re-formats the number value (original example: 123456) | |
| 0 | Formats a decimal number to an integer (123456). | |
| #,##0 | Formats a decimal number to a digit grouped integer (123,456). | |
| #,##0;-#,##0 | Formats a decimal number to a digit grouped integer. A minus sign is used as the negative prefix (123,456/-123,456). | |
| 0.00 | Formats a decimal number to a fixed-point number retaining 2 digits after decimal separator (123456.00). | |
| #,##0.00 | Formats a decimal number to a digit grouped number retaining 2 digits after decimal separator (123,456.00). | |
| #,##0.00;-#,##0.00 | Formats a decimal number to a digit grouped number retaining 2 digits after decimal separator. A minus sign is used as the negative prefix (123,456.00/-123,456.00). | |
| 0.00E00 | Formats a decimal number to a number in scientific notation. The mantissa is often in the range 1.0 <= x < 10.0, and the number of digit characters after the exponent character (E) gives the minimum exponent digit count (1.23E04). | |
| ##0.0E0 | Formats a decimal number to a number in scientific notation. The minimum number of integer in the mantissa is 1, and the number of digit characters after the exponent character (E) gives the minimum exponent digit count (12.34E3). | |
| $0 | Format a decimal number to an integer, prefixed with a currency symbol $ ($123456). | |
| $#,##0 | Formats a decimal number to a digit grouped integer, prefixed with a currency symbol $ ($123,456). | |
| $#,##0;-$#,##0 | Formats a decimal number to a digit grouped integer, prefixed with a currency symbol $. A minus sign is used as the negative prefix ($123,456/-$123,456). | |
| $0.00 | Formats a decimal number to a fixed-point number retaining 2 digits after decimal separator, prefixed with a currency symbol $ ($123456.00). | |
| $#,##0.00 | Formats a decimal number to a digit grouped number retaining 2 digits after decimal separator, prefixed with a currency symbol $ ($123,456.00). | |
$#,##0.00; | Formats a decimal number to a digit grouped number retaining 2 digits after decimal separator, prefixed with a currency symbol $. A minus sign is used as the negative prefix ($123,456.00/-$123,456.00). | |
| $0.00E00 | Formats a decimal number to a number in scientific notation, prefixed with a currency symbol $. The mantissa is often in the range 1.0 <= x < 10.0, and the number of digit characters after the exponent character (E) gives the minimum exponent digit count ($1.23E$04). | |
| $##0.0E0 | Formats a decimal number to a number in scientific notation, prefixed with a currency symbol $. The minimum number of integer in the mantissa is 1, and the number of digit characters after the exponent character (E) gives the minimum exponent digit count ($12.34E$3). | |
| 0% | Formats a decimal number to percentage: multiplied by 100 and shown as an integer percentage (12%). | |
| 0.00% | Formats a decimal number to percentage: multiplied by 100 and shown as a fixed-point number percentage retaining 2 digits after decimal separator (12.34%). | |
Notes:
| ||
| Date/Time | Re-formats the date/time value (For example, Wednesday, December 25, 00:00:00 GMT-08:00 2002) | |
| G | Formats a date to era designator (AD). | |
| yyyy | Formats a date to year (2002). | |
| yy | Formats a date to year in short form (02). | |
| yyyy G | Formats a date to year, tagged with era designator (2002 AD) | |
| MM | Formats a date to month in year, shown in number format (12). | |
| MMM | Formats a date to month in year, shown in the abbreviated form (Dec). | |
| MMMMMMMM | Formats a date to month in year, shown in the full form (December). | |
| dd/MM/yy | Formats a date to a simple date form (25/12/02). | |
| dd-MMM-yy | Formats a date to a simple date form (25-Dec-02). | |
| dd-MMM | Formats a date to a simple date form (25-Dec). | |
| MMM-yy | Formats a date to a simple date form (Dec-02). | |
| MMM yyyy | Formats a date to a simple date form (Dec 2002). | |
| dd | Formats a date to day in month (25). | |
| DDD | Formats a date to day in year (359). | |
| ww | Formats a date to week in year (52). | |
| W | Formats a date to week in month (4). | |
| EEE | Formats a date to day in week, in short form (Wed). | |
| EEEEEE | Formats a date to day in week (Wednesday). | |
| HH | Formats a date to hour in day, 0~23 (00). | |
| kk | Formats a date to hour in day, 1~24 (24). | |
| KK a | Formats a date to hour in am/pm, 0~11, with am/pm marker (00 AM) | |
| hh a | Formats a date to hour in am/pm, 1~12, with am/pm marker (12 AM) | |
| mm | Formats a date to minute in hour (00). | |
| hh:mm a | Formats a date to hour in am/pm, 1~12, minutes in an hour, with am/pm marker (12:00 AM). | |
| ss | Formats a date to second in minute (00). | |
| hh:mm:ss | Formats a date to hour in am/pm 1~12, minutes in an hour, and second in minute (12:00:00). | |
| hh:mm:ss a | Formats a date to hour in am/pm 1~12, minutes in an hour, and second in minute, with am/pm marker (12:00:00 AM). | |
| mm:ss | Formats a date to minute in hour and second in minute (00:00). | |
| MMMMM dd yyyy G (EEEEEE) hh:mm:ss aa z | Formats a date to a full form date, which contains month in year (shown in full form), days in a month, year, era designator, day in week (shown in the full form), hour in a day, 1~12, minutes in an hour, second in minute, am/pm marker, and time zone (December 25 2002 AD (Wednesday) 12:00:00 AM GMT-08:00). | |
Notes:
| ||
| Text | Specifies the length of the value. | |
| Default Length | Uses the length of the string Default Length, which is 14, as the length of the string. Any letters that exceed this length will be cut. You can modify this string, adding or deleting letters to increase or decrease the length limit. For example, type Teddy, the label displayed may probably be Wed D, which originally could be Wed December 25 2002. If you want to show all, you can type Teddy is a lovely bear!, where the length is longer than the length of string Wed December 25 2002. | |
| Mapping | Maps new value to one or more values. | |
| One-to-one Mapping | Maps a new value to one value. Type the argument number that you want to replace, and then specify the new value in the Map to box. For example, if you want to replace the second data label on the X axis, which is Thu Jul 04 2002, with a new string US National Day, just type 2 in the Argument# box, type US National Day in the Map to box, and then select Add to add it to the Filter Stack. If you want to map more values, repeat this procedure. | |
| Range Mapping | Maps a new value to a range of values. Type the argument numbers to define the range you want to map together, and then specify the new value in the Map to box. For example, if you want to replace the data labels from the second one to the fifth with a new name My Holiday, just type 2 in the first Argument# box and 5 in the second Argument# box, then type My Holiday in the Map to box, and finally select Add to add it to the Stack. If you want to map ranges that are not consecutive, repeat this procedure. | |
| Text Mapping | Changes the data in the axis data label to a customized string. For example, if you want to change Chen in the first data label of the X axis to Jinfonet, type Chen in the Original Text box, type Jinfonet in the Map to box, and then select Add to add it to the Stack. You can only change the data one by one. Make sure that your spelling in the Original Text box is exactly the same as the data shown in the axis label. | |
| Prefix | Adds a string before the text in the data label of the axis. For example, if you want to add NA before the text in the data label of the X axis, type NA in the Map to box, and select Add to add it to the Stack. | |
| Suffix | Adds a string behind the text in the data label of the axis. For example, if you want to add NA behind the text in the data label of the X axis, type NA in the Map to box, and select Add to add it to the Stack. | |
Note: The data labels along each axis count from 0, which means the number of the first data label you see on the axis will be 0, the second will be 1, the third will be 2, and so on. | ||
Properties
Specifies the properties for the selected format.
Specifies whether to automatically scale the tick mark labels that are of the Number data type when the label values fall into the two ranges:
- When 1000 <= value < 10^15, the following quantity unit symbols of the International System of Units are used to scale the values: K (10^3), M (10^6), G (10^9), and T (10^12).
- When 0 < value < 0.001 or value >= 10^15, scientific notation is used to scale the values.
By default it is set to auto which means that the setting follows that of the chart. When it is true, the specified format applies to the integer part of the values after scaled, but if the specified format conflicts with Auto Scale in Number, for example, the values are displayed in percentage, then the Auto Scale in Number setting is ignored.
Sample
Displays the selected format effects.
Stack
Lists all the formats that you selected from different categories.
Add
Adds a format to the Stack box.
Remove
Removes a format from the Stack box.
Apply
Applies the specified format to values on the chart.
Minor Label
Specifies the data format of the minor tick mark labels on the axis.
To specify a format, first select a category from the Category box, then select the required format of this category from the Format box. The selected format will be displayed in the Properties text box. You can further edit the format if you want. Then, select the Add button to add the format to the Stack box. You can also check Correlate with Major Label to specify that the data format of the minor tick mark labels correlates with that of the major tick mark labels. Repeat the steps to specify formats for other categories. Only one format can be defined for each category.
Correlate with Major Label
If checked, the data format of the minor tick mark labels will correlate with that of the major tick mark labels automatically. Only when it is unchecked can the format properties of the minor tick mark labels take effect.
Lists the category types and formats available for each category.
Properties
Specifies the properties for the selected format.
Auto Scale in Number
Specifies whether to automatically scale big and small numbers in the tick mark labels.
Sample
Displays the selected format effects.
Stack
Lists all the formats that you selected from different categories.
Add
Adds a format to the Stack box.
Remove
Removes a format from the Stack box.
Apply
Applies the specified format to values on the chart.
When you open the dialog to specify the data format for objects other than the category axis (the X axis) in a chart, the options in the dialog are as follows.
To specify a format, first select a category from the Category box, then select the required format of this category from the Format box. The selected format will be displayed in the Properties text box. You can further edit the format if you want. Then, select the Add button to add the format to the Stack box. Repeat the steps to specify formats for other categories. Only one format can be defined for each category.
Lists the category types and formats available for each category.
Properties
Specifies the properties for the selected format.
Auto Scale in Number
Specifies whether to automatically scale big and small numbers. Available when the chart object can be bound with a data field.
Sample
Displays the selected format effects.
Stack
Lists all the formats that you selected from different categories.
Add
Adds a format to the Stack box.
Remove
Removes a format from the Stack box.
Apply
Applies the specified format to values on the chart.
OK
Applies the changes and closes the dialog.
Cancel
Does not retain any changes and closes the dialog.
Displays the help document about this feature.
Ignores the setting and closes this dialog.