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.
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.
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.
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.
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
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
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).
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
