s0x IT Services Cloud How to create conditional statements for drop-down lists in Google Sheets

How to create conditional statements for drop-down lists in Google Sheets

How to create conditional statements for drop-down lists in Google Sheets post thumbnail image

Using conditional statements in Google Sheets is an easy way to bring more power and accuracy to your invoices and more.


Image: Google

If you fancy yourself a Google Sheets power user, have I got a tip for you. Have you ever wanted to create a spreadsheet that included the ability to select from a drop-down list, from which your selection would then dictate a value in another cell? This is called conditional statements and it’s an incredibly powerful tool that can make your spreadsheets far more accurate and user friendly.

For instance, say you had a list of services or products that you frequently sold to customers and clients. Each of those services or products had an associated and constant price. Instead of having to manually enter that price each time you create an invoice, you could simply select from a drop-down and your selection would then automatically populate another cell with the price.

Believe it or not, this is actually not too difficult. 

I want to show you how to create such a conditional statement.

SEE: Google Sheets: Tips every user should master (TechRepublic)

What you’ll need

The only thing you’ll need to make this work is a Google account. As long as you can log in to Google drive, you should be good to go.

How to create the drop-down list

The first thing we have to do is create the drop-down list, from which we’ll select our options. Let’s create a list with the following options:

  • Blue with a value of 1

  • Red with a value of 2

  • Pink with a value of 3

  • Purple with a value of 4

  • Yellow with a value of 5

  • Green with a value of 6

Of course, you use whatever you want for the “product” name as well as the value.

SEE: Hiring kit: Network administrator (TechRepublic Premium)

To create this drop-down, open a new Google Sheets document and select a cell. From the Data menu drop-down, select Data Validation (Figure A).

Figure A

<a href="http://s0x.org/wp-content/uploads/2020/03/how-to-create-conditional-statements-for-drop-down-lists-in-google-sheets-1.jpg" target="_blank" data-component="modalEnlargeImage" data-headline="

The Data Validation entry in the Data menu drop-down.&nbsp;

” data-credit rel=”noopener noreferrer nofollow”>conditionala.jpgconditionala.jpg

The Data Validation entry in the Data menu drop-down. 

In the resulting window, you should see the cell you selected listed (Figure B).

Figure B

<a href="http://s0x.org/wp-content/uploads/2020/03/how-to-create-conditional-statements-for-drop-down-lists-in-google-sheets-2.jpg" target="_blank" data-component="modalEnlargeImage" data-headline="

Creating the drop-down list in Google Sheets.

” data-credit rel=”noopener noreferrer nofollow”>conditionalb.jpgconditionalb.jpg

Creating the drop-down list in Google Sheets.

In the Criteria drop-down, select List Of Items. This will change the field to the right such that you can enter your items in a comma-separated list, so:


Once you’ve entered the list of items, click Save and your drop-down is ready to go (Figure C).

Figure C

<a href="http://s0x.org/wp-content/uploads/2020/03/how-to-create-conditional-statements-for-drop-down-lists-in-google-sheets-3.jpg" target="_blank" data-component="modalEnlargeImage" data-headline="

Our data validation drop-down is ready.

” data-credit rel=”noopener noreferrer nofollow”>conditionale.jpgconditionale.jpg

Our data validation drop-down is ready.

How to create the conditional statement

This is the more challenging part of the task, only because the conditional statement is a bit complex. 

Remember, we’re assigning numerical values to specific colors. The format of the conditional statement is:


Make sure you have as many right parenthesis as you have left, otherwise the statement will fail.

For our example, our drop-down is in cell A8, so the conditional statement would be:


Once you’ve typed out the statement, hit Enter on your keyboard and it’s ready to go.

If you select a different entry from the drop-down, you’ll see the associated value populates the cell with the conditional statement.

That’s it, you’ve created a powerful tool to enhance your Google spreadsheets. Use this to give your spreadsheets more power and more accuracy.

Also see

Related Post

Remoting even remoter?Remoting even remoter?

<img width="640" height="376" src="http://s0x.org/wp-content/uploads/2020/06/remoting-even-remoter-1.jpg" class="webfeedsFeaturedVisual wp-post-image" alt="Remoting even remoter?" style="float: right;margin-left: 5px" /><p>The post <a rel="nofollow" href="https://www.comparethecloud.net/articles/remoting-even-remoter/">Remoting even remoter?</a> appeared first on <a rel="nofollow" href="https://www.comparethecloud.net">Compare the Cloud</a>.</p> <div class="media_block"></div>

Salesforce launches a new streaming TV service, Salesforce+Salesforce launches a new streaming TV service, Salesforce+

<div class="teaser-image"> <a href="/business-intelligence/9199/salesforce-launches-a-new-streaming-tv-service-salesforce"><img src="http://s0x.org/wp-content/uploads/2021/08/salesforce-launches-a-new-streaming-tv-service-salesforce-1.png" /></a> </div> <span class="field field-name-field-article-type field-type-taxonomy-term-reference field-label-hidden"> <span class="field-item even"><a href="/news">News</a></span> </span><div class="field field-name-field-published-date field-type-datetime field-label-hidden"> <div class="field-items"> <div class="field-item even"><span class="date-display-single">11 Aug, 2021</span></div> </div></div><span