Helpful tip for data series labels in Excel

I've never received as many emails from readers as I did for my most recent post on line graphs, analytics, Amazon, Excel, and Tufte, among other things. It turns out that countless consultants, bankers, and analysts still wake up in a cold sweat at the recollection of spending hours formatting graphs in Excel. I opened every last Excel spreadsheet you sent me, apparently we all have one or another lying around as a souvenir of our shared trauma.

[It's fun to hear directly from my readers, I encourage more of it. When so much of online discourse is random strangers performing drive-by violence, or chucking Molotov cocktails at you, it's somewhat old-fashioned and comforting to receive a friendly email. It reminds me a lot of the early days of the Internet, a more idyllic time, when Utopian dreams of the power of this technology hadn't been crushed by the darkness in the souls of mankind.]

Many readers just shared their stories of Excel frustration, but a few offered helpful tips. One class of these was just a recommendation to try alternative programs for charting, like Tableau, ggplot, or D3. I have not had time to play with any of these, though I remember working with Tableau briefly at a company that had purchased a license. I'm not familiar enough with any of these to offer any meaningful comparison to Excel. 

I still hope that someone who works on Excel will just upgrade their default charting options because of the sheer number of Microsoft Office users. Most companies don't license Tableau, and many may not have the inclination or time to learn to use ggplot and D3, though the latter, in particular, seems capable of generating some beautiful visualizations. In contrast, I can't recall any company I worked at that didn't offer me a Microsoft Office license if I wanted one.

A few readers mentioned they wrote macros to automate some or most of the formatting tricks I set out in my post. Maybe at some point this will become an open source tool, which I'd love. If I spot one I'll share it here. Excel users are a tight knit community.

A couple readers offered a similar solution to my problem with generating dynamic data series labels, but my favorite, the most comprehensive option, came from a reader named Jeffrey, a finance associate.

It's a clever little hack. Here's how it works:

1. Fill in your data table in Excel as normal, with data series in rows, time periods (or whatever the x-axis will be) in columns.
2. Add one duplicate row for each data series below those data series in your table, and name each of those the same as the data series above, in order. For example, if your data series are U.S., Japan, and Australia, you now add three more rows, named U.S., Japan, and Australia.
3. For these dummy data series, leave all the data cells blank except the last one, which in my example was the year 2014. For those three cells, just use a formula so that that cell equals the corresponding last data point from the corresponding data series above. So, for the dummy data series for Australia, my formula for 2014 just points to the value in the cell for 2014 for the actual Australia data series.
4. Now create the chart for your data table, including both the actual and dummy data series. In the resulting line graph, you now have six data series instead of three, but the three dummy data series just have a single data point, the last one, which overlaps the corresponding last point in the actual data series.
5. Do all the visual hacks I mentioned in my previous post. When it comes to your three dummy data series, instead of displaying the actual data label, select "Series Name" in the Format Data Labels menu.

That's it! Now you have data labels that will move with any change in the last data point of your three data series.

It's a hack, so it's not perfect. You still have to update your table each time you add to it, moving the dummy pointers one cell to the right. And selecting the data series when it exactly overlaps the actual data series can be tricky. But the thing is, all my old ways of setting up the charts were hacks, too, and this one saves the work of moving data series labels whenever the scale of the graph is changed.

It would be easier if Excel just offered a way to display the data labels and the series name for every data series. Maybe someday? The communication arc of the universe is long, and I like to think it bends towards greater efficiency.

Thanks Jeffrey! Also, thanks to my old coworker Dave who, more than any of my other readers, sends me precise copy-edits to my posts. I wish I were a better editor of my own work, but familiarity breeds myopia.

A mind trick to think more rationally

But one interesting way to try and inject some rationality is to think from an outsider’s perspective. So here’s what happens. When you think about your own life, you’re trapped within your own perspective. You’re trapped within your own emotions and feelings and so on. But if you give advice to somebody else, all of a sudden you’re not trapped within that emotional combination, mish-mash, complexity and you can give advice that is more forward-looking and not so specific to the emotions.
 

...
 

So for example, in one experiment, we asked people, we said, "Look, you went to your doctor. They gave you this diagnosis. You know that the thing that the doctor recommended is much more expensive and there are other things that would be much cheaper. Would you go for a second opinion?" And people say, "No, my doctor recommended it. How could I not take their advice? How could I say, 'Can you please refer me for a second opinion?'" Then we asked another group. We said, "Here is the situation. If this happened to your friend, would you recommend that they go for a second opinion?" People said, "Absolutely. How could you not go for a second opinion?" So one idea is to try and get ourselves from an outside perspective. You look at the situation and then you say to yourself if this was about somebody else, somebody I love and care about and then when this situation what would I advise them? And you would realize that often your advice will be different and often a more rational, useful perspective.
 

A useful trick from Dan Ariely on how to think more rationally.

Hackers

Interview with Arthur Chu, famous now for studying Jeopardy carefully and developing a strategy to optimize his chances to win. Some are criticizing his playing style, accusing him of hacking the system, but isn't that how one should try to play every game? Learn the rules and develop the optimal strategy?

I noticed some interesting things. Everyone's talking about my strategy on the show, it seems, but I didn't make anything up—I just read people's observations online. In 1985, the second year of the Alex Trebek version of the show, this guy Chuck Forrest really dominated by bouncing around the categories, and they call it the "Forrest Bounce." There's no logical reason to do what people normally do, which is to take one category at a time from the top down. Your only point of control in the game is your ability, if you get the right answer to a question, to select the next question—and you give that power up if you make yourself predictable. The more unpredictable you are, the more you put your opponents off-balance, the longer you can keep an initial advantage. Multiple people over the years have used [the Forrest Bounce] and yet most people haven't used it. When they programmed the computer Watson to maximize its chances of winning, it did the Forrest Bounce. And it specifically did Daily Double hunting. Watson knew that the bottom two rows of the Jeopardy! board are more likely to contain Daily Doubles, and it knew that whoever gets the most Daily Doubles is the most likely to win the game—that's just statistical analysis. So it was programmed to hunt those Daily Doubles, and I figured I had no reason not to do that.
 

Another excerpt of interest:

There are a few specific composers they want [you] to know. If they mention "a Norwegian composer"—this happened in a game, I think the Wednesday game—it will be Edvard Grieg. That's the method they use to write the clue. If they mention a "Polish Nobel Prize Winner," it's likely to be Marie Curie. If they mention a "Female Nobel Prize Winner," it's very likely to be Marie Curie. Jeopardy! is aimed at the sort of average TV viewer, so they're not going to ask things that are pointlessly obscure, they're not going to go in-depth on any particular subject, they're going to focus on these cultural touchstones that we all know.And if you watch the show, and you can identify those, you can literally make flashcards.

So I used a program called Anki which uses a method called "spaced repetition." It keeps track of where you're doing well or poorly, and pushes you to study the flashcards you don't know as well, until you develop an even knowledge base about a particular subject, and I just made flashcards for those specific things. I memorized all the world capitals, it wasn't that hard once I had the flashcards and was using them every day. I memorized the US State Nicknames (they're on Wikipedia), memorized the basic important facts about the 44 US Presidents. I really focused on those. But there's a lot more stuff to know. I went onJeopardy! knowing that there was stuff I didn't know. For instance, everyone laughs about sports—but I also knew that [sports clues] were the least likely to come up in Double Jeopardy and Final Jeopardy and be very important. So I decided I shouldn't sweat it too much, I should just recognize that I didn't know them and let that go, as long as I can get the high value clues.
 

