Want quick insights into your data? Conditional Formatting can help you make sense of your data by highlighting trends or patterns in your data based on rules that you create.
You can apply conditional formatting to a range of cells, an Excel table, or even a PivotTable report! You can even apply multiple conditional formats to the same cells.
Option A: Use the Quick Analysis button or CTRL+Q to display the pop-up menu.
1. To see a live preview of your data with formatting applied, hover over the different options in the Formatting tab. The formatting options differ depending on the type of data you have selected, for example, if your data contains only text as opposed to text and numbers.
2. Click on the formatting option you want.
Note: If you change the data in a cell, so that it satisfies the rules, the format automatically changes too.
Option B: If you want more control over what type and when conditional formatting applies, you can create your own rule. Use this option to format blank cells or error cells.
1. Go to Home > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog box, select a Rule Type and then Edit the Rule Description to set your specific parameters.
In the following example, we have used a formula to highlight rows where our Actuals for Costs exceeded our Budget, by looking at whether the Variation column (column G) is negative.
The rows where the formula G<0 is true, are highlighted.
The result of conditional formatting is below:
To present your data more clearly, you can also sort or filter your data based on conditional formatting.