Through number formatting, you can control how numbers, dates, currencies, and other types of values appear in your spreadsheets.
---
1. Introduction to Number Formatting in Excel
Number formatting changes how numbers appear in cells without altering their underlying values. For instance, you can display a number with currency symbols, percentage signs, or custom decimal placements. The formatting helps users understand data context and, in some cases, can improve readability by adding visual appeal.
---
2. Types of Number Formats in Excel
Excel supports various built-in number formats. Here’s an overview of the most commonly used ones:
General: The default format for numbers. Displays the number as it is, without any specific styling.
Number: Adds comma separators for thousands, and lets you set decimal places.
Currency: Displays numbers with currency symbols (e.g., $, €, ¥), along with commas and decimals.
Accounting: Similar to Currency, but aligns the currency symbols to the left of the cell, creating a cleaner look for financial data.
Date & Time: Offers multiple date and time formats, making it easy to track events or schedules.
Percentage: Multiplies the number by 100 and appends a percent sign, useful for displaying ratios or growth.
Fraction: Displays numbers as fractions (e.g., ½, ⅔), rather than decimals.
Scientific: Formats large numbers in scientific notation (e.g., 1.23E+10).
Text: Treats numbers as text, which is helpful when dealing with data like phone numbers or ID codes.
---
3. Applying Number Formats
There are multiple ways to apply number formats in Excel:
Ribbon Menu: Select cells, go to the "Home" tab, and choose a format from the "Number" dropdown.
Right-click Menu: Right-click a selected cell or range, choose “Format Cells,” and navigate to the “Number” tab.
Keyboard Shortcut: Press Ctrl + 1 to open the Format Cells dialog box and select a format.
---
4. Custom Number Formats
Excel allows you to create custom number formats for even greater control over how data appears. Custom formatting uses a set of symbols that represent specific components of the number.
Syntax of Custom Number Formats
Custom number formats have four parts, separated by semicolons ;:
1. Positive numbers
2. Negative numbers
3. Zero values
4. Text
Each part is optional, and you can mix and match as needed. For example, a custom format like 0.00; -0.00; "—" will format positive and negative numbers to two decimal places and display dashes instead of zeros.
Custom Format Codes
0: Digit placeholder. Displays insignificant zeros (e.g., 0.00 forces two decimals).
#: Digit placeholder without displaying insignificant zeros.
?: Aligns numbers with varying decimals in a column.
, (Comma): Thousand separator or for scaling values (e.g., 0.0, displays thousands as 1.2 for 1200).
%: Percentage. Multiplies by 100 and adds a percent sign.
E+: Scientific notation.
"Text": Displays text literally within quotes.
_ (Underscore): Adds space equal to the width of the next character, often used for aligning currency symbols.
@: Placeholder for text content within a format.
Example Custom Formats
Phone Number Format: "(###) ###-####" converts 1234567890 to (123) 456-7890.
Currency with Units: $#,##0,," M" displays millions as $1 M.
Date Format: dd-mmm-yyyy formats a date as 01-Jan-2023.
---
5. Date and Time Formatting
Excel’s date and time formats can be tricky since they rely on a system that represents dates as serial numbers. Here are some commonly used codes:
d or dd: Day as a number.
ddd: Abbreviated day (e.g., Mon).
dddd: Full day name (e.g., Monday).
m or mm: Month as a number.
mmm: Abbreviated month (e.g., Jan).
mmmm: Full month name (e.g., January).
yy: Two-digit year.
yyyy: Four-digit year.
h or hh: Hour in 12-hour or 24-hour format.
m or mm: Minute.
s or ss: Second.
AM/PM: Appends AM or PM to times.
For instance, the format dd-mmm-yyyy hh:mm AM/PM displays 15-Mar-2024 03:45 PM.
---
6. Using Conditional Number Formatting
Excel’s conditional formatting lets you apply formats based on specific criteria, like highlighting sales below a target or displaying negative values in red.
Basic Conditional Formatting: Go to the “Home” tab, choose “Conditional Formatting,” and select rules like “Greater Than,” “Less Than,” or “Between.”
Custom Formatting Rules: Use “New Rule” for complex criteria and specify custom formats for values that meet your criteria.
---
7. Dealing with Common Issues in Number Formatting
Number formatting in Excel can sometimes lead to issues if not applied correctly:
Leading Zeros Disappearing: Set the cell format to “Text” to retain leading zeros, or use a custom format like 0000 for a fixed number of digits.
Dates Displaying as Numbers: Excel treats dates as serial numbers, so apply a date format (e.g., dd-mm-yyyy) to make them readable.
Scientific Notation in Large Numbers: For large numbers, Excel may switch to scientific notation automatically. Change the format to “Number” and increase decimal places to avoid this.
---
8. Tips and Tricks for Efficient Number Formatting
Use Format Painter: To copy number formatting across cells, select a formatted cell, click the Format Painter, and apply it to other cells.
Use Keyboard Shortcuts:
Ctrl + Shift + ! for two decimal places with a comma.
Ctrl + Shift + $ for currency.
Ctrl + Shift + % for percentages.
Avoid Formatting Entire Columns: Apply formatting to specific cells or ranges to prevent Excel from slowing down on large data sets.
Combine Conditional and Custom Formatting: This can highlight critical data with custom symbols or color coding.
---
9. Advanced Examples of Custom Number Formats
Let’s explore some advanced scenarios using custom number formats:
Color-Coded Financial Values: [Green]$#,##0;[Red]-$#,##0;[Black]$0 formats positive values in green, negative in red, and zero in black.
Temperature Data with Units: 0.0 "°C" for temperature data will display numbers like 25.4 °C.
Dynamic Time Formatting: [h]:mm:ss keeps track of cumulative hours, useful in timesheet calculations.
---
---