Compare Values with Data Bars, Color Scales and Icons in PlanMaker

Make a compact five-team progress report with three different visual encodings, while keeping the actual quantities visible. A data bar conveys relative size, a color scale conveys a gradient, and an icon set groups values into bands. None should silently become a pass/fail policy.

Prepare a repeatable report

At A1:E6 enter the following grid. These are progress points on a 0–100 scale, stored as ordinary numbers rather than fractions formatted as percentages.

Team	Bars	Scale	Icons	Written status
Amber	0	0	0	Not started
Birch	25	25	25	In progress
Cedar	50	50	50	In progress
Dune	75	75	75	In progress
Elm	100	100	100	Complete

For repeatable updates, type the numbers only in B2:B6. Set C2 to =B2 and D2 to =B2, then fill those formulas through row 6. The table above shows expected displayed values. Keep E as explicit words for this small exercise and update them if the meaning changes. Each numeric column totals 250; the middle value is 50. Save progress-before.pmdx, then a separate working copy.

Apply three separate rules

1. Select B2:B6. Choose Home → Format → Conditional formatting → New rule.
2. Set Type to Format all cells based on their values and Format style to Data bars. Choose a readable available appearance and confirm.
3. Select C2:C6, create another rule of the same Type, and choose 2-color scale. Use distinguishable endpoint colors. For this fixture the minimum is 0 and maximum is 100; inspect the rule’s actual endpoint settings before interpreting the colors.
4. Select D2:D6 and create an Icon sets rule under the same Type. Select a three-icon set and inspect its direction and threshold settings. Keep the numeric values visible so a reader can still compare 25 with 50.
5. Open Conditional formatting → Manage rules and set Source to Current worksheet. Verify the three Apply to ranges: B2:B6, C2:C6 and D2:D6. None should include a heading or another encoding’s column.

The quick Data bars, Color scales and Icon sets submenus also exist, but New rule exposes the rule type and makes the three independent ranges easier to inspect. Avoid stacking all three encodings on one range while learning: overlapping appearance changes make mistakes harder to isolate.

What can be checked without guessing the defaults

B6 should represent the largest magnitude and B2 the smallest in the positive fixture. Intermediate bars should preserve the ordering 25 < 50 < 75. Do not measure a 25-point bar and assert that it occupies exactly 25 percent of cell width: minimum bar length and presentation options can affect pixels.

For a relative two-color scale, C2 and C6 anchor the low and high endpoint colors; C4 lies halfway through the numeric interval. Reversing endpoint colors reverses their meaning without changing any data. Label the report’s color direction in words.

The manual illustrates a three-icon scheme as lower, middle and upper bands. It does not settle every preset’s boundary comparison or threshold type. Record the thresholds actually displayed in your rule before asserting an icon for a boundary value. In particular, do not equate “upper band” with “complete.” E5 correctly says In progress at 75 even if an icon happens to be green. For a business threshold with an exact inclusive/exclusive boundary, use a separately specified threshold rule and test the boundary.

Source change and range growth

Change B3 from 25 to 60. C3/D3 should also display 60. Each numeric column now totals 285, and Birch must rank above Cedar’s 50 but below Dune’s 75. Confirm that bars and the relative gradient reflect that order. Any change of icon must agree with the thresholds you recorded, rather than an assumed default.

Add Fir at row 7 with B7 = 80, C7 = =B7, D7 = =B7, and E7 = In progress. In Manage rules select each rule and extend Apply to to row 7. Recheck all three ranges; do not infer automatic expansion from an adjacent formatted cell. With the changed Birch value retained, each column totals 365. The endpoints remain 0 and 100.

Save this completed six-team report as progress-formatted-expanded.pmdx, then close and reopen it. Confirm the total is 365 in each numeric column, C2:D7 retain the reference formulas, all three Apply to ranges end at row 7, and the recorded icon thresholds remain intact. Keep this saved formatted report unchanged for recovery; perform the following scale and edge-case experiments in separate disposable copies.

For a scale-sensitivity experiment, make a separate copy of the completed formatted report and change B6 from 100 to 200. Each column totals 465. A relative scale now compares the other values with a maximum of 200, so unchanged values can look less prominent. That makes relative scales suitable for within-report comparison, but potentially misleading for month-to-month dashboards. Inspect available bound controls and use explicitly documented, tested limits for cross-report comparisons; this tutorial does not claim a specific unobserved numeric-bound dialog layout.

Advanced edge-case audit and recovery

For the edge-case audit, create another disposable copy of the completed formatted report. Clear A7:E7 to remove Fir, and shorten all three rules’ Apply to ranges to rows 2:6. Restore B3 = 25 and B6 = 100, keep B5 = 75, then change B2 to -20 and clear B4. Put Missing in E4. B2:B6 now has four numeric entries totaling 180 and one blank. C4/D4 still contain reference formulas; their evaluated values may differ from a genuinely empty cell. Delete those formulas in this disposable copy if you intend to compare true blanks across columns.

Inspect how your chosen bars handle negatives, whether the scale includes -20 as its low bound, and what happens to blanks and icon thresholds. These are native acceptance checks, not claimed observed results. Keep the minus sign and an explicit Missing label visible, so neither zero nor a colored cell is mistaken for missing data. If your installed rule cannot make the distinction clear, retain the raw values and omit that visual encoding from the affected range.

To return to the completed controlled report, discard the disposable experiment and reopen progress-formatted-expanded.pmdx; verify six teams, three row-7 rule ranges and total 365. Reopening progress-before.pmdx instead restarts from the unformatted inputs and does not recover the completed rules. Manage rules → Edit changes the entire rule range; Delete likewise removes the selected rule from every cell it covers. Check Apply to before either operation. Do not delete a shared report-wide rule to clear just one cell.

Scope, verification and sources

PlanMaker 2026 paid desktop, English ribbon interface; documentation-backed instructions, native application execution pending. NX builds require matching controls; compatibility with every NX build is not established here. FreeOffice 2024 and Android/iOS are outside this tutorial scope.

The fixture values and expected arithmetic were checked independently. The new application steps have not been executed in a native PlanMaker GUI. No native screenshot is supplied; neither calculated fixtures nor website rendering prove application behavior. Check the stated acceptance results in your installed build before relying on the workflow.

• Creating new formatting rules
• Types of conditional formatting
• Managing formatting rules
• Welcome