Automatically color-code cells in Microsoft Excel
Q. Is there a way to color-code cells based on their cell content? For example, if cells state “Woman-Owned,” could those cells automatically become red and the cells that state “Large Company” automatically become yellow?
A. You can automatically have cells turn certain colors based on the text within those cells. This is easily done using one of the many types of conditional formatting. Having cells color-coded by the type of text they contain makes it much easier for a user to identify cells containing specific text, especially if there are many cells with that information. I have created a spreadsheet to mimic the scenario described above. See the screenshot below.
To color-code specific text, select the cells that contain (or will contain) the text that you want color-coded. In the example above, I have selected cells B2:B27. At this point, the Quick Analysis icon appears in the lower right corner under the last selected cell. See the screenshot below.
Click the Quick Analysis icon and select Text Contains under the Formatting category. The Text That Contains window will open. Enter one of the company types under Format cells that contain the text:. I will enter Woman-Owned. To the right of that, next to with, click the drop-down arrow. You have some formatting options given, or you can choose Custom Format…. This allows you to choose many types of formatting, including fonts, borders, and fills. I would like to color-code the cells based on the text for company type, so I will choose the Fill category. I will then choose a red color. Click OK. Repeat the same steps for the remaining company types and choose the preferred formatting. See the screenshot below for my color-coded spreadsheet.
Note that this content was based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
You can access the Excel workbook here and video demonstration at the bottom of this article.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
