[UPDATE 1/19/2015: A couple of comments have pointed out that COUNTIF would address this scenario in a single formula. That’s a great point…and one that had not occurred to me. The overall scenario here is pretty straightforward, so there are likely other equally efficient (or more efficient) ways to address the task. I’m leaving the post as is, because I think it’s a useful exercise on how nested Excel formulas can be used to parse text. And I’m also curious what other solutions might get proposed in the comments.]

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!

The Use Case

The use case where I’ve used this approach most often is with social media exports — most often, with Twitter. In the most recent situation, my client had an export of all tweets that used a specific conference hashtag. Her organization was trying to introduce a secondary (relevant) topic to the conversation around the conference, and they had a separate hashtag. So, she was looking to identify, from the 16,000 tweets at the event, what percent of them also included the hashtag that her organization was interested in? That’s a simple and reasonable ask, and, if the tweet volume is reasonable (let’s say less than 500,000), easy enough to do in under 2 minutes in Excel.

The Example

Obviously, I’m not going to use my client’s data here. But, it turns out that my own tweets are a reasonable proxy. I tweet sporadically, but I know that a decent chunk of my tweets use the “#measure” hashtag. So, how many of my tweets use that hashtag? Thanks to http://analytics.twitter.com, it’s easy enough for me to get an export of my tweets. I just exported the default, which was 1,356 tweets going back to early October 2013. Opening the .csv in Excel, it looks like this:

Simple enough. I just want to go through and add a flag to identify every row where column C contains the word “#measure.”

Step 1: Make It a Table and Add a Column for the Flag

This step isn’t strictly necessary, but Excel tables make soooooo many things more easy, that I’m including it here. If you’re, like, “What are you talking about? Isn’t data in rows and columns in a spreadsheet a ‘table’ already?” well… stop reading this post and go read this one. It’s two clicks to make the data into a table, so do that…and add a column where we’re going to put our flag:

Simple enough. I just want to go through and add a flag to identify every row where column C contains the word “#measure.”

Step 2: Use FIND() to Look for ‘#measure’

This is the core formula. All we need to do is add the FIND() formula to the rows in the first column to search column D (“[@[Tweet text]]”) for occurrences of “#measure:”

Once we add that formula to cell A2, it will autofill for all rows in the table and the table will now look like this:

That’s kind of ugly, isn’t it? But we now know that rows 7, 8, 12, and 19 all included the word “#measure,” because the FIND() formula tells us where in the cell the word started. All of the other rows didn’t include the word “#measure,” so they returned a #VALUE error.

The bulk of the work is done…but we’re not quite there yet, because we don’t yet have a pure “flag.”

Step 3: Use ISERROR() to Make a Flag

We can nest our original FIND() formula inside an ISERROR() formula. If we do that, then all of the #VALUE values will instead show as “TRUE,” and all of the situations where the FIND() formula returns an actual number will show as “FALSE.”

The resulting table:

<Whew> Isn’t that cleaner? Now, every value is either “TRUE” or “FALSE,” so we now have a true “flag.” But, this flag is a little confusing, because it’s “FALSE” whenever the tweet contains the hashtag “#measure.” That may be fine if we can just keep that straight and jump straight to step 5, but why not make it a bit more intuitive with one additional update to the formula?

Step 4: Use IF() to Flip the Flag

Since our ISERROR() is going to return a TRUE/FALSE response, we can nest the whole formula in an IF() statement to make those flags into a Yes/No flag that makes more intuitive sense:

The IF returns “Yes” instead of “FALSE” and returns “No” instead of “TRUE.” Not necessary for this exercise, but I went ahead and added a little conditional formatting to highlight the rows that include ‘#measure’ (based on whether the Column A value is “Yes”):

Step 5: A Case-Sensitivity Precaution

In this example, all of the tweets are my own, and I always use an all-lowercase “#measure.” But “FIND” is case-sensitive, so, what if I had used “#Measure” a few times? Or “#MEASURE?” Those would be mis-flagged using the above approach. So, it’s worth one more tweak to the formula to force the entire tweet to be all-lowercase before running the FIND() formula on it:

Note how the LOWER() addition is inside the FIND() function. Since Excel uses parentheses like plain old math does, the innermost parentheses (functions) will get evaluated first, and the first thing we want to do is make the tweet text all lowercase.

Step 6: Summarize with a Pivot Table

There are lots of ways this data could be summarized. You could just sort the table descending by the first column and see what row the last “Yes” occurs on. You could have used “1” and “0” rather than “Yes” and “No” in the formula and then just summed column A.

But, I’m never one to miss an opportunity to apply a pivot table. In a handful of clicks, we get our summary:

Voila! 14% of the tweets in the data set included the string “#measure” (regardless of case usage).

In Reality: Six Steps Were Three

