Home Featured Unlock the secret to easily formatting numbers in Excel with simplicity and...

Technology

Unlock the secret to easily formatting numbers in Excel with simplicity and precision

Excel is a powerful tool for working with numbers. So, when working with Excel, people tend to format numbers to make spreadsheets more visually appealing and facilitate data manipulation operations. Join FPT Shop to learn about the most detailed and simplest ways to format numbers!

Administrator

26 December, 2023

100

In Excel, there are various data formats, including numbers, text, currency… When using Excel to analyze data, you will need to format numbers in Excel to have a beautiful working spreadsheet with data in the format you need. So what is number formatting? How to format numbers? In this article, FPT Shop will show you how to format numbers in Excel simply and in detail!

Number formatting in Excel

The information that can be entered in an Excel spreadsheet will have different formats according to the user’s needs, such as formatting types like text, numbers, dates, or percentages… Number formatting in Excel is called Number, in addition, Excel also has other formats such as:

Text: text format
Currency: currency format
Percentage: percentage format
Accounting: accounting format
Scientific: scientific format
Custom: custom format according to user’s decision

You can refer to examples of data formatting in Excel in the image below.

How to format numbers in Excel

To format numbers in Excel, you need to open the Number tab, there are 2 ways to open the Number tab as follows:

Method 1: Open the Number tab using Format Cells

Step 1: Select the entire data that you want to format.
Step 2: Right-click and select Format Cells
Step 3: Select the Number tab, you will see the Number tab. You will perform number formatting here.

Method 2: On the Home toolbar, you can easily see the Number tab, here you can perform number formatting steps in Excel.

Step 1: Open the Number tab
Step 2: In the Number tab, in the Categories section at the top left, we will have options for formatting numbers in Excel such as Number, Accounting, Currency, Date…

Currency and Accounting formats

To format numerical data, first you need to adjust the decimal places in excel, in the Accounting tab to format accounting data, you can format the following items:

Decimal places: is the item that allows you to display how many numbers after the decimal point according to our needs, for example in the above case is 2, which means after the decimal point there will be 2 numbers like 0.22.
Symbol: Choose the currency symbol you want to use, for example VND

Currency is also a tab for formatting currency, but different from Accounting. The difference lies in the position of the currency unit, with currency formatting, the currency unit symbol will be written immediately before the amount, with accounting formatting, the currency symbol will be aligned to the left.

Another way to format is to select the data, then left-click on the currency icon ($) on the toolbar and select the currency unit you want to format.

Date and time formatting in Excel

In Excel, when you enter data in the form of dates, Excel will not automatically understand it as a date. Instead, Excel automatically stores data as Text (default is General). This will make it difficult to process data if you do not format them as dates. There are 2 ways to format dates as follows:

Method 1: Format according to available templates in Type

In the Categories section, you can easily see the Date tab.
In the Type section, choose the date format you want to format.
In the Sample section, you can preview how the date will be displayed in Excel.

Method 2: Format by region, country

Time formatting in Excel is very diverse because each country in the world will have its own date and time formats and Excel will have most of these formats. You can find the appropriate time format by selecting from Excel’s Locale (Location) list as shown below:

A little note for you, if you complete the steps and the date formatting still doesn’t change, the reason may be that the initial input data is in text format, you need to convert it to numbers and then perform the formatting steps.

Fraction formatting in Excel

When you want to enter data as a fraction in Excel, you cannot directly enter the fraction, you have to convert from decimal to fraction.

For example: To enter the fraction “1/4“, you first need to enter 0.25 and then use the fraction formatting feature in Excel to convert it to 1/4.

Scientific formatting in Excel

Scientific number formatting in Excel is used to represent very large or very small values. In that case, the value in the calculation cell often appears in the form of xE + y.

For example: We have a very large value of 10 billion, 10 billion is a number with 10 zeros. Instead of displaying it as 10,000,000,000, Excel will represent this number in the form of 1E+10. Because besides understanding it as 10 billion, we can also represent this number as 1 times 10 to the power of 10.

The only thing we can format in Scientific is to change the number of digits displayed after the decimal point.

Text data formatting

You can convert numerical data in the calculation cell to Text format simply by selecting the cells containing the data and selecting the Text format. When converted to text format, Excel will align it to the left margin by default.

Special formatting

In each country, special number sequences such as phone numbers, postal codes, social security numbers will often be different. To help users format these numbers, Excel provides the Special format feature.

User-defined custom formatting (Custom)

In Custom format, we can customize the format according to our own preferences.

When opening the Custom dialog box, we can see format templates with code as shown below:

The meanings of those code are as follows:

#: Only display meaningful digits, remove unnecessary 0 digits at the end, the number value will be rounded if necessary. For example, #.## means display up to 2 significant digits after the decimal point, with the input value of 5.5, Excel will only display 5.5, but if the input value is 5.555, Excel will display 5.56 (rounded).
0: Display 0 digits, including unnecessary 0 digits, the value of the number will be rounded if necessary. For example: #.00 means always display 2 decimal places, including the number 0, more specifically, if you enter 5.5, Excel will format it as 5.50.
?: Used to align decimal places.
@: Replaced with text data (Text)
[Red]: You can use color codes to format data. The colors supported by Custom include: [Black]; [White]; [Green]; [Blue]; [Magenta]; [Yellow]; [Cyan]; [Red].

Conclusion

Number formatting in Excel is an essential task when you work with data or create reports in Excel. Hopefully, through this article, you can easily and quickly format numbers. If you have any questions about Excel tricks, please leave a comment below!

Frequently asked questions

To format numbers in Excel, simply select the cells you want to format, then go to the Home tab and click on the ‘Number’ group. From there, you can choose from a variety of number formats such as General, Number, Currency, Percentage, and more. You can also customize your own number format by clicking on ‘More Number Formats’ and creating your own.

For simplicity, it’s often best to use the ‘Number’ format, which displays numbers with a thousand separator and two decimal places. If you want to round numbers, you can use the ‘Increase Decimal’ and ‘Decrease Decimal’ buttons in the ‘Number’ group. To ensure precision, always check the ‘Use 1000 Separator’ option when formatting numbers, and be mindful of the negative numbers and zero values in your data set.