Skip to content
Bitta Apps

Chapter 2 of 4Page 7 of 12

Workbooks

Cell controls and input rules

Add checkboxes, drop-down lists and date pickers to a Business Central sheet, set rules for what people type, and highlight values with conditional formatting.

updated: applies-to: 1.0.0.0

When people type into a shared sheet, give them clean input: a tick, a choice from a list, a date from a calendar. Then set rules for what each cell accepts, and let color point out what matters.

Add controls to cells

Select the cells first. On the Insert tab, Cell Controls has three controls:

  • Checkbox adds a checkbox to every selected cell. One click ticks it, and a ticked box stores 1, so formulas can count or sum the ticks.
  • Drop-down List adds a list to every selected cell and opens its rule panel. Type the choices, for example Approved, Review and Rejected, or take them from a range of cells.
  • Date Picker adds a calendar. Double-click a cell, pick the day, and OK writes it in.
Chair approvals sheet with a checkbox in every Approved cell, some of them ticked

Under Advance options, a drop-down list can show as a chip, with an arrow, or as plain text. You also choose whether a value that isn't on the list is only flagged with a warning or rejected.

Drop-down list rule panel with the options Approved, Review and Rejected, the display style and the choice to show a warning or reject input
Date picker calendar open on an Effective cell of the chair approvals sheet

Set rules for input

A rule says what a range of cells accepts. For example, the Change % column must hold a number between 0 and 20:

  1. Select the range. On the Data tab, open the Data Validation menu and choose New Rule....
  2. Set the type to Number, the operator to between, and the limits to 0 and 20.
  3. Under Advance options, choose Reject input to refuse anything else, or Show warning to flag it and keep it.
  4. Optionally add a helper message that shows when someone selects a cell. Then choose Done.
Input rule on the Change % column that allows numbers between 0 and 20 and rejects anything else

Type 30 in that column, and the sheet says no: the value must be between 0 and 20, and the old value stays. The rule panel lists every rule on the sheet.

The Data Validation menu on the Data tab holds the same controls, plus your own rules:

  • Data Validation... opens the rule panel.
  • New Rule... adds a rule.
  • Drop-down List, Checkbox and Date Picker add the same controls as Cell Controls on the Insert tab.
  • Clear Rules from Selected Cells removes the rules from the selected cells.
  • Clear All Rules from Sheet removes every rule on the sheet.

Highlight what matters

On the Home tab, Conditional Formatting colors cells by their values:

  • Highlight Cells Rules: for example, a profit percent less than 26 stands out.
  • Data Bars show each value as a bar, so you can compare prices at a glance.
  • Color Scales shade each value by its size.
  • Top/Bottom Rules and Icon Sets work the same way.
Conditional formatting rule that highlights a profit percent less than 26

Manage Rules lists the rules for the selected cells or the entire sheet, ready to edit or clear. New Rule adds one with your own condition.

Conditional formatting rules for the entire sheet: a color scale, data bars and a less than rule, next to the formatted sheet

When to use it

Use cell controls and rules wherever people type into a shared sheet: approvals, price reviews or order requests. To stop others from changing a workbook at all, share it with View access.

Next step

Ready to try Bitta Sheets?