Custom Format can be used to format numbers, text, dates and time. The technique can be extended to charts as well where the custom format feature can be used to color the chart axis and chart labels.
In this example, let’s say we wanted to show the sales numbers for a particular duration as a chart. We would, in addition, like to have some kind of separation between what is considered below, average and above average performance. Custom format can help us achieve this in no time. Let’s take the case of this simple chart.
One at a time, right click the chart axis and choose the Number tab (you can also use Ctrl+1 shortcut key). Enter the custom format as:
The first part tells Excel to format any number more than 80 using the color with index number 41, not to format any number less than 40 and anything else (which means numbers between 40 and 80, both included) in Red. You can change both the colors used and the conditions as needed. You may want to keep a proper contrast between the colors without going over the top !
So if you want to highlight a number between a certain range (say between 50 and 80) or say more than 100, you can change the custom format accordingly.
Once we are done, here are the results:
You can download a sample worksheet with example of using custom format on chart axis and labels here or click on the button below: