Numbers for iPad: Format Numbers table cells to display different types of data

Format Numbers table cells to display different types of data

You can format a table cell with a specific data format (for example, number, currency, percentage) that determines how data in the cell appears, and how it’s used in calculations.

You can also choose how many decimal places appear in cells that contain numbers, currency units, or percentage values, even if the exact value entered in the cell has more decimal places than you want to show. The actual value entered is always the value used in calculations, regardless of how many decimal places appear in the cell. If a formula refers to text in a cell, the displayed value is used in the calculation.

You can change a cell’s format even if you already typed content in the cell. For example, if you have a table that shows your monthly budget, you can have a currency symbol (for example, a dollar sign $) added to all cells automatically by formatting the cells as currency, then selecting the symbol you want.

Format cells automatically

By default, Numbers formats table cells automatically, so letters and numbers are formatted and appear the way you type them. If you change the data format for a cell, you can always revert back to the automatic format.

  1. Select a cell or a range of cells, then tap the Format button.

  2. Tap Format, then tap Automatic.

Numbers

By default, cells formatted as numbers display as many decimal places as you type in them. You can change this setting so cells formatted as numbers display the same number of decimal places.

Changes to decimal settings apply to both numbers and percentages. For example, if you change a cell with a number into a percentage, the number of decimal places displayed doesn’t change.

  1. Select a cell or range of cells you want to format, tap the Format button, then tap Format.

  2. Tap the Info button to the right of Number.

  3. Tap a format (Number, Scientific, or Fraction).

    Tip: Tap the E key on the numeric keypad to enter numbers in scientific E-notation (for example, 1E+04).

  4. Set the number of decimal places and other display options.

Currency (units of monetary value)

By default, cells formatted as currency display two decimal places. You can change this setting so cells display as many decimal places as you type in them, or so all cells display the same number of decimal places.

  1. Select the cell or range of cells you want to format, tap the Format button, then tap Format.

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

  3. To modify how many decimal places show, do one of the following:

    • Display as many decimal places as you type in each cell: Tap the Adjustable button next to Decimals until you reach the Auto setting.

    • Increase or decrease the number of decimal places displayed: Tap the Adjustable button next to Decimals.

  4. To show the thousands separator, turn on Thousands Separator.

  5. To display negative values within parentheses, turn on Accounting Style; to select a different display style, tap a red or black option.

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

Percentages

By default, cells formatted as a percentage display as many decimal places as you type in them. You can change this setting so all cells display the same number of decimal places.

Changes to decimal settings apply to both percentages and numbers in a selected range of cells. For example, if you change the format of a cell from a percentage to a decimal, the number of decimal places displayed doesn’t change.

  1. Select the cell or range of cells you want to format, tap the Format button, then tap Format.

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

  3. Do one of the following:

    • Display as many decimal places as you type in each cell: Tap the Adjustable button next to Decimals until you reach the Auto setting.

    • Increase or decrease the number of decimal places displayed: Tap the Adjustable button next to Decimals.

  4. To show the thousands separator, turn on Thousands Separator.

  5. To select how negative values are displayed, tap a red or black option.

If you format a cell that already contains a value, the value is assumed to be a decimal, and it’s converted into a percentage. For example, 3 becomes 300%.

If a percentage value is used in a formula, its decimal number version is used. For example, a value that displays as 3% is used as 0.03 in a formula.

Date and time

Numbers automatically interprets text strings that you type in a recognized date format as dates (for example, 6/10/17).

  1. Select the cell or range of cells you want to format, tap the Format button, then tap Format.

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

  3. Tap the options that match the display formats you want for date and time.

    If you tap None, no date or time is displayed in the cell, even if a date or time was entered and used in calculations.

If you don’t enter both a date and a time, Numbers adds a default value for you. For example, if you type “1:15 PM,” Numbers adds today’s date by default.

Tip: Numbers has a keyboard for quickly editing date and time. To open it, tap a cell that contains date and time data, then tap the Show Keyboard button, or tap the Date and Time Keyboard button on the keyboard then tap Date & Time.

Durations (units of time)

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

  1. Select the cell or range of cells you want to format, tap the Format button, then tap Format.

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

  3. To show or hide time unit labels, tap to select an option below 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, turn off Automatic Units.

    A range selector appears.

    The controls for formatting cells for durations of time.
  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).

Tip: Numbers has a keyboard for quickly editing duration. To open it, tap a cell that contains duration data, then tap the Show Keyboard button, or tap the Date and Time Keyboard button on the keyboard then tap Duration.

If you change the duration format after you enter data in the cells, the data automatically adjusts to the new duration format you set.

Published Date: Jun 28, 2019
Helpful?