Be able to format numeric data appropriately including to display the number of decimal places, different currency symbols, percentages

Topic 20: Spreadsheets

Objective

Be able to format numeric data appropriately, including displaying the number of decimal places, applying different currency symbols, and formatting numbers as percentages.

Key Concepts

• Cell formatting controls the visual representation of data without altering the underlying value.
• Decimal places determine how many digits appear after the decimal point.
• Currency formatting adds a symbol and may adjust decimal places automatically.
• Percentage formatting multiplies the cell value by 100 and appends a % sign.
• Custom number formats allow the creation of user‑defined patterns.

Formatting Decimal Places

1. Select the cells containing numeric data.
2. Open the Format Cells dialog (usually via right‑click or the toolbar).
3. Choose the Number category.
4. Set the Decimal places field to the desired number (e.g., 2 for \$1.23\$).
5. Click OK to apply.

Example: A value of 3.14159 formatted to 2 decimal places displays as 3.14.

Applying Currency Symbols

1. Select the cells to format.
2. In the Format Cells dialog, choose Currency or Accounting.
3. Pick the appropriate symbol (e.g., $ for US dollars, £ for British pounds).
4. Adjust decimal places if needed.
5. Confirm with OK.

Result: A value of 1234.5 becomes $1,234.50 when formatted as US currency.

Formatting as Percentages

1. Select the numeric cells.
2. Open Format Cells and choose Percentage.
3. Set the desired decimal places.
4. Confirm with OK.

Note: A stored value of 0.256 will display as 25.60% when formatted to two decimal places.

Custom Number Formats

Custom formats allow you to combine elements, e.g., displaying negative numbers in red or adding text labels.

Practical Exercise

1. Enter the following values into cells A1–A4: 1234.567, 0.256, 9876.543, -45.678.
2. Format A1 and A3 as US currency with two decimal places.
3. Format A2 as a percentage with one decimal place.
4. Format A4 as a negative number in red using a custom format.
5. Verify that the underlying values remain unchanged.

Assessment Questions

• What is the displayed value of 0.075 when formatted as a percentage with three decimal places?
• How would you format a cell to show a value of 1234 as £1,234.00?
• Explain the difference between the Number and Accounting categories for currency formatting.