Excel Dropdowns Done Right: Data Validation and Named Ranges

Published by Tim Wilson on May 10, 2011 All posts from Tim Wilson

NOTE: There is an updated version of this post posted here. 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 latest turn in world of privacy legislation, it’s not about my deepening fascination with two-tiered segmentation, it’s not about the perplexing and depressing indefinite postponement of Demystified Days, and it’s not even about pondering when Team Evil Forces will have a web site.

Nope. Not today. This is just a good ol’, “Hey, let’s look at a handy capability of Excel…and 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 way…but 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:

  1. Highlight all of the cells that will use the same dropdown values
  2. Select Data » Data Tools » Data Validation
  3. Change the Allow dropdown to List
  4. Enter the values in the Source box (separating different values using commas)
  5. Click OK
  6. 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. 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 this post).

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. Using the same household chores scenario, I would accomplish the same end result, sans limitation, as follows:

  1. Add a new worksheet (I usually name it something like “Lookups” and then hide the worksheet once everything is set up so it’s never something that the user sees)
  2. Enter the lists of values at the top of that sheet — one list per column
  3. Select all of the values for one set of dropdown options and enter a name for that range (in this case, “List_Priority”)
  4. Repeat this  for the other list of values (I named it “List_Status” — I like to prepend the names of similar types of named ranges so that they group easily in the Named Ranges dialog box)
  5. Now, it’s the same basic process as described earlier, except, rather than entering the specific values in the data validation Source field, you enter a named range (note the “=” before the named range!):
  6. Click OK, and you’re good to go again!

Now, if you ever want to update values in the list, you can edit the values on the Lookups sheet. This won’t update the cell values that have already been populated — just the available values in the dropdown anywhere that named range is used.

The Limitation: even this approach has a limitation, but it has a couple of workarounds. Let’s say you decide to add a value to one of your lists — say you want to add “Unknown” as an option for Priority. If you simply type it at the bottom of the list, it falls outside of the named range and won’t be reflected in your dropdowns. Two different ways to work around this:

  • After adding the value, edit the named range (Formulas » Defined Names » Name Manager) to include the additional cell
  • Before adding the value, select the bottom value in the current list, right-click, and select Insert » Shift cells down » OK.This will have effectively expanded the named range by a cell. You can then either add the new value in the blank cell or copy and paste the “bottom” value (“Low” in this case) into the blank cell and then enter the new value into the bottom cell

Both of these approaches are a little bit clunky, so let’s add a twist to make the named ranges a bit more elegant…

Data Validation with Named Ranges with a Clever Twist

[Update: See the first comment below -- from Julien. As he notes, the formula described here is a little messy, and he proposes a cleaner solution. I'm leaving my original approach here to provide a "multiple ways to skin a cat" demonstration...but I expect I'll be using the approach described in the comment.]

This is simply a couple of additional steps beyond the steps described in the previous section to make the named ranges a little smarter:

  1. Select Formulas » Defined Names » Name Manager
  2. Select List_Priority and click Edit to see the current definition
  3. Replace the Refers to: formula with the following formula:


And, voila! You can now go nuts with adding and removing values from the Priority list and the dropdowns will have updated values with no additional effort!

To do the same for the List_Status named range, the formula you would use for the named range would be:


To break down the OFFSET formula usage (using List_Priority as the example):

  • Lookups!$A$2: start at cell $A$2, which is the first value in the list
  • 0: stay in that same row (so still at $A$2)
  • 0: stay in that same column (so, again, still at $A$2)
  • COUNTA(Lookups$A:$A)-1: count the number of cells in column A that have values and then subtract 1 (the heading cell: “Priority”); grab an area that is that tall, starting with the cell currently “selected” ($A$2)

By checking Excel’s documentation on the OFFSET function and fiddling around a little bit with the formula, you can see how it’s working pretty easily.

Is It Worth the Effort?

I always use the second option described in this post. You just never know when a hastily hacked together spreadsheet will get “legs” and start growing and expanding its footprint. Better to spend an extra 10 seconds to add flexibility and maintainability.

Will I use the third option? I might. We’ll see. It didn’t occur to me that I should even try until I showed my co-worker the second option…and then watched her immediately get tripped up trying to add a new value to the list. If I’m handing off a document where flexibility in the dropdown values is needed, I might just Google my way back to this post to see how it’s done!


