It will open up a side panel with options. You can select the cell or column you want to assign colors to and go to Format > Conditional Formatting. Let’s say you have a list where you are likely to add or remove values, a dynamic drop down would be the best option to select data. Data validation can help you lock down cells so that your custom tools run as expected.Ĭonditional formatting is a tool that can help you color-code your spreadsheets-like if you want to auto-assign a color to a cell based on specific criteria.īuilding on the project tracker example, let’s say you want to remind team members to take action by making anything that’s categorized as “In progress” appear in yellow within your Sheet. A dynamic drop down list in Microsoft® Excel® is a convenient way of selecting data without making changes to the source. This is helpful if you’re trying to protect structured data, like formulas or Apps Scripts you’re running, from a multitude of editors who may be in your Sheet.

You can set the option in data validation to 'Reject input' for invalid entries. For example, if you check the box, it could mean “yes” or if you uncheck a box it could mean “no.” Go to Data > Data Validation > and select Checkbox under “criteria.” Choose “Use custom cell values” under the Criteria option and type in the meaning you’d like. Instead of simply adding a checkbox, you can use data validation to automatically mark the checkbox with a value. Step 5: Click on the cell where the dropdown. Again, I used 'Create from Selection' and named cells B2:B5 based on the names in cells A2:A5. In the Source field enter the range which contains the list of values to be used as your drop down list or you can just click inside the Source field and select the cells on. In the Data Tools section of the Data tab click the Data Validation button. In my case: 'Wine': Step 4: Name the cells where the photos are located. If you are working on a list of items then follow the steps to add a drop down list in Excel. Go to Data Validation >Īdd custom meanings to checkboxes. Step 3: Locate where you want the dropdown list, and using Data Validation Lists, enter the list based on what you named it. This is great if you need to review upcoming work at a glance. You can use the “dates” option in data validation, and combine it with conditional formatting (see instructions below), to set it up so that anything with a certain date-or past a certain date-gets automatically grayed out in your Sheet. What some folks don’t realize is that data validation can be used for so much more than creating dropdown lists.

Add a drop down list in excel