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.
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.
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:
- Select the range. On the Data tab, open the Data Validation menu and choose New Rule....
- Set the type to Number, the operator to between, and the limits to 0 and 20.
- Under Advance options, choose Reject input to refuse anything else, or Show warning to flag it and keep it.
- Optionally add a helper message that shows when someone selects a cell. Then choose Done.
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.
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.
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.