Essential Guide on How to Make a Graph With Two Y-Axis in Excel?

If a picture says a thousand words, a chart says ten thousand.

In the Digital Age, data is created at an increasing rate. Thus, data visualizations and charts are no longer just tools used in business meetings.

They are practical and necessary tools for conveying complex ideas and concepts without words or raw numbers.

This is why data visualizations and charts are everywhere now. The Internet has brought to view many informative, artistic and entertaining visualizations.

As a result, audiences are more engaged and familiar with visual depictions of data than ever before. They are effective communication tools to be used internally and externally.

The challenge facing marketers is choosing the correct visualization to express data in the best way possible.

There are many different types of charts available to PPC marketers. If you don’t choose the right one for the job, it may not effectively communicate the information.

In this discussion, we’ll look at various double y-axis charts that can be used for PPC marketing. For each chart type, we’ll answer the following questions:

• What is the chart?
• Why is it useful?
• When should it be used?
• What does it look like?
• How do you read the chart?

We’ll also discuss how to make a graph with two Y-axis in Excel using the ChartExpo add-in. Let’s begin!

Part 1: How To Tell Your Story With A Chart

Charts can provide a lot of value. Not only do they display the data, but they also help you see relationships between different numbers. This invites further interest or questions into the topic and deeper exploration.

Before making your chart, start by asking a question. What are you trying to prove or discover through visualization? Answering these questions will help you select the right chart to display your data.

When making your chart, simplicity is best. It’s not about impressing the viewer with your charting skills. It’s about telling the story of your data through a PPC chart.

Remember, the goal of your visualization is to simplify the complex nature of the raw data. If you use an overly complicated chart to do this, you’ll defeat the purpose. How can you tell the story of your data using the least amount of chart objects?

You do not want to bombard your audience with too many unnecessary charting elements. These extra details distract from the intended purpose of your chart.

When you use a chart effectively, your data is brought to life. You and your audiences will see the insights right away.

ChartExpo allows you to preview how your data looks in different charts. This is an excellent feature for determining the best visualization for your data.

How Can You Use Charts To Help Evolve Your PPC Campaign?

PPC data can be difficult to analyze without the assistance of visualization. There are several different metrics that each impact your performance in various ways.

To make it even more challenging, these metrics are constantly changing.

When you put the data into a chart, it is much easier to track and interpret changes. It’s not just about making the data look good; it’s about making it easier to analyze and understand.

Once you can see all of your data points in a PPC chart, you can make better management decisions. You’ll see valuable correlations in your data and identify potentially risky outliers.

Benefits Of Using Double Y-Axis Chart

If you know how to make a graph with two Y-axis in Excel, you can use it to compare different aspects of your data.

Tracking two variables at once supports PPC management because of how many metrics are involved in your campaigns.

With the double Y-axis, it is easy to illustrate a relationship between two metrics, even when those metrics have different magnitudes or scales of measurement.

Two Y-axis in one chart allows you to show double the information. This means you can get more value out of a single chart.

Here’s an example of a double Y-axis chart:

In this chart, two different KPIs are being compared. Even though each data has dramatically different scales, the double Y-axis can express both at the same time.

This allows you to compare data in a completely different way. Many different charts use this double-axis design. These various visualizations can lead to new discoveries.

Part 2: Developing Data Visualization In Excel With ChartExpo:

In this section, you’ll learn about different PPC chart examples available in Excel thanks to the ChartExpo add-in.

This is a free as trial add-in that expands Excel’s existing charting options with around 80 new selections. Many of the visualizations included in ChartExpo are designed specifically with PPC marketers in mind.

ChartExpo is the best tool to show your data in ways that immediately bring trends and insights into vision. Insights are the driving force behind change and growth in the business world.

This part of the guide teaches you how to create charts using ChartExpo and how each double Y-axis chart works.

Specifically, we’ll cover these 4 PPC charts:

1. Dual Axis Line Chart
2. Multi Axis Line Chart
3. Pareto Vertical Chart
4. The Component Trend Chart

1. Dual Axis Line Chart

This section will outline how to make a graph with two Y-axis in Excel using the ChartExpo add-in. The Dual Axis Line Chart is a prime example of why two axis are better than one!

As discussed earlier, the Dual Axis Line Chart is great for comparing two data sets with very different ranges. This frequently happens in PPC. Imagine comparing your clicks to your conversions. You may have 1,000 clicks and only a handful of conversions.

With the Dual Axis Line Chart, you can compare each trend line’s relative values, despite this imbalance in data ranges.

When To Use The Dual Axis Line Chart

To reiterate, two axis allow you to compare data with two very different ranges. This enables you to illustrate the relationship between these two variables without allowing these different ranges to skew the data.

There are many metrics in PPC marketing that share relationships, yet have very different data ranges, such as:

• Impressions: Clicks
• Clicks: Conversions
• Impressions: Conversions
• CPC: CTR
• Conversions: Conversion Value

Comparing these metrics will help you better understand your campaigns. The Dual Axis Line Chart allows you to include each data set in one chart.

Preparing The Data For A Dual Axis Line Chart

To make any graph with two Y-axis in Excel, you need to start with a spreadsheet. ChartExpo will then use this sheet to fill in the blanks of the chart.

For example, Wilma wants to compare the CPC to CTR for the last 24 hours in her campaign.

To do this, she needs a three-column spreadsheet.

The first column A will be numbered 0 through 24, for each hour of the day. Each number will be listed twice to accommodate both CPC and CTR data.

Column B will be the CPC and CTR labels. This will tell ChartExpo what data belongs to what metric.

Column C will have the values for each metric at every hour.

Creating A Dual Axis Line Chart With ChartExpo

To get started, Wilma will click on INSERT from the top toolbar in Excel. From the resulting toolbar, she’ll click the down arrow next to My Apps. Then, the ChartExpo™ for Excel option will appear.

It’s important to note that these steps assume you have already downloaded the ChartExpo add-in for Excel.

If you haven’t already done this, click here to download the ChartExpo add-in for Excel.

Once you click on the ChartExpo option, it will open the add-in. The first screen you’ll see gives you the opportunity to click Create Chart.

To help marketers navigate the expansive library of visualization options offered in ChartExpo, the tool groups charts into different categories. To find the Dual Axis Line Chart, you want to look under the Pay-per-click (PPC) category.

There is also a search bar that you can use to look up any visualization you want to use directly.

Once you’ve selected the Dual Axis Line Chart, you want to press Create Chart From Sheet Data. This will use your spreadsheet to produce a chart.

Based on the example spreadsheet of Wilma’s data, here is what the visualization would look like:

What Insights Does This Chart Give?

The first thing to note with this chart is how similar the two trend lines are. The Dual Axis Line Chart immediately shows Wilma the relationship these two metrics share.

From there, Wilma can begin looking hour-by-hour for interesting trends or anomalies.

The big spike in CPC around hour 3 is the most obvious change in the data. This is also the point where CTR is at its lowest. This could signal a hyper-competitive type slot where costs are high and performance is lacking.

Wilma may want to pause her campaign for this period!

For the rest of the day, the data remains pretty normal. Around hour 20, there is a notable increase in both CPC and CTR. This may be a time that Wilma wants to investigate further. Even though her clicks are costing slightly more, she sees a promising rise in CTR.

2-Multi Axis Line Chart

A Dual Axis Line Chart allows you to compare two values, but a Multi Axis Line Chart gives you the opportunity to compare more than two.

This means you are comparing 2 or more values with differing scales. Thus, each data set needs its own Y-axis.

Since this is a line chart, it’s helpful to compare these differing values over a stretch of time.

When To Use The Multi Axis Line Chart

In PPC marketing, there are a lot of metrics that are relative to one another.

For example, if your ad doubles its impressions or ad views, you should see a similar spike in clicks. After all, more people seeing your ads means more potential clicks.

In turn, this also creates more conversions. If 100 people click your ad and 5 convert, then it stands to reason that 1,000 clicks will generate approximately 50 conversions.

However, each metric has a very different scale. You may have 2,000 impressions, 100 clicks and only 5 conversions. The Multi Axis Line Chart makes it possible to graph all of this data in a single visualization.

