Tech Forum

Custom formats in Excel - II

Article summary

Last article covered formatting specific to date and time data. In this article we explore the concept behind formatting codes and its various applications.

Knowing about custom formatting is likely to help you in performing many tasks in a simpler manner. It may even open new possibilities of handling data.

Defining a custom format

The custom format can be entered in the Format – Cells – Number tab after selecting Custom from the list. The formats are entered in the Type textbox. These formats are applied to the cell(s) that were selected at that time. Applying a new format removes any existing formats. Formats cannot be additive.

Please note, the word ‘format’ is being used only to the custom formatting codes which can be entered in Excel. We are not referring to other types of formatting like colour, font, border, and so on.

Excel can contain numbers or text. Yes, it can contain dates and logical fields, but internally these are stored as numbers and text anyway.

Now, numbers can be positive or negative. Text is just text. Excel provides a method of defining formats for all these types of data.

Formatting codes are specified for four different items at a time:

1. Positive number

2. Negative number

3. Zero value

4. Text

Note that technically, there is no fifth type that can be contained in a cell (excluding errors, of course). The formatting codes for each of these are specified and are separated by commas.

Base data

Consider the information shown below. This will be used as the base data for exploring the various format options. In this case, the data is shown as entered, without any custom formats being applied.

Now let us try a simple custom format and see how things change. Select these cells and then enter the following format in the Custom – Type textbox.

0.00 ; (0.00) ; “Not Known” ; “Prefix”@

Please note: In this article the format codes will be shown in bold as shown above. This is just to differentiate it from other text. In reality, the format codes are just entered as regular text.

Now let us see how the data changes.

Now let us understand what happened.

As you can see, the whole number 2 is represented as 2.00. Whereas the decimal 10.234 is rounded off. Negative number is shown in brackets. Blank is not affected and the word “Prefix” was put before the actual text “nitin”.

This is how each cell can be affected by specifying a set of format codes. Of course, all of these are not mandatory. You can choose the effect you want to achieve and then specify the codes.

• Hiding zero values

Here is a practical use of this format feature. Consider the following list.

Without format Zero hidden Zero converted to hyphen

As you can see here, in the second column, we have left the third parameter (which denotes the format for zero values) blank. Therefore, the zeros are hidden. In the third column the Zero value format code is just a hyphen. Therefore it is shown instead of Zero. You could have written any other text instead of they hyphen like “Not known” , “Nothing”, “Null”.

• Storing and removing custom formats

When you add custom formats, these are stored for future use. These are stored only within the particular Excel file. If you created a format code by default, you can simply highlight it and delete it in the same dialog box.

The examples shown above are only a simple introduction of formats. However, it is only the beginning. There are a large number of format codes available depending upon the data type. It is not possible to cover all of them here. But they are documented well in the online help, as usual!

• Formatting numbers

Code Purpose Without code Code With Code

The question mark is a nice way of providing decimal alignment using format codes. In Word there is a special Tab called Decimal tab. But in Excel there is nothing equivalent of it. Using the ? format code solves the problem.

• Colouring numbers

You can put the colour code in square brackets as the first part of each part of the format code to specify the colour. For example, if you use the format

[green]0.00;[red]-0.00 will show positive and negative numbers as follows:

Please note here that for negative number format, the minus sign is specifically added. If it was not added, the negative numbers would be shown only in red colour without the negative sign.

• Changing visual format based upon conditions

This is equivalent of conditional formatting in functionality. How ever it is implemented as a format code rather than using the conditional formatting feature.

See the following text. The format code used here was:

[Red][=2]General;[Blue][=3]General;[Green]General

This way, three conditions can be given. The last one is the format (colour only) to be applied to numbers which do not meet the first two criteria.

• Adding text to numbers

Here also there are some good possibilities. Text can be added to any part of the formatting codes by enclosing in inverted commas. It can come before or after the base content of the cell, even if the cell contains numbers.

For example the format 0.00 “Rs Profit”

will display the number 2.33 as “2.33 Rs Profit”

* Adding text to text in the cell

Applying the format

“The name is ” @ to the base text “nitin” will result in “The name is nitin”.

* Padding numbers with text

This is a very interesting use. If you have to fill up the gap between number in first column and content of second column, using a repeating character, like - , you can use the code 0*-;0*= after the number.

This is a very nice feature to provide a leading line from an uneven width column to the next column.

Developer corner

• Managing custom formatting in VBA

All this was end-user stuff. Now for our developer readers.

How do we handle formats programmatically? Simple.

Consider a cell which has some custom format assigned. To read the current format use the command

?ActiveCell.NumberFormat

To set the format:

ActiveCell.NumberFormat = “[green];[red];[blue];

[yellow]”

This will make positive numbers green, negative ones red, zeros blue and text yellow!

You can also use all other formats also.

ActiveCell.NumberFormat = “dd-mmm-yyyy”

There is another property for ActiveCell called NumberFormatLocal. This property is applicable to the Style object.

* Writing your own functions

It is possible that the existing Excel functions are not adequate for your specific business needs. In that case you simply write your own function and manage the show. How do you do this?

Simple.

1. Choose Tools – Macros - Visual Basic Editor

2. Choose Insert - Module

3. Write a function which accepts relevant parameters and performs the required calculation. Return the value as usual. For example

Function MySum (first as Integer, second as Integer)

MySum = first + second + 2

‘ just a sample of custom calculation

End Function

4. Now you can use this function in the worksheet as though it was a standard Excel function.

=mysum(g4,b9)

Remember that you cannot do anything in the function which changes the Excel environment. This means you can not add rows or columns, you cannot change formatting, set properties, change methods and so on. You should just work on the input data and return output.

About the Author:Dr Nitin Paranjape is the Chairman and MD of Maestros (Mediline). He is a consultant with many organisations, covering appropriate technology utilisation, business application of relevant technology, application architecture and audit as well as knowledge transfer. He has authored more than 650 articles on various technology-related subjects. He can be contacted at nitin@mediline.co.in