Categorized under Excel

  • Julien

    I doubt I’ll always remember the formula for your 3rd option.

    So what I usually do is:
    - set a tab for all the dropdown values
    - in column A, for instance, give the name for the list, like tasks
    - in column B, give the values for the list, just as you did, but without the header in the first row
    - select the whole column B and define a named range
    - use that name range in data validation, just like you did.

    Excal automatically removes the empty values from the name range and I just have to append my next value to the list.

    Hope this helps …

  • http://www.gilliganondata.com Tim Wilson

    Thanks, Julien! That is a cleaner approach for the 3rd option!

  • Matt

    Julien’s option may be cleaner in terms of adding future values, but it’s far less elegant if your name range is needed in other parts of your workbook. Moreover, you’ll need to add two columns for each dropdown / named range required.

  • http://www.gilliganondata.com Tim Wilson

    @Matt I’m not sure I understand the “if your named range is needed in other parts of your workbook” comment. As long as the range is set up to work for the whole workbook, I don’t think it’s any different than the third option I proposed, is it?

    As for the two columns requirement, that crossed my mind as a minor downside. However, one of the columns is solely for labeling purposes. If there were a lot of named ranges being created, I could see using just Column A and listing out, in order, what each of the named ranges are for reference. OR, even use a naming convention in the Name Manager that covers that.

    What these comments show, I think, is that there are multiple ways to skin this particular cat. I wish more Excel users realized that it was a cat worth skinning!

  • Elizabeth

    Much easier to type the wanted list, set it up as a table and then name the range. When you add a value at the bottom of the table the named range automatically stretches to include to the new value!

  • http://tunwinnaing.wordpress.com Tun Win Naing

    @Julien, ‘Excal automatically removes the empty values from the name range and I just have to append my next value to the list’

    Which Excel version you use? I’m using 2007 and blanks are showing up in the drop down list.

    @Tim Wilson, excel shows ‘you cannot use references to other worksheets or workbooks for data validation criteria’. Is this happening to you?

  • http://www.gilliganondata.com Tim Wilson

    @Tun Win Naing: I’ll need to track down a machine with Excel 2007 to confirm. Julien’s tip works great on 2010. As for the “you cannot use references…” are you having that happen with globally-defined name ranges as your reference for data validation? I’ve run into that issue in the past in some situations, but having a named range that is defined for the entire workbook has always worked fine.

  • http://tunwinnaing.wordpress.com Tun Win Naing

    Thanks @Tim Wilson, ‘you cannot use reference’ has been resolved now.

  • Ryan

    Sir, nicely posted. You said “I have an approach that updates cell values too, but that’s beyond the scope of this post.” Tim, I must have this method. Scope me in. It’s like one of those 3d photos that suddenly focus but I haven’t reached the suddenly focused part. Anyways, please share your method. As mentioned previously, I must have it.

  • http://www.severnunival.com Alex Lush

    Good post sir.

    I know this is nearly a year old but can anyone comment on the ability (or not) to use Tables instead of named ranges. Tables would be an obvious choice (for Excel 2007+ users) as they dynamically expand as you add new items (no need for the OFFSET formula), however the data validation screen does not seem to accept a table range as a list source (unless anyone can correct me on this).

    The workaround to allow use of tables is to create the table, then create a named range to point to the table range, then link to the named range!! This means your range does dynamically expand but the downside is you have to manually create a named range AS WELL AS the table!!

  • http://www.gilliganondata.com Tim Wilson

    @Alex Brilliant! Honestly, I only really discovered Excel tables in the last 9 months or so. I tried referencing a table directly from data validation, and you’re right, it doesn’t work. BUT, on a hunch, I then tried putting the table name inside of an INDIRECT function, and it did! So, if I created a table and left the default name as Table1, I then put the data validation formula as:


    It appears to work!

    Thanks for the comment — this post now needs to be dramatically shortened and rewritten!

  • Pingback: Excel Tables — Overlooked, Yet Awesome | Gilligan on Data by Tim Wilson

  • Francisco Flores

    Hi Tim,
    Would you be able to help me?
    I can provide a copy of the sheet I am working on if you need to see it.
    But I am trying to have the user select/type a style number and it will automatically list all similar entries that match the first characters as the user type them in. When done,
    the rest of the row will automatically be filled in with data pertinent to the selection.
    The problem exist because there are several color for one style, so after the user select the style, how would I be able to have the user select the color from the “Color” column using a drop down list just showing only the color for the style selected?
    Please advise

    • http://www.gilliganondata.com Tim Wilson

      Francisco – this sounds like something that some lookup tables could be used to make work. I’ll follow up with you via email to get an example document and see what we can do.

  • http://brisrocket.wordpress.com Andrew Newey

    Thanks Tim. Spending a little more time to make things more robust and scaleable is a good approach. Thanks for the article – good advice mate.

  • http://mpptree.blogspot.com/ Michael Pierce

    Tim – I definitely prefer the table approach, I’d just recommend using the column name in the INDIRECT() formula, rather than letting it default to the first column. That way, you can use values other than what’s in the first column. For example =INDIRECT(“Table1[Priority") versus =INDIRECT("Table1[Status]“).

  • http://www.goodcacao.com Edie

    Hello all-

    Quick question… I am working in a Workbook with a dynamically named range and am looking to use that named range in another sheet within the workbook. I tried the “Table” functionality, also something I am not familiar with, and got nothing, since I can’t use the table name in the Data Validation– even when I tried “Indirect” as a modifier.

    My problem: Data validation is not allowing my named range! Here is the OFFSET function I have as the source of the named range–> “=OFFSET(‘Ingredient List’!$A$1,1,0,COUNTA(‘Ingredient List’!$A:$A)-1,1)”. Its name is “Ingredients.”

    But Data validation won’t allow “Ingredients” as a source, because it says “The List Souce must me a delimited list, or a reference to single row or column.”

    Any ideas/workarounds/solutions? Appreciate your assistance.

    Just FYI- I am working in Excel for Mac 2011.

  • http://www.goodcacao.com Edie

    Hello all-

    Quick question… I am working in a Workbook with a dynamically named range and am looking to use that named range in another sheet within the workbook. I tried the “Table” functionality, also something I am not familiar with, and got nothing, since I can’t use the table name in the Data Validation– even when I tried “Indirect” as a modifier.

    My problem: Data validation is not allowing my named range! Here is the OFFSET function I have as the source of the named range–> “=OFFSET(‘Ingredient List’!$A$1,1,0,COUNTA(‘Ingredient List’!$A:$A)-1,1)”. Its name is “Ingredients.”

    But Data validation won’t allow “Ingredients” as a source, because it says “The List Source must me a delimited list, or a reference to single row or column.”

    Any ideas/workarounds/solutions? Appreciate your assistance.

    Just FYI- I am working in Excel for Mac 2011.

  • http://www.gilliganondata.com Tim Wilson

    Hi Edie,

    It may be that Excel for Mac is causing the issue — I’ve definitely run into issues with the Mac version of Excel when it comes to tables (and pivot tables) in the past.

  • http://borekb.cz/ Borek Bernard

    Hi, I would be interested in your approach to renaming values in the list so that the already entered values auto-update themselves. I am trying to figure it out and macros really seem like the only option at the moment.

    Thanks for a nice article BTW!

  • P. K.

    FYI – your recommended method utilising named ranges has one significant draw-back. It breaks the point of data validation in that Excel will no longer prevent input that is outside the named range. Essentially defeating almost the entire purpose of the exercise.

    • jwk

      The way I understand your statement, it is false. Excel will prevent input that is not in the (dynamic) named range. The named range expands to include a range of values that presumably is explicitly entered to be used as validation.

      • P. K.

        You’re wrong according to Microsoft Excel 2010. When using named ranges, Excel (for whatever reason), no longer prevents the user from imputing whatever they want whether it’s in the named range as specified by the validation rule or not. I’d almost classify it as a bug to be honest or at least an oversight by MS programmers. However if using simple formula e.g. “$A$1:$A$1000″, Excel will then enforce the validation rule thus only allowing the user to input what is specified within the aforementioned range.

    • http://tim.webanalyticsdemystified.com/ Tim Wilson

      That’s a good point. I primarily use data validation not for actual validation but, rather, to have in-cell dropdowns to enable the user to select from a list of values.

      Thanks for calling that out!

  • Daniel Boles

    “Let’s say you decide to add a value to one of your lists — say you want to add “Unknown” as an option for Priority.
    If you simply type it at the bottom of the list, it falls outside of
    the named range and won’t be reflected in your dropdowns. Two different
    ways to work around this:”

    Bro, do you even Excel?

    This scenario of unnoticed expansion of ranges arises often and is simply solved as follows.

    The easy, functional, and plainly logical way to work around this is to store the list of choices in a column of an Excel Table and to link the Named Range to that column – by name, not cell numbers or anything like that. When the table expands, the Named Range automatically reflects the expansion, and thus the list for Data Validation will now offer the new options from the column.

  • McKay Savage

    The very cleanest way is to create a named table of your data (rather than using named ranges) because this gets updated differently than ranges. The trick is that structured references of tables is not possible in the list validation formulas (why, who knows). The way around this is to create a name (File menu Insert:Name:Define…) of the structured reference you require. And presto, clean validation referring to a data table that will automatically adjust is reference as new data is added.

    So using your example if you name the whole table ValidationData

    then the structured reference for the priority column is:
    Define a new name ‘ValidationPriority’ with the formula above
    The formula for the validation is then simply:
    And presto! It’ll refer to whatever data is is column A

    Here is where I learned this: http://superuser.com/questions/235999/putting-table-name-as-a-reference-for-the-purpose-of-cell-values-validation

    • Mark Glotzbach

      Thank you for that advice. I was using the tables to manage input data as I like the dynamic nature of the table and structured references, however I couldn’t figure out why Data Validation wouldn’t work. The extra level of indirection saved me. I wouldn’t have figured it out on my own. Thanks again.

    • Howard Payne

      Good tip…

      A while back I came across this:


      …for the Data Validation List Source…(replace tablename and columnname with your table and column and notice the left and right brackets surrounding the column name all in quotes…)

      It works!

      I’m not sure how it differs from creating a Defined Name…that is, are there maintenance, performance, etc issues…

  • Darren Bartrup-Cook

    I used to use the OFFSET method, but found that this function is Volatile – meaning it recalculates any time anything needs recalculating on the spreadsheet. A better method is to use the INDEX function: =Lookups!$A$2:INDEX(Lookups!$A:$A,COUNTA(Lookups!$A:$A)) is the non-volatile version of the OFFSET formula above.

    • Tim Wilson

      That’s an interesting point, and something I wasn’t aware of. Are you saying that, similar to using SUMIFs when a pivot table would do, overuse of OFFSET can bog down the spreadsheet performance?

  • FiendDuke

    Thanks for this. Really helped me.

  • chandra shekar

    I have excel file with sheet one which contains data in text and numbers which are in bold. italic, red color and blue color. I need to create the drop down list in sheet 2. When I select the list, all text and numbers turns to normal. How can I retain the original form? Please help.

    • http://tim.webanalyticsdemystified.com/ Tim Wilson

      Are you asking for the dropdown list itself to have the text formatting applied to the values in the list? I don’t think that’s possible.

  • Mark Lazarides

    Hey, thanks for sharing this. I think I found a slightly neater way:

    * Make the list into a table (ie DropDwnValues_Table).

    * Create a named range of the table: ie DropDwn Values =Table[DropDwnValues_Table]

    * et voila. Adding to the bottom of your list normally (with standard table settings) adjust your table size, and thus your named range.


Recent Blog Posts

Is On-Demand Radio the Next Big Digital Channel?
Tim Wilson, Partner

No, I’m not referring to SecondLife (which, BTW, is still around and, apparently, still has life in it). I’m referring to the fact that podcasts just turned ten, and there are a lot of signs that they might be one of the "next big things" in digital. Earlier this year, when I wrote a post announcing the launch of the Digital Analytics Power Hour podcast, I listed three examples as to how it seemed like podcasts were making a comeback ...

Continue reading this article ... ... more from Tim Wilson

The Right Use for Real Time Data
Michele Kiss, Senior Partner

Vendors commonly pitch the need for “real-time” data and insights, without due consideration for the process, tools and support needed to act upon it. So when is real-time an advantage for an organization, and when does it serve as a distraction? And how should analysts respond to requests for real-time data and dashboards?

Continue reading this article ... ... more from Michele Kiss

Using Excel to Count Text Occurrences
Tim Wilson, Partner

I had this come up a couple of weeks ago with a client, and I realized it was something I’d done dozens of times…but had never written down the “how” on doing. So, here we go. This is a post about one very specific application of Excel, but it is also implicitly a post about how, with an intermediate level of knowledge of Excel, with a little bit of creativity, and a strong aversion to manually parsing/copying/pasting anything, a spreadsheet can accomplish a lot! And very quickly!

Continue reading this article ... ... more from Tim Wilson

The Curse of Bounce Rate and 'Easy' Metrics ...
Michele Kiss, Senior Partner

One of the benefits of having a number of friends in the analytics industry is the spirited (read: nerdy) debates we get in to. In one such recent discussion, we went back and forth over the merits of "bounce rate." I am (often vehemently) against the use of "bounce rate." However, when I stepped back, I realized you could summarize my argument against bounce rate quite simply ...

Continue reading this article ... ... more from Michele Kiss

Happy New Year from Web Analytics and Team Demystified
Eric T. Peterson, Senior Partner

Happy belated new year to everyone reading this blog — on behalf of everyone at Web Analytics Demystified and Team Demystified I sincerely hope you had a wonderful and relaxing holiday season and that you’re ready to wade back into the analytical and optimization fray! Since I last wrote a few cool things have happened ...

Continue reading this article ... ... more from Eric T. Peterson

Introducing the Digital Analytics Power Hour Podcast
Tim Wilson, Partner

Happy New Year! Reflecting on 2014, I have to give it high marks from a personal and professional fulfillment front, and I’m looking to outperform those results in 2015 ... by podcasting!

Continue reading this article ... ... more from Tim Wilson

Every Analyst Should Follow fivethirtyeight.com
Tim Wilson, Partner

I’ll admit it: I’m a Nate Silver fanboy. That fandom is rooted in my political junky-ism and dates back to the first iteration of fivethirtyeight.com back in 2008. Since then, Silver joined the New York Times, so fivethirtyeight.com migrated to be part of that media behemoth, and, more recently, Silver left the New York Times for ESPN — another media behemoth.

Continue reading this article ... ... more from Tim Wilson

Demystified's Data Governance Principles
John Lovett, Senior Partner

In digital analytics, "Governance" is a term that is used casually to mean many different things. In our experience at Web Analytics Demystified, every organization inherently recognizes that governance is an important component of their data strategy, yet every company has a different interpretation of what it means to govern their data. In an effort to dispel the misconceptions surrounding what it means to truly steward digital data, Web Analytics Demystified has developed seven data governance principles that all organizations collecting and using digital data should adhere to.

Continue reading this article ... ... more from John Lovett

Three Foundational Tips to Successfully Recruit in Analytics
Michele Kiss, Partner

Hiring in the competitive analytics industry is no easy feat. In most organizations, it can be hard enough to get headcount – let alone actually find the right person! These three foundational tips are drawn from successful hiring processes in a variety of verticals and organizations.

Continue reading this article ... ... more from Michele Kiss

Slack Demystified
Adam Greco, Senior Partner

Those of you who follow my blog have come to know that when I learn a product (like Adobe SiteCatalyst), I really get to know it and evangelize it. Back in the 90′s I learned the Lotus Notes enterprise collaboration software and soon became one of the most proficient Lotus Notes developers in the world, building most of Arthur Andersen’s global internal Lotus Notes apps. In the 2000′s, I came across Omniture SiteCatalyst, and after a while had published hundreds of blog posts on Omniture’s (Adobe’s) website and my own and eventually a book! One of my favorite pastimes is finding creative ways to apply a technology to solve everyday problems or to make life easier.

Continue reading this article ... ... more from Adam Greco

Profile Website Visitors via Campaign Codes and More
Adam Greco, Senior Partner

One of the things customers ask me about is the ability to profile website visitors. Unfortunately, most visitors to websites are anonymous, so you don't know if they are young, old, rich, poor, etc. If you are lucky enough to have authentication or a login on your website, you may have some of this information, but for most of my clients the "known" percentage is relatively low. In this post, I'll share some things you can do to increase your visitor profiling by using advertising campaigns and other tools.

Continue reading this article ... ... more from Adam Greco

A Primer on Cookies in Web Analytics
Josh West, Partner

Some of you may have noticed that I don't blog as much as some of my colleagues (not to mention any names, but this one, this one, or this one). The main reason is that I'm a total nerd (just ask my wife), but in a way that is different from most analytics professionals. I don't spend all day in the data - I spend all data writing code. And it's often hard to translate code into entertaining blog posts, especially for the folks that tend to spend a lot of time reading what my partners have to say.

Continue reading this article ... ... more from Josh West

Excel Dropdowns Done Right
Tim Wilson, Partner

Do you used in-cell dropdowns in your spreadsheets? I used them all the time. It's both an ease-of-use and a data quality maneuver: clicking a dropdown is faster than typing a value, and it's really hard to mis-type a value when you're not actually typing!

Continue reading this article ... ... more from Tim Wilson

The Downfall of Tesco and the Omniscience of Analytics
Michele Kiss, Partner

Yesterday, an article in the Harvard Business Review provided food for thought for the analytics industry. In Tesco's Downfall Is a Warning to Data-Driven Retailers, author Michael Schrage ponders how a darling of the "analytics as a competitive advantage" stories, British retailer Tesco, failed so spectacularly - despite a wealth of data and customer insight.

Continue reading this article ... ... more from Michele Kiss

Creating Conversion Funnels via Segmentation
Adam Greco, Senior Partner

Regardless of what type of website you manage, it is bound to have some sort of conversion funnel. If you are an online retailer, your funnel may consist of people looking at products, selecting products, and then buying products. If you are a B2B company, your funnel may be higher-level like acquisition, research, trial and then form completion.

Continue reading this article ... ... more from Adam Greco

10 Tips for Building a Dashboard in Excel
Tim Wilson, Partner

This post has an unintentionally link bait-y post title, I realize. But, I did a quick thought experiment a few weeks ago after walking a client through the structure of a dashboard I'd built for them to see if I could come up with ten discrete tips that I'd put to use when I built it. Turns out…I can!

Continue reading this article ... ... more from Tim Wilson

Exploring Optimal Post Timing ... Redux
Tim Wilson, Partner

Back in 2012, I developed an Excel worksheet that would take post-level data exported from Facebook Insights and do a little pivot tabling on it to generate some simple heat maps that would provide a visual way to explore when, for a given page, the optimal times of day and days of the week are for posting.

Continue reading this article ... ... more from Tim Wilson

What I Love: Adobe and Google Analytics*
Tim Wilson, Partner

While in Atlanta last week for ACCELERATE, I got into the age-old discussion of "Adobe Analytics vs. Google Analytics." I'm up to my elbows in both of them, and they're both gunning for each other, so this list is a lot shorter than it would have been a couple of years ago.

Continue reading this article ... ... more from Tim Wilson

Top 5 Metrics You're Measuring Incorrectly ... or Not
Eric T. Peterson, Senior Partner

Last night as I was casually perusing the days digital analytics news - yes, yes I really do that - I came across a headline and article that got my attention. While the article's title ("Top 5 Metrics You're Measuring Incorrectly") is the sort I am used to seeing in our Buzzfeed-ified world of pithy "made you click" headlines, it was the article's author that got my attention.

Continue reading this article ... ... more from Eric T. Peterson

Bulletproof Business Requirements
John Lovett, Senior Partner

As a digital analytics professional, you've probably been tasked with collecting business requirements for measuring a new website/app/feature/etc. This seems like a task that's easy enough, but all too often people get wrapped around the axle and fail to capture what's truly important from a business users' perspective. The result is typically a great deal of wasted time, frustrated business users, and a deep-seated distrust for analytics data.

Continue reading this article ... ... more from John Lovett

Welcome to Team Demystified: Nancy Koons and Elizabeth Eckels!
Eric T. Peterson, Senior Partner

I am delighted to announce that our Team Demystified business unit is continuing to expand with the addition of Nancy Koons and Elizabeth "Smalls" Eckels. Our Team Demystified efforts are exceeding all expectation and are allowing Web Analytics Demystified to provide truly world-class services to our Enterprise-class clients at an entirely new scale.

Continue reading this article ... ... more from Eric T. Peterson

When to Use Variables vs SAINT in Adobe Analytics
Adam Greco, Senior Partner

In one of my recent Adobe SiteCatalyst (Analytics) "Top Gun" training classes, a student asked me the following question: When should you use a variable (i.e. eVar or sProp) vs. using SAINT Classifications? This is an interesting question that comes up often, so I thought I would share my thoughts on this and my rules of thumb on the topic.

Continue reading this article ... ... more from Adam Greco

5 Tips for #ACCELERATE Exceptionalism
Tim Wilson, Partner

Next month's ACCELERATE conference in Atlanta on September 18th will be the fifth - FIFTH!!! - one. I wish I could say I'd attended every one, but, sadly, I missed Boston due to a recent job change at the time. I was there in San Francisco in 2010, I made a day trip to Chicago in 2011, and I personally scheduled fantastic weather for Columbus in 2013.

Continue reading this article ... ... more from Tim Wilson

I've Become Aware that Awareness Is a #measure Bugaboo
Tim Wilson, Partner

A Big Question that social and digital media marketers grapple with constantly, whether they realize it or not: Is "awareness" a valid objective for marketing activity?

I've gotten into more than a few heated debates that, at their core, center around this question. Some of those debates have been with myself (those are the ones where I most need a skilled moderator!).

Continue reading this article ... ... more from Tim Wilson

Advanced Conversion Syntax Merchandising
Adam Greco, Senior Partner

As I have mentioned in the past, one of the Adobe SiteCatalyst (Analytics) topics I loathe talking about is Product Merchandising. Product Merchandising is complicated and often leaves people scratching their heads in my "Top Gun" training classes. However, many people have mentioned to me that my previous post on Product Merchandising eVars helped them a lot so I am going to continue sharing information on this topic.

Continue reading this article ... ... more from Adam Greco

Team Demystified Update from Wendy Greco
Eric T. Peterson, Senior Partner

When Eric Peterson asked me to lead Team Demystified a year ago, I couldn't say no! Having seen how hard all of the Web Analytics Demystified partners work and that they are still not able to keep up with the demand of clients for their services, it made sense for Web Analytics Demystified to find another way to scale their services. Since the Demystified team knows all of the best people in our industry and has tons of great clients, it is not surprising that our new Team Demystified venture has taken off as quickly as it has.

Continue reading this article ... ... more from Eric T. Peterson

SiteCatalyst Unannounced Features
Adam Greco, Senior Partner

Lately, Adobe has been sneaking in some cool new features into the SiteCatalyst product and doing it without much fanfare. While I am sure these are buried somewhere in release notes, I thought I'd call out two of them that I really like, so you know that they are there.

Continue reading this article ... ... more from Adam Greco

Hello. I'm a Radical Analytics Pragmatist
Tim Wilson, Partner

I was reading a post last week by one of the Big Names in web analytics…and it royally pissed me off. I started to comment and then thought, "Why pick a fight?" We've had more than enough of those for our little industry over the past few years. So I let it go.

Except I didn't let it go.

Continue reading this article ... ... more from Tim Wilson

Competitor Pricing Analysis
Adam Greco, Senior Partner

One of my newest clients is in a highly competitive business in which they sell similar products as other retailers. These days, many online retailers have a hunch that they are being "Amazon-ed," which they define as visitors finding products on their website and then going to see if they can get it cheaper/faster on Amazon.com. This client was attempting to use time spent on page as a way to tell if/when visitors were leaving their site to go price shopping.

Continue reading this article ... ... more from Adam Greco

How to Deliver Better Recommendations: Forecast the Impact!
Michele Kiss, Partner

One of the most valuable ways to be sure your recommendations are heard is to forecast the impact of your proposal. Consider what is more likely to be heard: "I think we should do X ..." vs "I think we should do X, and with a 2% increase in conversion, that would drive a $1MM increase in revenue ..."

Continue reading this article ... ... more from Michele Kiss

ACCELERATE 2014 "Advanced Analytics Education" Classes Posted
Eric T. Peterson, Senior Partner

I am delighted to share the news that our 2014 "Advanced Analytics Education" classes have been posted and are available for registration. We expanded our offering this year and will be offering four concurrent analytics and optimization training sessions from all of the Web Analytics Demystified Partners and Senior Partners on September 16th and 17th at the Cobb Galaria in Atlanta, Georgia.

Continue reading this article ... ... more from Eric T. Peterson

Product Cart Addition Sequence
Adam Greco, Senior Partner

In working with a client recently, an interesting question arose around cart additions. This client wanted to know the order in which visitors were adding products to the shopping cart. Which products tended to be added first, second third, etc.? They also wanted to know which products were added after a specific product was added to the cart (i.e. if a visitor adds product A, what is the next product they tend to add?). Finally, they wondered which cart add product combinations most often lead to orders.

Continue reading this article ... ... more from Adam Greco

7 Tips For Delivering Better Analytics Recommendations
Michele Kiss, Partner

As an analyst, your value is not just in the data you deliver, but in the insight and recommendations you can provide. But what is an analyst to do when those recommendations seem to fall on deaf ears?

Continue reading this article ... ... more from Michele Kiss

Overcoming The Analyst Curse: DON'T Show Your Math!
Michele Kiss, Partner

If I could give one piece of advice to an aspiring analyst, it would be this: Stop showing your "math". A tendency towards "TMI deliverables" is common, especially in newer analysts. However, while analysts typically do this in an attempt to demonstrate credibility ("See? I used all the right data and methods!") they do so at the expense of actually being heard.

Continue reading this article ... ... more from Michele Kiss

Making Tables of Numbers Comprehensible
Tim Wilson, Partner

I'm always amazed (read: dismayed) when I see the results of an analysis presented with a key set of the results delivered as a raw table of numbers. It is impossible to instantly comprehend a data table that has more than 3 or 4 rows and 3 or 4 columns. And, "instant comprehension" should be the goal of any presentation of information - it's the hook that gets your audience's brain wrapped around the material and ready to ponder it more deeply.

Continue reading this article ... ... more from Tim Wilson

Automating the Cleanup of Facebook Insights Exports
Tim Wilson, Partner

This post (the download, really - it's not much of a post) is about dealing with exports from Facebook Insights. If that's not something you do, skip it. Go back to Facebook and watch some cat videos. If you are in a situation where you get data about your Facebook page by exporting .csv or .xls files from the Facebook Insights web interface, then you probably sometimes think you need a 52" monitor to manage the horizontal scrolling.

Continue reading this article ... ... more from Tim Wilson

The Recent Forrester Wave on Web Analytics ... is Wrong
Eric T. Peterson, Senior Partner

Having worked as an industry analyst back in the day I still find myself interested in what the analyst community has to say about web analytics, especially when it comes to vendor evaluation. The evaluations are interesting because of the sheer amount of work that goes into them in an attempt to distill entire companies down into simple infographics, tables, and single paragraph summaries.

Continue reading this article ... ... more from Eric T. Peterson

Funnel Visualizations That Make Sense
Tim Wilson, Partner

Funnels, as a concept, make some sense (although someone once made a good argument that they make no sense, since, when the concept is applied by marketers, the funnel is really more a "very, very leaky funnel," which would be a worthless funnel - real-world funnels get all of a liquid from a wide opening through a smaller spout; but, let's not quibble).

Continue reading this article ... ... more from Tim Wilson

Reenergizing Your Web Analytics Program & Implementation
Adam Greco, Senior Partner

Those of you who have read my blog posts (and book) over the years, know that I have lots of opinions when it comes to web analytics, web analytics implementations and especially those using Adobe Analytics. Whenever possible, I try to impart lessons I have learned during my web analytics career so you can improve things at your organization.

Continue reading this article ... ... more from Adam Greco

Registration for ACCELERATE 2014 is now open
Eric T. Peterson, Senior Partner

I am excited to announce that registration for ACCELERATE 2014 on September 18th in Atlanta, Georgia is now open. You can learn more about the event and our unique "Ten Tips in Twenty Minutes" format on our ACCELERATE mini-site, and we plan to have registration open for our Advanced Analytics Education pre-ACCELERATE training sessions in the coming weeks.

Continue reading this article ... ... more from Eric T. Peterson

Current Order Value
Adam Greco, Senior Partner

I recently had a client pose an interesting question related to their shopping cart. They wanted to know the distribution of money its visitors were bringing with them to each step of the shopping cart funnel.

Continue reading this article ... ... more from Adam Greco

A Guide to Segment Sharing in Adobe Analytics
Tim Wilson, Partner

Over the past year, I've run into situations multiple times where I wanted an Adobe Analytics segment to be available in multiple Adobe Analytics platforms. It turns out…that's not as easy as it sounds. I actually went multiple rounds with Client Care once trying to get it figured out. And, I've found "the answer" on more than one occasion, only to later realize that that answer was a bit misguided.

Continue reading this article ... ... more from Tim Wilson

Currencies & Exchange Rates
Adam Greco, Senior Partner

If your web analytics work covers websites or apps that span different countries, there are some important aspects of Adobe SiteCatalyst (Analytics) that you must know. In this post, I will share some of the things I have learned over the years related to currencies and exchange rates in SiteCatalyst.

Continue reading this article ... ... more from Adam Greco

Linking Authenticated Visitors Across Devices
Adam Greco, Senior Partner

In the last few years, people have become accustomed to using multiple digital devices simultaneously. While watching the recent winter Olympics, consumers might be on the Olympics website, while also using native mobile or tablet apps. As a result, some of my clients have asked me whether it is possible to link visits and paths across these devices so they can see cross-device paths and other behaviors.

Continue reading this article ... ... more from Adam Greco

The 80/20 Rule for Analytics Teams
Eric T. Peterson, Senior Partner

I had the pleasure last week of visiting with one of Web Analytics Demystified's longest-standing and, at least from a digital analytical perspective, most successful clients. The team has grown tremendously over the years in terms of size and, more importantly, stature within the broader multi-channel business and has become one of the most productive and mature digital analytics groups that I personally am aware of across the industry.

Continue reading this article ... ... more from Eric T. Peterson

Ten Things You Should ALWAYS Do (or Not Do) in Excel
Tim Wilson, Partner

Last week I was surprised by the Twitter conversation a fairly innocuous vent-via-Twitter tweet started, with several people noting that they had no idea you could simple turn off the gridlines.

Continue reading this article ... ... more from Tim Wilson

Omni Man (and Team Demystified) Needs You!
Adam Greco, Senior Partner

As someone in the web analytics field, you probably hear how lucky you are due to the fact that there are always web analytics jobs available. When the rest of the country is looking for work and you get daily calls from recruiters, it isn't a bad position to be in! At Web Analytics Demystified, we have more than doubled in the past year and still cannot keep up with the demand, so I am reaching out to you ...

Continue reading this article ... ... more from Adam Greco

A Useful Framework for Social Media "Engagements"
Tim Wilson, Partner

Whether you have a single toe dipped in the waters of social media analytics or are fully submerged and drowning, you've almost certainly grappled with "engagement." This post isn't going to answer the question "Is engagement ROI?" ...

Continue reading this article ... ... more from Tim Wilson

It's not about "Big Data", it's about the "RIGHT data"
Michele Kiss, Partner

Unless you've been living under a rock, you have heard (and perhaps grown tired) of the buzzword "big data." But in attempts to chase the "next shiny thing", companies may focus too much on "big data" rather than the "right data."

Continue reading this article ... ... more from Michele Kiss

Eric T.








Contact Us

You can contact Web Analytics Demystified day or night via email or by reaching out to one of our Partners directly.

» Contact Information

Web Analytics Demystified, Inc.
P.O. Box 13303
Portland, OR 97213
(503) 282-2601

Useful Links