Preparing The Data For A Multi Axis Line Chart

Daphne is a PPC manager who wants to see how her ads perform in terms of impressions, clicks and conversions.

Despite tracking multiple metrics, the spreadsheet is relatively easy to create. Daphne lists each date in Column A. She repeats each date three times to accommodate each metric (impressions, clicks and conversions).

In Column B, Daphne enters each of these metric categories for the corresponding data. Finally, she adds the count data for each date-metric combination in the third Column C.

Creating A Multi Axis Line Chart With ChartExpo

Similar to the Dual Axis Line Chart, Daphne starts by ensuring she has the ChartExpo add-in for Excel downloaded.

Then, it’s a simple matter of opening the add-in through INSERT and then My Apps in the top toolbar.

When the initial ChartExpo menu appears, select Create Chart.

You can find the Multi Axis Line Chart in the Pay-per-click (PPC) category. You can also search for it in the search bar.

Once you’ve selected the Multi Axis Line Chart, you want to choose to Create Chart From Sheet Data. Then, ChartExpo does the rest.

The final Multi Axis Line Chart will look something like this:

What Insights Does This Chart Give?

With this data visualization, Daphne can see how well her ads perform (or how well they aren’t performing) at each stage of the ad process.

Because of these three metrics’ relative nature, it is normal that they follow a similar trend pattern. It would be unlikely that conversions would spike when clicks and impressions are at their lowest.

The real exciting point in this example Multi Axis Line Chart is where the impressions and clicks trend lines intersect. This reflects a period of good CTR and not-so-good CTR.

Daphne may want to investigate this period further to see what changed.

3-Pareto Vertical Chart

The Pareto Vertical Chart is built on the idea that the majority of results come from a small minority of your efforts. In PPC, for example, most of your clicks and conversions will come from only a handful of keywords.

This is also known as the 80-20 rule — 80% of outputs are created by 20% of inputs. (It is not always a perfect 80-20 distribution).

Here’s an example of a Pareto Vertical Chart showing how a budget is being spent across different keywords.

On the surface, the Pareto Vertical Chart looks like a typical bar chart. The primary difference is the cascading line across the bars. This represents the cumulative total of each item.

In this example, the line shows how much each keyword contributes to the total budget. As the most expensive keyword, “mobile” spends 27.1% of the total budget. Meanwhile, the smaller bars represent just ~5% of the budget.

With this chart, you can see how much each small piece contributes to the larger whole.

When To Use The Pareto Vertical Chart

The best way to use a Pareto Vertical Chart is to identify vital versus useful opportunities in your campaigns.

Vital opportunities are the 20% of inputs that create the majority of your results.

Useful opportunities are the remaining 80% of inputs that, while not as vital, still produce some value.

When you can identify the components of your Google Ads account generating the majority of your results, you can optimize your strategies for these vital few components.

This ensures that you’re spending your time and budget on the strategies, keywords, campaigns, ad groups, etc., with the highest potential. This is a great way to maximize your productivity as a PPC manager.

Preparing The Data For A Pareto Vertical Chart

Let’s create the Pareto Vertical Chart shown above. In this example, Roger is the owner of a mobile phone shop. He just started a new PPC campaign and he wants to know how his budget is being spent across his first group of ten keywords.

Since double Y-Axis charts are used to draw comparisons between two items, Roger needs to repeat each keyword in Column A twice. He will use one keyword to represent how much budget is being spent in dollars, while the other reflects the percent of the total budget spent.

Column B is where Roger will list the “Metrics” for each keyword. In this case, his metrics are the cumulative budget being ‘consumed’ versus the actual ‘budget’ being spent for each keyword.

Last, Roger will fill in the count data in column C. For each keyword listed, he will enter the appropriate value.

Creating A Pareto Vertical Chart With ChartExpo

With the spreadsheet assembled, Roger will make the chart using the following steps.

In the toolbar at the top of Excel, select the dropdown arrow next to My Apps. Then, click on ChartExpo™ for Excel.

Next, ChartExpo will show you the different visualization categories available. You can find the Pareto Vertical Chart under Pay-per-click (PPC). You can also look for it via the search bar.