When I most recently did this for a client, it wasn’t really six steps. It was three: 1) create the table, 2) plug in a formula, 3) generate a pivot table. But, I realize that just throwing out =IF(ISERROR(FIND(“#measure”,”LOWER([@[Tweet text]]))),”No”,”Yes”)  can be a little intimidating. I do regularly iterate “from the inside out” when building formulas. The result can look messy, but not as messy as manually inspecting tweets!

Now…chime in with the other 10 ways this exercise could have been approached entirely differently!

 

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!

I use in-cell dropdowns a lot when I’m making a list of things and I want to classify the values in the list. For instance:

• Prioritizing the items: high / medium / low
• Assigning a person (submitter or owner of a task, for instance, if that’s a finite list)
• Assigning a status: open / in work / completed / on hold / cancelled
• Assessing whether each item meets some sort of criteria: yes / no / unknown

If each of these criteria uses a dropdown list that is well-built, then I’m just a few clicks away from List Analysis Nirvana Via Pivot Tables (LANVPT!).

I also use in-cell dropdowns when I’m giving the recipients of the spreadsheet some simple controls over what is displayed and how. For instance:

• Selecting a start date or end data (or both) for the displayed data
• Selecting which metric to display or to sort values by
• Selecting the granularity of trends being displayed (daily / weekly / monthly)

I’ve actually counted the number of ways in-cell dropdowns can be used and arrived at a number: oodles.

Now, while Excel form controls can be used to create dropdowns, I always-always-always use Excel’s “data validation” capability to create these (unfortunately, data validation in Google spreadsheets blows like a tuba player in the Boston Philharmonic…but I use it there, too, to the extent possible).

The technique I use is simple, fast (don’t be turned off by the length of this post – it takes less than 2 minutes to set up once you’ve done it a couple of times!), and flexible, and it allows easily updating a bunch of cells that need to have the same set of values in their dropdown. It relies on four main features of Excel:

• Data validation (obviously)
• Hidden sheets
• Tables
• The INDIRECT() function

“Enough with the lengthy preamble!” you exclaim. “I’m sold! Get on with it!”

Setting the Stage: The Example We’ll work from

Let’s say I have a spreadsheet that lists a bunch of different ideas for how I could try to take better pictures (this is a silly example, obviously – clearly, I just need to more liberally apply Instagram filters!).

Let’s say my initial list looks something like this:

Over time, I know I’m going to be adding to the list, and I’d really love to be able to select the value in the second column from a dropdown:

That’s really all we’re looking to do (but I’m going to add a small twist later in the example).

The Wrong Way: Entering the List of Values

The obvious way (if you know to search for “data validation”…which ain’t exactly “obvious,” IMHO), to create these dropdowns is to highlight all the cells where you want the dropdown to appear, click on Data >> Data Validation, and then enter the list of values you want to use:

That seems like a good way to go about things, but it is a fragile and risky approach indeed, as we’ll discuss later (spoiler: it has to do with updating that list over time).

There is a better way, and it doesn’t take much more time to set up than the painting-yourself-into-a-corner approach I just described.

Step 1: A Table on a Hidden Worksheet

In my last post – about Excel-based dashboards – one of my tips was to always create a Settings sheet. If you have one of those, use it. If not, make a new worksheet called Lookups (or Settings or Tim Is Awesome…the name of the worksheet doesn’t really matter).

On that worksheet, make a list (with a heading) of the values you want in your dropdown:

You may wind up with multiple lists on this sheet. You can arrange them any way that makes sense. Ultimately, we’ll hide this sheet, anyway.

Tip: In practice, when I’m creating high/medium/low-type lists, I often wind up adding a number to them: 1 – High / 2 – Medium / 3 – Low. That makes the list more easily sortable. Alas! In English, an alphabetical sort of these three words does not put them in a reasonable order!

After making the initial list, turn it into an Excel table:

1. Select any cell in the list
2. Select Insert >> Table

The result, if you’re using the default Excel sheet, looks something like this:

You can adjust the table style if you like (I usually do), but that’s not strictly necessary.

Then, for built-in documentation purposes, give the table a name by clicking on Design under Table Tools and entering a name:

I like to prepend these tables with some sort of consistent prefix – “tbl_,” “lookup_,” or even simply “t_.” That way , if I use a lot of named ranges, all of my lookup tables will show up in one group in the Name Manager.

Step 2: Figure Out How to Reference Those Cells

Ultimately, we want the dropdown list to be “the first column in this (simple, 1-column) table.” But, the specific syntax for referencing table components can get a little confusing, so we can cheat to figure out exactly how to reference the cells.

First, make an unused cell active and put an equal sign in it:

Then, move the cursor over the heading of the table until a black down arrow appears. Click in that spot, and just the data in the column (not the whole column, and not the heading for the column) gets highlighted. And, in our blank cell, we now have the proper syntax for referencing that column:

All we have to do is cut the value in that blank cell (omitting the equal sign, so just “lookup_cost[Cost]” in this example) so we have it on the clipboard.

In theory, we can now hide this worksheet. In practice, we’ll leave it unhidden until we’ve got everything built out and the spreadsheet is ready for distribution. At that point, we might even go beyond hiding it and set its property to xlVeryHidden (overachievers reading this post who do not know how to do that already: feel free to open a new tab and start Googling).

Step 3: Create the Data Validation

Now, we go back to the sheet where we want to use this list for our dropdown. The first thing we do is exactly what I described in the “Wrong Way” section earlier: we highlight the cells we want the dropdowns in and select Data >> Data Validation.

But, then, rather than manually entering a list of values for the dropdown, we actually enter a formula:

“Whoaaaaaa, Nellie! Where did this ‘INDIRECT’ nonsense come from?!!!” you ask! Well…that’s the teensiest of wrinkles: Excel, for some inexplicable reason, just doesn’t quite play nice with normal cell references when it comes to data validation. So, logically, this should work:

=lookup_cost[Cost]

In practice, we have to drop that reference inside the INDIRECT() function (inside quotation marks!) for it to actually work:

=INDIRECT(“lookup_cost[Cost]”)

‘tis a trifle to do!

That’s it! You’re done!

But…the Reason for Doing It This Way?!

In the example we’re using here, what happens if we realize that our list is incomplete? What happens if we suddenly realize we really need a “Very High” option in the dropdown and we need an “Unknown” value in the dropdown? (In a more realistic example, we may realize that our task status dropdowns of Open / In Work / Complete are missing two values: Cancelled and On Hold).

In our “Wrong Way” approach (entering the list values manually in the Data Validation dialog box), it’s still no big deal: we simply re-highlight the cells, select Data >> Data Validation again, and update our list.

That’s a little clunky, but maybe not too bad. BUT, imagine what happens if we actually have multiple lists: in addition to this worksheet, we have another worksheet where we’ve listed ways I can become a better guitar player, and yet another worksheet where we’ve listed ways I can become a better analyst? If we want to update the possible values on all three worksheets, we have to go worksheet by worksheet selecting all the cells with dropdowns in them and update the list values. Ick!

That’s where the beauty of the approach described in this post comes in. By using a table on a hidden worksheet, all we have to do is update a single table! And, tables have the nice feature of autoexpanding when you enter something in the cell that abuts the table. So, when we enter “Very High” like this:

Once we press Enter, the table expands to include the new value:

We can also insert a new row in the table and add a new value anywhere in the list (in this case, “Unknown” as the first entry):

This won’t change any of the values already selected in our existing dropdowns, but, now, all of the dropdowns that reference that table will have an updated list of values to choose from:

Easy, peasy, no?

Extending the Usage a Bit

While “updating a bunch of cells that have the same dropdown list values” is the most compelling use case (in my mind) for this technique, there are some other ways it can come in handy.

Example 1: Say we have a date selector dropdown that updates what gets displayed on a bunch of charts. As we add data to the spreadsheet, there are more possible dates that could be selected. But, we want the dropdown to be manageable, so we only want to give the user a list of the most recent 12 weeks to choose from.

The solution? A 12-row table where we dynamically figure out what the most recent available data is (with a formula), and we put that in the top row. Then, with a simple formula ([the cell above this one] – 7), we populate the next 11 values in the table. If we use data validation to reference that table, we always have a compact, timely list!

Example 2: Say we want to assign numeric values to Unknown / Low / Medium / High / Very High in our original example so we can estimate total costs. We can simply add another column to the lookup_cost table and populate a value for each option (if you’re using Adobe Analytics, think of this as Classifications; if you’re using Google Analytics, think of it as dimension-widening; if you’re not a web analyst, skip this entire parenthetical comment). Now, with a simple VLOOKUP, we can grab a numeric value for each entry, and we know the VLOOKUP will always return a value, because the list of options in the dropdown comes from the same table where each of those options has an assigned value:

Note that, even though we added a column to the table, the original reference to the first column — lookup_cost[Cost] — is still valid. We haven’t affected the dropdown functionality itself at all.

Slicker than greased baby poop, ain’t it?

What other tips do you have for creating dropdowns in Excel? I’d love to hear ‘em!

 

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! I struggled to figure out the best order to put them in, loosely tried to make it from an early-to-late in the process thing, and then threw my hands up and just started writing.

Pre-Tip: Skip the “Insights”

This is more of a soapbox than a tactical tip, so I’m sneaking it in before we get into the meat of the content:

Do NOT leave a place for text-based insights or recommendations.

I’ll leave it at that. If you care to hear (or argue with) my rationale…I’ve written a whole post on it.

Tip #1: Plan First

This should go without saying, but massive overhauls after a dashboard are built can be tough. So, start by getting s solid set of requirements that includes:

• What metrics will be included
• Which of these metrics are KPIs versus just supporting/contextual information
• Which views of each metric will be included: a trend (and, if so, its granularity: daily, weekly, monthly, etc.), a total, a comparison to a target or a prior period, etc.

I often even do a little sketching of the dashboard — quick thoughts on how I can organize the information based on what I’m planning to include. It’s a lot faster to quickly scrawl boxes on a piece of paper than doing wholesale rearrangements of the information in Excel.

I’ve never had my plan be a 100% match with the final product. But, if I get it 80% figured out up front, I’ll have more time to figure out how to best cover what I discover along the way as I build it out.

Tip #2: Stick to One Page (One Screen)

This is a neuroscience-based tip. As Stephen Few puts it:

“…information that belongs together should never be fragmented into multiple dashboards, and scrolling should not be required to see it all. Once the information is no longer visible, unless it is one of the [3 or 4] chunks stored in working memory, it is no longer available.”

Since the point of a dashboard is to provide an at-a-glance view of performance, and since we want to see it all at once, the dashboard should be limited to one screen.

This doesn’t mean that there can’t be additional screens of drilldown information, but this should be wholly contained subsets of data. The main dashboard should be one screen and one screen only.

Tip #3: Figure Out Some “Widgets”

Based on the planning that you did, you’ll likely have 1-3 different types of metric displays. Figure out how you’re going to display them. Maybe, for some of the metrics, you need to show a total, a comparison to target (because they’re KPIs), a comparison to a prior period, and a trend. For others, you may just need to show the total and a comparison to a prior period. I like to design “widgets” for each type of metric display that I’m going to include. Some examples:

It’s worth putting some care into the design of your widgets. Figuring out the font size (I like to really bump my KPIs up by a lot), the palette (match your corporate one!), and how multiples of the widgets will fit next to or above/below each other (in the widgets above, you can see where there are labels outside of the widget and can imagine how those labels don’t need to be repeated when the widget is reused for additional metrics).

Tip #4: Make Narrow Columns

While Excel is way better than any of the major web analytics tools when it comes to layout flexibility, it’s still, inherently, a grid. For years, I would try to figure out what the ideal configuration of column sizes was to support the layout that I wanted. A year or two ago, I got tired of inadvertently painting myself into corners with that approach and started just making all of my columns (for the presentation layer sheet — not for all sheets! More on this in Tip #6) the same width and narrow:

It looks a little odd (but users never need to see it; see Tip #10), and it then requires merging cells as you build out the layout, but it’s worth it. And, this is one of the benefits of using widgets: once you do the requisite cell-merging to build the widget, that entire widget can be copied and pasted for each new metric.

Tip #5: Design for Printability

This goes for more than just dashboards. I’m amazed how often I see spreadsheets that someone might want to print out — to review offline, to take with them to a meeting, or even to mark up — that aren’t readily printable. Go ahead and define the Print Area (this lets you leave a little extra white space at the top and left of the spreadsheet — a blank row and a blank column — without impacting printing; exclude those from the Print Area). Adjust your layout, as well as the orientation, margins, and header/footer, to make sure that someone can easily print the dashboard.

Note: It’s tempting to just use the “Fit to Page” feature. I try to avoid that, because it’s then easy for the dashboard to start scaling drastically — 60% or 50% — to the point of unreadability when printing. Better to just add a little care and testing to the setup of the dashboard itself.

Tip #6: Be Organized

This is a “dashboard architecture” tip. But, over the years, I’ve found myself consistently using different worksheets for different distinct purposes:

• Presentation Layer — this is the dashboard itself; there is no data directly housed in this worksheet. I usually name this tab “Dashboard.” If I have drilldown sheets, then those also are considered Presentation Layer sheets. The data shown on these worksheets come from either the Data sheets or the Transformation sheets (or some combination)
  
• Transformation — this type of worksheet is not always needed. It depends on the structure of the raw data and the complexity of the dashboard. But, this is one or more sheets where I put pivot tables or lookup tables that grab data from the Data sheets and put it into an aggregated (pivot table) form, and/or grabs only a subset of the data (for instance, the data for only the selected report period).
• Data — this is the worksheet(s) where the raw data for the dashboard actually goes. Be it Report Builder queries, Facebook Insights exports, or anything in between, the key is for these sheets to be structured as close to the structure of the raw data as possible. If the data is automated (see Tip #9), then the structure will have to match the raw data exactly!
• Settings — this is always a single worksheet, and it includes the various constants and lookup tables that the dashboard needs. Examples of constants include: the % below target a metric has to fall before I display a red indicator next to it on the dashboard, how many periods I want to include in my sparkline trends, formulas to calculate the start and end dates for different metric displays based on the selected report date (see Tip #8), and so on. I always store these values in named cells, as they get referenced extensively by the Transformation and Presentation Layer sheets. Examples of lookups include mapping a “pretty name” to raw identifiers that are included in raw data exports. Or, the values that need to be shown in the date selection dropdown (see Tip #8).

Most of these worksheets ultimately get hidden, but they make for a clean overall architecture for maintaining and documenting (Tip #7) the ins and outs of the file.

Tip #7: Document, Document, Document!

Just as it’s important to document the specifics of where the data came from and how it was pulled when doing an analysis, I use comments and ancillary cells in the Data and Settings worksheets to provide in-context documentation. The closer this goes to the actual data, and the more complete it is, the better! There will come a time when the dashboard needs to be updated or an underlying data source changes (Facebook Insights, anyone?), and having in-context information of exactly what the data is and where it came from is an enormous time-saver.

There is also some documentation that needs to go on the Presentation Layer (Tip #6): the date / date range for the report, definitions for any included metrics that are not clear to the casual user, and any important caveats/clarifications about the data. Much of this “on the dashboard” documentation can be included in footnotes, but a dashboard is a failure if one of the recipients has questions about what the data actually is.

Random personal background anecdote: I spent several years as a technical writer early in my career, which included writing online help for the software my company developed. That experience has likely contributed to my stickler-ness on the clarity-and-completeness-of-documentation front.

Tip #8: Dropdowns…Even If Only You Use Them

At a minimum, every dashboard I develop that is not fully automated (read: “scheduled and published through Report Builder“) has at least one dropdown on the dashboard itself: a selector to choose the date to display. That single cell (as a named cell!) should be the key by which all of the displayed data gets updated. I rely heavily on dynamic named ranges to make that happen, but it means I never-ever-ever manually update the chart or cells that display the final data.

Depending on the dashboard, I use in-cell dropdowns to enable additional control of what gets displayed on the dashboard. For example:

• Controlling whether the dashboard is a weekly, monthly, or quarterly display of the data
• Controlling how many periods to include in historical trends (sparklines or charts) on the dashboard
• Controlling which metric is used to sort any “Top X” lists of values on the dashboard, as well as whether the metric is sorted ascending or descending

The possibilities are endless. Obviously, the more control you put in a dropdown, the more logic you have to build into the Transformation sheets (Tip #6), and that requires something of a cost/benefit assessment. An Excel dashboard cannot be a standalone in-depth analysis tool, but it can provide a first-level dive into the data as a jumping off point for deeper analysis.

Note: I’ve found the best Excel feature to use for making in-cell dropdowns is the Data Validation feature. I generally wind up using the INDIRECT (see step #3 in this post)  function to reference named ranges for this — often named ranges that reference cells or tables on the Settings tab (Tip #6).

Tip #9: Automate! (Or Quasi-Automate!)

This tip should probably go without saying, but I regularly run into recurring dashboards or reports that are not as automated as reasonably possible. Several of the tips I’ve already listed help with automation or near-automation, but, the better you know Excel, and the better you develop a good overall dashboard structure (Tip #7), the more automated your dashboard can be.

The biggest challenge with automation is usually getting the raw data out of its home system and into Excel. Depending on the data source(s) needed, the platform itself may have an Excel automation tool already (Adobe Analytics has Report Builder, of course, and Google Analytics has a number of third-party tools to do the same thing; my three favorites: Shufflepoint, Analysis Engine, and Supermetrics).

In the absence of an Excel integration, most platforms offer exports in Excel or comma-delimited text (or both). Figure out which export gives you as much of the data as you need in a single flat table, and then use that as one of your Data worksheets (Tip #6). For Facebook data, I always use the .csv export option, but there are so many columns, and the specific order of the columns can change, so I actually have a macro I use to clean up that export. This is an example of quasi-automation: I still have to go to Facebook Insights and export a file, but I then have a macro that takes 5 seconds to turn that file into the raw data that I drop into a dashboard to update the data.

Tip #10: Hide the Unnecessary

I once sat in a meeting with the CMO of a $4 billion company where we reviewed an Excel dashboard I’d built. One of his top VPs asked, “Will I be able to view this dashboard on my phone or my iPad? Does the software support that?” The only real clue she would have had that the dashboard was actually just a spreadsheet was the little green icon in the top left of the window, and that was the subtlest of clues. At the same time, the dashboard had some basic interactivity (using the techniques described in earlier tips) and, while she probably wouldn’t have much luck with it on her phone, her organization’s incremental licensing fee for the dashboard was: $0.

So, what to hide? It doesn’t take much:

• All the worksheets that aren’t the Presentation Layer (I sometimes use the xlVeryHidden property for this; that’s a good way to bury the sheet one level deeper than the basic Hide feature without using a password that you will then have to keep track of; if someone knows enough to change the property back to Visible, they probably know their way around Excel enough that it’s okay for them to poke around in that content)
  
• The column headings — because I make a bunch of narrow columns, the headings can be very distracting; this is a simple checkbox in the View group in Excel:

• The formula bar — this is also a checkbox on the toolbar
• Sometimes…I hide the actual worksheet tabs at the bottom of the screen, too.

If the dashboard is something that I have to manually update, I often record a simple macro that does all of my hiding (as well as one that does the unhiding) for me. I don’t like to distribute workbooks with macros in them — they have a pesky habit of giving the recipients a scary warning when they open them — so I sometimes put these macros in a separate workbook that only I use when working on the dashboard.

<whew!> I Didn’t Promise “Quick Tips”

Hopefully, there’s a nugget or two here that you can apply in your day-to-day work. Some of the tips I considered are more “Excel tips” than “dashboard tips,” but (go figure!) I actually covered a number of those a while back in this post.

What’s missing? What are some of your tips and techniques for getting more power out of Excel as a dashboard delivery platform?

It was a fairly innocuous vent-via-Twitter tweet last week that inspired this post:

I was surprised by the Twitter conversation it started, with several people noting that they had no idea you could simple turn off the gridlines. There was only one vigorous defender of the all-white fill: Michele Kiss pointed out that, for both Windows and Mac, if you launch a New Window for your spreadsheet (so that you have two or more windows to allow viewing multiple worksheets in the same workbook at the same time), that the gridlines return. She’s right. And, no amount of changing of Excel’s settings or saving default book.xlt and sheet.xlt files seems to get around that. It would be pretty easy to put a macro in your personal workbook to toggle gridlines from a hotkey…but I don’t use New Windows all that often, so I didn’t pursue it.

That’s all somewhat beside the point. During the ensuing exchanges from that initial tweet, Alyson Murphy nudged me to see if I could make a quick blog post of other little things like this in Excel. I jotted down a list and had ten things before I knew it, so here goes! They’re numbered…but they’re in no particular order.

No. 1 – Turn Off Gridlines

The tip/opinion that spawned the post. I won’t belabor it. Just know that there’s a checkbox under the View >> Show group in Windows and under Layout >> View on the Mac. Deselect it, and the gridlines go away.

No. 2 – Vertical Align: Top

Definitely on the top 10 list of asinine Excel default settings is the bottom-alignment for cells. It looks unnatural. It’s not how the human brain wants to read a multi-line row. Once text starts wrapping in a cell and rows go to multi-line heights, you’re going to want the single-line contents to be top-aligned. Sometimes, you may want it middle-aligned, but never-never-never bottom-aligned. That’s just silly.

No. 3 – Default Workbooks, Worksheets, and Charts

Excel has so many atrocious defaults that, if you start from the defaults with every new workbook, you could easily spend 2-10 minutes just undoing their ickiness. That should only be necessary when you’re passed some sort of default-heavy abomination from someone else. For your new workbooks, you can save a default workbook and a default worksheet into the xlstart folder. Turn off gridlines, vertical align all the cells, cut the number of worksheets from 3 to 1, even change the font from Calibri to something else. Do what you want and save it as a workbook and worksheet template.

You can also save charts as templates: turn off drop shadow, switch to non-default colors, remove tick marks, lighten up gridlines, adjust font sizes, etc. Then…save it for future use!

I’d love to say I also regularly customize themes so that I have different palettes to choose from that I’ve truly customized, rather than manually setting chart and table colors as I go. I occasionally do that, but I always have to go re-remind myself how. But, if you’re bothered by the default Office theme, and none of the other ones that are pre-created suit your fancy, you can totally make a palette that matches your company’s color scheme. If you do that, take the time to track down the actual RGB colors — don’t just eyeball them. There are a half-dozen different easy ways to do that…but that’s a topic for another post.

No. 4 – Building Error Checking into Your Formulas

There is no need for your workbooks to ever show #DIV/0, #N/A, #REF, #NAME?, #NUM!, or #NULL!. If you’re distributing a workbook to other users, these are jarring values to see, and they can cause a momentary hesitation as to the veracity of the entire workbook.

I have a standard formula structure that I use any time I’m building a workbook that will be distributed for any cell that, over time, may result in one of the above errors. These errors can be totally legitimate…but I want them handled more elegantly. For instance, say I have a workbook that shows revenue by search keyword and also compares that revenue to the revenue for the same keyword the previous month. Each month, I’ll import new data, and that list of keywords will be updated. So, what happens when I have a keyword that generates NO revenue one month? The next month, when I try to calculate the % change in revenue, I’m going to get a #DIV/0 error:

With a slightly more complicated formula (but, trust me, you get used to this structure really quickly and learn how to copy and paste even more elaborate base formulas into the structure), I can permanently eliminate that error display.

Essentially, here’s the logic I build into the formulas:

1. Evaluate the formula
2. If it returns an error, then put something clean in the cell rather than an Excel error
3. If it does not return an error, then put the result of the formula in the cell

[The next paragraph and image were updated based on Alyson Murphy’s note in the comments. I was aware of IFERROR, but I’d always misinterpreted how it worked. I’ve now replaced what was originally in the following paragraphy — IF(ISERROR(<formula>,”-“,<formula>) — with the simplified IFERROR() function shown below.]

I (now) use the IFERROR() function for this, which is the broadest error-checking function in Excel (if you’re curious, you can read up on ISERR() and ISNA()). So, rather than the formula shown above, I put the formula inside an IFERROR() function:

The error result doesn’t have to be “-“. It can be null (“”) or 0 or even a cleaner error message (“N/A”, “Unknown”).

This isn’t just for aesthetics. It can also be used when you’re running formulas on a column of evaluated values. Depending on the formula, a single error value may cause the aggregating formula to error out as well!

I use this structure all the time!

No. 5 – Print Preview Is Your Friend

95% of the time, your workbooks are viewed online. And, sometimes, there are so many columns that printing isn’t even feasible. But, it drives me nuts when I get a workbook that should be printable…but the analyst clearly hasn’t spent two minutes making that easy to do cleanly.

Use print preview to check printability. I almost always reduce the page margins to 0.5″ to help out. But, I also, rather than simply clicking the “fit to page” checkbox, do a little massaging of column widths in the base document so that the aspect ratio of the viewable content is printer-friendly.

And, of course, don’t forget to:

1. Select rows to repeat on every page if the document has column headings that should appear on every page
2. Add a footer with a page number and other useful information

There’s something of a minor art to support printability, but, if someone is trying to print out the workbook, it’s worth giving them a file that supports that!

No. 6 – Absolute and Relative Cell References

This may be a no-brainer, but I’m regularly surprised when I see formulas that don’t appropriately use “$” in the cell references to support dragging formulas down rows and over columns.

No. 7 – Named Cells and Named Ranges

I might have an unhealthy adoration of named cells and ranges. But, related to the previous tip, if I’ve got a cell or a range of cells that I know I’m going to be keying off throughout the workbook (i.e., the report date), I make it a named cell. All it takes is selecting the cell and then clicking in the box at the top left of the workbook and giving it an intuitive name. Then, I can use that name rather than a cell reference anywhere in the workbook.

Named ranges can be a huge timesaver when it comes to charting — let them do the heavy lifting of adjusting the timeframe to display. Hands-down my most popular blog post ever was this one on using dynamic named ranges for charting.

No. 8 – Excel Tables — A Special Kind of Named Range

I also have something of an obsession with Excel tables. But, that’s only because they’re so freakin’ awesome! I’ve written an entire blog post on that front.

No. 9 – Dropdown Selectors…Using Tables

I always use tables in conjunction with data validation to make in-cell dropdown selectors. It requires using the INDIRECT() function, which is an inexplicable, but minor, Excel quirk. Details are in the same blog post I referenced in the prior tip. Just scroll down to the “Referencing Tables and Parts of Tables” section.

No. 10 – Tricks within a Cell

I’m doubling up on this one, because they’re both related to entering stuff inside of cells:

• To force Excel to display the contents of a cell exactly as you entered it — not converting something that looks like a date to a date, not removing leading zeros (although this can be done with a custom text format as well), or something else — precede the contents of the cell with an apostrophe. Annie Cushing wrote a recent post where she lays out how to use the apostrophe to “save” a complex formula mid-stream (since Excel won’t let you enter a “broken” formula).
• Line breaks are doable within a cell. Sometimes, you want to make a mini-list inside a single cell, for instance. Other times, you want to put two paragraphs. In Windows, simply press <Alt>-<Enter> for a line break. On the Mac, press <Alt>-<Cmd>-<Enter>

And, of course, custom cell formats are super-super handy (Do you want a “+” displayed in front of positive numbers AND a “-” displayed in front of negative numbers? There are cases where you do, and custom formats are your friend!). Jon Peltier wrote a great post explaining the ins and outs of custom formats, and I regularly find myself returning to that post for a refresher.

And That’s It! Except…it’s not…

This post hasn’t included, I realized as I wrote it, some of my other favorites:  pivot tables and the GETPIVOTDATA() function, the TEXT() function (when concatenating strings to have a cell say something like, “Visits increased by 20,135 (8% growth) over the prior week” or “Report Dates: January 1, 2014 to January 8, 2014”); conditional formatting for in-cell bar charts for quick and condensed visualization of a list of numbers; the xlVeryHidden worksheet property; worksheet and cell protection; the triple thread of INDEX(), MATCH(), and OFFSET(); and on and on…

But, I have to stop somewhere!

What are your favorite tips / underused Excel capabilities?

 

The single post on this blog that has, for several years now, consistently driven the most traffic to this site, is this one that I wrote almost three years ago. Apparently, through sheer volume of content on the page and some dumb luck with the post title, I consistently do well for searches for “Excel dynamic named ranges” (long live the long tail of SEO!).

The kicker is that I wrote that post before I’d discovered the awesomeness of Excel tables, and before Excel 2010 had really gone mainstream. I’ve been meaning to redo the original post with an example that uses tables, because it simplifies things a bit.

This is that post — 100% plagiarized from the original when it makes sense to do so. The content was created in Excel 2010 for Windows. However, it should work fine on Excel 2007 for Windows, too. Macs are a bit of a crap shoot, unfortunately (but you can always run Parallels, so I hear, and use Excel for Windows!).

---

This post describes (and includes a downloadable file of the example) a technique that I’ve used extensively to make short work of updating recurring reports. Here are the criteria I was working against when I initially implemented this approach:

• User-selectable report date
• User-selectable range of data to include in the chart
• Single date/range selection to update multiple charts at once
• No need to touch the chart itself
• Reporting of the most recent value (think sparklines, where you want to show the last x data values in a small chart, and then report the last value explicitly as a number)
• No use of third-party plug-ins
• No macros — I don’t have anything against macros, but they introduce privacy concerns, version compatibility, odd little warnings, and, in this case, aren’t needed

The example shown here is pretty basic, but the approach scales really well.

Sound like fun?

Setting Up the Basics

One key here is to separate the presentation layer from the data layer. I like to just have the first worksheet as the presentation layer — let’s name it Dashboard — and the second worksheets as the data layer — let’s call that Data. (Note: I abhor many, many things about Excel’s default settings, but, to keep the example as familiar as possible, I’m going to leave those alone. This basic approach is one of the core components in the dashboards I work on every day, and it can be applied to a much more robust visualization of data than is represented here.

Data Tab Setup — Part 1

This is a slightly iterative process that starts with the setup of the Data tab. On that worksheet, we’ll use the first column to list our dates — these could be days, weeks, months, whatever (they can be changed at any time and the whole approach still works). For the purposes of this example, we’ll go with months. Let’s leave the first row alone — this is where we will populate the “current value,” which we’ll get to later. I like to use a simple shading schema to clearly denote which cells will get updated with data and which ones never really need to be touched. And, in this example, let’s say we’ve got three different metrics that we’re updating: Revenue, Orders, and Web Visits. This approach can be scaled to include dozens of metrics, but three should illustrate the point. That leaves us with a Data tab that looks like this:

Now, turn that range of data into a table by selecting the area from A2 to D19 and choosing Insert » Table. Then, click over to the Table Tools / Design group and change the table name from “Table1” to “Main_Data” (this isn’t required, but I always like to give my tables somewhat descriptive names). The sheet should now look like this:

Because this is now a table, as you add data in additional rows, as long as they are on the rows immediately below the table, the table will automatically expand (and that new data will be included in references to Main_Data, which is critical to this whole exercise).

While we’re on this tab, we should go ahead and defined some named cells and some named ranges. We’ll name the cells in the first row of each metric column (the row labeled “Current–>” as the “current” value for that metric (the cells don’t have to be named cells, but it makes for easier, safer updating of the dashboard as the complexity grows). Name each cell by clicking on the cell, then clicking in the cell address at the top left and typing in the cell name. It’s important to have consistent naming conventions, so we’ll go with <metric>_Current for this (it works out to have the metric identified first, with the qualifier/type after — just trust me!). The screen capture below shows this being done for the cell where the current value for Orders will go, but this needs to be done for Revenue and Web Traffic as well (I just remove the space for Web Traffic — WebTraffic_Current).

And, of course, we’ll actually need data — this would come later, but I’ve gone ahead and dropped some fictitious stuff in there:

That’s it for the Data tab for now…but we’ll be back!

Dashboard Tab Setup — Part 1

Now we jump over to the Dashboard worksheet and set up a couple of dropdowns — one is the report period selector, and the other is the report range (how many months to include in the chart) selector. Start by setting up some labels with dropdowns (I normally put these off to the side and outside the print range…but that doesn’t sit nice with the screen resolution I like to work with on this blog):

Then, set up the dropdowns using Excel data validation:

First, the report period. Click in cell C1, select Data » Data Validation, choose List, and then reference the first column in the Main_Data table (see the “Referencing Tables and Parts of Tables” section in this post for an explanation of the specific syntax used here, including the use of the  INDIRECT function):

When you click OK, you will have a dropdown in cell C1 that contains all of the available months. This is a critical cell — it’s what we’ll use to select the date we want to key off of for reporting, and it’s what we’ll use to look up the data. So, we need to make it a named cell — ReportPeriod:

Now, let’s do a similar operation for the report range — this tells the spreadsheet how many months to include in each chart. Click in cell C3, select Data » Data Validation, choose List, and then enter the different values you want as options (I’ve used 3, 6, 9, and 12 here, but any list of integers will work):

And, let’s name that cell ReportRange:

Does this seem like a lot of work? It can be a bit of a hassle on the initial setup, but it will pay huge dividends as the report gets updated each day, week, or month. Trust me!

Before we leave this tab, go ahead and select a value in each dropdown — this will make it easier to check the formulas in the next step.

Data Tab Setup — Part 2

Now is where the fun begins. We’re going to go back over to the Data worksheet and start setting up some additional named ranges. We’ve got Main_Data, which is the table that includes the full range of data. We want to look at the currently selected Report Period (a named range called ReportPeriod) and find the value for each metric that is in the same row as that report period. That will give us the “Current” value for each metric. All you need to do is put the exact same formula in each of the three “Current” cells:

=VLOOKUP(ReportPeriod,Main_Data,COLUMN())

In this example, these are the values for each of the three arguments:

• ReportPeriod — Jul-12, the value we selected on the Dashboard tab
• Main_Data — this is the full table of data
• COLUMN() — this is 2, the column that the current metric is listed in (this function resolves to “3” for Orders and to “4” for Web Traffic (Note: If you have additional columns in your data sheet, you may have to make this “COLUMN()-<some fixed value>.” If, for instance, you have a blank column A before the table starts to provide some space, you would use “COLUMN()-1.” This applies to other uses of COLUMN() throughout this post.)

So, the formula simply takes the currently selected month, finds the row with that value in the data array, and then moves over to the column that matches the current column of the formula:

Slick, huh? And, because the ReportPeriod data validation dropdown on the Dashboard worksheet is referencing the first column of the data table on the Data tab, the VLOOKUP will always be able to find a matching value. (Read that last sentence again if it didn’t sink in — it’s a nifty little way of ensuring the robustness of the report)

This little bit of cleverness is really just a setup for the next step, which is setting up the data ranges that we’re going to chart. Conceptually, it’s very similar to what we did to find the current metric value, but we want to select the range of data that ends with that value and goes backwards by the number of months specified by ReportRange. So, in the values we selected above, Jul-09 and “6,” we basically want to be able to chart the following range of data:

We’ll do this by defining a named range called Revenue_Range (note how this has a similar naming convention to Revenue_Current, the name we gave the cell with the single value — this comes in handy for keeping track of things when setting up the dashboard). We can’t use VLOOKUP, because that function doesn’t really work with arrays and ranges of data. Instead, we’ll use a combination of the MATCH function (which is sort of like VLOOKUP on steroids) and the INDEX function (which is a handy way to grab a range of cells). Pull your hat down and fasten your seatbelt, as this one gets a little scary. Ultimately, the formula looks like this:

=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(Revenue_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(Revenue_Current))

It’s really not that bad when you break it down. I promise!

Working from the outside in, you’ve got a couple of INDEX() functions. Think of those as being INDEX(First Cell) and INDEX(Last Cell).

The range is defined, in pseudocode, as simply:

=INDEX(First Cell):INDEX(Last Cell)

The Last Cell calculation is slightly simpler to understand. As a matter of fact, this is really just trying to identify the cell location (not the value in the cell) of the current value for revenue — very similar to what we did with the VLOOKUP function earlier. The INDEX function has three arguments: INDEX(array,row_num,column_num). Here’s how those are getting populated:

• array — this is simply set to Main_Data, the full data table
• row_num — this is the row number within the array that we want to use; we’ll come back to that in just a minute
• column_num — we use a similar trick that we used on the Revenue_Current function, in that we use the COLUMN() formula; but, since we set up this range simply as a named range (as opposed to being a value in a cell), we can’t leave the value of the function blank; so, we populate the function with the argument of Revenue_Current — we want to grab the column that is the same column as where the current revenue value is populated in the top row.

Now, back to how we determine the row_num value. We do this using the MATCH function, which we need to use on a 1-dimensional array rather than a 2-dimensional array (Main_Data is a multi-column table, which makes it a  2-dimensional array). All we want this function to return is the number of the row in the Main_Data table for the currently selected report period, which, as it turns out, is the same row as the currently selected report period in the first column (“Report Period”). The formula is pretty simple:

MATCH(ReportPeriod,Main_Data[Report Period])

The formula looks in the first column of the Main_Data table for the ReportPeriod value and finds it…in the seventh row of the table. So, row_num is set to 7.

INDEX(First Cell) is almost identical to INDEX(Last Cell), except the row_num value needs to be set to 2 instead of 7 — that will make the full range match the ReportRange value of 6. So, row_num is calculated as:

MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1

(The “+1” is needed because we want the total number of cells included in the range to be ReportRange inclusive.)

Now, that’s not all that scary, is it? We just need to drop the full formula into a named range called Revenue_Range by selecting Formulas » Name Manager » New, naming the range Revenue_Range, and inserting the formula:

=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(Revenue_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(Revenue_Current))

The whole formula is there, even if you can’t see it!

Repeat this last step to create two more named ranges with slightly different formulas (the differences are in bold):

• Orders_Range: =INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(Orders_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(Orders_Current))
• WebTraffic_Range: =INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1, COLUMN(WebTraffic_Current)):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]), COLUMN(WebTraffic_Current))

Tip: After creating one of these named ranges, while still in the Name Manager, you can select the range and click into the formula box, and the current range of cells defined by the formula will show up with a blinking dotted line around them.

You’re getting sooooooo close, so hang in there! In order for the chart labels to show up correctly, we need to make one more named range. We’ll call it Date_Range and define it with the following formula (this is just like the earlier _Range formulas, but we know we want to pull the dates from the first column, so, rather than using the COLUMN() formula, we simply use a constant, “1”:

=INDEX(Main_Data,MATCH(ReportPeriod,Main_Data[Report Period])-ReportRange+1,1):INDEX(Main_Data, MATCH(ReportPeriod,Main_Data[Report Period]),1)

If you want, you can fiddle around with the different settings on the Dashboard tab and watch how both the “Current” values and (if you get into Name Manager) the _Range areas change.

OR…you can move on to the final step, where it all comes together!

Dashboard Tab Setup — Part 2 (the final step)

It’s back over to the Dashboard worksheet to wrap things up.

Insert a 2-D Line chart and resize it to be less than totally obnoxious. It will just be a blank box initially:

Right-click on the chart and select Select Data. Click to Add a new series and enter “Revenue” (without the quotes — Excel will add those for you) as the series name and the following formula for the series values:

=DynamicChartsWithTables_Example.xlsx!Revenue_Range

(Change the name of the workbook if that’s not what your workbook is named)

Click to edit the axis labels and enter a similar formula:

=DynamicChartsWithTables_Example.xlsx!Date_Range

You will now have an absolutely horrid looking chart (thank you, Excel!):

Tighten it up with some level of formatting (if you just can’t stand to wait, you can go ahead and start flipping the dropdowns to different settings), drop “=ReportPeriod” into cell E6 and “=Revenue_Current” into cell E7, and you will wind up with something that looks like this:

Okay, so that still looks pretty horrid…but this isn’t a post about data visualization, and I’m trying to make the example as illustrative as possible. In practice, we use this technique to populate a slew of sparklines (no x-axis labels) and a couple of bar charts, as well as some additional calculated values for each metric.

To add charts for orders and web traffic is a little easier than creating the initial chart. Just copy the Revenue chart a couple of times (if you hold down <Ctrl>-<Shift> and then click and drag the chart it will make a copy and keep that copy aligned with the original chart).

Then, simply click on the data line in the chart and look up at the formula box. You will see a formula that looks something like this:

=SERIES(“Revenue“,DynamicChartsWithTables_Example.xlsx!Date_Range, DynamicCharts_ExampleWithTables.xlsx!Revenue_Range,1)

Change the bolded text, “Revenue,” to be “Orders” and the chart will update.

Repeat for a Web Traffic chart, and you’ll wind up with something like this:

And…for the magic…

<drum rollllllllllll>

Change the dropdowns and watch the charts update!

So, is it worth it? Not if you’re going to produce one report a couple of times and move on. But, if you’re in a situation where you have a lot of recurring, standardized reports (not as mindless report monkeys — these should be well-structured, well-validated, actionable performance measurement tools), then the payoff will hit pretty quickly. Updating the report is simply a matter of updating the data on the Data tab (some of which can even be done automatically, depending on the data source and the API availability), then the Report Period dropdown on the Dashboard tab can be changed to the new report period, and the charts get automatically updated! You can then spend your time analyzing and interpreting the results. Often, this means going back and digging for more data to supplement the report…but I’m teetering on the verge of much larger topic, so I’ll stop…

As an added bonus, you can hide the Data tab and distribute the spreadsheet itself, enabling your end users to flip back and forth between different date ranges — a poor man’s BI tool, if ever there was one (in practice, there will seldom be any real insight gleaned from this limited number of adjustable dropdowns, and that’s not the reason to set them up in the first place).

I was curious as to what it would take to create this example from scratch and document it as I went. As it’s turned out, this is a lonnnnnnngggg post. But, if you’ve skimmed it, get the gist, and want to start fiddling around with the example used here, feel free to download it!

Happy dynamic charting!

[Update] Troubleshooting Tip

A few people who have left comments have run into a snag where, for one reason or other, one of the named ranges has not been properly created. When that named range gets used in a chart, it either throws an error or doesn’t work. One way to check the named ranges is to open the named range manager, highlight the named range used in the chart, and then click in the formula box at the bottom of the window. A flickering/moving dashed line should then appear around the cells that the named range refers to:

If this highlighting doesn’t occur, then there is something not right with the formula.

I was on-site at a client for a couple of days last week and, during a break, got into a discussion with their resident CRM system expert. This fellow had managed some crazy stuff in Siebel for a major credit card company and was now working with Salesforce.com and a mishmash of other systems for a nonprofit. We got to talking about data, reporting, and analysis. Inevitably, Microsoft Excel came up — a tool of which both of us are huge fans.

This led to my “Two Ways to Gauge if Someone Really Uses Excel” theory (this fellow clearly did!):

1. Do they use pivot tables? It’s not just if they’ve ever seen a pivot table or struggled to create one in a pinch. It’s, when asked the question, “Do you use pivot tables?” their reaction involves some sort of significant change in facial expression along with a forceful response. Something along the lines of, “Oh my god, YES!!! I don’t know what I would do without them!”
2. Do they use VLOOKUP? Again, the key here is not whether they have heard of VLOOKUP. It’s whether they see it as a function that they simply could. Not. Live. Without!

What is interesting is that both of these features of Excel are database-like functionality. Pivot tables are, basically, a way to do semi-dynamic SQL GROUP BYs (or basic aggregation in MS Access). VLOOKUP is a poor man’s SQL join. But, both are fast (for pivot tables, I’ve learned that I can start the pivot table wizard and then, 99 times out of 100, simply click Finish without going through the intermediate wizard steps to get what I want) and, if data arrives for analysis in Excel, then both are native to the environment the data is already in.

One other note on these features is that, if someone is to the point where they automatically and naturally use pivot tables and VLOOKUP, then it is practically guaranteed that they use string manipulation functions (&, LEFT, RIGHT, MID, LEN, FIND) and conditional and comparison functions (IF, AND, OR, SUMIF). These are every bit as important as VLOOKUP, but, in my experience, they come before or with a VLOOKUP addiction; seldom do they come after.

There is a separate dimension of Excel usage that does not get covered by this test, and that is Excel’s data visualization capabilities. As a matter of fact, up through Excel 2003, pivot tables were particularly difficult to use for charting — charting pivoted data creates a pivot chart, which, while dynamic and manipulable, is pretty ugly. Excel can be used to generate very professional, impactful, clean visual representations of the data. Unfortunately, the tool’s defaults do not do this! But, that is a topic for a whole separate series of posts!