Format Cells.
Excel’s Format Cells dialog box has a Number tab that you should become proficient with if you want to improve the clarity and appearance of your data. The correct formatting can make all the difference whether working with financial data, dates, timings, or specialized statistics.
10 Takeaways for Formatting Cells in Excel Using the Number Tab
- Flexibility: For various data formats, the Number tab provides a wide range of formatting options.
- Precision: Adjust the number of decimal places to the exact number required by your data.
- Clarity: To make enormous numbers easier to understand, use thousands separators.
- Currency Formatting: To properly indicate monetary values in financial data, use currency formats.
- Consistency: The decimal points and currency symbols are consistently aligned according to the Accounting standard.
- Customize Date and Time: Adapt time and date formats to local, cultural, and contextual requirements.
- Percentage Representation: Converting decimal numbers to percentages is a simple process.
- Fraction Display: In some situations, it may be easier to interpret values when they are displayed as fractions.
- Scientific Notation: For extremely large or extremely small values, use scientific notation.
- Customization: To satisfy certain needs not addressed by default settings, create bespoke formats.
About and how to?
Excel is a powerful tool for data analysis and presentation, and one of its key features is the ability to format cells to display data in a variety of ways. The “Number” tab in the Format Cells dialog box provides numerous options for customizing the appearance of numbers, dates, times, percentages, and more. This guide will walk you through the various formatting options available under the Number tab, complete with examples and practical tips.
How to attain or access the Format Cells Dialog Box to format your data or numbers:
- Click on a cell to select it or select a range of cells you want to format.
Press CTRL along with 1 (CTRL and 1).
- Click on a cell to select it or select a range of cells you want to format.
Right-click on cells — >>> choose Format Cells.
- Click on a cell to select it or select a range of cells you want to format.
From Home Ribbon — >>> select Alignment settings.
Then choose or click the Number tab.
Categories in the Number Tab:
General
This is the default format for numbers, text, etc..
Example: New York or London displayed as New York or London. The number 4567.03 is displayed as 4567.03.
Number
The number format allows you to specify the number of decimal places, use a thousand separator, and display minus numbers in different style.
Example: 4567.03 formatted with two decimal places and a thousand separator is displayed as 4,567.03.
2534248.57 → 2,534,248.57
Select the cells — >>> go to the Number tab — >>> choose Number — >>> set Decimal places to 2 — >>> and check Use 1000 Separator (,).
Currency
The currency format is used for monetary values, displaying the currency symbol and formatting minus numbers.
Example: $4567.03 formatted with the currency symbol and two decimal places is displayed as $4,567.03.
Select the cells — >>> go to the Number tab — >>> choose Currency — >>> set Decimal places to 2 — >>> and check Use 1000 Separator (,).
Accounting
Accounting format is similar to Currency, however, it aligns currency symbols at the start position of the cell.
Example: $4567.03 formatted as accounting is displayed with the currency symbol aligned to the left edge of the cell “$ 4,567.03”.
Select the cells — >>> go to the Number tab — >>> choose Accounting — >>> set Decimal places to 2 — >>> and check Use 1000 Separator (,).
Date
Provides various date formats.
Example: January 1, 2024 can be formatted as 1/1/2024, 01-Jan-24, etc.
Select the cells — >>> go to the Number tab — >>> choose DATE — >>> and select the desired format.
Time
The time format is used to format the value as time.
Example: 9:30 PM can be formatted as 21:30, 9:30 PM, etc. or can show seconds as well. 21:30:17 or 9:30:17 PM.
Select the cells — >>> go to the Time tab — >>> choose Time — >>> and select the desired format.
Percentage
The percentage format multiplies the cell value by a hundred and displays the result in a percentage format.
Example: (=16/120) 0.1333 formatted as a percentage is displayed as 13.3333% or 13.33%.
Select the cells — >>> go to the Number tab — >>> choose Percentage — >>> set Decimal places to 2, 3, or 4 as you desire.
Fraction
Fraction format displays numbers as fractions or ratios.
Example: 0.25 can be formatted as 1/4, 21/25, 6/8 etc…
Select the cells — >>> go to the Number tab — >>> choose Fraction — >>> And choose a format as you desire example: 1/4, 21/25, 6/8 etc…
Scientific
Scientific format displays numbers in scientific notation.
Example: 0.1333 can be formatted as 1.3333E-01.
Select the cells — >>> go to the Number tab — >>> choose Scientific — >>> and select the desired format.
Text
Text is used to treat the cell content as text, even if it’s a number etc… When you desire MS Excel to treat, Inventory part numbers, employee IDs, P.O. BOX, and Contact numbers. In other words we use text format where numerical values are irrelevant and calculations are not necessary. This format helps in sorting the data or columns.
Select the cells — >>> go to the Number tab — >>> choose Text.
Special
The special format provides style for Zip codes, Zip Codes + 4, Phone numbers, social-security numbers.
Example: +1985744523 can be formatted as +(1) 98-57-44523.
Select the cells, Format Cells — >>> go to the Number tab — >>> choose Special and select “Zip Code or Phone Number.”
Custom
Custom format allows you to create custom number formats of your choice.
Example: Custom format #,##0.00_);(#,##0.00) formats 4567.03 as 4,567.03 and -1234.56 as (1,234.56).
Example:
Select the cells, Format Cells — >>> go to the Number tab — >>> choose Custom — >>>, in the box type #,##0.00_);(#,##0.00) — >>> for text once can type “@” etc…
Further to learn more about CUSTOM tab in details click on Custom tab.