Color drop list item trong sheet google
In this tutorial, you will learn how to create drop-down list in google sheets with color. While Google Sheets is best known for its number crunching abilities, it’s also frequently used for other purposes, such as status or inventory tracking. In many cases, simply entering a number into a cell won’t convey the necessary information, but a problem arises if
users are allowed to manually type data into cells, as typos will often occur and then formulas relying on these entries may break. The best way to prevent this sort of input error is to create a drop-down list that allows users to enter only the exact values the spreadsheet can handle. Using this method also allows for easy implementation of conditional formatting to color code different values. In this tutorial, we’ll cover how to create a drop-down list and apply conditional formatting to
differentiate between the drop-down list items. Read on to learn how. Drop-down lists are essentially menus located in the cells of the spreadsheet. Users can click on the cell to open the menu and easily select an option. Here’s how to add them to your spreadsheet: Step 1Select a cell or range to add drop-downs to and open the Data menu, then select Data Validation Step 2In the Data Validation popup, choose the data validation criteria you want to use. The two options that will display as drop-downs are List from Range and List from Items Step 3To use the List of Items option, type the desired list items into the text field beside it, separated by commas Step 4To use the List from Range option, type the range reference in the text field beside the criteria type drop-down. You can also use the Select Data Range button in the text field to choose the range. Be sure to include dollar signs to make the reference absolute Step 5Ensure the box for Show Dropdown List In Cell is checked, and choose if you want to allow users to enter values not in the list (it’s recommended to reject invalid input unless you have a reason not to). Then click Save. Step 6The selected cells will now display a downward pointing triangle to show they contain drop-downs. Click on the cell to open the list and select an option Color CodingOnce you have the drop-down lists set up, color code the list items by following these steps: Step 1Select the same range you added drop-downs to and open the Format menu, then choose Conditional Formatting Step 2In the Conditional Format Rules sidebar, under Format Cells If…, select the Text is Exactly option, and type the exact text used for the first drop-down list item Step 3Choose formatting options from the Format Style section, then click Done to apply the rule Step 4To add more rules for each list item, click Add Another Rule and repeat Steps 2-3 as many times as needed Step 5Now when you select list items from the drop-down, the cells will automatically format based on the conditional formatting rules SummaryExample Spreadsheet: Make a copy of the example spreadsheet In this tutorial, I covered how to create drop-down list in google sheets with color. Want more? Check out all the Google Sheets Tutorials. You might also like: |