Google Sheets – Dynamically Format Diagonal Cells

google sheetsgoogle-apps-script

I want all of the diagonal cells (A1, B2, C3, D4, E5, etc) of a Sheet to be formatted as Black Background, White Foreground. Is there any way to do this for a Sheet that constantly increases in size?

If I manually do so for the first 50 diagonal cells and the Sheet increases in size, the 51st diagonal cell will not share the same formatting. I'll have to go back and format it myself again.

Is there an easy way to go about this?

Best Answer

Automatic if within the existing formatted range (ie added rows are above and added columns are to the left) would be conditional formatting with Custom formula is

=row()=column()

and Text Colour: white (assuming this is what you mean by foreground) and Background Colour: black applied to Range: to suit.

Formula

=ARRAYFORMULA(IF(COLUMN(B1:S1)=ROW(A2:A19), 0, ""))

Explained

The IF statement, in combination with the ARRAYFORMULA, validates whether the column indices match the row indices. When they do, show a zero and in all other cases, just show nothing.

Note

Now that you have the solution you want, you're not able to make use of it by adding something in that range. If you do, it will destroy the ARRAYFORMULA. Making a print-out seems like the only benefit.

Best Answer

Related Solutions

Google-sheets – Custom actions in Google Spreadsheets

Google-sheets – How to fill the diagonal of a matrix with zeros

Formula

Explained

Note

Related Topic