Connect with us


How to create advanced number formatting in excel



What is Advance Number Formatting

Advance number formatting refers to the process of changing the appearance of data contained in cells. excel provides a wide variety of formatting options. In the following sections, you see how to use many of Excel’s formatting options to quickly improve the appearance of your worksheets.

In this table, there are some examples of formatted and unformatted text. There it is also mentioned as a type of format we have used for that particular cell.

How to Format numbers by using the Ribbon

1. The Number Format drop-down list contains li common number formats.
2. Drop-down list to select a currency format.
3. Percent Style button jGen I.
4. Comma Style button
5. Increase Decimal place
6. Decrease Decimal place Number
Using Shortcut Keys to Format Numbers

Key CombinationFormatting Applied 
Ctrl+Shift+∼General number format(that is unformatted values)
Ctrl+Shift+$Currency format with two decimal places (negative number appear in parentheses.
Ctrl+shift+%Percentage format, with no decimal places
Ctrl+Shift+∧Scientific notation, number format with two decimal place
Ctrl+Shift+#Date format with the day, month, and year
Ctrl+Shift+@Time format with the hour
Ctrl+Shift+!two decimal places, thousands separator, and a hyphen for negative values

Formatting numbers using the Format Cells dialog box

In most cases, the number of formats accessible from the Number group of the home tab is only fine. However, sometimes you want to have more control over how your values appear. Excel offers a lot of control over number formats by using the Format Cells dialog box shown in Figure below. Use them for formatting numbers.

Number Formats

Select Number control tab, to apply any number formatting.

General —  The default format of the number will be applied. Mainly it is required to remove or cancel any number format from the selection.

Number – This type of formatting allows you to set number o decimal places, with a thousand separators, and formatting of negative numbers.

Currency – Numbers are immediately preceded by a default currency sign. A number of decimal places and the formatting of negative numbers can also be set here, zero values are displayed.

Accounting – The currency sign and decimal places can be set with the numbers. But the number setting of the currency symbol is different from the currency format. Currency symbols come with left alignment, whereas numbers or values are right aligned in a cell.

Date — This formatting gives a variety of Date formats.

Time – Different tune formats provided by this option.

Percentage — This formatting attached a percentage sign with the number as a suffix and multiplying the number by lOO hundred).

Fraction – Includes formats based on either the number of digits to display in the divisor (1,2. or 3) or the fractional unit (halves, quarters tenths, and so on).

Scientific — Numbers are displayed in scientific notation. For example 1.O1E+03.

Text – Changes a text number without adding different formatting. For numeric labels which may include leading zeros, this is useful. All standard numerical formats strip leading zeros. Before entering the contents of the cell, you must apply the text format.

Special – This is mainly used for Telephone numbers, Zip Codes, etc. those are mainly having some special formats.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *