How to Create Stacked Column Chart With Two Sets of Data in Google Sheets?

You’ll agree when we say conducting part-to-whole comparisons across key categories in your data is not easy.

The situation becomes further complicated if your raw data is bulky. You need a chart that’s primarily designed to help you display parts of multiple totals. A Pivot table is not recommended, especially if the size of your data is enormous.

This is where the Stacked Column Chart with two sets of data comes in.

This chart can help uncover hidden part-to-whole insights into your data. Besides, you can use both nominal (i.e. data without natural order or rank) and ordinal (i.e. data that has natural order or rank) data types to plot this visualization.

Data visualization tools, such as Google Sheets come with pretty basic Stacked Column Charts. And this implies you have to do a ton of customizations and editing, which can take significant amounts of time.

But, you can transform Google Sheets into a reliable tool by installing third-party add-ons.

This blog will walk you through how to best leverage the Stacked Column chart with two sets of data using the third-party add-on. Besides, you’ll also learn how to deploy Stacked Column Chart in your data stories.

Before we dive into the how-to guide, let’s go through the definition of a Stacked Column Chart.

In this blog you will learn:

1. What is a Stacked Column Chart?
2. When Should You Use a Stacked Column Chart With Two Sets Of Data?
3. How to Read a Stacked Column Chart with Two Sets of Data?
4. What are the Advantages of Stacked Column Charts?
5. What to Keep in Mind When Creating Stacked Column Chart with Two Sets of Data?
6. How to Visualize Data Using ChartExpo Add-on (for Google Sheets)
7. How to Edit a Stacked Column Chart with Two Sets of Data

Video Tutorial:

What is a Stacked Column Chart?

Definition: The Stacked Column Chart is essentially an extension of the standard Column Chart.  Lets have an example of Stacked Column Chart

Each column in this chart is divided into several sub-bars stacked end-to-end. The Stacked Column Chart with two sets of data is best suited in tracking the trends of crucial data points over time. Besides, it outperforms the Pie Chart by far in displaying part-to-whole relationships.

Why?

The Stacked Column Chart is easy to read and interpret. Besides, it can handle bulky data without appearing cluttered.

When Should You Use a Stacked Column Chart With Two Sets Of Data?

The main goal of the Stacked Column Chart is to help you gain insights into the levels of categorical variables in your data.

In this chart, columns are plotted for each level of the categorical variable. And each column’s length indicates the value as total value against that particular dimension. More so, each column is made up of sub-bars, each corresponding with a categorical variable.

Use this chart if your goal is to display the relative decomposition of each primary column. You can also use the Stacked Column Chart to display comparisons between key data points. You can also use this chart for visualizing Hierarchical Data with proper formatting.

How to Read a Stacked Column Chart with Two Sets of Data?

To interpret the Stacked Column Charts quickly, practice the tips below religiously:

• Understand the data points you’re comparing

We recommend you take your sweet time to understand the critical data points and their respective contexts.

E.g. below image is showing data of population in UK which accumulatively showing 25 Million. At the same time you can compare it within same country with other age group or at the same time with other country’s age groups.

You need to know to whom you have to present the data and what should be your focus to understand the data points.

• Analyze the trend

Review the overall trend within the chart to gain more insights. For example, a hypothetical company’s sales in the United States have grown steadily over the past three years.

However, a Stacked Column Chart may reveal minor downtrends as well.

• Spot outliers

Use the gridlines in Stacked Column Chart to compare the trends of key variables in your data. You’ll be able to spot outliers present in your data. Although these outliers are not as presentable as dot but as a bar length of a particular value which will make it prominent from the rest.

What are the Advantages of Stacked Column Charts?

Check out below the reasons why you should try the Stacked Column Chart with two sets of data this 2021:

• Easy to spot hidden insights

One of the strengths of a Stacked Column Chart is that you can easily spot comparison insights.

Besides, you can easily see data points clearly because they’re on top of each other. You can also undertone the percentage of each data point with respect to the total value.

• Outliers

A Stacked Column Chart is one of the best charts to use if your goal is to uncover outliers in your data.

For example, imagine you run a hypothetical company that deals with ten different smartphone brands. Over the years, you’ve established the best and worst-selling brands.

One of the underperforming brands did so well in one month. And this forced you to respond to the surging demand by ordering more quantities.

But then, the sales declined drastically and never recovered. In fact, this is the second financial year, and you still have a massive inventory of the worst-selling phone brand.

This is a typical case of an outlier misleading you.

• Saves time

A stacked Column Chart with two sets of data can save you the time you normally consume, primarily when visualizing bulky data sets.

For example, you can use the Stacked Column Chart to visualize the results of a survey.

Instead of manually sorting through thousands of responses: you can simply use this chart to find out the number of respondents who agreed or disagreed with a particular question.

What to Keep in Mind When Creating Stacked Column Chart with Two Sets of Data?

You can easily compare data points across the primary categories in your data. But, it’s less straightforward, especially when comparing key variables across the secondary categories.

Use this chart if you want to uncover hidden comparison insights into secondary variables versus the primary categories.

You can easily achieve this goal (above) using a baseline.

Note: it’s quite challenging to use a Baseline to compare secondary variables because it’s likely to experience a shift. And this might distort the key insights.

Check out the Stacked Column Chart below. Note how it’s challenging to identify the actual trend of the central (yellow) variable.

Note how hard it is to spot changes in non-baseline groups over time. Besides, comparing the sub-bars in each primary bar is also challenging and might result in distorted interpretation.

