Format by Rules in Fabric IQ Planning - Conditional Formatting with IF Logic

A planning grid with three hundred cells and no visual cues is a grid nobody reads properly. The finance manager scans the first screen, nods, and misses the one cost centre that has blown through its budget by 40 percent. We see this on nearly every planning engagement we start. The numbers are all there, but the eye has nowhere to land.

Conditional formatting is the obvious fix, and most planning tools have some version of it. In Microsoft Fabric IQ planning sheets, the option that does the most useful work is Format by rules, which uses IF conditions to decide when and how a cell should look. The simpler options are fine if you only need one threshold. Once you need two or three conditions working together, the rules option is where things get interesting, and where it is easy to build something confusing.

This post walks through how the rules work, what we have found useful on real planning work, and where we think you should be careful.

What you need before you start

You need access to a planning sheet, and you need to have already created a conditional formatting rule. Format by rules is a way of configuring a rule, not a separate feature that sits outside the rules framework. If you are starting from nothing, create a basic rule first so you understand the structure, then switch it to rules-based logic once you know what you are trying to achieve.

Switching to IF conditions

In the Format by dropdown, select Rules (If conditions). The configuration panel then changes to show two main sections: Style, which controls how the cell looks when the rule fires, and Conditions, which controls when it fires.

It is a small step, but it matters. The point of this option is that you are describing logic, not just a colour threshold. Once you think of it as a sentence ("if actuals are over budget by more than ten percent, and the cost centre is marked as discretionary, then show a red icon"), the interface makes much more sense.

Styling what the rule does

The Style section offers several ways to make a matching cell stand out:

• Font style, meaning bold, italic or underline
• Font colour
• Cell background highlight
• Borders
• Icons or text indicators
• Hide values, which masks the number when the condition is met

You can align icons within cells, display only the icon or text without the underlying value, and apply formatting to charts or labels where that makes sense.

My honest view on styling is this: fewer is better. Most planning sheets we have seen with conditional formatting are over-decorated. Every row has a background colour, half the cells have bold text, and the icons multiply until they mean nothing. Pick one or two visual signals per rule. An icon plus a font colour usually works better than a full background fill, and it keeps the sheet readable when it is printed or projected in a board meeting.

Pay attention to colour blindness too. Red and green alone is a poor choice because a meaningful share of men have some form of colour vision deficiency. Pair the colour with an icon or a text marker so the meaning survives if the colour does not come through.

Building the conditions

The Conditions section is where the real logic lives. Each condition has four parts:

• The measure or field you want to evaluate
• The operator, such as greater than, less than or equal to
• The condition type, which tells the rule where the comparison value comes from
• The value itself, or a reference to where the value lives

The condition types available are Number, Data selection, Value, Formula, and User selection. Here is how we think about them in practice:

Number is for fixed thresholds. "Variance is less than minus 50,000" is a number condition. Simple, predictable, and easy to explain to the next person who opens the file.

Data selection lets the rule reference another cell dynamically. This is useful when the threshold changes by row or by period, such as a target that lives in its own row of the planning sheet.

Value compares one measure or column against another. "Actuals is greater than Budget" is the typical example.

Formula lets you build an expression that combines measures and values. This is the most powerful option and the one that needs the most care. Check the expression syntax the interface expects before you rely on it, and test with a few known cells so you know the rule is firing where you think it is.

User selection applies formatting depending on something the user chooses at runtime. We have found this useful for scenario views, where the same sheet is shown with different assumptions. It also adds moving parts, so test it with the sheets people will actually use rather than just the one you built it on.

An example worth building

Here is a rule we would build for a typical cost-management planning sheet. The business wants to see any cost centre where actual spend is more than 10 percent over budget.

• Measure: Actuals
• Operator: Greater than
• Condition type: Formula
• Formula: Budget multiplied by 1.1

Style it with a red warning icon and bold font, and leave the cell background alone. Then add a second condition with AND logic if you only want the flag to fire for cost centres marked as discretionary, so that a bit of overspend on a fixed lease does not trigger the same alarm as an uncontrolled marketing spend.

That is two conditions, one style, and a result that tells the reader exactly where to look. It is far more useful than a sheet where every over-budget cell turns bright red.

Nested conditions and AND/OR logic

Selecting Add Condition lets you combine multiple conditions with AND or OR logic. This is where rules start to become genuinely useful for planning, because you can express business rules that cross dimensions. Flag any product line where margin falls below target and volume is growing. Highlight any region where forecast deviates from budget in either direction.

The trap is complexity. A rule with five nested conditions can be correct and still be impossible for the next analyst to understand. When we build these, we write the logic down in plain English in a note next to the sheet, or in the planning sheet documentation, so a future editor knows what the rule was meant to do. It sounds like admin work, and it is, but it saves a lot of confusion later.

Another practical point: when multiple rules apply to the same cell, which one wins? We would not assume you know the answer without testing it. Build a small test sheet with a cell that meets several conditions, and check the result before you roll the rules out to a real planning cycle.

Hide values is not a security control

This one needs its own paragraph because it is a common misunderstanding. The Hide values option in the Style section masks the number when a condition is met. That is a presentation control. It changes what someone sees on that sheet.

It does not protect the underlying data. Anyone with access to the model can still see the value through other views, through exports, or by changing the formatting themselves. If the numbers are sensitive, the protection needs to come from access control on the planning model, not from hiding digits in a grid. We have seen teams use Hide values to keep salary-related lines out of a shared view and then assume the data was secure. It was not.

Use it for tidying up a presentation, or for a view where you want the flag without the figure. Do not use it as a substitute for permissions.

Where it is still rough

Honest assessment time. The rules option is useful, but it has rough edges. The interface is sometimes slower than we would like when a sheet has many rules, and debugging why a rule has not fired can take longer than building it. When it does not fire, the most common causes we have found are a measure that is blank for that row, a period mismatch between the rule and the data, or a condition type that compares against the wrong thing. Check those first before you assume the rule itself is broken.

Also, the more logic you put into formatting, the more you are relying on formatting to carry business meaning. That is fine for a flag. It is risky if the only way a user knows a figure is wrong is a colour they might not notice or might not understand. Keep the important controls in the data itself, such as a check row or a variance column, so the sheet still makes sense when formatting is switched off.

Where to start

If you are new to this, start with one rule. A single over-budget flag with a clear icon will teach you more than a dozen rules you do not yet understand. Test it with real data, get feedback from the people who will read the sheet, then add a second rule when the first one is doing its job.

If your team is working through a Fabric IQ planning rollout and wants a second opinion on the model design or the rules, our Microsoft Fabric consultants have been through this set-up on a number of projects. For reporting that sits on top of the same data, our Power BI consultants can help you decide where the logic should live.

The original Microsoft article is Configure rules using IF conditions in conditional formatting on Microsoft Learn, and it is worth working through alongside a test sheet.