Charts & Visual Data
Column Chart with Target Line & Conditional Above/Below Series Formula
Stage a two-color Column Chart with a constant horizontal Target Line by splitting actual values into mutually exclusive Above-Target and Below-Target helper series.
Color-code goal attainment automatically without manual bar recoloring!
Manually coloring individual columns green or amber breaks the moment next month’s actuals update! Instead, stage three clean chart columns next to your raw data: (1) a constant Target_Line column (= $B$6) plotted as a Line series, (2) an Above_Target column (= IF(Actual >= $B$6, Actual, 0)) styled in green, and (3) a Below_Target column (= IF(Actual < $B$6, Actual, 0)) styled in amber with 100% Series Overlap so only the active bar renders in each period!
Because each period populates either Above_Target or Below_Target (never both), changing the target threshold in B6 instantly flips bar colors and updates the attainment count =COUNTIF(B2:B5, ">=" & B6).
| Quarter ($250k Target) | Actual ($k) | Above-Target Series | Below-Target Series |
|---|---|---|---|
| Q1 | 210 | 0 | 210 (Amber) |
| Q2 | 265 | 265 (Green) | 0 |
| Q3 | 235 | 0 | 235 (Amber) |
| Q4 | 290 | 290 (Green) | 0 |
Conditional Above-Target Column Series Value (Q2 Row)
Returns the actual value when B3 meets or exceeds Target B6, or 0 so the green series stays hidden on shortfall periods.
=IF(B6 <= 0, "Invalid Target", IF(B3 >= B6, ROUND(B3, 2), 0))Periods Meeting or Beating the Target Line
Counts how many periods in B2:B5 sit on or above the dynamic benchmark line B6.
=IF(B6 <= 0, 0, COUNTIF(B2:B5, ">=" & B6))Try the example
Use the original values or change them to test your understanding.