Conditional Formatting by Row in a Matrix


This blog will show you how you can color individual rows differently based on different conditions and the row headers in matrix (Not alternating rows).

Our Scenario:

I want to apply colors to different rows of the following Buckets:

  1. Current – No Color
  2. 1-30 Days Past Due – Yellow
  3. 31-60 Days Past Due – Orange
  4. 61-90 Days Past Due – Red
  5. 91 or More Days Past Due – Red

Step 1:

Create a new calculated column in your data source which applies a numeric value to each header type that you would like to have highlighted. We have created a Calculated Column using the following query.

Step 2:

Select the Matrix to which you want to apply the formatting and go to conditional formatting section in the Format Tab and turn the Background Color Option “On”.

Step 3:

The Conditional Formatting is applied for different fields in the Values section in the Matrix. So we will apply conditional formatting according to No.  field first.

Select Format by “Rules”. In Based on field select “Sum of Color Column” and in Summarization select “Sum”. In the Rules section add the Rule as shown in the Screenshot.

 Step 4:

Apply the other rules for different colors same as above.

Step 5:

The Colors have been applied to different buckets according to our rule for “No.” Column.

Step 6:

Repeat the same steps by selecting different fields from the drop down under Conditional Formatting, one by one.

Step 7:

Thus we have colored the different rows of the Matrix successfully based on our condition.

Share Story :