Cell and column formatting

Cell formatting paints the cells of one Table or Matrix column: a background colour, data bars or icons. Column formatting tints a whole column with a colour from the report's chart palette. Both sections sit on the Visual tab of a DataPrism report and save with it.

Paint cells by a rule

1. Select the visual and open the Visual tab. You need edit mode for this; see Format a visual for the pane and how changes apply.
2. Under Cell formatting, choose the column in Column.
3. Set Format to Background colour, Data bars or Icons. Choosing a kind gives the column a default format of that kind right away; None deletes the column's format.
4. Select Configure to open the settings window for that format.
5. Set up the format, then select Apply to commit it.

The window edits a draft. Nothing lands on the visual until you select Apply; Cancel throws the draft away. Dismissing the window another way, with Escape or its close control, keeps the draft, and the section then reads "Unapplied changes. Reopen the configuration to apply them." until you reopen the window. Clicking the backdrop does not close the window, and choosing a different column or visual abandons the draft.

Configure stays disabled until you pick a format. Reset to defaults returns the settings in the window to their defaults after asking for confirmation; the question names the format, for example "Reset background colour to defaults?", and explains: "Every change made in this editor is discarded and the default settings are restored. The column keeps its current formatting until you press Apply." The reset control stays disabled while the format sits at its defaults.

Set the scale

Background colour and Data bars paint against a scale with two ends:

• Minimum and Maximum each read Lowest value, Highest value or Number. The hint reads "Where the scale starts and ends, and what each end reads from."
• An automatic end reads the values of the cells on screen. A paged table shows different cells on the next page, so the paint can move with them. Set both ends to Number for a paint that stays put. For paging itself, see Visuals.
• The Preview rail shows a sample strip labelled Low, Mid and High, with a caption that matches the kind: "Low numbers get one colour, high numbers another.", "Bigger numbers get a longer bar." or "A cell takes the first icon its value matches." Moving the ends moves the preview the same way as the visual.
• On a Matrix, Include totals under Applies to covers the totals as well as the values.

Background colour: a gradient or rules

A new background format starts as a Gradient from Surface to Primary. Under Fill, Format style offers Gradient or Rules ("A ramp between two ends, or a list of thresholds you set yourself."):

• Gradient draws a ramp between the two ends. Add a middle colour adds a Centre colour to the ramp, starting from the neutral surface colour.
• Rules uses your own list of thresholds. A fresh list starts from three percentage bands: 0 to 33, 33 to 67 and 67 to 100, in the error, warning and success colours. Add a row with New rule, and remove one with the remove control on its row. While the list is empty it reads "No rules yet, so nothing is painted. Add one to get started."
• Rules are tested from top to bottom, and a cell takes the first row it matches.

Data bars

• Under Bars, Positive, Negative and Axis set three colours: the positive bar, the negative bar and the axis. The Axis swatch carries the hint "The axis only appears when the range crosses zero; otherwise bars grow from whichever end sits closest to it."
• Bar direction picks Left to right (the default) or Right to left, and Show bar only draws just the bars, without the values.
• A new data bar format uses Primary for the positive bar, Error for the negative bar and Foreground for the axis.

Icons

• Icons draws one icon per cell from a set. Style picks the set: Arrows, Trends, Lights, Flags or Checks; Icon layout places the icon: Left, Right or Icon only. A new icon format starts with Arrows and Left, and its rules start from the same three default bands as a background.
• Each threshold rule picks the icon for its range. Switching sets keeps your ranges: every rule is remapped to the icon in the same position in the new set.
• Rules keep their order: "Tested top to bottom; a cell takes the first row it matches."

Set a threshold

Each rule row reads as one line: the rule's colour, the operator, the value, the unit and the remove control.

• The operator menu offers At least, More than, At most, Less than and Exactly; the closed row shows the matching symbol.
• A rule compares against one end or a range. The upper end offers No upper bound, Less than or At most. Choosing an upper bound fills its value in from the lower one, and an open-ended rule keeps its greyed field in place.
• A # or % switch, labelled Number and Percent, sets whether a threshold is a plain number or a percentage.

Tint a column

Column formatting sits below Cell formatting for the same Table and Matrix visuals, and tints one column with a colour from the report's chart palette.

1. Choose the column in Column.
2. Pick a colour in Colour. The swatches are the report's chart colours, labelled Chart 1 to Chart 12, so the tint follows the report theme.
3. Choose where the tint lands: Apply to header, Apply to values, Apply to totals and, on a Matrix, Apply to subtotals. Until you pick a colour the toggles stay disabled and say "Pick a colour first".
4. Clear the colour with the reset control on the swatch to remove the column's format.

The totals toggles need something to paint. Until the row or column grand total is on, Apply to totals is disabled and says "Turn on a row or column grand total in the Matrix section" on a Matrix, or "Turn on Grand total in the Table section" on a Table. Apply to subtotals needs the row or column subtotals switched on and says "Turn on row or column subtotals in the Matrix section".

How the settings behave

• Both sections belong to the visual, so two Table tiles can carry different rules, and the settings save with the report. See Build a report.
• Formats follow the column: when a column leaves the well, its formats are dropped the next time you change a format in either section.
• Only Table and Matrix carry these sections; other visual types hide them.

Questions, answered

Why is the Configure button disabled?

Configure stays disabled until a format is chosen. Set Format to Background colour, Data bars or Icons first; None has nothing to configure.

I closed the settings window without pressing Apply. Are my changes lost?

No, unless you selected Cancel. Dismissing the window with Escape or its close control keeps the draft, and the section reads "Unapplied changes. Reopen the configuration to apply them." until you reopen it and select Apply. Cancel throws the draft away, and choosing a different column or visual abandons it.

Why does the paint change between pages of a long table?

The scale ends are reading the cells on screen, and a new page brings new cells. Set both ends, Minimum and Maximum, to Number so every page paints against the same ends.

How do I paint the totals as well as the values?

On a Matrix, turn on Include totals under Applies to in the settings window. With Column formatting, use Apply to totals, and Apply to subtotals for subtotals, after the matching total is switched on in the Matrix or Table section.

Why is Apply to totals greyed out?

Its target is not visible yet. Turn on the row or column grand total in the Matrix section, or Grand total in the Table section, and the toggle becomes available.

Why did my rules change when I switched the icon set?

Nothing is lost. Every rule keeps its range and is remapped to the icon in the same position in the new set, so your thresholds carry over between Arrows, Trends, Lights, Flags and Checks.

What does "No upper bound" mean?

The rule matches every value from its lower end upwards. Give it a top end by choosing Less than or At most; its value is filled in from the lower one.

How do I remove a format from a column?

For cell formatting, set Format to None: the column's format is deleted and there is nothing left to configure. For the tint, clear the colour with the reset control on the Colour swatch; a column without a colour has no column format.

Why does my column colour look different on another theme?

The swatches are theme colours, so they resolve for the light or dark theme the report is shown in. Column formatting accepts colours from the report's chart palette only.

Why can't I find Cell formatting on my bar chart?

These sections exist on Table and Matrix visuals only. On every other visual type they are hidden.

I removed the column from my table. Where did its colours go?

The column left the well, so its format is on its way out: formats are stored per column, and they are dropped the next time you change a format in either section.