Using conditional formatting with custom Excel formulas

Conditional formatting is an effective Microsoft Excel feature that allows you to highlight important information, for example; the ability to find duplicate values within your spreadsheet. You can create your own rule by applying conditional formatting to individual cells or a range of cells.

When you have selected the data you want to format, you can easily change the appearance of how your information is presented to help you easily analyze your data.

You are welcome to download the workbook to practice.

Applies to: Microsoft Excel 2010 and 2013

The format window allows you to change how the information in the cell is presented, without changing the information in the cell itself. However, you can create your own custom rules that go beyond the standard formatting options.

In this example, we have a number of cells containing values in excess of one million. We’ll help you create a rule that will change the appearance of how the values are presented.

1. Open the Excel workbook and select cells B2:B5

Excel on steriods_custom formats_june 2015_2

2. Right click on the selected cells and select ‘Format Cells’ (alternatively, use the keyboard shortcut Ctrl + 1)

Excel on steriods_custom formats_june 2015_3

3. In the numbers tab, under the Category group, select Custom

Excel on steriods_custom formats_june 2015_4

4. In the Type box, delete the current values and enter the following formula

#,###,, “M” and click OK

NB: If you have any difficulty with the above formula, please try the following one: # ###  “M”

Excel on steriods_custom formats_june 2015_5A

5. The values in the cells are automatically rounded according to their value, along with the letter ‘M’ to signify a million.

Excel on steriods_custom formats_june 2015_6

Now that you have applied your own conditional formatting rule, you can customize your spreadsheets to help you visually analyse data, and identify patterns and trends.