Excel Tip – Custom Number Formatting
Almost every Excel user has faced the need to format data in their spreadsheets in some way, shape or form. Not surprising, this task can often lead to multiple trips to the formatting ribbon. Wouldn’t it be nice to be able to format data in one simple step so that it is displayed as per your specific requirements? Come to the rescue Custom Number Formatting!
At Marquee, we are strong advocates of presenting data in a clear, logical, and user-friendly manner. Custom Number Formatting allows a user to format the contents of a cell in a multitude of ways. The focus of this article will be on the basic syntax that is followed and to provide a simple example. Another fundamental principle at Marquee is Excel efficiency which typically translates to minimal use of the mouse. As such, certain excel keyboard shortcuts will also be mentioned throughout the article to enhance user efficiency.
Many people know that to format a cell, they can right click their mouse, click on Format Cells to open the Format Cells Dialogue Box, and then apply the necessary formatting. However, what many people do not know is that the same box can be opened by simply hitting CTRL + 1. Once open, you can apply a custom number format by hitting the “Tab” key to get to the Category list and then by pressing the “C” key twice to get to the Custom field (it will be highlighted if done correctly). Now press the “Tab” key to get to the Type box – this is where you can define a custom number format in the following sequential syntax order: Positive;Negative;Zero;Text. Colour can also be added to the format by putting [color] before any of the above inputs. The diagram below shows a visual depiction of the Format Cells Dialogue Box and the syntax described above.
Let’s look at an example where the model/user requirement is to have positive numbers to one decimal place displayed with the $ sign, negative numbers to one decimal place displayed with the added formatting of parenthesis and red font, and a zero to be displayed as “-”.
To demonstrate, we will use the data set below as our sample (Assume the data is in cells B3:B5).
To convert the above data to our desired format, please follow the steps below:
Step 1: Highlight the Cells to be formatted (e.g. cells B3:B5)
Step 2: Hit CTRL + 1 to open the Format Cells Dialogue Box and navigate to the Custom Number Type box (see above)
Step 3: Type $0.0;[Red]($0.0); “-”;
Note: This is the format required in this specific example. It is here where there are numerous options and formats for users to define.
Step 4: Press ENTER and Voila!
This article was meant to serve as an introduction to the basic features and syntax of Custom Number Formatting. At Marquee, we utilize custom number formatting in all our models and find it extremely beneficial as it ensures consistency in presentation throughout the model.
Remember, don’t just customize your suits, customize your number formats as well!