Number Formatting in Excel

What is Number Formatting

Number formatting in Excel refers to changing the visual presentation of numeric values in cells. While the actual values remain the same, the format of how those numbers appear changes. You can format cell(s) by using formatting options like Number, Currency, dates, or percentages, etc., available in the Excel Ribbon or by using Format Cells.

• For example, you can make 1000 appear as $1,000.00, 100000%, or 9/26/1902, depending on the chosen format.

Why Use Number Formatting in Excel?

When it comes to data analysis or manipulation, one of the most useful features of Excel is number formatting. By formatting numbers appropriately, you can:

• Make data more consistent and easier to analyze.
• Number formatting plays a huge role in making worksheets more readable, whether you’re creating reports, percentage-based analyses, or timelines.

In this tutorial, we’ll dive into essential types of number formatting in Excel: Number, Currency, Percentage, and Date formatting.

General Format

This is the default format for all cells; it displays numbers as typed in cell(s) without any additional symbols or formatting.

Apply Number Formatting using the Ribbon

• Select the cell(s) you want to format.
• Go to Home → Number group.
• Click the dropdown 🡳 to select the number format.
• Click on Number.

Apply Number Formatting using Format Cells

• Right-click the selected cells and choose Format Cells or Press Ctrl + 1 to open the Format Cells.
• Choose the Number tab.
• Select the category Number and customize settings like decimal places, use 1000 separator (,).
• Click OK to apply the formatting.
• The sample area will display how the chosen format Type will appear.

Currency Formatting in Excel

Currency formatting automatically adds the currency symbol, decimal places, and thousands separators. It aligns numbers properly for easy comparison and improves clarity in financial reports.

Currency Formatting from Excel Ribbon:

• Select the cell(s) you want to format.
• Go to Home → Number group
• Click the dropdown 🡳 to select the Currency Format.
• Click on Currency or
• Use the keyboard shortcut Ctrl+Shift+$ to apply Currency Formatting

Currency Formatting from Format Cells:

• Select the cells with the numbers you want to format.
• Right-click and choose Format Cells, or press Ctrl+1 to open the Format Cells dialog box.
• In the Format Cells, navigate to the Number tab.
• Select Currency from the list of categories.
• You can adjust the number of decimal places and choose the desired currency symbol (e.g., $, €, £).
• Click OK to apply the formatting.
• The sample area will display how the chosen format Type will appear.

Percentage Formatting in Excel

• Percentage formatting is used to represent values as percentages, often for data that involves rates, ratios or completion percentages etc.
• It multiplies the number by 100 and adds a percentage sign (%) next to the number.
• For Example: A cell with number 12 when converted to Percent Formatting will appear as 1200.00%

Percentage Formatting from Excel Ribbon:

• Select the cells you want to apply the Percentage.
• Click Home → Number group.
• Click the dropdown 🡳 to select the Percentage Format.
• Click on Percentage or
• Use the keyboard shortcut Ctrl+Shift+% to apply Percent Formatting

Format Numbers as Percentages from Format Cells:

• Select the cells you wish to format as percentages.
• Right-click and choose Format Cells, or press Ctrl+1 to open the Format Cells dialog box.
• Go to the Number tab and select Percentage from categories.
• Choose the number of decimal places you want to display.
• Click OK to apply the formatting.
• The sample area will display how the chosen format Type will appear.

Date Formatting in Excel

Date formatting is essential for organizing time data. It enhances timelines and project tracking.

Date Formatting from Excel Ribbon:

• Select the cells to which you want to apply the Date Formatting.
• Go to Home → Number group
• Click the dropdown 🡳 to select the Short Date or Long Date.
• Click on Short Date or Long Date.

Date Formatting from Format Cells:

• Select your date cells.
• Right-click and choose Format Cells, or press Ctrl+1 to open the Format Cells dialog box.
• Go to the Number tab and select Date.
• Pick a format Type from the available options.
• Click OK to apply the formatting.
• The sample area will display how the chosen format Type will appear.

Custom Formatting

Excel lets you create your custom number formats. Below are some examples:

Custom Formatting Examples:

• ,##0.00 it adds comma separators and two decimals.
• If you apply “USD “0 to the number 1234, it will add static text “USD” before the number 1234.
• d/m/yyyy will display Day/Month/Year without leading zeros.
• “Qty: “0 adds text before the number.

How to apply custom formatting:

• Go to Format Cells > Number > Custom.
• Enter your custom code in the Type field.
• Click OK to apply the formatting.
• The sample area will display how the chosen format Type will appear.

Conclusion

Mastering number formatting in Excel is essential for producing professional, readable, and organized spreadsheets. By understanding how to apply these formats effectively, you can not only enhance the appearance of your data but also ensure that it’s clear, consistent, and easy to analyze. It not only boosts readability but also saves time during reporting.

To explore all our posts covering the fundamentals of Excel, please follow this link.

FAQs