Updated June 12, 2023
Part -8 – Number Format In Excel
Download Excel Template – Number Format In Excel
Excel Format – Number Format In Excel (with solution)
Excel Format – Number Format In Excel (without solution)
Learn how to organize, format, and calculate data smoothly. Develop skills to master Excel tools, formulas, and functions. Analyze data from different perspectives.
Transcript For The Video – Number Format In Excel
Now having done with the basic formulas, let us now look at formatting this table. This table still looks bit crude, and we can look at efficient ways of improving the visual appeal of this table. So the first thing that we need to kind of learn here is how to format these numbers. These numbers are essentially monthly salary, annual salary; these are in dollar, likewise minimum and maximum. Can we represent this in a format of a table?
So let me take you to sheet 3, where we will now discuss different kinds of number formats in Excel. There is basically three kinds of number format in Excel, which we should really understand. The number format in Excel are basically available within this box, and more information on the number format can be gauged by clicking on this additional dialogue box. Which we will discuss in a minute; what we need to do here is to understand clearly how the concept of currency format works. There is a format of comma, and you must also understand percentage format, so let us type 500 here and see how this currency format works, so let me click on dollar sign here, and we see that it has updated this number in two ways one is that there is a clearly visible dollar sign in front of 500 and the second one is that there are two decimal places which are available here.
The other way in which we can include the dollar sign is that by clicking on this number, right-click, go to format cells, and you will find that this number can have different kinds of formats available. The one which we may choose is the currency format, and we can press OK. Now when you observe these two currency formats or the formats will find that the dollar sign in the second case is closer to 500. However, the dollar sign in the first case is far off. This is primarily because the first one actually uses the accounting format, so if you go on the top, you will find that there is an accounting format; you can also right-click and go to format cells, and you may find this the accounting format to decimal places, so ideally people may like to choose the second format if they are not using it for accounting purposes so we can go and choose the currency format here so this is how you the currency format may work. Also, if you may like to increase the decimal or decrease the decimal, you can do that by increasing the decimal here by clicking here or maybe by reducing the decimal by clicking on the right-hand side. So this is how you know you can play with the currency formats; the other set of formats are basically related to the comma format, so let me type some random number here.
Now if you observe this number very closely, this is very difficult to really gauge what this number actually is how many digits it contains. However, when you look at, let us say, comma format, comma format essentially means just click here, and it converts the overall cell or the number into a similar currency format but without the dollar sign. So what we can see here is that there are commas in between. That’s why it is called as comma style with two decimal places, and you can increase or decrease these decimal places depending on what exactly the format you require. So here, the readability improves by including the comma format.
The 3rd format, which you must make yourself quite aware of, and I must warn that majority of the mistakes in terms of formatting and otherwise actually take place in the percentage case. So let us try to type 10% so the moment I write 10 here, this is basically a number, and if I wish to convert this into the percentage, we can go back to the dialogue box and click on percentage, but what we find is that instead of 10% it gets converted into 1000%. This is where the problem lies. MS Excel actually implies that if you use a number which is 1, this is basically equal to 100%, and if you actually want to type 10%, you need to include this no.0.1 and then convert into a percentage which will mean it is 10%.
However, if you want to still change the original number, you can go back and remove the set of zeros and type it and the other way to write percentages is just type 10 with the percentage sign so you are not using the menu command from the top, but you are writing it like a text, and it gets automatically by default visualized as 10% so this is how you know you can take care of the number format in excel now let us go back to our sheet 1 were we were discussing the case study and let us try to format the number which is given here. So monthly salary, what we find is in the dollar, and let’s say we want to have a dollar sign and front of all of this.
So the way we can do it is we can right-click again and go to format cell and choose currency format, and two decimal places should be O.K., and we can press ok. So this is where you know the first cell gets converted into a currency format. Now let’s say if you want to include the dollar sign in the remaining monthly salaries; what you can do is you can select this column at once and right click and go to format cells again and choose currency, and press OK. So this is one kind of approach by which you can convert your regular number into a currency format.
There is a very simple approach as well which you can utilize in order to ensure that you can save time. On the left-hand top corner, you may see this is a format painter. Let’s see what it says copy formatting from one place and apply it to the other place. So let us click on it once, and what happens is when we click on it once, you will see that along with the cursor, there is the paintbrush. You just need to select the range on which you want to apply this format and release that range.
So what you will note is that all the formats which were in dollar 2500 get applied in the range which was selected. Likewise, let us say if I want to apply the same range here across the total salary, maximum salary, minimum salary, and average salary, I can do the same thing here. Let’s go back to the 2500-dollar cell. Go to format painter. I can choose these rows and columns and just release it, and you will find that all the columns and rows get converted into the formats which you really want. So as we have seen, this is a kind of number format in Excel.
Recommended Articles
Here are some articles that will help you to get more detail about the Number Format In Excel, so go through the link.
