onlyoffice · Conditional formatting · Intermediate
Manage Conditional Rules and Visual Scales in ONLYOFFICE
Maintain overlapping conditional rules, fixed data bars, color scales and icon thresholds as a report grows.
Quick answer
Keep bars, scales and icons in separate columns with visible numbers. Fix numeric endpoints and threshold operators, inspect overlapping rules in the rule manager, and explicitly extend every target range when you add a record.
Version and evidence
ONLYOFFICE Desktop Editors 10.0.0 documentation baseline, English desktop interface on Windows/Linux; official help and desktop changelog checked 2026-10-19. No native application execution or screenshots were obtained. Exact installed build, OS behavior and saved-file round trips remain manual verification requirements; hosted and mobile behavior is outside scope. The steps below are a documented procedure with independently calculated checkpoints, not a native test report. Use an editable .xlsx practice copy. Do not treat the online help as proof of every older desktop build.
A conditional-formatting report needs stable meanings as values change. Build three parallel visual views of the same scores, then maintain an overlapping exception rule. The numbers remain visible throughout so a color or icon never becomes the only way to read the report.
Prepare a reproducible score report
Create visual-rules-practice.xlsx and name its sheet Scores. In A1:E1, enter Team, Score, Bar, Scale, Icon. Enter teams A, B, C, D, E in A2:A6 and numeric scores 0, 25, 50, 75, 100 in B2:B6. In C2 enter =$B2; copy it through C6, then copy C2:C6 to D2:E6. The locked column keeps all three display columns linked directly to B; the relative row changes as you copy down. Inspect C3, D3 and E3: each should contain =$B3. Each numeric column must read 0, 25, 50, 75, 100 and sum to 250.
Column B will hold overlapping fills, C data bars, D a color scale, and E icons. Separating them avoids a chart-like bar obscuring the fill you are trying to debug. Put this legend in G1: Scores out of 100; high is better. Add text labels for the icon bands in G2:G4: High: 75 or more, Middle: 50 to below 75, Low: below 50.
Apply magnitude rules
The entry point is Home → Conditional Formatting. Select C2:C6 and add Data Bars. In the rule editor use Number endpoints 0 and 100, retain visible numbers, and choose a legible bar color. For D2:D6 add a three-color scale; edit its low, middle and high point types to Number with values 0, 50, 100. Choose distinct low/middle/high colors. These fixed anchors make reports comparable across future snapshots.
Select E2:E6 and add a three-icon set. Edit it to Number thresholds: top icon greater than or equal to 75, middle icon greater than or equal to 50, lowest icon below 50. Keep numbers visible and do not reverse the icon order. The expected classifications are Low, Low, Middle, High, High. Pay particular attention to 50 and 75: a strict greater-than operator would place these boundary values in the wrong band.
The checks are numerical, not pixel measurements. Score 0 should be at the low end, 50 at the midpoint, and 100 at the high end. Do not assert an exact bar width from a screenshot; cell width, theme and renderer can change it.
Manage an overlapping exception
On B2:B6 create a Value is rule greater than or equal to 50 with a pale blue fill. Add another Value is rule greater than or equal to 75 with an orange fill. Open Manage Rule, show This worksheet, and inspect both ranges. Move the 75 rule above the 50 rule with the precedence arrows. The intended outcome is orange for B5:B6 and blue for B4, while B2:B3 have neither fill.
Inspect the overlap in B5, where both conditions are true. The documented top-to-bottom precedence controls the order, but this guide does not claim a Stop If True option. If your installed build renders the conflicting fills differently, record that result. A robust alternative is to make the first band non-overlapping, using a formula condition =AND(B2>=50,B2<75) for B2:B6, and keep the 75 rule for the high band. Validate row-relative evaluation before deploying that alternative.
Change values and extend the report
On Scores, change B3 from 25 to 80. C3:E3 should follow it because they contain formulas. The high-band count becomes three, the middle count one, and the low count one. Each numeric column now totals 305.
Add team F in A7 and score 60 in B7. Copy C6:E6 down to C7:E7; inspect that C7, D7 and E7 each contain =$B7. Use Manage Rule → This worksheet and extend every relevant Apply to range from row 6 to row 7. There are five rules in the intended final arrangement: two in B, one each in C, D and E. Do not assume adding a row extended them.
Final values in B2:B7 are 0, 80, 50, 75, 100, 60, summing to 365. The icon counts should be three High, two Middle and one Low. B7 should receive the middle-band blue fill. The fixed color scale still places 50 at its midpoint; the added 60 does not redefine that anchor.
Save and check the maintained rules
Save, close and reopen visual-rules-practice.xlsx. Activate Scores. Recheck B3, row 7, the five rules, their precedence, explicit endpoints, operators and target ranges. Verify totals from the values, not from color alone. Persistence and visual appearance require this manual check; no native screenshot or round-trip test is claimed here.
Advanced case and troubleshooting
Create visual-rules-threshold-test.xlsx with Save As. Change B3 to 49, then 50, then 75, inspecting E3 at each step. The bands should be Low, Middle, High. Restore 80 if you keep the copy. This checks operators more directly than using distant values.
If all icons look alike, inspect Number versus Percent or Percentile and the threshold order. Those types answer different questions. If only row 7 lacks formatting, inspect Apply to instead of recreating the rule. If the overlap is unclear, inspect the top rule first and simplify conflicting formatting. Keep values and a written legend visible for accessibility. For a simpler row-status example, see conditional-formatting basics.