Building Excel Custom Format Using Format Cells

Hi Friends,

In this posting we are going to discuss about how to use “Format Cells”, when you want to build your own custom formats for your values. Using FORMAT CELLS feature, you can change the format of the selected cell to any of the format from the available options like NUMBER, PERCENTAGE, CURRENCY etc.

If you want to build your format for your values, you should know the nuances of format cells.

Any custom format you want to build, you should define four inputs for it. You need to specify how positive value, negative value, zeros and text should be formatted.

Syntax would be

POSITIVE VALUES ; NEGATIVE VALUES ; ZEROS ; TEXT VALUES

Let us see few examples to understand this better;

In the above screenshot we have positive values, negative values, zeros and text values. Our requirement is, positive values should appear along with a word “BUY”, negative values should appear with a word “SELL”, zeros should appear along with a word “NO CHANGE” and finally for text values it should be appear as “NO DATA”

Selecting the RESULT column values in the above screenshot, go to “Format Cells” by using shortcut key CTRL+1 or you can simply right click on the selected cells and choose the option “Format Cells”

In the FORMAT CELLS option, go to the category “CUSTOM” and on the right-hand side of the dialog box under the word TYPE, clear the content in that box and start typing your custom format.

“BUY” 0.0 ; “SELL” 0.0 ; “NO CHANGE” ; “NO DATA”

Finally the values will start appearing in your required custom format, please refer the below screenshot;

If you closely observe this process, this changes only the appearance of the values. It will never disturb the actual value; these values can be used for any other calculations as well.

Give a try from your side to design your own formats for your values.

Happy Learning !!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.