In Excel sometimes you need to format you cell in a way that is outside of the default and standard formatting provided. However, with the help of the custom formats feature, there’s no end to the possibilities of how you can format your data!
Custom formats allows you to define the way that numbers, text, date or time are shown on the screen. What it doesn’t do is change the actual value that is in the cell, but rather, it shows something different on top which ‘masks’ the real value in the cell. You can apply a custom format to any cell or a group of cells and here’s how to do it:
Creating your custom format
Custom formats are created using one or more ‘expressions’ constructed from a number of available operators. You can read about the operators used for creating custom formats in the Microsoft Help page here and it’s worth taking time at this stage to familiarise yourself with what they are.
The basic operators that you can use are:
- 0 (zero) = A ‘digit’ placeholder that adds zeros to the format.
- # = A ‘digit’ placeholder
- ? = A ‘digit’ placeholder that leaves a space for additional zeros if required but does not them unless necessary.
- % = Turns the value into a percentage.
- , (comma) = A thousands separator.
- [red] = defines the colour of the data if it meets your formula. You can also use [green], [blue] or [yellow]
Composition of your custom format
In order to create a custom format, you need to understand how excel looks at numbers. They are broken down into four sections:
Before creating your custom format it can help to think through carefully what you are trying to achieve and map it out on a piece of paper which can help with the construction process.
In the scenario below we want to a) identify any numbers in our database that are out of stock b) display wording when stock is over subscribed so that we know to order more as a priority.
So we use the format:
#,###;[Red]-#” ORDER STOCK”;”out of stock”
- Expression 1 (#,###) tells us the any positive numbers remain as the default, using a comma to separate any thousands
- Expression 2 ([Red]-#” ORDER STOCK”) tells us to colour any minus numbers in red and add the words ‘order stock’
- Expression 3 “out of stock” tells us to show these words when the value is zero
Using the custom formats feature
- Highlight the cell or cells that you want to format
- From the main ribbon select Home tab > Numbers group > click the icon in the bottom right hand corner of the group. This displays the Format Cells dialog box
- From the categories on the left hand side select Custom. You’ll then see various options to choose from.
- You may choose one of the preset custom formats (of which there are many) or alternatively you may create your own by constructing a formula in the ‘type’ text box (you can see our formula typed into the text box in the example below
- When you are happy, click ‘ok’ and you’ll see your highlighted cells are now showing the new format.
In part 2 of this article we’ll look at displaying customised dates and times in your cells and identify the operators needed to do this.