Any Google Sheets experts out there can advise please?

Hi all

Slowly trying to pull myself away from Excel and onto Google Sheets, but hit a sticking point.

Per the below screenshot, column L is formatted to colour code based on folio allocation percentage.

Is there a function in sheets that will format the rest of the table’s row to match that of column L automatically? I know you could do something along these lines in Excel but I’ve forgotten how and I think it highlighted the entire row, not just to the borders of the table.

Any experts that can advise would be most appreciated.

1 Like

Hope this will help.

Select the whole column then go to Format > Conditional Formatting, from which you will see your existing rule. Go into your rule, and edit “Apply to range” section. From there you can add the same format to other columns or the whole column L:L.

1 Like

Hi @cezar, although won’t extending the range just mean that every cell in the table needs to be numary in order for it to format the way that column L does? Rather than column L dictate what the rest of the row does?

I don’t think I understood your question properly then. You wish to have the same format (as in number, text etc) or conditional format (colour coding, formula,etc) ?

So column L is already formatted so that if the value is within a certain numeric range (eg. below 2.5%), it will colour a certain way.

I’m looking to extend that colour coding across the whole row of the table based on column L’s value. I have gotten as far as trying to write a custom format rule, but no idea how to say ‘if L2 = between 2.5 and 5%, colour table row amber’ for example.

Hope that’s clearer.

Isn’t this what you are after? It applied to multiple rows.

That’s what I’ve done for column L, but as columns A - D are not numerical, the formatting won’t apply. There are also other numerical columns whereby the value might change the cells a different colour to column L.

The key here is to have column L drive the formatting of the entire row if that makes sense.

you can achieve this by writing a few “single colour” rules, but if you are using “color scale” it is not possible.

Why is everyone pulling out of excel personally? I much preferred excel, but I kept getting sign in issues where I’d have to relogin every few minutes and I got fed up with it and moved to sheets

Single colour is what I’m currently using - don’t think I need colour scale.

Think the format I need is something along the lines of ‘=$L2<2.5’

But the above denotes a ‘below’ condition. I’m not sure how to write out a ‘between’ condition…

Don’t change the subject. :wink:

Personally I prefer Excel, but I appreciate the live price updates I get via GOOGLEFINANCE links.

1 Like

small example here, this will paint whole row green if C is greater than 3 and less than 5
you can add as many or/ands there as you want.

and if you need another color, click the “add another rule” at the bottom and add that one as well.

edit:

yes, this should just work you had problems with it? only thing is since that field is a “%” formatted, I assume the underlying value for 2.5 = 0.025 so try =$L2<0.025

1 Like

Boom, exactly what I need I’m sure. Will implement it now and let you know. Thanks

I use google app engine to process “contract notes” from brokers, some of which are PDF, and when you have access to google cloud platform the sky is the limit.

May be if they introduce some Microsoft Azure - Excel integration in the future it might become an option but leaps and bounds different at the mo.

I really liked that it had the google finance in combination with other stuff, I made a sheet in which I automatically calculated forward dividends (scrapped from yahoo finance by sheets) for my portfolio to make my portfolio dashboard more complete, doing that in excel would be difficult (especially with currencies and prices sheets works way better imo than excel).

1 Like

I guess the difference is my spreadsheet doesn’t take external input. Mine’s all manual entry and calculations. Takes a couple of minutes every week to enter the value, deposits and dividends etc. I have made a whole video on my spreadsheet to be honest

Done the trick - thank you sir!

Actually just needed to add the ‘%’;

1 Like

And that’s what I’m tired of maintaining. Just had another kid - need some time back. :wink:

@cavanhagan just to show of a bit of examples :stuck_out_tongue:

4 Likes

Liking the look for that, so you working your dividends out for the year and beyond. Nice work :+1:t3::+1:t3:

1 Like