Tuesday, July 15, 2008

Excel Conditional Formatting for Columns

Here is the solution if you want to conditionally format your spreadsheets so every third column is highlighted (highlights all the quarters out of the months, i.e. Q1, Q2, etc.). For example, if you want a column filled blue and skip two columns and fill the next one.

If you want something like this:

Use this formula: =IF(MOD(COLUMN()-1,3)=0,1,0)

I can't believe I couldn't find the answer anywhere online and had to invent it myself. It took like 30 minutes.

1. Hit "ctrl+a" to select all cells or region you want to formatted.
2. On Home Tab, click "conditional formatting"-->"New Rule".
2. Select "Use a formula to determine which cells to format"
3. Like screenshot above, paste: =IF(MOD(COLUMN()-1,3)=0,1,0)
Select "Format" and choose your fill color.
5. Select OK.

Modifying and Editing the Formula

If you want to make it skip three columns instead of two just replace # with 4. Basically make the # = what you want +1.

If you want to shift the starting column of blue, just replace the # with 0 or 1 or 2 etc.

Helpful video example for a row. Cuz I don't have screencast software. And there isn't one for columns. Damn I am getting good at excel. Dacuzzi.

No comments: