Share Visual Data with Excel Color Formula

Spreadsheets are great for managing large amounts of information and deriving new insights from data, but they can be difficult to comprehend at a glance. When you need to share information with non-technical colleagues or want to visualize your data, an excel color formula can be your best friend.

Your excel color formula will use conditional formatting. Conditional formatting changes certain characteristics of a cell or multiple cells based on rules that you create. With conditional formatting, you can change the fill color or text color to easily share visual data.

Step-by-Step

  1. To create a conditional formatting rule, select the cells to be formatted and click on the Conditional Formatting option in the Styles group of the Home ribbon.Fred Pryor Seminars_Excel Color Formula_figure 1
  2. From the drop-down menu, select New Rule. The New Formatting Rule dialog box will appear.
    Fred Pryor Seminars_Excel Color Formula_figure 2 Fred Pryor Seminars_Excel Color Formula_figure 3
  1. Choose the Rule Type that suits your needs. If the available Rule Types do not meet your needs, select Use a formula to determine which cells to format. This example shows the Format only cells that contain Rule Type to format cells with values between 1.5 million and 5 million. Fred Pryor Seminars_Excel Color Formula_figure 4
  2. After entering the conditions to be met, select the style to be applied by clicking the Format button next to the Preview.
  3. The Format Cells dialog box will appear. You can apply any style of formatting you would like. Because we are focusing on using color to share visual data, change the cell background color for maximum impact. Click the Fill tab and choose an appropriate color. Click the OK button. Fred Pryor Seminars_Excel Color Formula_figure 5
  4. The New Formatting Rule dialog box will re-appear. If you like the preview, click OK and the rule will take effect. You can add multiple rules by repeating this process. Fred Pryor Seminars_Excel Color Formula_figure 6

To edit or delete rules, you will need to use the Conditional Formatting Rules Manager. You can access the manager by selecting Manage Rules from the Conditional Formatting dropdown menu. Fred Pryor Seminars_Excel Color Formula_figure 7

Once you understand how to create an excel color formula using conditional formatting, you should reflect on the nuances of sharing data visually.

Ask Yourself:

  • Is my color-coding system appropriate? Does it make this document easy to interpret?
  • Is the rule I created meaningful? Does the conditional formatting create important distinctions in the data?
  • Will this document be printed? How will the colors appear in black and white.

Now you know how to use color to make your data easy to understand! Just follow this process the next time you need to quickly share information with a colleague.