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
2. Right click on the selected cells and select ‘Format Cells’ (alternatively, use the keyboard shortcut Ctrl + 1)
3. In the numbers tab, under the Category group, select Custom
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”
5. The values in the cells are automatically rounded according to their value, along with the letter ‘M’ to signify a million.
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.