Love It Or List It

“What gets measured gets managed.”

Peter Drucker


Analysts estimate anywhere from 50-80% of their time is dedicated to cleansing information (Source: New York Times). Making use of Excel’s data validation controls can drastically reduce this necessary evil when developed properly. No longer will you spend hours trying to infer if “S” really means “Saturday” or “Sunday”; or trying to match “John Doe” with the employees actual name “Johnathan Doe”. Because the time you invest now in planning what is truly important and creating a set of sound variables will pay off infinitely in the end. Not only in making data entry easier for others, but also when it comes time to analyzing the things that matter: RESULTS.


Download Workbook


Make It:

  • Select a cell from within your worksheet
  • Navigate to the Data Tab
  • Select Data Validation
  • Allow: List
  • Source: =$A$2:$A$10
    • Use the cell selector button on the right side to select the range
  • Select OK to complete

DataValidationRange


This is great and “it works,” but let’s say tomorrow you want to add an item to A11. Then, you have to go back to the data validation tab and edit the source to $A$2:$A$11. And then the next day, through range A12, and then A13 and then A14. You could always just change source to be A:A, ignore blank and be done with it all right? Well sure, if you are a big fan of forcing your computer to unnecessarily count to 1,048,576, or if you enjoy closing and reopening your workbook every time you make edits to see them reflected properly.

ezGIF-ListAA


“Insanity Is Repeating The Same Mistakes and Expecting Different Results”

Not Albert Einstein


WHAT’S YOUR NAME?!

  • Select Cells A2:A11
  • To the left of the Formula Bar is the Name Box. Select the default value shown and replace with ProductList.
    • Note: this cannot contain spaces and must start with a letter or underscore
  • You can also use the Name Box to easily navigate to the range locations. Select the drop down icon to view available selections.

ProductList


I’D LIKE TO SPEAK TO A MANAGER…

  • Navigate to the Formulas Tab
  • Select Name Manager
    • Name: The named ranges and objects contained within your workbook. The ProductList displays a named range icon while the tblProducts displays the icon for a table object.
    • Value: From our previous lesson we recognize the curly braces surrounding each named range value as information stored within an array
    • Refers To: Information either stored locally or declared using a function

NameManager


Make It Again:

  • Select a cell from within your worksheet
  • Navigate to the Data Tab
  • Select Data Validation
  • Source: =ProductList
    • Note: this must include the = symbol, otherwise Excel will interpret the source as a string of text
  • Select OK to complete

DataValidation

As you continue to add and remove items from the column titled Product within your table object, you will now notice that your drop down list is automatically updating without forcing you to make any changes to the cell range.


Honestly, I’ve stopped using cell ranges for some time now, and I want to make a strong argument that you should too. Even the lessons on this site were designed since day one specifically for tables and my use of cell ranges is rare unless absolutely necessary. If you’re skeptical of their importance there’s an entire book dedicated to JUST tables “Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables.” Ultimately, though if the majority of your time is spent cleansing unstructured information, you have two choices: either start loving it or start listing it.

12 thoughts on “Love It Or List It

  1. Alex, I love your blogging style. This is such an important topic. I spend so much time cleaning up junk data at work. Like you mentioned, names are a repeat offender — Jon vs. Jonathan, changed last names due to marriage, etc.

    Tools like this are essential to cut down on that 50-80% of prep work.

    Also just curious — you tagged this post Canada and the reference is lost on me.. what’s the Canada connection? Other than it is the runner up to Ohio in Excel talent 🙂

    1. “Love It or List It is a Canadian home design TV show. The show premiered as a primetime program on W Network on September 8, 2008, and has since aired on OWN Canada as well as HGTV in the United States.”

      I wanted to give a nod to the TV shows home country as well as capitalize on the current landscape of American politics – “I’ve had enough of this place. I’m leaving for Canada!” If someone is looking for Excel jobs in Canada and my blog post pops up I consider the lessons well learned from https://excel.tv/43-george-mount-at-georgejmount-com/ that you should look for every creative opportunity possible to market yourself and your talents to make it to the top of the list.

    1. Hey Kevin – Are you referencing the Amount column? I believe in my original pictures I was probably using a =ROUND(RAND()*100,2) formula so that could be completely MY BAD on that one if they are not matching up.

Questioning My Mental Health?