Excel tips and tricks

Often with these kinds of training videos it’s the ‘ooh I’d forgotten you could do that‘ moments that are the most useful. The overall topic of the tutorial itself might not be what you’re after (do you really need to know how to make a Christmas Tree in Excel?), but just simple reminders about things like F9 or index match are just the ticket.

Here are some of my recent favourites:

From Exceljet‘s channel, How to fill in missing data with a simple formula

From Leila Gharani‘s channel, Excel index match advanced: lookup multiple criteria in rows or columns

From the My Online Training Hub channel, Labeling events in Excel charts

From the Excel Is Fun channel, Excel magic trick 1474: Excel twinkling Christmas Tree with star & formulas as presents!

A time before Excel?

An article from 1984 about these crazy new electronic ledger sheets, or “spreadsheets”.

A spreadsheet way of knowledge
There is no doubt that the electronic spreadsheet saves time and provides insight; there is no doubt that even greater benefits will one day be derived from these grids. Yet all these benefits will be meaningless if the spreadsheet metaphor is taken too much to heart. After all, it is only a metaphor. Fortunately, few would argue that all relations between people can be quantified and manipulated by formulas. Of human behaviour, no faultless assumptions – and so no perfect model — can be made.

I wonder what the author would have made of the Quantified Self movement, that attempts to incorporate data from a person’s daily life into various formulas and models. Spreadsheets are everywhere now, not just in the office of accountants from Cincinnati.

But who could possibly hate Excel?

This tool makes it stupid simple to turn data into charts
Here’s something handy for people who hate Excel. Venngage, the company that lets you make infographics with the click of (a few) buttons, recently launched a new product called Beam. Beam does for charts what Venngage does for infographics as a whole. Which is to say, it makes the process of transforming data into useful visuals very, very easy.

And they look really spidery too

Another great Excel article from Mynda Treacy​, this time about her​​ views on radar charts. It’s safe to say she’s not a fan. Some great points here about data visualisation and how to get messages across. I’ve never used a radar chart, but that’s more because I’ve never really understood them.

Excel alternatives to radar charts
Radar charts display data in a circular fashion, which is the opposite of the straight line comparisons we’re able to subconsciously perform. This means we have to work hard to make any comparisons and as a result we’re likely to make mistakes in our assumptions.​

She mentions this article ​by Stephen Few, who weighs in further but does recognise when these radar charts might have their uses.

Keep radar graphs below the radar – far below (pdf)
This one advantage motivates me to ease up just a bit on my repugnance toward radar graphs.

If only

PowerPoint should be banned. This PowerPoint presentation explains why.
It is estimated that more than 30 million PowerPoint presentations are given every day. But as PowerPoint conquered the world, critics have piled on. And justifiably so. Its slides are oversimplified, and bullet points omit the complexities of nearly any issue.​

A great PowerPoint about PowerPoint, with examples that really hit the mark. “Make these slides the last ones you ever read”? If only. It’s far too ingrained and we’re far too lazy. And don’t think Prezi’s any better, unless you’re a fan of motion sickness.

But wait, here’s a different view.

Tools are not to blame, blame laziness
PowerPoint and Excel aren’t the enemy, though a lot of people to seem to think they are. Frequently, I hear others say “don’t use PowerPoint” or snicker when someone mentions conducting analysis with Excel. This is wrong. There is nothing wrong with Excel or PowerPoint; I am a fan and user of both programs. The problem is the creativity and capability of those using those programs and it’s time to realize that.

And here’s a great piece on PowerPoint from Russell Davies.

29 Bullets
9. Lingua franca PowerPoint is how organisations communicate. Email someone a PowerPoint file and there’s a high chance that they’ll be able to open it. Most organisational knowledge is probably stored in PowerPoint files. (Though it feels like I should be writing that as ‘knowledge’ and ‘stored’) Microsoft understand this power, of course, and they work very hard to make PowerPoint backwardly compatible. You can open a PowerPoint 3.0 file from 1992 in today’s version on an iPad Pro and all the animations will run as intended.

Clever lists in Excel

Another Excel solution in search of a problem. I must be able to try this out on something…

Create dependent drop down lists in Excel
“Limit the choices in an Excel drop down list, by using named ranges and the INDIRECT function, to create dependent data validation lists. For example, select Fruit in column A, and only Fruit items appear in the drop down list in column B.”​

Excel and the cat’s whiskers

Excel Box and Whisker Diagrams

Excel box and whisker diagrams (box plots)
Box and Whisker Charts (Box Plots) are commonly used in the display of statistical analyses. Microsoft Excel does not have a built in Box and Whisker chart type, but you can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars. This tutorial shows how to make box plots, in vertical or horizontal orientations, in all modern versions of Excel.