Excel's conditional formatting tool applies formatting depending on cells' contents. Validation rules can change cells font, style, size or border. They can also change the text's color. For example, rules may highlight negative figures by printing them in red. Rules may also highlight maximum or minimum values, color values by their rank or catch duplicate entries. For more complex validation rules, write formulas into spreadsheet cells and supply the formula to the conditional
formatting tool. Highlight the cells that you want to validate. For example, if you want to validate all the numbers in column C, highlight the entire column. Click "Home" in the menu bar. Click "Conditional Formatting" in the ribbon's Style tab. A drop-down menu opens. Click "Highlight Cell Rules," which is the first of the menu's options. A new menu opens to the right. Click "More Rules..." and the New
Formatting Rule dialog box opens. Enter the validation rule using the "Edit the Rule Description" boxes. For example, if you want to change the color of all figures less than zero, select "Cell Value" and "less than" from the first two drop-down boxes. Type "0" in the text box. Click the "Format..." button. The Format Cells dialog box opens. Click on the dialog box's Font tab. Click the "Color" drop-down box. By default, the box is set
to "Automatic." Click the color you want to apply to figures that pass the validation rule. Click on the dialog box's Fill tab. Click the color you want to apply to the validated cells' backgrounds. Click "OK" in the two open dialog boxes.
One way to streamline data entry is to create custom lists in Excel and use them to create drop-down lists in specified cells. When you use the program's Data Validation tool, along with its Conditional Formatting feature, you can make a drop-down list that is color-coded, enabling you to determine data at a glance. You can customize the list colors to coordinate with your logo, topic or other data. Create a list in an unused or hidden column of your
spreadsheet. Enter the items into consecutive cells in a single column, one list item per cell. Leave no blank cells in-between. Click on the cell or select the range of cells in which you want to add the drop-down list. Select the "Data" tab, click "Data Validation" and choose "Data Validation." Choose "List" in the Allow section. Click inside the "Source" box and select the cells containing the list you created. Click "OK." Make sure the cell or the range of cells
in which you want to add the drop-down list is still selected and click on the "Home" tab. Select "Conditional Formatting" in the Styles group. Point to "Highlight Cells Rules" and select "More Rules." The New Formatting Rule dialog opens. Choose "Format Only Cells that Contain" in the Select a Rule Type list. Choose "Specific Text" in the Format Only Cells With list. Enter the first list item in the third box and click the "Format" button. Select the
"Fill" tab of the Format Cells box. Choose the color with which you want to fill the cell, if the first list item is selected. For instance, if your list consists of the options "High," "Average" and "Low," you might want to shade cells with the word "High" in green, "Average" in yellow and "Low" in red. Click "OK" to apply the formatting. Format the cells again with the second list item and continue until you have added a rule and a color for each item on the list. Save the
worksheet before closing Excel. When a user chooses an option from the drop-down list, it will automatically fill the cell with the appropriate color.
This article will talk about how to highlight rows based on drop down list, take the following screenshot for example, when I select “In Progress” from the drop down list in column E, I need to highlight this row with red color, when I select “Completed” from the drop down list, I need to highlight this row with blue color, and when I select “Not Started”, a green color will be used to highlight the row. Highlight rows with different colors based on drop down list by using Conditional
Formatting Highlight rows with different colors based on drop down list by using a useful feature
Highlight rows with different colors based
on drop down list by using Conditional FormattingNormally, the Conditional Formatting feature can help you to deal this task, please do as follows: 1. First, please insert the drop down list, select the cells where you want to insert the drop down list, and then click Data > Data Validation > Data Validation, see screenshot: 2. In the Data Validation dialog box, under the Settings tab, select List form the Allow dropdown, in the Source text box, please
click button to select the values you want to use in the drop down list. See screenshot:3. After inserting the drop down list, then apply the Conditional
Formatting to the data range, please select the data range that you want to highlight the rows based on dropdown, see screenshot: 4. And then click Home > Conditional Formatting > New Rule, and in the New Formatting Rule
dialog box, click Use a formula to determine which cells to format in the Select a Rule Type list box, and then enter this formula =$E2="Not Started" into the Format values where this formula is true text box, see screenshot: Note: In this formula, E2 is the cell where the first drop down list is located, the text “Not Started” is the value
in drop down list you have created. 5. Then click Format button to go to the Format Cells dialog, please choose one color that you want to highlight the specified rows when the value “Not Started” is displayed in the drop down list, see screenshot: 6.
And then click OK > OK to close the dialogs. 7. Then repeat the above 4-6 steps to apply the conditional formatting to other drop down values, for instance, enter the formulas: =$E2="Completed" and =$E2="In Progress" for the Completed or In Progress rows, and specify the colors for each item individually as you need. 8. And now, all the drop down list
values have been applied with the conditional formatting, when you select the item from the dropdown, the row will be highlighted with the color you specified. See screenshot:
Highlight rows with different colors based on drop down list by using a useful featureThe
above method may be somewhat troublesome if there are multiple drop down list items needed to be colored, so, here, I will introduce an easy feature, Kutools for Excel's Colored Drop-down List, with this powerful feature, you can solve this task as soon as possible. Click to download Kutools for Excel! Tips:To apply this Colored Drop-down List feature, firstly, you should download the Kutools for Excel,
and then apply the feature quickly and easily. After installing Kutools for Excel, please do as this: 1. First, you should create the drop down list that you want to use, see screenshot: 2. Then, click Kutools > Drop-down List > Colored Drop-down List, see screenshot: 3.
In the Colored Drop-down list dialog box, please do the following operations: - Select Row of data range option from the Apply to section;
- Then, select the drop down list cells and data range you want to highlight the rows;
- At last, specify the color for the drop down list items separately as you need.
4. And then, click OK to close the dialog box, now, when you select the item from the dropdown, the row will be highlighted with the color you specified. Click to Download Kutools for Excel and free trial Now!
Demo: Highlight rows based on drop down list with Conditional Formatting
More relative articles:- Create Drop Down List With Hyperlinks In Excel
- In Excel, adding drop down list may help us to solve our work efficiently and easily, but, have you ever tried to create drop down list with hyperlinks, when you choose the URL address from the drop down list, it will be open the
hyperlink automatically? This article, I will talk about how to create drop down list with activated hyperlinks in Excel.
- Create Drop Down List But Show Different Values In Excel
- In Excel worksheet, we can quickly create a drop down list with the Data Validation feature, but, have you ever tried to show a
different value when you click the drop down list? For example, I have the following two column data in Column A and Column B, now, I need to create a drop down list with the values in Name column, but, when I select the name from the created drop down list, the corresponding value in Number column is displayed as following screenshot shown. This article will introduce the details to solve this task.
- Create Drop Down List With Images In Excel
- In Excel, we can quickly and easily create a drop down list with cell values, but, have you ever tried to create a drop down list with images, that is to say, when you click one value from the drop down list, its relative image will be displayed at the same time. In this article, I will talk about how to insert a
drop down list with images in Excel.
- Increase Drop Down List Font Size In Excel
- Drop down list is a very helpful feature in Excel, when you create drop down list in your worksheet, have you ever tried to increase the font size of the drop down list to make the selected record larger and more readable as
left screenshot shown? This article will talk about a workaround to solve it.
- Create Multi Level Dependent Drop Down List In Excel
- In Excel, you may create a dependent drop down list quickly and easily, but, have you ever tried to create multi-level dependent drop down list as following screenshot shown? This
article, I will talk about how to create a multi-level dependent drop down list in Excel.
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create
Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select
Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy
deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
How do I add highlight to Data Validation list in Excel?
Example. Example for Excel drop down list with color.. Drop down options.. Select the cell.. Click on Data Validation.. Click on drop down and select List.. Enter or select the Options.. Select the options for the drop down.. The drop down is created.. How do I highlight colors in Data Validation in Excel?
How to Make Colors Change With Validation in Excel. Highlight the cells that you want to validate. ... . Click "Home" in the menu bar.. Click "Conditional Formatting" in the ribbon's Style tab. ... . Click "Highlight Cell Rules," which is the first of the menu's options.. How do I add color to a drop down list in Excel?
Method B Color coded drop down list with a handy tool-Colored Drop-down List. Select the drop-down list cells, then click Kutools > Drop-down List > Colored Drop-down List.. In the Colored Drop-down list dialog, do below settings. 1) Check the scale you want to add color to in the Apply to section. ... . Click Ok.. How do I highlight invalid data in Excel?
On the Data tab, in the Data Tools group, click the arrow next to Data Validation, and then click Circle Invalid Data. Excel displays a red circle around any cells that contain invalid data.
|
