What Is Secondary Axis In Google Sheets?
A Secondary Axis in Google Sheets helps users view the second or the required data variable on a different axis because we cannot show two different datasets on a single axis. This separate axis is called the “Secondary Axis”.
We can add the Google Sheets Secondary Axis on an existing chart, but first, we must ensure to plot a base chart that accepts Secondary Axis such as Column Chart, Line Chart, Bar Chart…
For example, the below table shows a firm’s region-wise quarterly sales figures and we will analyze the data visually using the Clustered Column chart and then add theGoogle Sheets Secondary Axis
Select cell range B2:E6 and enter the Clustered Column chart from the charts group.
Now, that we have a base chart, the Secondary Axis plotted will be as shown below.
The inserted Secondary Axis in Google Sheets is seen on the right side in red color, as a right axis.
Key Takeaways
- A Secondary Axis in Google Sheets is needed to view the required variable data because when plotting a chart with multiple variables or datasets, the generated chart cannot display all the variables on a single axis
- The charts containing the Secondary Axis displayed, are also known as Combo Charts. We can add, change or remove the Secondary Axis from a chart.
- We can use the Column Chart, Line chart, etc., as a base chart to add a Secondary Axis.
- We can format or customize the chart according to our requirements or highlight the columns is a presentable way, while generating reports.
How To Add A Secondary Axis In Google Sheets?
We can Add A Secondary Axis In Google Sheets in 2 ways, namely,
- Simple to Add Secondary Axis in Google Sheets.
- Manually Add Secondary Axis to the Chart.
Method #1 – Simple to Add Secondary Axis in Google Sheets
Step 1: First, organize the dataset in a way so that we know the X and the Y axes variables.
Step 2: Next, choose the entire dataset. Select the “Insert” tab – choose the “Chart” option.
Step 3: A default chart, i.e., the “Column chart”, gets generated. The “Chart editor” window opens on the right-side.
Step 4: Instead of selecting a chart and then adding a Secondary Axis, we can simply select a best suitable Combo Chart containing a Secondary Axis. Therefore, to select a different “Chart type”, select the “Setup” menu, click the “Chart type” drop-down and select the required chart types from the “Column” section, as shown below.
Method #2 – Manually Add Secondary Axis to the Chart
Step 1: First, organize the dataset in a way so that we know the X and the Y axes variables.
Step 2: Next, choose the entire dataset. Select the “Insert” tab – choose the “Chart” option.
Step 3: A default chart, i.e., the “Column chart”, gets generated. The “Chart editor” window opens on the right-side.
Step 4: We can continue with the Column Chart as it is compatible with adding a Secondary Axis. However, to select a different “Chart type”, select the “Setup” menu, click the “Chart type” drop-down and select the required chart types from the “Column” section, as shown below. And once we have the requiredcompatible Chart, we can then customize it as per our requirements.
Step 5: Let us Add the Secondary Axis. In the “Chart editor” window, click the “Customize” tab and then click the “Series” option drop-down, as shown below.
Step 6: In the “Apply to all series” drop-down. Select the required data series for which the Secondary Axis must be added, as shown below.
Step 7: Select “Right axis” in the “Axis” drop-down, as shown below. It is because the existing chart or the base chart already has a left axis.
Step 8: Now, close the “Series” option. Open the “Right vertical axis” option drop-down, as shown below.
Step 9: Now, the Secondary Axis will be added and visible. In the “Right vertical axis”, alwaysselect the “Text color” as “Red”, so that the Secondary Axis is brightly visible and check the “Show axis line” checkbox, as shown below.
Examples
We will consider some Secondary Axis in Google Sheets examples to add, change or remove the Secondary Axis.
Example #1
We will plot a Secondary Axis for the below table that shows the monthly customer votes data for two brands.
The steps to create a Clustered Column Chart and add a Secondary Axis for the given data are,
Step 1: First let us create a compatible chart, here, Clustered Column Chart. Therefore, choose the cell range A2:E4 – select the “Insert” tab – choose the “Chart” option and the “Chart editor” window opens on the right-side, as shown below.
The chart generates as shown below.
Step 2: Let us customize the chart as required and update the titles, chart styles, range values within the chart, etc. Then, the final 3D Clustered Column Chart is shown below.
Step 3: To add the Secondary Axis, go to the “Chart editor” window and click the “Customize” tab. Then, click the “Series” option drop-down and click the “Apply to all series” drop-down, as shown.
Step 4: In the “Apply to all series” drop-down, select the “Apple” option and select the “Right axis” in the “Axis” drop-down, as shown below, because the existing chart or the base chart already has a left axis.
Step 5: Now, close the “Series” option and open the “Right vertical axis” option drop-down. Always select the “Text color” as “Red”, “Label format” as “Bold”, so that the Secondary Axis is brightly visible and check the “Show axis line” checkbox, as shown below.
The final chart with the Secondary Axis, is shown below.
Example #2
The below table shows the units ordered data for the listed items in the specified months at a Houston warehouse. Also included is the required target unit data for each item. The requirement is to check visually the items that met the targets during the specified months.
The steps to create a Clustered Column chart and then add a Secondary Axis for the given data are,
Step 1: Choose the cell range A2:D13 – select the “Insert” tab – choose the “Chart” option and the “Chart editor” window opens on the right-side, as shown below.
The generated Clustered Column Chart is shown below.
Step 2: Update the titles, chart styles, range values within the chart, etc. Then, the final Clustered Column Chart is shown below.
Step 3: To add the Secondary Axis, follow the steps learned in Example 1 from Step 3 to Step 5.
To understand better, check the following images. Here, we have chosen “Column 2” for Secondary Axis
The final Google Sheets Secondary Axis on Clustered Column Chart, is shown below.
Example #3 – Change the Secondary Axis in Google Sheets
Let us consider the Example 1’s final chart and Change the Secondary Axis in Google sheets.
The steps to change the Secondary Axis in Google Sheets are as follows,
Step 1: Open the “Chart editor” window. Remember we had applied to the “Apple” series, in Step 4 of Example 1. Let us change it back to “Apply to all series” in the “Series selector” drop-down and to “Left axis” in the “Axis” drop-down, as shown below.
Step 2: Now, the chart will be just like how it was before adding the Secondary Axis. Here, we can add the new Series, or change the Chart from Apple Series to Samsung Series.
We get the final Secondary Axis changed from Apple to Samsung, as shown below.
Example #4 – Remove the Secondary Axis in Google Sheets
Let us consider the Example 2’s final chart and Remove the Secondary Axis in Google sheets.
The procedure to remove the Secondary Axis in Google Sheets is,
- Open the “Chart editor” window and click the “Customize” tab.
- Click the “Series” option drop-down, select the “Apply to all series” in the “Series selector” drop-down and select the “Left axis” in the “Axis” drop-down, as shown below.
We will get the Chart without the Secondary Axis, or it removes the Secondary Axis, as shown below.
Important Things To Note
- The Secondary Axis requires a different chart type from the primary axis chart. This type of chart is also called the “Combo Chart”.
- We can read the primary axis numbers from the left side vertical axis and read the Secondary Axis numbers from the right-side vertical axis.
Frequently Asked Questions (FAQs)
An alternate way to insert achart to add Secondary Axis in Google Sheets is,
First, select the dataset – click the “More” option on the toolbar, as shown below.
Next, click the “Insert chart” icon, as shown below, to open the “Chart editor” and we know the rest.
A few reasons the Secondary Axis in Google Sheets may not work are,
a. The dataset used to generate the chart is modified or deleted.
b. The dataset is not organized in a right way to understand which variable will be X or Y axis.
c. The series needed for to add the Secondary Axis is not selected and the axis is not changed from left to right.
For the Secondary Axis, we can use the Combo Chart, Line Chart, etc., from the Recommended Charts category in Excel. We can also use the Clustered Column Charts, as we learnt in this article.
Download Template
This article must help understand the Secondary Axis in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Secondary Axis In Google Sheets. We learn how to add it in Google Sheets to view another dataset with detailed examples. You can learn more from the following articles.
Leave a Reply