Although it is more common to shade rows of data with conditional formatting, to make it easier to read the data, it is also possible to shade columns of data as well.
By combining a formula using the MOD and COLUMN functions with conditional formatting, different patterns of shaded columns can be applied to a worksheet automatically.
Dynamic Shading
As an added bonus, if you add or remove columns, the shading updates automatically in order to maintain the pattern.
Shading Alternate Columns Formula
This tutorial covers shading alternate columns, but by modifying the formula, different patterns can be achieved.
- Open an Excel worksheet - a blank worksheet will work for this tutorial.
- Drag select cells A1 to H10 to highlight them.
- Click on the Home tab.
- Click on the Conditional Formatting icon on the ribbon to open the drop down menu.
- Choose New Rule option to open the New Formatting Rule dialog box.
- Click on the Use a Formula to determine which cells to format option from the list at the top of the dialog box.
- Enter the following formula in to the box below the Format values where this value is true option in the bottom half of the dialog box.
=MOD(COLUMN( ), 2) =1
- Click the Format button to open the Format Cells dialog box.
- Click the Fill tab to see the background color options.
- Select a color to use for shading the alternate rows of the selected range.
- Click OK twice to close the dialog box and return to the worksheet.
- Alternate columns in the selected range, beginning with column one, should now be shaded with the chosen background fill color.
Breaking Down the Formula
Conditional formatting is only applied when the specified condition is true.
In this example, the formula we entered into the conditional formatting dialog box is true when the remainder of the formula is equal to 1.
The component parts of the formula are:
- The COLUMN() function gives us the number of each column. For example, column A = 1, column B = 2, column C = 3, and so on
- The MOD() function divides the column number by 2 - determined by the " 2 " in the formula - and checks to see if there is a remainder
- If there is a remainder, the formula checks to see if it is equal to 1
- If it is, then the formula is true, and the formatting is applied (column shading)
- If the remainder does not equal 1, then the formula is false, and the formatting is not applied
Changing the Shading Pattern
Changing the two numbers in the formula allows us to change the shading pattern in the worksheet.
For example, if the formula is written as =MOD( COLUMN( ), 2) =0, the shading will still shade alternate columns, but the shading will begin with column 2 instead of column 1.
Likewise, if the formula is written as =MOD( COLUMN( ), 3) =1, the shading starts with column 1, but every third column will be shaded instead of every second one.
