Number Formatting in Excel: A How-To Guide

Excel’s number formatting lets you change how numbers appear in cells without altering the underlying values. You can display currency, percentages, dates, phone numbers, and more, making your data clearer and more professional. This guide covers the essential formatting options, custom formats, and common pitfalls.

What You’ll Need

• Microsoft Excel (2010 or later, including Microsoft 365)
• A spreadsheet with numeric data you want to format
• Basic familiarity with selecting cells and navigating the Ribbon

Step-by-Step

1. Select the cells you want to format. You can click and drag to select a range, click a column/row header to select an entire column/row, or press Ctrl+A to select the whole sheet.
2. Open the Format Cells dialog by right-clicking the selected cells and choosing “Format Cells…” (or press Ctrl+1). This dialog gives you access to all formatting options.
3. Choose a category from the left list: Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, or Special. For most basic needs, “Number” is the starting point.
4. Set decimal places and thousands separator (for Number and Currency). For example, to show two decimals and a comma separator, set “Decimal places” to 2 and check “Use 1000 Separator (,).”
5. Apply a currency symbol by selecting “Currency” or “Accounting”. Currency places the symbol right before the number (e.g., $1,234.56), while Accounting aligns currency symbols and decimals in a column.
6. Format percentages by selecting “Percentage” and setting decimal places. Excel multiplies the cell value by 100 and appends a % sign. So typing 0.25 becomes 25%.
7. Format dates and times by selecting “Date” or “Time” and picking a locale and format from the list. For example, “3/14/2021” or “14-Mar-21”.
8. Use the Quick Format buttons on the Home tab for common formats: the $ (Currency), % (Percent), and , (Comma) buttons apply one-click formatting. Use the increase/decrease decimal buttons to change precision.
9. Create a custom format by selecting “Custom” in the Format Cells dialog and typing a format code. For example, to show numbers as “1.2K” for thousands, use the code #,##0.0,"K". Custom codes give you full control over how numbers appear.
10. Apply conditional formatting (optional) to automatically format cells based on their values. Select the cells, go to Home → Conditional Formatting, and choose rules like “Greater Than” or “Data Bars”. This is not number formatting per se, but it changes appearance dynamically.

Common Mistakes

• Formatting numbers as text: If you type an apostrophe before a number (e.g., ‘123) or format cells as Text, Excel treats it as text. This breaks calculations and sorting. Always use Number formatting for numeric values.
• Confusing display with value: Formatting only changes how a number looks; the underlying value stays the same. If you format 0.1 as 10%, the cell still contains 0.1, not 10. This can cause confusion in formulas.
• Using Currency when you need Accounting: For financial reports, Accounting aligns decimals and makes negative numbers clearer. Currency is fine for simple displays.
• Overusing decimal places: Showing too many decimals can clutter a sheet. Use 2 decimals for most financial data, and fewer for large numbers.
• Forgetting to apply formatting to new data: If you add rows later, they may not inherit the formatting. Use Excel Tables (Ctrl+T) or copy formatting with Format Painter.

Tips

• Use keyboard shortcut Ctrl+Shift+1 for Number format with two decimals and thousands separator.
• Custom formats can include text and colors. For example, [Green]#,##0;[Red]-#,##0 displays positive numbers in green and negatives in red.
• Use the “General” format to reset a cell to default. It shows numbers as entered, without extra formatting.
• Format dates as you type: If you type “3/14/2021”, Excel auto-detects and formats it as a date. You can then change the format via Format Cells.
• Use “Special” formats for phone numbers and ZIP codes. These add dashes and parentheses automatically, e.g., (555) 123-4567.
• Copy formatting with Format Painter: Select a formatted cell, click the brush icon on the Home tab, then click the target cells to apply the same format.

Troubleshooting

• Numbers display as #####: This means the column is too narrow. Double-click the column header boundary to auto-fit, or increase column width manually.
• Formulas show results as text: Check if the cell is formatted as Text. Change it to Number and re-enter the formula (F2, then Enter).
• Percentages show as 0.25 instead of 25%: The cell is not formatted as Percentage. Select the cells and apply the Percent style.
• Dates appear as serial numbers (e.g., 44258): This happens when the cell is formatted as Number. Apply a Date format to display it as a date.
• Custom format doesn’t work as expected: Check the syntax. For example, to show a literal comma, you must enclose it in quotes: #,##0,", ". Also, ensure you’re using the correct locale (e.g., decimal comma in some European countries).
• Formatting disappears after sorting: Sorting can mix formatted and unformatted cells. To avoid this, apply formatting to the entire column before sorting, or use a Table.

Frequently Asked Questions

How do I format numbers as text in Excel?

Select the cells, go to Format Cells (Ctrl+1), choose “Text” under Category, and click OK. Alternatively, type an apostrophe before the number. Note that text-formatted numbers won’t work in calculations.

Can I change the default number format for the whole workbook?

Yes, but it’s not a single setting. You can change the default font and some number formats by going to File → Options → Advanced and adjusting “When calculating this workbook”. For a consistent look, apply a custom format to cells or use a template.

Why does Excel show 2.50 instead of 2.5?

That’s because the cell is formatted with two decimal places. To show only one decimal, select the cells, go to Format Cells, and set “Decimal places” to 1.

How do I format negative numbers in parentheses?

In the Format Cells dialog, under Category “Number” or “Currency”, choose the option that shows negatives in parentheses, e.g., (1234.10).

What is the difference between Currency and Accounting formats?

Currency places the currency symbol immediately before the number and allows negative numbers to be shown with a minus sign. Accounting aligns the currency symbol at the left edge of the cell and the decimal points vertically, making it ideal for financial statements.

Can I format a cell to show both a number and text?

Yes, using a custom format. For example, the code 0" units" displays 5 as “5 units”. The text in quotes is literal and does not affect the underlying value.

Related

• FaceTime Portrait Mode iOS 15: Step-by-Step Guide
• Laptop Battery Calibration: Accurate Power Guide
• Troubleshoot Slow Linux Server: Step-by-Step Fixes