Picture yourself shooting the ducks in a gun range while blindfolded. How many would you take down?

Try sorting out the secondary category levels by overall size. You’re likely to get distorted insights. In the screenshot above, you can’t tell whether the purple “West” group outperforms the yellow “Central” group in terms of size.

Keep the following in mind.

The main goal of a Stacked Column Chart with two sets of data is to uncover part-to-whole insights. This makes the comparison of secondary bars with the primary columns really essential.

If your goal is to assess sub-groups’ performance within your data, then your go-to chart should be a Stacked Column Chart.

How to Create a Stacked Column Chart with Two Sets of Data?

Google Sheets is one of the most used tools for visualizing data. And this is because it’s free and easy to use.

But the freemium data visualization tool has a very basic Stacked Column Chart in its library. This means you’ll have to spend more time customizing the chart to make it appealing and insightful.

You don’t have to ditch Google Sheets. Why?

You can actually supercharge your Google Sheets by downloading and installing third-party add-ons. Yes, you can access advanced charts, such as Stacked Column Chart, via third-party applications (also known as add-ons).

The add-on we recommend you to use is ChartExpo. Use this tool to create irresistible data stories with the Stacked Column Chart with two sets of data.

So what is ChartExpo?

We recommend ChartExpo because it has an ultra-friendly user interface (UI). You don’t need programming or coding skills just to extract insights from your data.

Furthermore, this data visualization tool is more of a ‘drag-and-drop tool’. The aforementioned means you’ll only need to scroll your mouse and fill in the respective metrics and dimensions.

The ChartExpo comes with a free trial period.

The core benefit of ChartExpo is that it produces charts that are easy to read and interpret. More so, it allows you to save charts in the world’s most recognized formats, namely PNG and JPG.

ChartExpo comes loaded with 50-plus charts, including a Stacked Column Chart with two sets of data, Donut, Area, Scatter Plots, Sankey, Survey Charts, and Sentiment charts, among others.

How to Visualize Data Using ChartExpo Add-on (for Google Sheets)?

Imagine you want to compare the proportion of the age groups (0-14 versus 15-64 years) in the population of various countries for hypothetical data.

Which chart would you go for?

If your answer is the Stacked Column Chart with two sets of data, you’re absolutely right. Let’s assume the tabular data below is what you’ve collected.

Let’s get down to business.

Country	0 – 14 years	15 – 64 years
UK	5	20
Germany	6	29
Mexico	17	31
Japan	9	44
Russia	13	49
Brazil	26	55
United States	29	91

Once you have installed ChartExpo for Google Sheets follow the easy steps below:

• Open your Google Sheets desktop application.
• Open the worksheet and click the Extension menu button.
• Once the ChartExpo-Best Data Visualization Tool drop-down menu shows, click the Open button.

• You can add your data in sheet and click the Create New Chart button from ChartExpo on right side of the screen, as shown below.

• You will see list of charts provided by ChartExpo.

• You can find Stacked Column Chart from the list.

• Select the sheet holding your data, as shown above.
• Fill in your metrics and dimensions. In our example, we have 2 metrics: 0-15 and 15-64 years. The dimensional variable in our data is Country.
• Complete the simple task by clicking the Create Chart button.

How to Edit a Stacked Column Chart with Two Sets of Data?

To edit your Stacked Column Chart click the Edit Chart button, as shown below.

• To Change the chart’s title, click the pen-like icon.
• You’ll come across a window called Chart Header Top Properties. Proceed and click the Line 1 box and fill in your title. In our example, the title is “Age Analysis by Population.”
• Toggle the button (just below the Line 2 box) to the right side.
• Save all the changes by clicking the Apply button, as shown below.

• After brief editing, your final Stacked Column Chart with two sets of data should look like the one below.

Insights

• The United States has the highest proportion of people aged between 15-64 years.
• The UK and Germany have the lowest proportion of people aged between 0-14 years.

FAQs:

What is the Stacked Column Chart used for?

You can use the Stacked Column Charts to display part-to-whole relationships in your data.

If your goal is to show parts of the grand total, consider other variants, such as a Bar Chart. This chart can use the available space more efficiently than a Stacked Column Chart with two sets of data. You can also use this chart to visualize ranking data.

How can you create a Stack Column Chart with two sets of data?

Freemium data visualization tools, such as Google Sheets, can help you visualize data using the Stacked Column Chart.

But, they come with basic and less appealing charts.

You can transform your Google Sheets into a visualization juggernaut by downloading and installing third-party add-ons, such as ChartExpo.

Wrap Up:

Extracting part-to-whole insights, especially from a bulk data set, is easier said than done.

To succeed, you need a specialized data visualization chart to help you extract reliable insights.

This is where the Stacked Column Chart with two sets of data comes in.

The Stacked Column Diagram is the chart we recommend, especially if your objective is to display hidden insights into sub-categories and their relationships to the whole in your data.

Google Sheets should not be your go-to visualization tool. Why?

The spreadsheet application comes with pretty basic Stacked Column Charts. You’ll need additional time and effort to customize charts to align with your requirements.

We recommend you install third-party apps, such as ChartExpo, into your Google Sheets to access ready-made and visually appealing charts.

ChartExpo is an add-on you can easily download and install in your Google Sheets.

This easy-to-use tool comes loaded with an insightful and easy to interpret Stacked Column Chart, plus over 50 more advanced charts.

Here’s the kicker.

You actually don’t need programming or coding skills to visualize your data using the add-on. Sign up for a 7-day free trial today to access easy-to-interpret, responsive, and visually appealing Stacked Column Charts.