How to Hide Secondary Axis in Excel Without Losing Data

Sometimes, you need to use a secondary axis in Excel to express the dataset more clearly. Because of using a secondary axis, we will have a new axis and scaling. Your secondary data will be set into charts according to secondary axis scaling. This article will show you how to hide the secondary axis in Excel without losing data which means you will hide the secondary axis but the data remains the same. I hope you find this article very interesting and gain lots of knowledge regarding this topic.


Download Practice Workbook

Download the practice workbook below.


2 Suitable Methods to Hide Secondary Axis in Excel Without Losing Data

To hide the secondary axis in Excel without losing data, we have found two different approaches through which you can hide the secondary axis in Excel but the scaling of the data remains the same. Both of the methods are highly applicable for your future purpose.

First, you need to add the secondary axis in Excel using the process we discussed previously. Then, you can hide the secondary axis from the chart element.

Conceal Secondary Axis in Excel

But in this case, after hiding the conversion rate, the scaling will be changed.  As the conversion rate value is very small compared to sales and profit, you won’t get it in the chart until you add the required secondary axis for the conversion rate.

To eliminate the problem, we have found two effective methods through which you can solve the issue. Follow the methods carefully to understand them.


1. Using No Fill from Format Axis to Hide Secondary Axis

Our first method is based on using the no fill for the secondary axis. First, we take a dataset where a secondary axis is a must. After that, we add the secondary axis. Then, we would like to hide the secondary axis without losing data and also without losing the scaling. For our case, we get the following chart including a secondary axis.

Using No Fill from Format Axis to Hide Secondary Axis in Excel

To do this follow the steps.

Steps

  • First, right-click on the secondary axis.
  • Then, select Format Axis from the Context Menu.

  • After that, the Format Axis dialog box will appear.
  • Then, select the Text Options tab at the top.
  • Select No fill from the Text Fill section.

Applying No Fill from Format Axis to Hide Secondary Axis in Excel

  • As a consequence, we will get the following chart where the conversion rate data and scaling remain similar.

Utilizing No Fill from Format Axis to Hide Secondary Axis in Excel

Read More: Excel Bar Chart Side by Side with Secondary Axis


2. Setting Axis Transparency to 100% to Hide

Our next method is based on setting transparency to 100% while using the Solid fill. First, we take a dataset where a secondary axis is a must. After that, we add the secondary axis. Then, we would like to hide the secondary axis without losing data and also without losing the scaling. For our case, we get the following chart including a secondary axis.

Follow the steps properly to understand the method.

Steps

  • First, right-click on the secondary axis.
  • Then, select Format Axis from the Context Menu.

Setting Axis Transparency to 100% to Hide Secondary Axis in Excel Without Losing Data

  • After that, the Format Axis dialog box will appear.
  • Then, select the Text Options tab at the top.
  • Select Solid fill from the Text Fill section.
  • Then, set the Transparency to 100%.

Defining Axis Transparency to 100% to Conceal Secondary Axis in Excel Without Losing Data

  • As a consequence, we will get the following chart where the conversion rate data and scaling remain similar.

Read More: How to Create a Combination Chart in Excel (4 Effective Examples)


How to Add Secondary Axis in Excel

Before hiding the secondary axis in Excel without losing data, we need to add a secondary axis in Excel. To show the process, we take a dataset that includes the date, sales, profit, and conversion rate. We would like to add a secondary axis for conversion rate. Follow the steps carefully.

Steps

  • First, select the range of cells B4 to E15.

  • Then, go to the Insert tab on the ribbon.
  • Select Insert Column or Bar Chart drop-down option from the Charts group.

  • Then, select Clustered Column from Insert Column or Bar Chart drop-down option.

  • As a result, you will get the following chart.
  • But the secondary axis is absent. As the conversion rate value is very small compared to sales and profit, you won’t get it in the chart until you add the required secondary axis for the conversion rate.

  • Then, right-click on the chart.
  • A Context Menu will appear. From there select the Change Chart Type option.

  • Then, the Change Chart Type dialog box will appear.
  • Select Combo chart options from the Recommended Charts section.
  • Then, select the Custom Combination option where you can customize your chart combination.
  • After that. Check on the secondary axis for conversion rate.
  • Finally, click on OK.

How to Add Secondary Axis in Excel Before hiding Secondary Axis in Excel Without Losing Data

  • As a consequence, you will get the following chart along with the secondary axis that represents the conversion rate.

  • To get a better view, we can alter the color of the conversion rate line.
  • To do this, right-click on the line.
  • Then, select Format Data Series from the Context Menu.

  • As a result, the Format Data Series dialog box will appear.
  • Then, select Fill & Line.
  • After that, change the color of the line.

  • Finally, we get the following chart along with the secondary axis. See the screenshot.

How to Include Secondary Axis in Excel Before hiding Secondary Axis in Excel Without Losing Data

Read More: How to Add Secondary Axis in Excel Pivot Chart (with Easy Steps)


Things to Remember

You can hide the secondary axis in Excel from the Chart Elements options or Chart Design In both cases, the scaling will alter. So, you can’t get the data after hiding the secondary axis.


Conclusion

We have shown two different and effective methods to hide the secondary axis in Excel without losing data. All of these methods are user-friendly. In both cases, the scaling of the data remains similar. I hope covered all possible areas of this topic. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.


Related Articles

 

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo