How to Use Microsoft Excel Custom Number Formats

When entering data into a spreadsheet, you often need to include units of measurement alongside your numbers—for example, typing “50 kg” or “100 km”. However, if you type letters into a cell alongside numbers, Microsoft Excel immediately classifies that cell as text, not data.

This creates a major problem. If you try to use the =SUM() function to add “50 kg” and “100 kg” together, Excel will return an error because it cannot perform mathematics on text strings. To solve this, you must use Custom Number Formats, a feature that allows you to visually display units (like “kg”) while secretly keeping the underlying cell data as pure, calculable numbers.

How to Create a Custom Number Format

Instead of typing the letters directly into the cell, you will tell Excel to automatically append the letters to whatever number you type.

1. Highlight the cells, column, or row where you plan to enter your numerical data.
2. Right-click the highlighted area and select Format Cells… from the context menu (or press Ctrl + 1 on your keyboard).
3. The Format Cells dialogue box will appear. Ensure you are on the Number tab.
4. In the “Category” list on the left side, click on Custom.
5. Look at the right side of the window. You will see a text box labelled Type:. This is where you will write your custom formatting code.

Writing the Custom Formatting Code

The code consists of two parts: the number placeholder and the text you want to display.

• The symbol 0 represents a required number. (If you type 5, Excel displays 5).
• The symbol # represents an optional number, suppressing unnecessary zeros.
• Any text you want to append must be wrapped inside double quotation marks (e.g., " kg").

If you want your cells to display whole numbers followed by “kg”, click inside the Type: text box and enter the following code:

0 "kg"

Note: Notice the space before the “k”. This ensures the output looks like “50 kg” instead of “50kg”.

If you want to display decimals (e.g., “50.5 kg”), you would use:

0.00 "kg"

Once you have entered your code, click OK.

Using Your New Format

Now, test your new formatting. Click on one of the cells you highlighted earlier and simply type the number 50. Do not type the letters “kg”. Press Enter.

Excel will automatically transform the display to read “50 kg”. However, if you look at the formula bar at the top of the screen, you will see that the cell’s actual value remains simply 50.

Because the underlying data is purely numeric, you can now freely use the =SUM() or =AVERAGE() functions on these cells, and Excel will perform the mathematics flawlessly.