Have you recently read about a product and thought: “it does too much?” Yet it’s also easy to disqualify a product or service as too narrow and “not for me”. How do you split the gap and build for the people who really want (and need) what you are selling without simply disappearing into the noise?
Taking a look at an everyday need (daily frequency, high recall, habit-forming) is a good frame for thinking about product decisions. Take the mundane experience of drinking coffee (sorry tea drinkers, this applies to you as well but the parameters might be different.)
As a coffee drinker, I want to have a hot beverage that stays warm, is true to taste, and (ideally) doesn’t spill. It would be great if that was a portable container, and was reusable. Bonus points if the container was nicely designed, had a good feel when held as a cup, and was durable.
I can imagine what you might be thinking at this point: why bother having a design discussion about a coffee cup? It’s exactly because the discussion is mundane that it’s important. Design decisions about materials might be optimized for heat retention (stainless steel liner) or grip (handle, or grippy finish) or the top (does it have one, and how easy is it to remove or drink out of) or price (is the target market $5, $10, or $50).
This coffee cup wins for me because it does a few things really well:
Keeps the coffee warm for a long time
Doesn’t spill easily
Easy to hold
Note that it doesn’t have a handle. The traditional mug design wasn’t followed here. It also isn’t the cheapest coffee mug I have. Yet it’s the one I use every day.
What are the lessons you can take to your next product or design decision? Do fewer things well, and buyers will find you. Be open to changing long-held beliefs, especially around habit-driven items. Measure the results based on input goals and output results. And enjoy your coffee!
When you need to analyze a large list of items, you need to create areas of commonality to enable grouping and sorting that list. A simple example of grouping might be to think about a grocery list, where certain items (bananas, oranges) are fruits and other items (paper plates, napkins) are non-perishable items that need to be managed differently. Short lists of a few items are easy to categorize; longer items take an effort to sort and manage the data; and large lists (>200 items) might require a programmatic approach.
Fortunately, spreadsheets are excellent for a large, boring sort that might drive you a bit nuts if you tried to do it by hand. Spreadsheets require a logical framework to decide exactly how to organize your items into categories, especially if you have multiple criteria. To organize your items, you might look for an obvious sort (alphabetical) or count individual things, but you are more likely trying to create a grouping that’s not obvious.
RegEx can help (really)
Regular expressions are a computer-geeky way of writing a pattern for text that you want to match, and provide a TRUE or FALSE result to your expression. This code is amazing and flexible, and also a bit hard to master (check out https://regexr.com/ if you want a handy playground to try them out). You use Regular Expressions when you want to apply the same test to multiple items, e.g. finding the domain name in an email, or retrieving a portion of text from a URL.
Using a logical test and validating it in Regular Expressions allows you to take an abstract problem (find the senior leaders in a list of titles) that’s easily understood by humans but needs instructions for computers to execute. This all sounds logical, but searching for Google Sheets RegEx formulas might leave you feeling like adding more than one condition at a time is a bit … challenging. So I decided to simplify that process.
Here’s a concrete example of applying multiple true/false conditions to a single problem. If you’ve built prospect lists for outbound campaigns, there’s one thing that almost always happens: you have to “bucket” the titles by groups to decide what to do next. You might manually decide that titles starting with CEO or “Chief” are C-level, and that ones starting with VP are VP-level, but it’s kind of a drag to do this manually. Wouldn’t it be easier to define the filters, then apply the true/false results of each filter to all of your titles at once?
A quick tool to help sort lists
I decided to build a small tool in Google Sheets to model using multiple filters to test the ability to arrange job titles into families. To build this utility, I used Google Sheets to list the testName I wanted to establish in each row, followed by an example title, a Regular Expression condition I wanted to match, and the result of TRUE or FALSE for that test. I also created a column to help you understand which part of the string/title matched your condition.
Several rows of tests, displaying the regular expression used to evaluate the test
The initial list has four tests, where the simplest is:
(?i)(mgr|manager)
Or – in English – “match everything that has either the string “mgr” or the string “manager”, in the source string, and don’t worry about whether it’s upper or lower case.”
These tests each evaluate a job title, determining what seniority it lives in based on a keyword or words in the title. I intended this rule stack evaluate from the top to the bottom, so you would evaluate the first rule, then stop if you get TRUE, then move on to the next rule. If you prefer, you can keep going even when you have multiple TRUE conditions as it will help you understand the distribution of your list between buckets.
A few titles, displaying the results of the testing run and the item that matched
Here’s how this works:
Taking the title text from the “Example Title” column,
We use the header column to look up the row of the test we want to execute. For isManager level, we would use a formula to look at the column labeled Test Name in the other tab, do a vLookup to find the row matching isManager, then read the expression in the Condition/Regex column, and return a TRUE/FALSE result using the REGEXMATCH formula, e.g. =REGEXMATCH(“Sr Manager of Spreadsheets”,”(?i)(mgr|manager)”)
use conditional formatting to alert a TRUE result;
and reverse the formula using a REGEXEXTRACT to identify the portion of the string that matched first.
Ok, so what?
Yes, you could apply these formulas one at a time or use complicated sorting methods to find boolean conditions that are true or false in your data set. This method is helpful because it allows you to flexibly add conditions (add a row in the RegEx Tester sheet; and a column in the ProspectTitles sheet labeled with that row’s test name). It also allows you to change your RegEx conditions to match your data set by updating a single field for each change you want to make in your expression across as many items as you want.
This method works well and quickly for evaluating several fields against a set of 10k records or fewer. As you think about longer lists that involve updating more than 50k cells at a time, you might combine your tests or use small portions of lists at a time to increase the speed (that’s a lot of VLOOKUPs 😉
What was the last document you created? I’m guessing it was either an email, a Word document, or a Google Doc. Perhaps it was a Slack message but since when you use Slack you create several hundred messages a month (or day), so let’s not count that. Why wasn’t it a spreadsheet (Excel or Google Sheets)?
The most likely reason you didn’t create a spreadsheet is that you wanted the freedom of an open page (hint: View > gridlines turns them off in Google Sheets). Maybe you wanted to insert pictures (hint: use the Image function or insert > image). Or maybe you just like the formatting that seems to happen almost automatically in Cloud-based apps these days. But really, why didn’t you start with a spreadsheet?
Spreadsheets are structured. True, they end up looking that way when they are done. Why not use a tab in your spreadsheet to test an idea (growth in a metric over time, adjusted by factors that change) and produce a sensitivity table to help illustrate a business challenge you are facing. When you are thinking about taking a metric from x to y by when (improve yield by 40% in 30 days) it’s important to show the impact of meeting or missing your expectations.
A suggestion: start with a written statement of what you are trying to solve, e.g. “produce a month over month forecast for the sales team based on a certain number of sales per month and an average sale of $x.” Using the problem statement will frame how you set up your data – you need to have available data series for the problem that match your problem – and start you on the way to presenting the “dashboard” version of your solution. Keep it simple to start by labeling cells that affect others as assumptions, like “sales increase per month.”
Spreadsheets are structured like modular pieces to assemble into a larger whole. Start with the smallest item in your model or argument and test it, then use either a Named Range or a consistently placed cell (e.g. all of your calculations are in a single tab in the same column) to organize where you will find the results. Spreadsheets are more flexible than you think.
Those formulas are hard to copy from one tab to another. Anyone who has ever worked on a spreadsheet has had the experience of copying or changing the data and seeing the dreaded #N/A or #VALUE or #REF show up in place of the data or calculation that they wanted. (And if you have no idea what I am talking about, open a spreadsheet tab in sheets.google.com, enter a formula e.g. =A1+A2 and then break the formula by putting in nonsense characters).
To test your ability to copy data from one tab to another, create a new tab, remove the gridlines for that tab, and title it “Dashboard”. If you can pull the data from the other tab (hint: just use a simple reference, e.g. “=YourOtherSheet!A2” to pull data from a tab called “YourOtherSheet” in cell A2) you can apply things like formatting and font choice to make your dashboard look like a document, not just a spreadsheet.
This method of data construction and display just plain feels weird. Building data-driven dashboards is a method to insert data conversations in your documents. The data needs to first exist in the format (or in a format that’s convertible to the format you need) and then you need to think about how to combine it. And you need to think of the visual display at the same time to make this a useful technique.
Spreadsheets aren’t scary if you think of the presentation layer as a sketch. You might even draw what you want to see on a Post-It note or find a dashboard that you like on the web and take a screen shot as a starting point. Then, consider what kind of data goes into the boxes and graphs to understand what you need to store in the data layer. Still confused, or want to learn more? Ben Collins is my go-to resource to explain Google Sheets to anyone. The best way to learn spreadsheets, however, is to start one.
I wrote and deleted this post several times before continuing to write. It’s not easy to think of yourself as a writer and not have the words to say what’s in your head. It is easy to delete the draft, go back to what you were doing, and go another day without writing.
The empty page is a metaphor for a lot of things. When the page is empty, you have a hard time beating yourself up for what you wrote. When the page is empty, there are no commitments for what you will do next. And when the page is empty, you haven’t taken a stand or offended anyone.
That’s exactly the right time to start writing. I guarantee it won’t be exactly the thing that’s in your head. But once you get going the writing will start to take shape and you’ll realize what was rattling around in your head in the first place.
For me, writing blocks almost always indicate that I am not facing something in my life or that I haven’t yet worked through a challenge I am thinking about. And writing about it almost always helps me face the challenge. Maybe not always head-on, but at least better than ignoring it.
Today I am thinking of the challenge of being a parent of a teenager. Yup, it’s not always easy. Or maybe it’s almost never easy. Maybe being a teenager is a bit like looking at the blank page. When you’ve tried few things in your life you don’t know which ones are going to be the ones that make people happy and which ones you might regret.
Either way you have to start by starting. In this case, it’s probably not the best writing I have ever done. Or will do. However, it’s now out of my head and forces me to commit to doing it again the next time I feel blocked.
We all use search forms every day. There’s usually an advanced search form of some sort that allows you to enter a boolean expression. You know Boolean expressions as “This OR That” – an inclusive search that increases results – or “This AND That” – an exclusive search that limits results.
When you need to filter your results, a powerful method is to make a list of companies, individuals, skills – whatever you are using to get more precise answers – and create a Boolean expression to get a better initial set of results from a search engine. Yet when someone gives you a list of 10, 20, 50, or 100 items the idea of making a delimited list of “Item1” OR “Item2” OR “Item3” is more difficult than you’d like to be.
Never fear: Google Sheets to the rescue! This is a perfect example of a task for a Lazy Programmer™ to solve, as it’s much easier to have a spreadsheet count values and rearrange text for you than it is for you to do the work by hand. Let’s take a look.
As an example, I created a utility spreadsheet that takes a range of values, counts those values, and outputs a result string that delivers a boolean expression to paste into your favorite advanced search page. Whether you’d like to create a list of criteria to find your next job, search Twitter more effectively, or simply create a customized search for Google, this expression builder can help.
This spreadsheet has two formulas that do the work for you. The first one joins a range of values into a concatenated string (turns list of Orange and Apple into ‘Orange’ OR ‘Apple’ OR ”):
There are five functions in this expression:
JOIN combines an expression of values into a string connecting each item with the string ‘ OR ‘;
INDIRECT tells the spreadsheet to wait until the range expression in the formula from A2 to the last unblank value in column A is completed and we know the number of the last row to select;
TEXT converts a complex expression into a value – in this case a calculation of the number of rows in the column of this spreadsheet minus the number of blank rows in the column of this spreadsheet – so that INDIRECT will take result of this expression and substitute “5”, the last row above that has a value. This results in a range from A2:A5 to feed back to JOIN;
ROWS counts the number of rows in a range – the standard number in a Google Spreadsheet is 1000 – and returns an integer to feed back to the expression inside of TEXT;
COUNTBLANK returns the number of blank rows in a range, so that our calculation of rows minus the number of blank rows leaves us with the number of rows containing values (4) or the total number of rows including the header (5).
Now that we have our formula, it creates the string:
'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit' OR ''
From the rows of Orange, Apple, Banana, Grapefruit above. And there is one problem here: that we need to remove the trailing “OR ”” that resulted from the expression.
Our second formula, REGEXREPLACE, removes the last “OR ”” from the string. RegEx is sort of like Black Magic, so you will probably need to use a helper site like Rubular. This string looks for a specific string right before the end of the line (represented by $)
OR ''
and replaces it with nothing.
This leaves you with the strong you want to paste into a search form:
'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit'
You’re all set! Now, use this utility with any number of rows in your list of items to create a Boolean expression made for you automagically.
Information Maven 