I received a comment from a reader recently on my post, Display of SSAS KPI Indicators in Excel, which asked:
Would it be possible to use the status to configure "conditional formatting" on the value column?
To which the response is, absolutely! Here’s how I set it up in a simple spreadsheet, created from the Adventure Works 2008 cube project:
Let’s say our business rule is: Give the Revenue column a back color of yellow if the Status is not satisfactory.
Formula to Format Cells
1. Select cell B4, which is our first Actual value.
2. Select Conditional Formatting > Highlight Cells Rules > More Rules.
3. In the New Formatting Rule Dialog box:
Apply Rule To: All cells showing “Revenue” values. This is very important…otherwise it’ll only do the one cell which would be difficult to maintain.
Select a Rule Type: Choose the last option, Use a formula to determine which cells to format.
Format values where this formula is true: D4 < 1 (i.e., the Status column is not “green” or “satisfactory”). Make sure there’s no $ signs in this formula. The $ signs make it an absolute reference, and since we want this to apply to all cells showing “Revenue” values, we want it to be a relative reference.
Format: Fill of yellow (or whatever formatting you desire).
And that’s it! You can use this formula-driven method to handle several different conditional formatting options in Excel.
Editing an Existing Rule
To modify the above rule after it’s set up:
1. Go to Conditional Formatting > Manage Rules.
2. Highlight the correct rule and choose Edit Rule. You’ll return to the same window you were working in previously.
