If you are an EXCEL USER, you must be acquainted with this word “NUMBER FORMAT” or sometimes we call it as “CELL FORMAT” i.e. the format of the cell or in layman language , specifying the type of content in the cell. If we go for a definition
“NUMBER FORMATS ARE THE TYPES OF DATA ANY CELL IS CONTAINING OR IT CAN BE STATED THAT THE WAY, IN WHICH THE VALUE IS STORED IN THE CELL. E.G. STRING, NUMBER, TEXT, DATE, CURRENCY ETC.” IT HELPS EXCEL TO CALCULATE WITH ACCURACY FOR THE USER.
So rephrasing the definition, suppose a cell is containing time and we don’t know that the cell is storing time. We add 10 to that cell. So do you think that the result would be accurate or anywhere near right?? The answer is NO!!!
Unless until we know what is contained in the cell , we can never be sure about the type of operation which we can apply on that particular cell.
This topic is very important, if you plan to even use EXCEL at a BASIC LEVEL. And if you plan to master it, you should know how to use the FORMAT OF THE CELL as a weapon to kill your problems.
YES!! the format of the cell is so important that many big problems are just converted into kindergarten problems if we choose the appropriate format for the cell.
Let us see what is there in the cell format.
In this article, we’ll learn about the procedure of changing the CELL FORMAT or NUMBER FORMAT or NUMBER TYPE, whatever you call it, of the cell and learn about the different types of cell formats present in the MICROSOFT EXCEL in detail.
The NUMBER FORMAT button is located under the HOME TAB in NUMBER group.
On the other hand, we can even get it by RIGHT CLICKING on the concerned cell and choosing FORMAT CELL
This is the default format of the EXCEL CELLS which it applies whenever we type anything in the cell. The data will be displayed the same way we type. We type the text, it would show the text, we type the number ,it would show the number.
But this format is not so simple. It plays tricks.
If you type the text, it would remain a general format but would behave as a text cell and if entered a number it would behave as a numbered cell. It would round the decimal parts if the number is lengthy and it’ll go to scientific notation too after 12 digits. [Scientific notation is in the format 1.12 E^12]. If you just type the % , it’ll convert itself to percentage type, similarly if time, it’ll go to time type, if date then date etc.
WHEN TO USE GENERAL FORMAT:
In these cases , we use GENERAL FORMAT for the cells.
The NUMBER FORMAT is used when we specifically declare any cell to contain numbers. It gives us additional option to choose the decimal places and if we want to use the separator after every 1000 or not.
It also gives the option to choose the way we want to show negative numbers.
We can show negative numbers by a minus (-) sign or by red color or both. This option is given below and shown in the picture below.
WHEN TO USE NUMBER-NUMBER FORMAT:
Used for general monetary (currency) values and displays the default currency symbol with numbers. You can specify the number of decimal places that you want to use and how you want to display negative numbers.
WHEN TO USE CURRENCY NUMBER FORMAT:
The options are available to choose the currency of the country and number of decimal digits for the figures.
WHEN SHOULD WE USE ACCOUNTING NUMBER FORMAT:
One of the very tricky and important format.
EXCEL stores DATE as a serial number counted from JAN 1 1900. And converts this serial number into different types of date formats which we can choose from the given list.
Choose any format from the given list.
Sometimes a problem occurs when we can’t find the format of our choice. Mostly this is due to the location settings. Choose the correct location and you would find your desired format of date.
We can choose the location and EXCEL would populate different formats available from which we can choose our format.
WHEN SHOULD WE USE DATE NUMBER FORMAT
Whenever we are working with the dates, which is going to be quite often, we should use date format for the ease which is provided by this format while using dates in our calculations.
Again very tricky format and very important. The time is stored in excel as the decimal part of the number. For example 0.5 means 12 noon and so on. There are different formats of time from which we can choose how to show the time.
Choose the time format from the given list.
If somehow, can’t find the option of your choice, choose your location and the options will change. Now choose the desired one.
WHEN SHOULD WE USE TIME NUMBER FORMAT:
We should use time number format when we are working with the time in reports. It is advisable to understand the time format and use it efficiently and it’ll make our job extremely easy.
It simply converts the value to percentage by multiplying by 100 and putting percentage symbol with it. (%). It gives the option of choosing decimal places too.
WHEN SHOULD WE USE USE PERCENTAGE NUMBER FORMAT:
Its very obvious, when we are dealing with percentages.
*There is another way of setting the format as PERCENTAGES. Just type the number and put % sign with this. It’ll convert the cell to percentage automatically.
Displays a number as a fraction, according to the type of fraction that you specify.
Choose the fraction type from the options given below.
WHEN SHOULD WE USE FRACTION NUMBER FORMAT:
Normally, when we enter any fraction in the cell e.g. 115/215, the division takes place and the result in decimals is shown in the cell. Now if we explicitly want the fraction to be stored in the number, we should make use of this format.
Displays the number in the scientific notation i.e. Number x 10 ^ 13 or like that. The decimal parts in the number can be set, rest will come with the exponent. E here will count for 10 as we are working in the decimal system.
Although when any number goes beyond 12 digits even in GENERAL FORMAT, the number itself converts to the exponential form.
Choose the number of decimals from the given option.
WHEN SHOULD WE USE SCIENTIFIC NUMBER FORMAT:
Its upto us. If the report needs the scientific notation , we can make use of this format.
Sometimes the figures go very large, and it is not advisable to use the figures as we need to count the digits, in such cases too, we can use it.
Now , one would need to keep counting the digits before he can read it.
We can also write it as $1.23 x10 ^ 15 which is 1.23 quadrillion.
Text format treats everything as a text. Numbers, decimals, anything whatever goes is a text and doesn’t operate any mathematical formula on this . It’ll just respond to text functions.
WHEN SHOULD WE USE TEXT NUMBER FORMAT:
When we need to enter text in the cell.
Displays any special type like social security number or zip code etc.
This is the most important and versatile kind of number type.
It is the final resort for the number type problems. If our problems are not being sorted out by any of the listed formats, we should go for the custom format.
We can create a custom format with the help of this option. The picture shows the steps to make your own custom format.
1. Search the desired format in the list. If not available type your custom format in the TYPE field as
shown in the picture.
2. Check the sample before finalizing.
3. Click OK and custom format is ready.
WHEN SHOULD WE USE CUSTOM NUMBER FORMAT:
It is extremely useful format choice.
Phone numbers, zip codes, any other special number etc.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE