Mission M07 · Excel · 15 min

Flag weak store-months with conditional formatting

Request from your manager

Laura wants a heat-map style grid: every store as a row, every month of 2025 as a column. Any store-month under $600 in net sales should turn red so she can call those store managers.

Data

Download the practice file(s):

⬇ sales_flat.xlsx⬇ sales_flat.csv

Steps

  1. Insert a PivotTable: store_name in Rows, order_date in Columns, net_sales in Values.
  2. If Excel did not group dates automatically, right-click a date > Group > select Months and Years. Filter Years to 2025 so you see 12 month columns.
  3. Select the value cells (not the Grand Totals) and go to Home > Conditional Formatting > Highlight Cells Rules > Less Than…
  4. Type 600 and choose 'Light Red Fill with Dark Red Text'.
  5. Count the red cells, or use =COUNTIF(range,"<600") on the 12×12 value area.

Check your answer

How many store-month cells in 2025 have net sales below $600?

Show solution

9 store-months fall under $600: Fort Lauderdale (Jan, Apr), Kissimmee (Jan), Miami (Feb), Orlando (Sep), Port St. Lucie (Feb), Sarasota (Feb, Sep) and St. Petersburg (Feb). February is the weakest month overall.

PivotTable: Rows = store_name | Columns = order_date (Months) | Values = Sum of net_sales | Filter = Years: 2025
Home > Conditional Formatting > Highlight Cells Rules > Less Than 600

' Count the flagged cells (adjust to your value range):
=COUNTIF(B6:M17,"<600")

Answer: 9

← Top 5 products of the yearMonthly sales trend chart →