*****

Gamifying world news instruction. Though gamification has some negative connotations thanks to many mobile games and online dark patterns of UI design, so perhaps we should refer to this as some variant of a nudge?

*****

The guy who hacked OkCupid to find love.

But mathematically, McKinlay’s compatibility with women in Los Angeles was abysmal. OkCupid’s algorithms use only the questions that both potential matches decide to answer, and the match questions McKinlay had chosen—more or less at random—had proven unpopular. When he scrolled through his matches, fewer than 100 women would appear above the 90 percent compatibility mark. And that was in a city containing some 2 million women (approximately 80,000 of them on OkCupid). On a site where compatibility equals visibility, he was practically a ghost.

He realized he’d have to boost that number. If, through statistical sampling, McKinlay could ascertain which questions mattered to the kind of women he liked, he could construct a new profile that honestly answered those questions and ignored the rest. He could match every woman in LA who might be right for him, and none that weren’t.
 

I expected the article to be about how his complex hacking led quickly to the perfect woman, but what's surprising (or depressing, depending on how much faith you put in market efficiency) is how much work he had to go through even after his complex data mining surfaced a candidate list.

Far easier, I suppose, if you are able to nail the three rules mentioned in the old Tom Brady sexual harassment sketch on Saturday Night Live:

1. Be Handsome.
2. Be Attractive.
3. Don't Be Unattractive.

