Languages

Numbers for iOS (iPad): Format cells to display different types of data

Format cells to display different types of data

Formatting a table cell with a data type lets you specify how you want the data that you’ve entered into that cell to be displayed, and how the data is recognized by functions that refer to that cell. For example, you can format a cell to display British pounds (£). Or you can format a cell to display a date and time, which can be used in functions that make calculations based on when an activity starts or finishes.

For consistency and ease of reading your spreadsheet, you can also specify how many decimal places are displayed within each cell that contains a number, currency unit, or percentage, even if the exact value entered into the cell is more precise than what you want to show. The exact value entered is always used in calculations, regardless of how many decimal places are displayed, except where a formula refers to text in a cell, in which case the displayed value is used in the calculation.

When you tap into a formatted cell to edit its contents, the correct keyboard for its data type automatically appears.

You can set or change a cell’s format even if you’ve already typed content into the cell. For example, you might type a series of calendar dates into a column using the text keyboard, and later select the column and change all the cells to display the date format you want.

Format cells as numbers:

By default, the number of decimals for Number is set to Auto, meaning that each cell displays as many decimal places as you type into it. But you can change this so that all cells formatted as numbers display the same number of decimal places.

Changes to decimal settings apply to both numbers and percentages within a selected range of cells. For example, if you change a cell with a number into a percentage, the decimal places displayed do not change.

  1. Select a cell or a range of cells, tap Info button in the toolbar, and then tap Format.

  2. Tap More Info button to the right of Number, and then tap a style of number formatting at the top of the Number Options window:

    • Number
      displays standard number formatting. You can adjust the formatting in the following ways:
      • To increase or decrease the number of decimal places displayed in the selected cells, tap the up or down Decimals arrows.

      • To show or hide the thousands separator, slide Separator to ON or OFF.

      • To specify how you want negative values to appear, tap a red or black option at the bottom of the window.

    • Scientific
      displays numbers in scientific notation. To limit the number of decimal places that appear, tap the Decimals arrows to specify the number of decimals.
    • Fraction
      displays numbers with a numerator and a denominator, instead of decimals. Tap an option to specify the maximum number of digits you want the cell to display, or to round the value to the nearest half, nearest quarter, and so on.

Format cells as currency (units of monetary value):

By default, the number of decimals for Currency is set to two, meaning that two decimal places are displayed in cells with the Currency format. You can change this setting so that the cell displays as many decimal places as you type into it, or another set number of decimal places.

  1. Select a cell or a range of cells, tap Info button in the toolbar, and then tap Format.

  2. Tap More Info button to the right of Currency.

  3. To change the number of decimal places displayed, do either of the following:

    • To display as many decimal places as you type into each cell, tap the Decimals down arrow until you reach the Auto setting.

    • To increase or decrease the set number of decimal places displayed, tap the Decimals arrows.

  4. To show or hide the thousands separator, slide Separator to ON or OFF.

  5. To display negative values within parentheses, slide Accounting Style to ON. Or tap a red or black option at the bottom of the window to select a different display style.

  6. To select a currency symbol, tap Currency, and then tap the symbol you want.

Format cells as percentages:

By default, the number of decimals for Percentage is set to Auto, meaning that each cell displays as many decimal places as you type into it. But you can change this to display the same number of decimal places in all cells formatted as percentages.

Changes to decimal settings apply to both percentages and numbers within a selected range of cells. For example, if you change a cell with a percentage into a decimal, the decimal places displayed do not change.

  1. Select a cell or a range of cells, tap Info button in the toolbar, and then tap Format.

  2. Tap More Info button to the right of Percentage.

  3. To increase or decrease the number of decimal places displayed in the selected cells, tap the Decimals up or down arrow.

  4. To show or hide the thousands separator, slide Separator to ON or OFF.

  5. To specify how you want negative values to appear, tap a red or black option at the bottom of the window.

Format cells as date and time:

  1. Select a cell or a range of cells, tap Info button in the toolbar, and then tap Format.

  2. Tap More Info button to the right of Date & Time.

  3. In the Date & Time Options window, tap the options that match the Date display format and the Time display format that you want. If you tap None under Date, no date is displayed in the cell, even if a date is entered and used in date and time calculations. If you tap None under Time, the time of day is not displayed in the cell, even if a time is entered and used in date and time calculations.

    The date and time formats that are available depend on the International settings you’ve selected in your device’s Settings.

Format cells as durations (units of time):

By default, cells containing duration data are automatically formatted to display all the time units that you enter. You can change this setting so that duration cells consistently display only certain units of time (for example, only hours, not minutes, seconds, or milliseconds), even though more precise duration values have been entered into the cell and are used in formulas that make calculations based on units of time.

  1. Select a cell or a range of cells, tap Info button in the toolbar, and then tap Format.

  2. Tap More Info button to the right of Duration.

  3. To show or hide time unit labels, tap to select an option under Format:

    • None
      hides all time unit labels.
    • Short
      displays time unit labels as abbreviations, for example, “m” for minutes.
    • Long
      displays time unit labels fully spelled out.
  4. To make all duration cells display the same kinds of units, slide Automatic Units to OFF in the Duration Options window.

    A range selector appears.

  5. Drag the left or right end of the duration range selector to encompass the scope of the time duration you want to use, from weeks (Wk) to milliseconds (Ms).

If you change the duration format after you’ve entered data into the cells, the data automatically adjusts to the new duration format you set.

If you want to define or limit the range of allowed values that can be entered into a table cell, you can create a cell control. To learn more about cell controls, see Add sliders, steppers, pop-up menus, checkboxes, and star ratings in cells.

Last Modified: Sep 26, 2012
Helpful?
Yes
No
  • Last Modified: Sep 26, 2012
  • Article: PH3373
  • Views:

    9381
  • Rating:
    • 20.0

    (1 Responses)

Additional Product Support Information