1. Data Grid docs
  2. Columns and formatting

Conditional formatting

Data bars, three-colour scales and value rules that colour cells, scaled over every matching record and following the light or dark theme.

Conditional formatting makes numbers readable at a glance: a bar in each cell shows its size, a colour scale shows where it sits between the lowest and highest value, and value rules flag the cells that need attention. Bars and scales measure against every matching record, so a cell looks the same whether it is the first row or the millionth.

Open the columns example: bars, a colour scale and value rules

In the grid

Choose Conditional formatting in a column's menu. Add a rule:

  • Data bar: a bar from zero to the value. Negative values extend the other way, in their own colour.
  • Colour scale: a background from the low colour through the middle to the high colour.
  • Value rule: a condition (greater than, at least, less than, at most, between, equals, not equal, contains) and a style: Green fill, Red fill, Amber fill, Blue fill, Bold text or a Custom colour.

A column can have several rules. The first value rule that matches colours the cell; a bar draws on top of the fill.

From code

Rules belong to a column: set them with setColumn(field, { rules }), or in state.columns[].rules. openDialog('conditionalFormatting', { field }) opens the grid's own dialog for a column.

RuleFields
{ type: 'bar', color? }color: any CSS colour; the theme's bar colour by default
{ type: 'scale', colors? }colors: [low, mid, high]; the theme's colours by default
{ type: 'value', op, value, value2?, style?, color?, background? }op: gt, gte, lt, lte, between, equal, notEqual, contains; style: good, bad, warn, info, strong; or your own color and background

The style presets follow the light and dark themes. Number and date columns compare numbers; other columns compare text, ignoring case.

JavaScript

import { mountTable } from '@8sheets/data-grid';

const grid = await mountTable(document.querySelector('#orders'), { title: 'Orders', data: orders });

await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
await grid.setColumn('Units', { rules: [{ type: 'scale', colors: ['#f8d7da', '#fff3cd', '#d1e7dd'] }] });
await grid.setColumn('Margin', {
  rules: [
    { type: 'value', op: 'lt', value: 0, style: 'bad' },
    { type: 'value', op: 'gt', value: 0.4, style: 'good' },
  ],
});
await grid.setColumn('Status', { rules: [{ type: 'value', op: 'equal', value: 'Overdue', style: 'warn' }] });

// Or let the user pick, in the grid's own dialog:
await grid.openDialog('conditionalFormatting', { field: 'Revenue' });

React

import { DataGrid } from '@8sheets/data-grid-react';

async function applyRules(grid) {
  await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
  await grid.setColumn('Margin', {
    rules: [
      { type: 'value', op: 'lt', value: 0, style: 'bad' },
      { type: 'value', op: 'gt', value: 0.4, style: 'good' },
    ],
  });
}

export function Orders({ options }) {
  return <div style={{ height: 480 }}><DataGrid options={options} onReady={applyRules} /></div>;
}

Vue

<script setup>
import { DataGrid } from '@8sheets/data-grid-vue';

defineProps({ options: Object });
async function applyRules(grid) {
  await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
  await grid.setColumn('Margin', {
    rules: [
      { type: 'value', op: 'lt', value: 0, style: 'bad' },
      { type: 'value', op: 'gt', value: 0.4, style: 'good' },
    ],
  });
}
</script>

<template>
  <div style="height: 480px"><DataGrid :options="options" @ready="applyRules" /></div>
</template>

Angular

import { Component, Input } from '@angular/core';
import { DataGridComponent, DataGridOptions, MountedDataGrid } from '@8sheets/data-grid-angular';

@Component({
  selector: 'app-orders',
  imports: [DataGridComponent],
  template: `<eight-data-grid [options]="options" (ready)="applyRules($event)" style="height: 480px" />`,
})
export class Orders {
  @Input() options: DataGridOptions = {};

  async applyRules(grid: MountedDataGrid) {
    await grid.setColumn('Revenue', { rules: [{ type: 'bar' }] });
    await grid.setColumn('Margin', {
      rules: [
        { type: 'value', op: 'lt', value: 0, style: 'bad' },
        { type: 'value', op: 'gt', value: 0.4, style: 'good' },
      ],
    });
  }
}

With a server source

Bars and colour scales need each column's smallest and largest value over the whole result: return them in TablePage.stats as { "Revenue": { "min": 0, "max": 98250 } }. Value rules compare the raw values of each row, so include raw in your rows. tableQueryService does both.

Limits

  • Only cells on screen are styled, and formatting doesn't change the data. A flat export holds no formatting; an XLSX export with layout: true turns the rules into Excel's own conditional formats (see Export).
  • Bars and scales apply to numbers; a text cell in a number column gets none.
  • Rules are not read from Excel files' own conditional formatting.