Excel Tip – Quickly Convey and Organize your Excel Sheets with Shading

Excel’s conditional formatting is a popular feature that allows you to apply different formatting options, such as background color, shading, borders, or font formatting to data that meets certain conditions. However, you cannot change the font or the font size of the contents in the cell via conditional formatting. This must be done manually. Nevertheless, cells can be formatted to show up with a blue background or you can alternate cell row color to better illustrate your data. (See fig. 4 below.)

Let’s look at how to apply conditional formatting for shading. Select the cells to which you want to apply conditional formatting. Click Home > Conditional Formatting > New Rule.(see fig. 1) A New Formatting Rule dialog box will pop out. Select Use a formula to determine which cells to format item. Next, input the formula “=MOD(ROW(),2)” in Format values where this formula is true. Click Format. (see fig. 2)   Another Format Cells dialog box will pop out. Go to Fill tab and choose one color for shading the background. Click OK to return the New Formatting Rule dialog box, and also you will see the color in the Preview box. (see fig. 3)   and click OK to get the result you want. You can also insert or delete a row in the shading range and you will see that the shading color will follow. If you want to shade alternate columns, use this formula “=MOD(COLUMN(),2)” (see fig. 4)   

Figure 1

Figure 2

Figure 3

Figure 4