Simon Hurst reviews the ‘Definitive 100 most useful Excel tips’ and turns the list into an interactive presentation.
Because of my former involvement, I was able to obtain an early draft of the data behind Excel with Business Office’s recent publication: The Definitive 100 Most Useful Excel Tips. If there is one certain thing about a 'definitive' list of Excel tips, it is that every Excel user will have a different idea of what the list should include.
To quote Excel with Business's own description of how they made their selection: As we’ve sold half a million Excel courses ourselves, we have a lot of data on what people know and don’t know, use and don’t use in the programme.
We wanted to summarise all of that Excel know-how into one document full of Excel tips. By combining the wisdom of the world’s 30 leading Excel experts with our own data, we created our visualisation: a 7,000-word infographic of the 100 most useful Excel tips, ordered from 1 to 100.
As well as deciding which tips to include, Excel with Business has used its data to provide additional information about each one:
- A usefulness score - out of 100
- A measure of complexity – out of 5
- Time in minutes for an average learner to get from no knowledge to proficient
- Percentage of users who answered the relevant questions in the course test correctly
However, the chance to use Excel to analyse itself proved too much of a temptation, so we are going to link Power BI to the Excel workbook containing the supporting data for the Definitive 100 tips, and create an interactive presentation instead.
Just before we get started on the creation of our presentation, the copy of the data I have used is an early version, before final proofreading and polishing, so advanced apologies for any errors and omissions.
We have covered the basics of Power BI in previous posts. Cutting Big Data down to size: Broadband speeds included an introduction to downloading and using Power BI. For this example, we are going to use a slightly different approach. Rather than the presentation using charts and graphics to show different summaries of our data, the key element will be the text of each individual Excel tip.
Having used Get Data from Excel to link to the data we can use two separate Card graphics to display the Final Commentary field and the title in the Name field. We have also added a Slicer based on the Name field so that we can choose individual tips to display.
Next, we'll consider the other information that we have about each tip. The Excel with Business data includes fields for the Difficulty of each tip, the amount of time taken to learn each tip in minutes, the Utility of the tip and finally, where available, the percentage of those taking the course test that got the correct answer to questions related to the tip.
For each of these we need to create an average, so that any overall total shows something meaningful. We can do this by using New Measure from the Calculations group of the Modelling Ribbon tab, or by right-clicking on a Field and choosing New Measure. We have chosen to describe our difficulty average as Rocket Science? as shown in this example:
Rocket science? = Average('1 Master List'[Difficulty])
We have set up four separate Gauge graphics to present this information Perhaps the easiest way to do this is to create and format the first as required, and then copy and paste this to create the other four with the same size and shape, before changing the fields used and the axis and target values:
We can select all four graphics by holding down the control key while clicking on each one and then use the Align and Distribute commands in the Arrange group of the Visual Tools, Format Ribbon tab to ensure that the graphics are aligned and spaced evenly.
Charts as filters
Currently, we have a simple Slicer that just lists the tips by name. Given the number of tips this is a bit unwieldy and also rather unexciting. Instead, we are going to use a scatter chart, with the learning minutes as the X axis and the utility index as the Y axis.
The Name field is added to the Details area so that each of our 100 tips will be placed according to the relationship between how useful it is and how long it takes to learn. Turning on the Category labels in the format section of the visualisation displays the tip title in our chart.
Our tips are split into different sections and we will use the Section field as the Legend. Rather than displaying the key to the Legend, we have added a bar chart to show the total learning times by section, and changed the individual colours of the bars to match the colours used in the scatter chart.
We have also added a treemap diagram to show the count of the different levels of difficulty:
We can use any element of our charts to filter our content. Selecting an individual 'bubble' in the scatter diagram will display the title and text of that tip and the corresponding gauge values. We can also select a particular level of difficulty, or section, to see all the tips that fall into that category.
If we want to absorb the tips more passively, we could install the Play Axis custom visual and play through our individual tips in order.
You might need to use the Animation Settings in the Format section to change the Time in milliseconds to allow enough time to read each tip:
After a bit more tidying up and adding the rank number to our tip details, we have used File, Publish, Publish to Power BI to publish our sample presentation. Once opened in Power BI we can use File, Publish to web to create a publicly available presentation.
No doubt, there will be lots of disagreements about the tips that did make it in to the Excel with Business top 100 but, given that Excel Zone has just published the second part of a two part series on Conditional Formatting, there seems to be some consensus as to the importance of tip number 1.
Of course, you might prefer the wallpaper approach:
The publication is available as a 106-page PDF. You could print this out and use it to paper just under 7 square metres of your office wall. The full Excel with Business PDF is available here.
Note that you will need to provide your email address before downloading it.
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.