I do, but I don’t get the disdain toward Excel that’s so common among many technically-inclined data and tech people. The way I see it, most tech professions have some medium for wireframing, prototyping and sharing mockups or light renderings to the audience. That’s precisely what Excel does, and does well.
And there’s no better prototyping tool in data than data visualization — it’s literally the way we humans are wired to process information.
This learning path provides some resources to get up and running with data visualization in Excel. This is a curated playlist of free online resources to fulfill the learning objective, which is:
Learning path objective
By the end of this path, learners should be able to create common univariate and bivariate visualizations in Excel using basic principles of information and experiment design.
Notes on nomenclature
- Visualizations in Excel are called “charts,” whereas most places elsewhere they’d be “plots.” I’ll use the terms interchangeably; e.g. a “scatter plot” and “scatter chart” are the same thing.
- “Dataviz” is a common nickname for “data visualization” and is a good hashtag to use and follow for the topic.
With that out of the way, time to explore some free resources for data visualization in theory and, using Excel, in practice:
Getting excited by dataviz!
It’s great to dive into the technical aspects of Excel charts or nerd out on cognitive science, but it’s even better to “start with why.” In this case, charts and spreadsheets are a means to the end of having fun with data and to making an impact with your audience. The former may not seem important when there’s a job to be done, but your audience can tell when you’re fired up about your work and responds accordingly.
I like to point to the late global health scholar Hans Rosling as someone who had fun and made an impact with data visualization. So check out this short video and get excited about dataviz! And yes, you may not be able to produce a wall full of animated charts in Excel… at least, not yet. But you can bring the same enthusiasm and insights to the task.
Data visualization as cognitive superpower
The right data visualization often makes a statement that mere numbers and figures never good. But did you catch the caveat? It has to be the right visualization — and there are a lot of wrong ones out there! In this section, learn about more about the purpose of data visualization and what makes it so powerful, along with some basic elements.
Data visualization as data analysis
It’s easy to think of data analysis (such as exploratory and confirmatory data analysis) as separate from data visualization, but dataviz is not a separate or optional step from these tasks.
Data visualization often tells a more revealing or even more correct story than mere summary statistics, as famous examples like Anscombe’s Quartet show. And while there’s a fair amount of “softer” thinking when it comes to visualization — such as choosing color palettes — one can’t overlook elements of data analysis too — such as the difference between univariate and bivariate data analysis.
The elements of Excel charts
Now that you’re getting a hang of the theory, it’s time to get your boots on and open a spreadsheet. Charts are a kingdom unto itself in Excel, and these resources serve as an overture to what awaits you there. From here, you’ll be ready to build and customize the charts covered in the remainder of the learning path.
Visualizing categories with bar charts
To paraphrase Toby Keith, I love this bar chart. One of the most common visualizations out there, it’s usually used to compare frequencies across categories.
You’ll see here, along with the other Excel chart types, not to be content with default settings. Often, it just takes a few clicks to turn an Excel chart from “meh” to “wow.” For example, you may want to flip the axes of your bar chart, which in Excel makes it a column chart.
Visualizing proportions with pie charts
Talk about controversial! There’s as much disdain toward the pie chart as there is toward Excel, but in both cases it’s a matter of misunderstanding proper context. Learn a bit here about the origins of pie-hate and why you still may want to make (or should it be bake?) one in Excel.
Visualizing changes over time with line charts
You know what they say: Time is money! Line charts are used to visualize trends over time. With these two posts you’ll get the basic theory and practice for building them in Excel.
Visualizing distributions with histograms
As discussed earlier, visualizations often impart information that numbers don’t. A histogram as a depiction of a variable’s relative frequencies is a great example. While they look a bit like bar charts and serve a similar purpose, they are different.
As you continue learning more about data visualization, an alternative to the histogram is the boxplot, or Box & Whisker chart as it’s known in Excel. As an optional step here, try inserting one on your own and do some research. What can you find out?
Visualizing relationships with scatterplots
This is the one and only bivariate visual we’ll get to know here, and it brings up some new design considerations for us: how do we know which axis what goes on? This gets into the basics of experiment design, independent versus dependent variables and so forth.
Quick wins to customize Excel charts
You are getting somewhere with your Excel charts! As you’ve seen, it often takes a bit of “hacking” from the default to make a stellar one. Here are some handy references for how to make Excel charts pop and what to avoid.
Stacked and combo charts
You can make some pretty incredible Excel charts… but it can quickly get complicated. Stacked and combo charts are two relatively more exotic chart types which you’ll likely come across in your data journey — learn the basics here.
Charts: Try it yourself!
Now it’s your turn: with the skills you’ve picked up in this learning path, it’s time to build some visualizations in Excel. I’ve provided a worksheet containing the population of New York City’s boroughs over time; use this or grab your own dataset and get started.
There are no right answers to what you visualize, except that you should aim to make an impact, use solid information design principles… and have fun doing it.
Bookmark these
This path is but a few steps on a long journey of Excel charting. Perhaps even more critical than what you know about the topic is knowing where to look for help. Here are a few top resources for data visualization, specifically in Excel. I’d also encourage you to check out any of the resources linked to in this path.
As you get comfortable with data visualization in Excel, I encourage you to branch out to other applications such as Tableau or Power BI. If you’re statistically inclined, R and Python are great to know and make fantastic visualizations. Check out my book Advancing into Analytics: From Excel to Python and R to get started.
The future of paths
This curated learning path is a new format for me.. what do you think? What makes it helpful? What could make it more helpful? What other topics would you like to see? Please let me know in the comments.
Hi George. I thought the learning path approach was great. It took me a few days to work through them all but it was worth it. You covered a lot of ground. I played around and enjoyed learning some new tips and tricks. I think a “structure” of data analysis would be a good learning path. The steps to go from raw data (or data collection) to exploratory data analysis with visualizations. That would be beneficial to see the steps clearly outlined in one place.
Awesome, thank you Joseph for your thoughts and I certainly see where you are going with that idea. I am working on a post offering frameworks for exploratory data analysis and presenting results so accompanying paths could play in there too. Thanks again for your support!
I should also really do one on Power Query.