1---2title: "Conditional formatting and data validation"3sidebar_position: 74---56# Conditional formatting and data validation78Rules make cells show and check what they hold, as Sheets' do.9Conditional formatting colors a cell by its value, or draws a bar or an10icon in it; data validation limits what a cell takes, and gives it a11dropdown or a checkbox. Both only change how cells look and what they12accept, never a value already there.1314151617## Conditional formatting1819Select the cells, then Format > Conditional formatting. The sheet's20rules open in a panel at the right of the grid, as Sheets' sidebar:2122232425- **Add a rule**: `+ Add rule` makes one over the selection and opens26 its form. Up and Down pick a line, Left and Right change a choice,27 Space flips a check, text lines are typed into; Enter saves and Esc28 goes back without saving.29- **Change one**: Enter edits the highlighted rule, Del removes it, and30 Shift+Up and Shift+Down move it up or down the list.31- **Take cells out of every rule**: Format > Clear conditional formats32 on the selection.3334Every change is an undo step. A rule applies to ranges (`A2:A100`, or35several: `A2:A9,C2:C9`), and is one of four kinds:3637| Rule | Does | Shows |38|---|---|---|39| Single color | Formats the cells that meet a [condition](#conditions) | A text color, a fill, and bold, italic, underline or strikethrough |40| Color scale | Shades every number from its [lowest point to its highest](#points) | A fill along two or three colors |41| Data bar | Draws a bar across every number, as long as the number is far from the shortest point to the longest | A bar in one of the named colors; the text over it in reverse video. Show bar only hides the numbers |42| Icon set | Puts an icon at the left of every number by the thresholds it reaches | Arrows (3, 4 or 5), circles filling up (3, 4 or 5), symbols (3) or rating bars (4 or 5), the lowest red, the highest green, those between yellow (rating bars blue). Reverse icons turns them around; Show icon only hides the numbers |4344Data bars and icons are characters (eighth blocks, arrows, circles), so45they read without color as they do in it.4647### Conditions4849A single-color rule formats the cells that meet its condition; a value50in it may be a formula (`=B2`).5152| Condition | A cell meets it when |53|---|---|54| Is empty, is not empty | It's blank, or not |55| Text contains, does not contain, starts with, ends with, is exactly | Its text does, ignoring case |56| Date is, is before, is after | Its date is, is before or is after a date or a [period](#dates-and-periods) |57| Greater than, greater than or equal to, less than, less than or equal to, is equal to, is not equal to, is between, is not between | Its number compares so |58| Custom formula is | The formula is TRUE for it |59| Top values, bottom values | It's among the highest or lowest 1 to 1000 of the rule's cells, ties included |60| Top percent, bottom percent | It's in the highest or lowest percent of them |61| Above average, below average | It's above or below their average |62| Duplicate values, unique values | Another of them holds the same (text ignoring case), or none does |6364The last four rows are Excel's, and compare a cell with the rest of the65rule's cells; the others are Sheets'. A custom formula (`=$C2>100`) is66written for the first cell of the first range: its relative references67move with each cell, as a copied formula's would.6869#### Dates and periods7071A date condition takes a date, or a period as Sheets and Excel name72them: today, tomorrow, yesterday, the past week, month or year, and73this, last or next week, month or year (weeks run Sunday to Saturday). A74date is in a period, before its first day or after its last.7576### Points7778A color scale runs from a minimum to a maximum, with an optional79midpoint, and a data bar from its shortest point to its longest. Each80point is the lowest or highest value, a number, a percent of the way81between them, or a percentile. A data bar's automatic points run from82zero (or the lowest number, when that's below zero) to the highest. An83icon set's thresholds are each a percent of the way from lowest to84highest, a number or a percentile.8586### Which rule wins, and colors8788Rules are tried in order, and the first that applies to a cell formats89it, so a rule higher in the list wins. The pointer and the selection90draw over a rule's colors; its text styles show through them.9192Colors are named (red, yellow, green, cyan, blue, magenta) rather than93picked from a color wheel, because each is one of the terminal's 16 ANSI94colors: the terminal's palette or the color scheme95([Themes](../terminal/themes.md)) decides what they look like, and a96fill's text is drawn in whichever of black or white reads on it. A text97color that wouldn't read on a rule's fill takes the fill's ink instead.98Color scales blend the scheme's colors (or the terminal's, as it reports99them) and pick readable text for each shade.100101Rules only change how cells look, so they may go on a pivot table's102results. They follow inserted and deleted rows and columns, and a rule103whose cells are all deleted goes.104105## Data validation106107Select the cells, then Data > Data validation: the sheet's validation108rules open in the same panel, and work as conditional formats' do.109Insert > Dropdown opens it with a dropdown for the selection, and110Insert > Checkbox makes the selection checkboxes at once.111112113114| Criteria | Cells may hold | Shows |115|---|---|---|116| Dropdown | One of the items typed (`Yes, No, Maybe`), ignoring case | The value on a chip, or ▾ at the right of the cell |117| Dropdown (from a range) | One of the values in a range, on any sheet (`Lists!A1:A20`) | A chip, or ▾ |118| Checkbox | TRUE or FALSE, or values of its own (`Yes` and `No`); a blank cell is unchecked | `[✓]` or `[ ]`, centered |119| Number | A number between, not between, equal to, greater than ... values | |120| Date | Any date, or one on, after, before, between ... dates | |121| Text length | Text whose length is between, less than ... | |122| Custom formula is | Anything for which the formula (`=B2<=C2`) is TRUE | |123124Blanks are always valid, and a formula is checked by the value it125computes. Each cell has one rule: a rule added over cells that had126another takes them from it. Validation can't go on a pivot table's127results, which take no entries. Data > Remove data validation takes the128selection out of every rule.129130### Dropdowns and checkboxes131132- **Pick an item**: Alt+Down, or a click on the ▾, opens a dropdown's133 list under the cell; type to search, and Enter enters the item as if134 typed. When the active cell has no dropdown, Alt+Down opens the135 column's filter.136- **Check a box**: Space, or a click on the box, checks or unchecks the137 selected checkboxes (all checked when the active one isn't, as in138 Sheets); Space anywhere else starts an entry as usual.139140A dropdown's Display is Sheets' too: Chip draws its value between141rounded ends in reverse video, with the ▾ inside (new dropdowns), Arrow142draws the ▾ at the cell's right, and Plain text neither. A checkbox's143Checked and Unchecked values, when set, are what checking and144unchecking enter instead of TRUE and FALSE; with only a checked value,145unchecking leaves the cell blank.146147### Rejected or warned148149Each rule either **rejects** an invalid entry or **shows a warning**.150Rejected, the entry stays open with Sheets' message on the context line151("Invalid entry in C2: Input must be a number between 0 and 80"), for152fixing or Esc. Warned, it goes in, and the cell's text gets a dotted153underline; on the active cell the context line says why ("Invalid:154Input must be ..."). A rule's help text, when it has one, replaces its155own words there.156157What validation checks:158159- **Typing, a dropdown's pick and macros' entries**, as they're160 entered, rejected or warned as above.161- **Pastes and fills** (Ctrl+V, paste values, text pasted from another162 program; Ctrl+D, Ctrl+R, Ctrl+Enter, the fill handle), after they're163 written, as in Sheets. When a cell's rule rejects what landed there,164 the whole paste or fill is undone and ERROR mode says which cell and165 why ("Paste undone: Invalid entry in B2: ..."); Ctrl+Enter keeps the166 entry open instead. When only rules that warn fail, it stays, the167 cells are marked, and the context line says how many ("2 cells168 invalid, first B2: ...").169- **Macros' pastes and fills** (`paste_text`, `fill`, `enter` with170 `fill=True`, and the paste and fill commands), the same way: a refused171 one is undone and stops the macro with the reason; what the macro did172 before it stays, and undoes with the rest of the macro as one step.173- **Imports** aren't checked; what they leave invalid is marked.174175Adding a checkbox or a dropdown changes no cells, so on a protected range176it doesn't ask first (on a protected sheet it does); checking a box or177picking an item asks as typing does ([protection](notes-protection.md#protected-sheets-and-ranges)).178179## Moving cells180181Conditional formats and validation rules move with cut and paste, as182in Sheets: the cut cells take their rules to where they land, on their183sheet or another, and the cells they land on lose theirs. A rule whose184cells partly move keeps both parts; with a custom formula it becomes185two rules, each with the formula written for its own first cell, so the186cells that stayed read what they read and the moved ones read the cells187beside their new places. Formulas in rules follow cells that move, and188a rule taken to another sheet names the sheet it came from for the189cells it still reads there (`=Sheet1!$B2>0`).