How to Add Percentage Axis to Chart in Excel

Most of the people that are dealing with computers have been asked to present their data visually, preferably with charts.

These charts could often include percentages, whether on the main or secondary axis. We will show how to do this in the example below.

Add Percentage Axis to Chart as Primary

For the example, let us presume that we have a loans table with the name of loan approver, loan amount, and the percentage of each loan in a total amount:

A picture containing table

Description automatically generated

You can notice that the numbers in column C are not formatted as percentages. We will now select the populated cells in columns A and C and go to Insert tab >> Charts and choose any chart that we want, in our case 2-D Column:

A picture containing graphical user interface

Description automatically generated

We now got a simple chart with the data for the loan approvers and percentages:

Chart, bar chart

Description automatically generated

To formulate the axis on this Chart, we will simply click anywhere on the data on the left (on the numbers) and select Format Axis:

Graphical user interface, application

Description automatically generated

We will get a window on the right side of our screen with Axis options shown. We will click on the Numbers, then choose Percentage under Category:

Graphical user interface, application

Description automatically generated

Our Chart now looks like this:

Chart, bar chart

Description automatically generated

Add Percentage Axis to Chart as Secondary

The above is a fairly easy example as we had only percentages to deal with. Now we want to present all of the data we have on one chart. Luckily, newer versions of Excel are pretty helpful in this regard.

We will select our table and then go to Insert >> Charts >> Recommended Charts:

Chart

Description automatically generated with medium confidence

Excel is smart and will give us the best suggestions based on the data type we have:

Graphical user interface, chart, application

Description automatically generated

In our case the Clustered Column is the best solution, so we will click OK and will have the same chart as in the picture above created:

Chart, line chart

Description automatically generated

We will change the format of our right axis again and define that it shows percentages instead of numbers. Now, to show these values on the graph as well, we will click anywhere on the percentage line, right-click and then choose Add Data Labels:

Chart

Description automatically generated

Now we have our percentages on the right axis and in our chart as well:

Chart, line chart

Description automatically generated

Keep in mind that numbers in the chart will always appear as they are defined in the original table, regardless of the change that we did on the axis, so we need to format these numbers as percentages on the right-side pop-up window, as we did earlier.

It is easy to have percentages on a second axis with the auto-suggestion charts. But what if we wanted to create it on a different chart type?

To do this, we will select the whole table again, and then go to Insert >> Charts >> 2-D Columns:

Chart, bar chart

Description automatically generated

To show percentages on a second axis, we first need to click anywhere on the orange bars that we have on our graph (this is not easy in this example as they are rather small). Once we do, we will right-click on it, and then select Format Data Series:

Graphical user interface, application

Description automatically generated

Then we will define (on the right window) that these series are moved to the secondary axis:

Graphical user interface, application

Description automatically generated

Currently, our chart looks like this:

Chart, bar chart

Description automatically generated

Now we need to select the percentages (orange bar) and then right-click on it, and then click on the Change Series Chart Type option:

Chart, waterfall chart

Description automatically generated

On a window that appears, we will choose Line as a chart type for percentages:

Graphical user interface, application, Word

Description automatically generated

We will then click OK and have our final chart:

Chart, line chart

Description automatically generated

You can notice that we got to the clustered column chart type again, but indirectly this time.