Originally written by Tim Wilson on May 10, 2011 NOTE: There is an updated version of this post posted. I recommend reading that one rather than this one.

Every once in a very rare while, I find myself not motivated to expound upon deep and meaningful subjects. So, this post is not about the, it’s not about my deepening fascination with, it’s not about the perplexing and depressing, and it’s not even about pondering when will have a web site. This is just a good ol’, “Hey, let’s look at a handy capability of Exceland how to use it to the best of its ability.” This came up last week when a co-worker asked me: “How do I get dropdowns working in cells in Excel?” She knew she had done it before, but she couldn’t remember how. In the course of showing her, I realized that, therein, was one of those handy little tips worth sharing. I’m going to walk through three different ways to accomplish this: • The totally common, mundane way — straightforward, but it has limitations • The way I always do it — almost no more effort to implement than the first waybut with fewer limitations • The way I may start doing it (sometimes), which would make the approach just that much slicker Bounce around as you see fit!

The Scenario You’re using Excel to enter a table of data, where one or more of the columns have a standard set of possible values. For instance, let’s say you’ve made a list of household chores, and you use that list to both assign a priority to each task as well as to note the status of the work: For both the Priority and the Status column, you’d like to enter the values using a dropdown menu, rather than needing to retype a value in each cell: The wrinkle is that you expect this list to live for a while, and there’s a good chance that you may want to have other values available for either the Priority or the Status columns (or both). We’ll get to that. The Standard Excel Way — Data Validation The quickest way to set this up is with basic data validation: • Highlight all of the cells that will use the same dropdown values • Select Data » Data Tools » Data Validation • Change the Allow dropdown to List • Enter the values in the Source box (separating different values using commas) • Click OK • Repeat for each set of cells that has a unique set of dropdown value options. That’s all there is to it, and it works.

The Limitation: Suppose that you decided you wanted to add a new value to the list of options, and that, rather than four cells right next to each other, this same data validation rule was used across numerous non-contiguous cells, even cells across multiple worksheets. Going in and updating the available list of values is a real pain.

How to get minecraft in mac for free no glitches 2017 pc. I didn’t see the point of playing a video game based around building when I could do the exact same thing with a Lego set—but after trying Minecraft with some friends I quickly learned the premise was addicting. Still, for all its good points, Minecraft is riddled with hundreds of glitches that can be surprisingly useful, if you know how to use them.

That brings us to My Standard Way — Data Validation with a Named Range I regularly use dropdowns to make Excel-based reports more dynamic — enabling the user to choose whether he wants to see a weekly or a monthly version of the report, as well as to select the specific date range (this isn’t so much for the user’s benefit as it is for mine — it means I don’t make a “new report” each week or month, but, rather, update the data in the same workbook and then update the dropdown to get the current report; read more about my approach for that in ). I have a standard way of generating dropdowns that gets around the limitation described earlier: rather than entering the list of values directly in the data validation dialog box, I reference a named range. Julien May 11th, 2011 I doubt I’ll always remember the formula for your 3rd option.