Format cells to display different types of data

You can format a table cell with a data type that determines how the data in the cell appears, and how the data is recognized by functions that refer to that cell.

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 is more precise than what you want to show. The actual value entered is always used in calculations, regardless of how many decimal places appear in the cell, except where a formula refers to text in a cell, in which case 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 automatically add a dollar sign ($) to all cells by formatting them as currency.

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 a range of cells.

  2. In the Cell pane of the Format inspector, choose an option from the Data Format pop-up menu.

    • Number: Displays standard number formatting.

    • Fraction: Displays numbers with a numerator and a denominator. Choose an option from the Accuracy pop-up menu to specify the maximum number of digits you want displayed, or to round the value to the nearest half, quarter, and so on.

    • Scientific: Displays numbers in scientific notation. In the Decimals field, type the number of decimal places you want displayed, or choose Auto to display as many decimal places as you type.

  3. You can adjust the number formatting in the following ways:

    • Set the number of decimal places: In the Decimals field, type the number of decimal places you want to display. Numbers rounds the display value instead of truncating the display value. For example, if your value is “5.75” and you set the number of decimal places to zero, Numbers displays “6.”

    • Display as many decimal places as you type in each cell: Delete the number in the Decimals field, or click the Decimals down arrow until you reach the Auto setting.

    • Specify how negative values appear: Choose an option from the pop-up menu to the right of the Decimals field.

    • Show the thousands separator: Select the Thousands Separator checkbox.

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 a cell or a range of cells.

  2. In the Cell pane of the Format inspector, choose Currency from the Data Format pop-up menu.

  3. Do any of the following:

    • Set the number of decimal places: In the Decimals field, type the number of decimal places you want to display. Numbers rounds the display value instead of truncating the display value. For example, if your value is “5.75” and you set the number of decimal places to zero, Numbers displays “6.”

    • Display as many decimal places as you type in each cell: Delete the number in the Decimals field, or click the Decimals down arrow until you reach the Auto setting.

    • Specify how negative values appear: Choose an option from the pop-up menu to the right of the Decimals field.

    • Show the thousands separator: Select the Thousands Separator checkbox.

    • Use accounting-style negative numbers: Select the Accounting Style checkbox to display negative values within parentheses.

  4. Choose a currency symbol, such as US Dollar ($), from the Currency pop-up menu.

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 a cell or a range of cells.

  2. In the Cell pane of the Format inspector, choose Percentage from the Data Format pop-up menu.

  3. Do any of the following:

    • Set the number of decimal places: In the Decimals field, type the number of decimal places you want to display. Numbers rounds the display value instead of truncating the display value. For example, if your value is “5.75” and you set the number of decimal places to zero, Numbers displays “6.”

    • Display as many decimal places as you type in each cell: Delete the number in the Decimals field, or click the Decimals down arrow until you reach the Auto setting.

    • Specify how negative values appear: Choose an option from the pop-up menu to the right of the Decimals field.

    • Show the thousands separator: Select the Thousands Separator checkbox.

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

  1. Select a cell or a range of cells.

  2. In the Cell pane of the Format inspector, choose “Date & Time” from the Data Format pop-up menu.

  3. Choose a format from the Date pop-up menu.

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

  4. Choose a format from the Time pop-up menu.

    If you choose None, no time is displayed in the cell, even if a time is entered and used in date and time 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.

The Smart Cell View at the bottom of the window shows you the actual value of both the date and the time for a selected cell, even if you choose to show only one in the table.

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 a cell or a range of cells.

  2. In the Cell pane of the Format inspector, choose Duration from the Data Format pop-up menu.

  3. Do one of the following:

    • Show or hide time unit labels: Click Automatic Units, then choose an option from the Style pop-up menu:

      • 0: Hides all time unit labels.

      • 0w: Displays time unit labels as abbreviations—for example, “m” for minutes.

      • 0 weeks: Displays time unit labels fully spelled out.

    • Make all duration cells display the same units: Click Custom Units, then click units on the right or left 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 enter data in the cells, the data automatically adjusts to the new duration format you set.

Format cells automatically

By default, Numbers formats table cells automatically.

If you change the data format for a cell, you can always revert to the automatic format.

  • Select a cell or a range of cells, then in the Cell pane of the Format inspector, choose Automatic from the Data Format pop-up menu.