You need to display numbers with units like kg or $ in Excel, but typing them directly breaks your calculations. Adding text to a cell containing a number makes Excel treat the entire cell as text, which stops formulas like SUM from working. This article explains how to format numbers to show symbols without changing the underlying value. You will learn to use custom number formats to keep your data functional for math.
Key Takeaways: Add Units and Currency Without Breaking Math
- Custom Number Format: Appends text like ” kg” or ” units” to a number while preserving its value for formulas.
- Accounting Number Format: Inserts a currency symbol aligned to the left of the cell, leaving the number for calculations.
- Format Cells Dialog (Ctrl+1): The main interface for creating and applying custom formats to your data.
Understanding Number Formatting in Excel
Excel stores two things for a cell: its actual value and its display format. The value is the raw number used in all calculations. The format controls how that number appears on your screen. When you type “10 kg” into a cell, Excel sees text, not a number. Any formula referencing that cell will ignore it or return an error.
Number formatting solves this by adding visual text without altering the stored value. For example, you can format the number 10 to display as “10 kg”. A SUM formula will still use the value 10. This method works for units of measurement, currency symbols, and descriptive text. The only prerequisite is that your data must start as proper numbers before you apply the format.
Steps to Add Units Using Custom Number Formatting
The most flexible method is creating a custom number format. This allows you to add any text, control decimal places, and handle negative numbers.
- Select your cells
Click and drag to highlight the cells containing the numbers you want to format. - Open the Format Cells dialog
Press Ctrl+1 on your keyboard. You can also right-click the selected cells and choose Format Cells. - Go to the Number tab
In the dialog box, ensure the Number tab at the top is selected. - Choose the Custom category
In the Category list on the left, scroll down and click Custom. - Build your custom format
In the Type field, you will see the current format code. To add a unit, type your desired number format followed by your text in double quotes. For example, to show numbers with ” kg”, type0.0 "kg". The “0.0” part shows one decimal place. - Apply the format
Click OK. Your numbers will now display with the unit, but formulas will still calculate using the original values.
Using Built-in Currency and Accounting Formats
For currency, use the built-in formats. They are designed to keep numbers calculable.
- Select your number cells
Highlight the cells containing financial data. - Apply the format from the ribbon
Go to the Home tab on the ribbon. In the Number group, click the Accounting Number Format button ($). This adds your local currency symbol. - Change the currency symbol
Click the small arrow next to the Accounting Number Format button. A dropdown list shows other major currencies like € or £. Select one to apply it.
Common Mistakes and Formatting Limits
Excel Still Treats Cell as Text After Formatting
If your numbers were originally entered with units (like “10 kg”), they are already text. Formatting will not convert them. First, you must convert text to numbers. Remove the unit text from the cells so only the number remains. Then apply the custom number format.
Custom Format Disappears When Pasting Data
Pasting values from another source often brings its own plain format, overwriting your custom one. To keep your format, use Paste Special. Copy your data, right-click the destination, choose Paste Special, and then select Values and Number Formats.
Units Don’t Align Properly in the Column
Custom formats place the text immediately after the number, which can look messy. For cleaner columns, consider using the Accounting format’s alignment for currency. For other units, you might add a helper column with a formula like =A1 & " kg" for reporting, while keeping the original number column for calculations.
Custom Format vs. Accounting Format: Key Differences
| Item | Custom Number Format | Accounting Number Format |
|---|---|---|
| Primary Use | Adding custom units, text, or specific number patterns | Displaying monetary values with a fixed currency symbol |
| Symbol/Text Placement | Appears directly after the number | Symbol is fixed at the left edge of the cell |
| Alignment | Number and text are aligned together | Currency symbol left-aligned, number right-aligned |
| Handling Zero Values | Displays as 0 or as defined in format code | Typically displays as a dash (-) |
| Flexibility | High – you define the exact display rules | Low – follows preset accounting standards |
You can now display numbers with units or currency in Excel while keeping them fully functional for formulas. Use the Format Cells dialog for custom units like meters or kilograms. Use the Accounting format on the Home tab for standard financial reporting. For advanced control, explore custom format codes to conditionally color negative numbers or add text only if a value exceeds a certain threshold.