Custom formula-based rules in Google Sheets - Dashboard Guide

Dashboard Mode - Custom formula-based rules

Goal

Help a small store manager quickly spot which sales orders need attention by using custom formula rules to highlight orders with low quantity or high total price.

Sample Data

Order ID	Product	Quantity	Unit Price	Total Price
1001	Notebook	2	5	=C2*D2
1002	Pen	10	1	=C3*D3
1003	Backpack	1	100	=C4*D4
1004	Calculator	3	20	=C5*D5
1005	Desk Lamp	5	20	=C6*D6

Dashboard Components

• KPI Card: Total Orders
  Formula: =COUNTA(A2:A6)
  Shows total number of orders (5).
• KPI Card: High Value Orders
  Formula: =COUNTIF(E2:E6, ">=100")
  Counts orders with total price $100 or more (2 orders).
• Table: Orders with Attention Needed
  Uses filter formula:
  =FILTER(A2:E6, (C2:C6<3) + (E2:E6>=100))
  Shows orders where quantity is less than 3 OR total price is $100 or more.
• Conditional Formatting Rule: Highlight Low Quantity
  Custom formula:
  =C2<3
  Highlights rows with quantity less than 3 in light red.
• Conditional Formatting Rule: Highlight High Total Price
  Custom formula:
  =E2>=100
  Highlights rows with total price $100 or more in light green.

Dashboard Layout

+----------------------+-----------------------+
| Total Orders (KPI)   | High Value Orders (KPI)|
+----------------------+-----------------------+
|                                      |
|      Orders with Attention Needed    |
|              (Filtered Table)        |
|                                      |
+--------------------------------------+ 

Interactivity

The filtered table updates automatically based on the custom formula rules for quantity and total price. If the data changes, the table and conditional formatting highlight the relevant rows instantly without manual filtering.

Self Check

Add a filter to show only orders where Quantity < 3. Which components update?

• The Orders with Attention Needed table will show only orders with quantity less than 3.
• The conditional formatting highlights remain on rows with quantity less than 3.
• The KPI cards remain the same because they count all orders and high value orders regardless of filter.

Key Result

Dashboard highlights orders needing attention by showing total orders, high value orders, and filtering orders with low quantity or high total price using custom formula rules.