Excel TIP (Add Data Validation with a Drop-Down List)

 

It is important to be mindful of how you are entering repetitive data in excel. There is nothing worse than trying to validate and modify a column that has the same value written multiple ways! Look at this contact information, where the same state is recorded differently.

texas2

If you needed to filter this list on state you’d have to account for all representations of Texas!

Adding drop down validation is a great way to ensure values will be recorded consistently – and it is super easy to do in excel!

Let’s say Maggie’s Pet Supply sales team is calling a list of pet stores. One of the items they need to capture is the product they are interested in ordering

4

Rather than rely on the sales team to capture the correct product name, we can store the list of products as a validation list that can be selected by a drop-down.

  • To do this, add a second sheet to store the list of possible products

3

  • Next, highlight the column you want to be able to select the drop-down items
  • Under the Home tab, select ‘Data Validation’

5

  • For Validation criteria, allow ‘List’

6

  • Click in the Source box and then Click on the Product sheet
  • Highlight the values in the list and select OK

7

  • Navigate back to the Call Sheet and you can now select items for your drop down!

8

 

 

Advertisements

Excel Trick: Using Formulas and Formatting to View Repetitive Data

rawpixel-com-296621-unsplash

With the approval of the good friend and colleague who first showed me, I wanted to share a helpful way to view repetitive data. Let’s assume that you are working with a list of companies and their associated contacts. After ordering the list by Company and scrolling down the list, it is easy to lose track of what you are looking at due to the repetitive nature of the data.

rep data

Try out this mixture of formulas and conditional formatting!

Before you begin, you’ll need a value that can identify each company which is present in each row, in this case I am using Company Name but you could also use Website, or a Company Number if available. You can use the data at the bottom of this post to follow along.

  • In the first row after the last column of the sheet, put a ‘0’. In this example, this will be in column J

0

  • In the cell below, write the following formula =if(A2=A1,J1,J1+1) and press ENTER

form1

  • For the sake of the example, the 0 will be in column J, and column A will store our Company Name
  • Here, we are saying IF the Company Name in this row equals the Company Name above it, return the value in J1, in this case a 0 and if not, return J1 + 1, in this case 0+1
  • Continue the formula all the way down
    • The formula knows to increment the values based on it’s current row’s values
    • The result is that the same company has the same value in Column J and when the Company Name changes, the value increments by one! Cool, huh?

continue

  • Next, we will nest our IF formula inside another, like a formula Inception without Tom Hardy or Leonardo DiCaprio. We will use the MOD function.
    • To do this, click into J1 where the formula resides. Add ‘MOD’ after the = sign followed by an open parenthesis.

MOD pt 1

  • At the end for the formula, add a comma followed by the number ‘2’ and a closed parenthesis

MOD pt 2

  • Here, we are telling excel to divide the number in J by 2 and return the remainder. A 2 will have a remainder of 0 when divided by 2 and a 3 will have a remainder of 1.
  • Drag the formula down the column
  • You’ll notice that each cell in Column J has either a 0 or 1

0 and 1

Great! We have the foundation to put some conditional formatting!

  • Select everything in the sheet and navigate to Conditional Formatting, under the Home Menu.
    • Select ‘New Rule’

Conditional

  • Select ‘Use Formula to Determine Which Cell to Format’

condi2

  • Under the Rule Description, enter the following Formula
    • =$J1=1
  • Select ‘Format’, then ‘Fill’ and select any color

fill

  • Click ‘OK’ and ‘Ok’ again
  • See you data color organized!

Oranize

It may seem like a bunch of steps now, but after some practice you’ll be able to recreate quickly. I still find many cases where this comes in handy!

Example Data:

Company Name Website Address City State Country Postal Code Contact Name Email 0
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 John Erich john@theintelcom.com 1
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 Grant Christian grant@theintelcom.com 1
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 Jeff Gulder jeff@theintelcom.com 1
Intelligence Network Committee http://www.theintelcom.com 654 Dangerzone Iceville TX United States 829918 Brain Burke brain@theintelcom.com 1
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Karen Lyons klyons@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Jeff Lyons jlyons@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Sandy Hookshank shookshank@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Johnny Boy jboy@thesalezone.com 0
Sales Zone http://www.szone.net 718 Winner Los Angeles CA United States 90210 Pupper Doggo pdoggo@thesalezone.com 0
The Mobile Phone Store http://www.mphonestore.com 123 Fake Street Fakevill KY United States 92011 Goldi Sampson goldi@mobilephonestore.com 1
The Mobile Phone Store http://www.mphonestore.com 123 Fake Street Fakevill KY United States 92011 Aaron Sampson aaron.sampson@mobilephonestore.com 1
The Office Gentleman http://www.theofficegentleman.org 555 Example Street Mainville CA United States 99221 Grant Ongstad grant.ongstad@theofficegentleman.org 0
The Office Gentleman http://www.theofficegentleman.org 555 Example Street Mainville CA United States 99221 Sarah Connor sarah.connor@theofficegentleman.org 0
The Office Gentleman http://www.theofficegentleman.org 555 Example Street Mainville CA United States 99221 Maggie May maggie.may@theofficegentleman.org 0
Tim’s Tool Shack http://www.timstoolshack.net 829 Rochester Way New York NY United States 291 Tim Anderson Tim@timstoolshack.com 1
Tom’s Baseball http://www.tomsbaseballstore.com 705 Mainstreet Hoopville IN United States 77266 Tom Johnson tjohnson@tbaseballstore.com 0