I just lately wrote a tutorial on including a drop-down record in Google Sheets. With this straightforward trick, you can also make it exponentially simpler for collaborators to fill in knowledge from a useful record of choices (as an alternative of getting to kind every thing manually). This could additionally stop customers from coming into the flawed info right into a vital area in your spreadsheet.
You should utilize one other nifty trick to assist make these cells stand out as soon as a collaborator has made a piece. That trick known as Conditional Formatting.
Merely acknowledged, conditional formatting codecs a cell based mostly on particular circumstances. Within the instance I’ll present you, the conditional formatting will add a background shade to a cell based mostly on the colour chosen from the drop-down. For example, if the colour pink is chosen from the drop-down, the background shade for that cell will mechanically be, you guessed it, pink.
In fact, you are not restricted to solely including colours to your cells, as you can even use different (fundamental) formatting, reminiscent of daring, italics, underline, strikethrough, and alter the colour of the textual content.
Conditional formatting additionally permits you to apply the formatting based mostly on a number of circumstances, reminiscent of:
- If a cell is/just isn’t empty.
- Accommodates (or doesn’t comprise) particular textual content or formulation.
- Textual content begins/ends with particular textual content or formulation.
- Dates.
- Better than, lower than, or equal to.
You possibly can add a single rule and even a number of guidelines to your conditional formatting.
Let’s have a look at how that is executed.
Including conditional formatting to a drop-down record
Bear in mind, within the tutorial for including drop-down lists; we created an inventory of the next colours:
- blue
- pink
- inexperienced
- yellow
- black
- orange
- purple
- white
We’ll use conditional formatting to set the cell’s background shade based mostly on the colour chosen. So, if a collaborator chooses inexperienced from the drop-down, the cell background shade will likely be set to inexperienced.
Easy.
1. Open the conditional formatting rule editor
Choose the cell that accommodates the drop-down after which click on Format > Conditional formatting (Determine 1).
Determine 1: Conditional Formatting is discovered within the Formatting menu in Google Sheets. Picture: Jack Wallen
Upon clicking conditional formatting, you will see a brand new sidebar on the fitting aspect of the window (Determine 2).
Determine 2: I’ve already added two guidelines, one for blue and pink. Picture: Jack Wallen
2. Create a brand new conditional formatting rule
Click on + Add one other rule to open the Rule editor (Determine 3).
Determine 3: The conditional formatting rule editor makes it easy to create new guidelines. Picture: Jack Wallen
You must have already got the right cell listed within the vary. From the Format cells, drop-down, choose Textual content accommodates. Within the Worth or formulation space, kind inexperienced after which choose the colour inexperienced from the colour picker (Determine 4).
Determine 4: Including the correct shade of inexperienced for our conditional formatting rule. Picture: Jack Wallen
As soon as you have chosen the colour, click on Performed, and the rule is prepared. Proceed to create extra guidelines till you may have one for every shade in your drop-down.
Now, when a consumer selects a shade from the drop-down, the background shade will mechanically change based mostly on the situation (Determine 5).
Determine 5 Choose inexperienced and the background shade will change to inexperienced. Picture: Jack Wallen
And that, my fellow Google Sheets customers, is the way you add conditional formatting to a drop-down record. You are not restricted to drop-downs for this function, as you possibly can apply it to any kind of name (and even a spread of cells). Benefit from the recent style of automated formatting.
Extra how-tos
This text was initially revealed by zdnet.com. Learn the authentic article right here.
Comments are closed.