A lot of color in a spreadsheet can be distracting, but shades of gray are acceptable and naturally suited to visual expressing a gradient scale.
Excel's palette is generous, but don't throw buckets of pigment all over your spreadsheets just because you can. In fact, color is often distracting and even annoying. I promise you, users will not quickly learn to identify specific people, clients, or conditions if you color coordinate them. Even if they do memorize your color key, they won't appreciate you for forcing them to do so.
The one color you can use with good results is gray. Applying shades of gray is an acceptable way to show a gradient scale. Simply use several shades of gray to visually identify progress or change from one level to the next.
The immediate problem with applying a grayscale is the availability of gray tones. There aren't many grays on the built-in pallet, but you can add more as follows:
All those shades of gray are now available from the palette!
- Choose Options from the Tools menu and then click the Color tab.
- In the Chart Fills section, select any of the available color tiles. Don't choose one that you use frequently; choose a color that you seldom or never use.
- Click Modify.
- Click the Standard tab (the default) and choose a shade of gray that's not already on the palette.
- Click OK to return to the Color tab. Notice that the Chart Fills color you selected is now light gray.
- Repeat steps 2 through 5 to add enough shades of gray to accommodate your scale.