Once you have the Pareto Vertical Chart selected, you want to select Create Chart From Sheet Data.

ChartExpo will use your spreadsheet information to create the chart.

What Insight Does This Chart Give?

Every PPC campaign involves multiple keywords. On the surface, it isn’t always easy to gauge each one’s performance in terms of how it contributes to the whole. This is true for budget, clicks, conversions and almost any other metric.

In short, you know that some keywords are better than others. The Vertical Pareto Chart shows how much better one keyword is to the next. This chart is also great because it lists your keywords in order of significance.

This allows you to identify your most and least impactful selections immediately. Then, you can optimize your bids accordingly.

4-The Component Trend Chart

In PPC marketing, you have many examples of multiple metrics or data points contributing to a larger whole.

This is not unlike what we saw with the vertical Pareto Chart, where multiple keywords made up the total budget for that campaign.

This parts-and-whole relationship is prevalent in PPC. There are times where the Pareto Chart isn’t the best way to display the information. An alternative method is the Component Trend Chart.

This chart is handy when looking at how a change to one component affects the whole.

When To Use The Component Trend Chart

The best use for the Component Trend Chart is to analyze Quality Scores.

When managing a PPC campaign, paying attention to Quality Scores is absolutely vital to your campaigns’ success and performance.

High Quality Scores can improve your ad ranks, lower your costs, improve your clickthrough rates (CTR) and create an overall healthier Google Ads account.

Google uses multiple factors to determine Quality Scores. Three of the most significant factors are landing page experience, ad relevance and expected CTR.

The Component Trend Chart is helpful to see how changes in these areas could positively or negatively affect your overall quality score.

Preparing The Data For A Component Trend Chart

In this example, Oliver is a PPC manager that wants to improve his Quality Scores. He understands the significant impact that improving these ratings will have on his overall account performance.

Throughout two months, Oliver works hard to improve his landing page experience, ad relevance and CTR. He tracks his quality ratings for each category throughout this period and enters the data into a spreadsheet.

This is what Oliver’s data looks like:

This type of chart looks at data over a specified period. In column A, Oliver will put “Date” information for each data point.

Columns B and C are called ‘SubTopic 1’ and ‘Subtopic 2.’ The first SubTopic includes Ad Relevance, Expected CTR and Landing Page Experience, while the second deals with the rating scores (below average, average or above average.)

Finally, Column D shows the actual rating scores.

Creating A Component Trend Chart With ChartExpo

Again, you’ll start by accessing the ChartExpo tool in the top bar of Excel.

Click on Create Chart.

You can find the Component Trend Chart by searching directly or finding it under the Pay-per-click (PPC) category.

On the next menu, choose to Create Chart From Sheet Data.

The finished chart looks as follows:

What Insight Does This Chart Give?

With this Component Trend Chart, Oliver can accurately see how each change he made to ad relevance, expected CTR and landing page experience influenced his higher Quality Scores.

Ad relevance was a weak point for Oliver’s campaign. It started with a Below Average rating. Then, around Week03, Oliver improved this component, which resulted in a slight increase in Quality Score.

With ad relevance improved, Oliver worked on landing page experience in Week05. This created another uptick in Quality Score. Shortly after, he increased his expected CTR rating.

At the close of the 9 weeks, Oliver has a high, 9/10 Quality Score for his campaign. Looking at the Component Trend Chart, it’s evident that increasing ad relevance again is the next step. This will give Oliver’s campaign the extra push to get that perfect score!

Wrap Up

As a PPC marketer, you have to make many decisions and you don’t have a lot of time to make them. If you wait too long, you could be missing out on valuable ways to improve performance.

Learning how to make a graph with two Y-axis in Excel is the first step in becoming a more productive PPC marketer. With two axis, you’re able to compare and contrast multiple values in the same chart.

ChartExpo charts are already powerful tools that can help you make quick sense of your data. By adding an extra axis, you can pack even more value into every visualization you create.

ChartExpo transforms your raw PPC data into stunning visualizations. With some of the best charts added to Excel, you’ll be able to see deeper insights in your data.

Deeper insights mean a superior competitive advantage!