Data Visualisation in Excel doesn’t need to be second class
As with most crafts, data visualisation can succumb to the temptation of snobbery especially about data visualisation in Excel. All too often I’ve seen Tableau, Power BI or programming language users look down their noses at those still using just Excel. So, it’s time for an advocate to step forward and that man proves to be Jon Schwabish. You may already know him as a leading light in the global data viz community, economist & senior fellow at the Urban Institute.
The book I review in this post should help to counter that prejudice. Yes, there are good reasons to favour some of the scalability & features of Tableau or PowerBI. Yes, a great deal of artistic freedom and control can be achieved my mastering use of R or Python. But, the reality is that far more analysts need to use Excel. The reasons for that can be manifest. Limited budgets, data, familiarity to users, time to learn etc etc. All I know is that they are the majority of people who attend my data viz courses.
So, I was delighted to see a new data visualisation book published by my friend Jon Schwabish. With the straightforward title “Data Visualization in Excel“, it delivers on that promise. Given the ubiquity of Excel being used for data analysis & the plethora of books to help you learn Excel, it is surprising how few focus on data viz. A noble exception is the classic “Data at Work” from Jorge Camoes. But this very practical book from Jon fills a need and closes a gap. I’ll explain why as I unpack what to expect.
What is there to cover on Data Visualisation in Excel?
First, let me address the misconception that this is just a repeat of your existing Microsoft manual. Although Jon does explain the user interface (and better than Microsoft’s explanation) this book is focussed on taking you further. Grounded in his practical experience as a data visualiser, Jon reveals how you can go further than the obvious charts. How some clear formatting & data prep and enable you to support a wider repertoire of charts than you’ll find on the ribbon.
To deliver in that learning experience, he divides this book into three sections. In “Setting“, Jon introduces both how to use this book & how to approach graph building in Excel. What may surprise some readers is the time also taken to help you master a number of formula which will be key to data prep. Next, in “Making“, he provides detailed walkthroughs of how to produce 23 different charts. These range from simple to advanced examples and cover a variety of data & chart types. Finally, in “Moving” Jon provides practical advice on exporting your graphs from Excel. He also includes an engaging chapter on how to reproduce some famous charts produced in other software using Excel.
This book is more like completing a training course (especially with the support of Excel data files which you can download). It can later be a handy reference guide, but your best learning will come from experiencing applying the steps to data yourself. One of the ongoing benefits of this textbook will be the appendices. Here Jon opens his treasure trove of useful resources and lets us peak inside. He provides recommended Colour Tools, Data Viz Tools, Excel Resources and a Quick Instruction list as an aide memoire. All these will be handy launching points to continue your data viz CPD journey.
Which other charts will this enable me to produce?
Like other people who enjoy Andy Kirk’s great crowdsourced project The Chartmaker Directory, I often want to know what charts a solution will support. Just as that is often a relevant question to ask about software, it’s legitimate for those thinking of buying this book/training to want to know the charts included. So, here is my summary of what you’ll be able to produce by the end of this learning aid.
Beginner charts (less steps, more directly supported)
- Temporal data (Sparklines, Gantt Chart, Block Shading for same frequency)
- Categorical data (Heatmap, Stripe Chart)
- Part-to-whole data (Waffle Chart)
Intermediate charts (more steps, more data prep, more formatting)
- Temporal data (Event Line on Chart, Slope Chart, Block Shading for different frequencies)
- Categorical data (Connected Dot Plot, Diverging Bar Chart, Bar + Dot Combo Charts, Broken Stacked Bar Chart, Overlaid Gridlines, Lollipop Chart)
- Distribution data (Strip Chart)
Advanced charts (many steps, much data prep, much formatting)
- Temporal data (Cycle Plot)
- Categorical data (Bullet Chart, Marimekko Chart)
- Geospatial data (Tile Grid Map)
- Distribution data (Histogram, Raincloud Plot)
Now those with good attention to detail may have spotted that some of the above charts are now ‘supported’ within Excel as chart options, for instance sparklines & histograms. Even in these cases, Jon brings to life ways you can take more control of the way your data is presented than the default option. He also supplements all the above with an excellent chapter on formatting better tables.
What did I learn from completing this book/training?
Firstly my Excel skills are improved, thanks Jon. One of the triumphs of this book is the way it can work for both complete beginners & experienced users. Novices will find Jon’s introduction helps them get to grips with Excel’s UX and his chapters truly explain each step to take. But even those who consider themselves proficient will discover new ways to format data or charts that you didn’t know possible.
For those familiar with Jon’s past work or advice as my podcast guest, this adds to those others. I previously reviewed “Better Data Visualizations“. That is still worth buying to understand the design principles and process for effective visualisations. This book will help you apply those principles in Excel. Anyone who has previously purchased one of Jon’s Excel eBooks from his fun PolicyViz store will find familiar material here. But again Jon has both updated and expanded upon that material. You’ll also learn more about the options that you could choose and why (many chapters show multiple ways to do it).
In conclusion, I highly recommend ‘Data Visualisation in Excel’. It’s a very practical help for anyone who uses Excel as a tool for their data visualisation. It will also help you feel more confident in that choice (given the bigotry I mentioned at the start). The abiding lessons that have stayed with me are:
- Don’t skimp on preparing your data layout to make chart design easier
- Do less manually & more using formula (including for labelling & in situ annotation)
- Consider the power of dual charts to enable formatting (esp. scatter plots & error bars)
- Practice, practice, practice (and check back on some of the hacks that Jon used)
Perhaps the best concluding words for this book review is the thought that Jon leaves us with in his final chapter. Wise words indeed from an ever generous contributor to data viz best practice today.
“When you find a visual that inspires you, consider if it consists of lines, bars or circles and if it lives within an X-Y space. If so, you might find that Excel – with a little tweaking and tricking – can be the right tool for you.”
Jonathan Schwäbisch (Data Visualization in Excel, 2023)