I don't envy the best man at McKinlay's wedding, having to recount the story of how the couple met.

Polyphasic Sleep

Under the Quora question "What is something useful I can learn right now in 10 minutes that would be useful for the rest of my life?" Swami Nathan posted a sleep hack called Polyphasic Sleep:

You may know that sleep is divided into five stages. Polyphasic sleep concentrates on the fifth and most important stage of sleep, rapid eye movement (REM.) This is the most beneficial stage of sleep; it is when the brain is most active and is when dreaming occurs. REM is the only stage of sleep that is actually required to survive and function normally. The interesting part of all this is that you only spend 1 to 2 hours in this stage of sleep every night. The other 6 or so hours spent asleep every night are seemingly wasted.

Polyphasic sleep cycles basically cut out the other useless phases, giving you an additional 4 to 6 hours of time awake.

I enjoy my sleep, but perhaps I could get the same pleasure with less time. Here's one polyphasic sleep cycle pattern:

The Siesta

This sleep cycle is actually pretty common around the world in warmer countries such as Latin America, where the temperature is so hot during the middle of the day that people retire to take a short nap after lunch. It involves 6 hours of core sleep and one short 20-30 minute nap. You will find in these countries that most shops close during the early afternoon, as everyone is ‘busy’ taking their siestas!

I'm surprised I don't see any answers from Tim Ferriss in that Quora thread.

Hmmm, it's early afternoon here in San Francisco. I'm going to take a 20 minute siesta.

Clever Uses for Reverse Image Search

Via a coworker, some clever uses for Google Reverse Image Search over at Lifehacker.​ For example:

Whatever your social network of choice is, a number of fake profiles exist that try to friend you. Since most of these use stock photos or random pictures of the internet, finding the fakes is easy with a reverse image search.

If only this had come out earlier for Manti Te'o's sake.​

Where this really becomes powerful is on a mobile phone, especially as image recognition algorithms become better at identifying objects from different angles.

It only works for a limited set of products, but the iOS app Flow​ intrigued me early on with its ability to identify products from simply an image. It wasn't that useful at the time because most products it could identify were products that were, well, self-identifying, like a book with its title on the, ahem, cover. But the germ of something cool is there. Think facial recognition databases and Google Glasses, however creepy it might sound, and you get some sense of the potential.

Of course, we knew this day was coming. The internet has always excelled, above all other uses, at moving information more efficiently, and the day when pictorial information is so easily identified is easily correlated to metadata is not far away. When I was at Hulu we already had facial recognition working fairly well for characters on screen in video, and they've since rolled it out on some videos.​

Personally, I'd love that feature for Game of Thrones, where I spend half of each episode asking people I'm with who so and so is. And if such technology existed back in the day, a show like The Americans would seem more implausible, since even without that technology it's pretty clear it's Keri Russell under the endless supply of strange wigs and hairpieces she dons as costumes.​

Which reminds me, I'm glad the new Man of Steel trailer​ seems to hint that they won't even bother establishing the whole Clark Kent persona (they also try to explain the S on his chest which is why I'm guessing they're going for a more realistic depiction of the fantasy, much like Nolan's Dark Knight did for Batman). I have never been able to get over the fact that Superman's disguise was simply a pair of glasses. Everything else about him, his hair, his voice, his build, was exactly the same.

You might wonder why I'd pick on that implausibility and not any of the other ridiculous things like his red underwear (overwear), the fact that he could fly, the fact that they speak English on Krypton, all that stuff. And the answer is, there is no reason, at least none that is acceptable for a grown adult.