Why advanced rules highlight patterns in Google Sheets - Dashboard Impact

Dashboard Mode - Why advanced rules highlight patterns

Dashboard Goal

This dashboard helps you see patterns in sales data by using advanced conditional formatting rules. It highlights trends like increasing sales, low sales, and repeating values so you can quickly spot important information.

Sample Data

Month	Sales	Region
Jan	120	East
Feb	150	East
Mar	130	East
Apr	170	West
May	160	West
Jun	180	West
Jul	140	East
Aug	190	East

Dashboard Components

• KPI Card: Average Sales
  Formula: =AVERAGE(B2:B9)
  Shows the average sales for all months.
• Conditional Formatting Rule 1: Highlight Increasing Sales
  Formula for custom formula rule:
  =B3>B2
  Highlights sales cells where the current month's sales are higher than the previous month.
• Conditional Formatting Rule 2: Highlight Low Sales
  Rule: Format cells in Sales column less than 140.
  Formula:
  =B2<140
  Highlights sales below 140 to spot low performance.
• Conditional Formatting Rule 3: Highlight Duplicate Sales
  Rule: Use built-in 'Custom formula is' with formula:
  =COUNTIF(B$2:B$9,B2)>1
  Highlights sales values that appear more than once to find repeating patterns.
• Summary Table: Monthly Sales by Region
  Formula:
  =QUERY(A2:C9,"select C, sum(B) group by C label sum(B) 'Total Sales'",0)
  Shows total sales per region.

Dashboard Layout

+----------------------+-----------------------+
|      KPI Card        |   Summary Table       |
|  Average Sales       | Monthly Sales by Region|
+----------------------+-----------------------+
|      Sales Data Table (with conditional formatting)       |
|  Months and Sales with highlights for patterns            |
+-----------------------------------------------------------+

Interactivity

You can add a filter for Region (East or West). When you select a region, the Sales Data Table and Summary Table update to show only that region's data. The conditional formatting rules adjust automatically to highlight patterns within the filtered data.

Self Check

If you add a filter to show only the West region, which sales values will be highlighted as increasing? Which sales values will be highlighted as low? Which sales values repeat?

Key Result

Dashboard highlights sales patterns using advanced conditional formatting and summarizes sales by region.