CODEWITHSUNDEEP
google-sheetsdynamicformat
Jeremy
Jeremy

Reputation: 1

Conditional Formatting Pivot table in Google Sheets

In the example picture, I have a basic conditional format set to highlight the words Home and Home Connect.

I am looking for a formula or query that will dynamically extend the conditional format colour to fill the blank cells in the column to group all the Home topics. as well as the word without formatting the entire row or column.

Since new data will be added the solution should work for the entire column.
Something like highlight all cells under Home until not null.
Maybe another one that highlights all cells under Home Connect until not null.

example picture

Upvotes: 0

Views: 6829

Answers (2)

Fabian K
Fabian K

Reputation: 59

Although this post is a bit old: the easiest and "cleanest" way to accomplish this IMHO is to "repeat row labels" and use conditional formatting to achieve the desired coloring:

enter image description here

If you don't mind the repeating values in the first column, you're actually already done by just activating the checkbox in your pivot table settings using your existing conditional formatting (as in the red "Home Connect"). If it bothers you, you can add another conditional formatting to make the repeating text "disappear" (as in the yellow "Home").

enter image description here

The conditional formatting rules to hide repeating values is:

Custom Formula: =and(offset(A1,-1,0)=A1,A1="Home")

This checks for two things:

a) is the value equal to the value above it?

AND

b) is the value "Home" ?

=> If both are true, we'll color both background and the font in the same yellow-tone.

The other ones are self-explanatory I presume.

If you want to hide the red text as well you'd just need another custom formula rule for "Home Connect", coloring both text and backround in red.

Obviously you can also "dim" the repating text by using a slightly different yellow / red tone for the text in your custom formula conditional formats:

enter image description here

Upvotes: 1

Alessandro
Alessandro

Reputation: 2998

Approach

The goal here is to build a pattern matching that will result in custom cell formatting. 

          _
         | KEY , value
         |     _
       1_|    | /  , value
         |  2_| /  , value
         |    |_
         |_

I think it would be better to use a programming language to express this pattern matching feature.

The idea is to build a Map that holds the Keys and the colors. Then you can loop through the columns and check for the pattern and set the background color accordingly:

// In this case a custom menu is added to trigger the validation any time you want.
function createMenu() {
  SpreadsheetApp.getUi().createMenu('Custom Formatting').addItem('validate pattern', 'validatePattern').addToUi(); 
}

function validatePattern() {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let rows = ss.getRange("D1:E").getValues();
  let map = new Map();
  map.set('Home', 'green');
  map.set('Home Connect', 'red');
  //Set new (key,color) entries to tune your pattern matching.

  let color ='';
  for (let i in rows) {
    if (map.has(rows[i][0])) {
      color = map.get(rows[i][0]);
    }
    if (color != '' && rows[i][1] != '') {
      ss.getRange(`D${parseInt(i)+1}`).setBackground(color).setFontWeight('bold');
    } else {
      color = '';
    }
  }
}

Reference:

Javascript Map

Class Range

Spreadsheet Menu

Upvotes: 0

Related Questions