From the course: Data Visualization: A Lesson and Listen Series

Lesson: Best practices in Excel

From the course: Data Visualization: A Lesson and Listen Series

Start my 1-month free trial

Lesson: Best practices in Excel

“

(upbeat music) - Five years ago, a lesson about visualization and best practices in Excel would probably have been much simpler, in a way, but much longer. That's because back then, the lesson would have consisted of two key ideas. First, every default setting in Excel is wrong, so you need to change everything. The second thing would have been, now, here are all the things to change and how. But Excel has evolved over the years. And I don't intend to bash Microsoft or Excel in this lesson at all. The fact is, Excel is the one tool every single person who works with data probably has on their machine, and it's probably the one they've used the most. You can't overstate the importance of Excel. That being said, I'm really glad many of the default settings have improved. Even still, there are plenty of suggestions I would make to improve your work in Excel. First starters, when creating a chart with multiple series, like a grouped bar chart, for example, Excel still just decides to assign a unique color to every group. This makes sense theoretically, but when you have a bunch of series, you end up with a color palette that's way too large and isn't helpful to understand the data. You could say the answer is to not try to visualize so many different series at once, which is totally valid, but if you need to, odds are, you can make most of them gray and simply use color to highlight the one or two that need highlighting. Speaking of color, the default colors in Excel are not as terrible as they used to be, but they could still use some love. The axis lines and labels are still a bit too dark, the chart element color options are still a bit too stark, and the fact that color keys and legends are still shunted off to the side or below the chart, and there's no way to do inline labeling. So you should think carefully about these things and reduce your color contrast for axis lines and labels, and do inline labeling manually when possible. Another tip is to get familiar with the copy, paste special, transpose dialog box option. Excel gets a bit quirky, making certain types of charts depending on how your data is organized. For instance, if you have data in columns and you want to generate a line chart, in older versions of Excel, you would need to transpose the data into rows to get it to work sometimes. Same thing with pie charts and other chart types. The program sometimes gets confused by the organization of the data either vertically or horizontally in the sheet. So knowing how to flip that orientation without losing the integrity of the data is a good skill to know and play around with if you're getting odd results with your charts. Another really great tool built into Excel that isn't about data visualization and best practices, but I love to teach people about, is Power Query. If you're on a PC, go to the Data tab and click Get Data. From there, you can open a file or connect to a dataset, and do all kinds of transformations of the data before bringing it into Excel to work with and visualize. And each of your transformations is saved and reversible and editable so you can tweak this logic down the road. This is one of the most powerful and least-used features in all of Excel. Finally, my advice when it comes to doing great visualization in Excel is to just google it, of course! Say you want to make a slope graph in Excel, which is not a built-in chart type. Just google it, and you'll find plenty of articles and videos about how to make it happen. Which brings me to my guest for this episode, Stephanie Evergreen. She's one of those people whose website comes up all the time when you google how to make visualizations in Excel. She's an expert at data visualization and best practices and Excel. So she's the perfect person to talk to on this subject. Join us.

Contents