Conditional Formatting _ Custom Number Formatting in Excel

Conditional Formatting 

Conditional formatting allows you to help visualize, explore and analyze data. It can help you to detect critical issues, patterns and trends over a period. 

You can use conditional formatting to highlight cells if they meet a certain criterion. If they don’t meet the criteria, they remain unchanged.

Suppose there is a price field in your data base and you want to keep the track of costly equipments so that next time you open up your worksheet you can quickly analyze which items are costly and which items are not so costly and plan your next financial budget for your institution effectively.

One way you can visualize such information is by highlighting important factors, interesting cells, range of cells, emphasize unusual values and visualize data by using data bars, colour scales, and icon sets that correspond to specific variations in the data.

The feature to do the above criteria specific highlighting is known as Conditional Formatting in Excel.

How to apply conditional formatting to cells?

Let us learn this by using a simple example.

Suppose, we have a database of the prices of vegetables and we want to analyse and visualise the data to create patterns and identify price trends. We will now apply conditional formatting to the range of the cells which contain the prices in a monthly basis.

Our Database looks like this:

Now we would like to format our data in such a way that prices above 100 should be highlighted in Red (gives a sense of costliness).

To do this:

• Select the range of cells which contains the prices. In our example case it is show below:

• Now go to the Home tab and under the Styles category click on Conditional Formatting Button

• Now go to Highlight Cell Rules -> Click on Greater than

• A dialog box appears, give the value after which you want to highlight the values which are greater than the given value by Red Colour.

We are giving a value of 95, to track only the costly vegetables

Now click on OK

After clicking on OK, you now have the cells highlighted by Red which have a price more than 95.

See, how easily you can identify the costly vegetables and also identify the trends i.e. over which months the vegetables were costly. 

Here with our example data, we can easily determine that onion and garlic were the two most costly vegetables over this period.

So, now you have an idea of how to visualise and analyse data to identify patterns and trends.

You can also identify critical faults within your data. If something blows up, you could easily determine what it is.

You can also apply other methods to conditionally format your data in the way you want. There are many other conditions to put on and many other types of visualizations you can opt for to efficiently analyse the data.

Some more options you can opt for are:

These options present you more powerful and visualization tools. Try these yourself to find which one suits you the best.

An example to show you how the other options look visually.

Personally, I like the bars view.

Custom Number Formatting

In Excel, you can format the style of numbers as per the data requirements of your workbook.

If you want your numbers to have a hyphen after every two digits (e.g. 00-00-00), you can use the inbuilt tool provided by Excel to customize the format of your numbers.

Create a custom number format

1. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list   .

2. In the Format Cells dialog box, under Category, click Custom.
3. In the Type list, select the built-in format that most resembles the one that you want to create. For example, 0.00.

The number format that you select appears in the Type box.

4. In the Type box, modify the number format codes to create the exact format that you want. For example, 000-000-0000.

Your changes will not alter the built-in format. Instead, your changes create a new custom number format.

5. When you have finished, click OK.

Apply a custom number format

1. Select the cell or range of cells that you want to format.
2. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list  .
3. In the Format Cells dialog box, under Category, click Custom.
4. At the bottom of the Type list, select the built-in format that you just created. For example, 000-000-0000.

The number format that you select appears in the Type box.

5. Click OK.

Delete a custom number format

1. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list  .
2. In the Format Cells dialog box, under Category, click Custom.
3. In the Type list, select the custom number format, and then click Delete.

Notes: 

• Built-in number formats cannot be deleted.
• Any cells in the workbook that were formatted with the deleted custom format will be displayed in the default General format.

Hope you have found this article to be very useful. We usually post these types of educational articles for your growth and future. 

We believe in you!