Exploring more complex charts

1. Exploring more complex charts

Welcome back! Now that you know the basics, let's progress to slightly more complex charts.

2. Single vs multi-series charts

So far, we have worked with single-series charts. Now, we will introduce the concept of multi-series charts. Imagine that you run a chain of restaurants. As a business owner, you are following up on the sales generated by your restaurants each year as well as on the number of restaurants in your business. You are pleased to see that both graphs are progressing in the right direction.

3. Single vs multi-series charts

This prompts you to consider plotting the evolution of both measures, sales and the number of restaurants, on the same chart. You suddenly realize that the rapid growth in restaurants primarily drives the continuously rising sales. Upon calculating the average sales generated per restaurant, you observe a decline. This is unwelcome news! You can identify interconnected patterns and uncover deeper insights by incorporating various measures on the same graph, often requiring multiple data series.

4. Combo charts

Excel allows us to plot multiple series onto one graph with the help of Combo charts. We can decide the type of visualization for each series. For example, sales can be plotted as a line, while columns can represent the number of restaurants. Moreover, we can choose whether the data series should be presented on the primary axis (on the left) or secondary axis (on the right). This is particularly useful when dealing with vastly diverse levels of magnitude, as in our example: thousands of sales are presented alongside double-digit numbers of restaurants.

5. Bullet chart

One example of a combo chart is a bullet chart developed by Stephen Few, a renowned guru in data visualization. As illustrated on this slide, this type of visualization is often used when comparing results against benchmarks. Bullet charts require working with dual axes. In our case, we are comparing sales, plotted on the secondary axis, and the benchmark, plotted on the primary axis. Both are presented as clustered columns, and with the use of colors, they provide clear insights: we have scored below the benchmark for several years.

6. Waterfall chart

Ready for more interesting charts? The waterfall chart explains the net change in value between two points, such as the evolution of the customer base. It usually begins at a baseline, in this case, 100K customers we start with in Q4 of 2020, and then a series of columns guides us through the events of each quarter. The use of colors helps differentiate between positive and negative values, so increases and decreases with respect to Q4-20 baseline. This type of graph reveals the hidden complexity behind an aggregated number. We understand that the overall increase of 10K results from both positive and negative net additions in the previous quarters.

7. Scatter plot (bubble plot)

Now onto scatter plots, also known as bubble plots in Excel. They present a relationship between two numerical variables plotted simultaneously along the X and Y axes. They can be powerful as they compare various categories based on how they score on two measures. Here, we are comparing the contribution to sales and costs across product categories, where the size of the bubble represents the absolute sales. The Food category drives our business, outclassing other bubbles in terms of sales and costs. However, notice that Cosmetics generate significant sales with exceptionally low costs! These kinds of unexpected insights are uncovered when working with scatter plots.

8. Chart formatting options

In data visualization, the devil is often in the details. Excel offers various chart formatting options, providing ample data series, axes, or plot area choices. In this chapter, we will get our first taste of these options and continue exploring them throughout the course.

9. Let's practice!

Enough theory for now - let's practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.