PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

PUT TEXT LABELS ON VERTICAL Y AXIS IN EXCEL CHARTS

INTRODUCTION

As we know that Charts of graphs are the graphical representation of a tabular data. Charts are very easy to analyze as it is a scientifically proven fact that visual data is more easily to understand and interpret.

Excel is a super tool and provide us so many type of charts within seconds. We can create a chart by just selecting the data and clicking a button, which makes it really very easy to create.

But even then there are a few situations which become difficult to handle. For tackling such situations we need to use different tools available in Excel and make the things work. In this article, we are going to handle one such situation.

Have you ever tried to put the TEXT LABELS on the vertical axis??

Yes, in the clustered bar chart, it can be done easily but what about the others. Suppose we want to create a Line chart , where the labels are on the vertical or y axis. It is not easy to create Labels on the vertical axis.

So, in this article, we will create a graph showing a line chart and the labels on the vertical axis.

PLANNING FOR THE SOLUTION OF PUTTING LABELS ON VERTICAL AXIS

Let us try to plan our solution.

We have to create a chart with categories on the vertical axis and line chart on the horizontal axis.

In a single chart, it won’t be easy to create such chart, as it is not available. As soon as we choose the Line chart, it’ll

create a chart of course, but put the labels on the horizontal axis itself.

So, it derives that we need to create a combo chart. The first chart will be CLUSTERED BAR as it’ll help us to get the labels on the

vertical label and second will be XY Scatter chart as It is the only chart which allows the repetition of the values, as X,Y values

can repeat at different levels.

So let us try to build our chart.

EXAMPLE TO SHOW THE CREATION OF TEXT ON VERTICAL AXIS

We’ll take the following example.

We want to find out the number of frequencies of failures of a machine in the different years. [ It is just an example ]

The following table shows the data.

YEAR	NUMBER OF FAILURES
2015-2016	20
2016-2017	16
2017-2018	12
2018-2019	16
2019-2020	10

We’ll create a vertical line chart in Excel for this data.

Follow the steps to create one.

STEPS TO CREATE A VERTICAL CHART IN AXIS

CREATE A MODIFIED TABLE TO EASE OUR WAY

As, we need to create a combo chart, we need one more column which will ease our way to create the desired chart.

For this, we , will create one more column with a simple series of 1,2,3……

So now our table becomes something like this. It’ll become more clear when we move further creating a chart.

YEAR	TEMP	NUMBER OF FAILURES
2015-2016	1	20
2016-2017	2	16
2017-2018	3	12
2018-2019	4	16
2019-2020	5	10

CREATE A COMBO CHART

After our table is ready, let us start the process.

STEPS TO INSERT A COMBO CHART IN EXCEL:

The first requirement of any chart is data. So create a table containing the data.[We have already created in the form of table above]
Select the complete table and go to INSERT TAB.
Under the INSERT TAB go to CHARTS SECTION and click on the COMBO CHARTS OPTION .
Under the options dropdown click CREATE CUSTOM COMBO CHART.

The process till now is shown in the picture below.

After clicking the CREATE CUSTOM COMBO CHART, A dialog box with the choices will appear.
Excel would ask for the choices which kind of charts we would like to have.
For the TEMP series, choose from the drop down menu , CLUSTERED BAR and for the NUMBER OF FAILURES choose from the XY SCATTER, SCATTER WITH STRAIGHT LINES AND MARKERS. which almost looks like a line chart.
Click OK.
The Combo chart will be created.
Now go to the ADD CHART ELEMENT option on the top left of the DESIGN TAB [ which will appear when chart is selected]
Click ADD>ADD AXIS> SECONDARY HORIZONTAL. We did this to add one more axis to the chart so that we can sagregate the axis of both the charts. Now one chart is from left to right and other one is from right to left.
THE ANIMATED PICTURE BELOW SHOWS ALL THE STEPS.

EDITING THE DATA OF MAIN SERIES

The data of main series that is THE NUMBER OF FAILURES need to be edited for the data.

Follow the steps.

Select the chart.
It’ll activate the DESIGN TAB in the RIBBON. If not selected, select it manually.
Click SELECT DATA as shown in the picture below.
The SELECT DATA SOURCE dialog box will open.
Select the NUMBER OF FAILURES field and click EDIT.

After clicking the EDIT BUTTON, the following dialog box will open.

Enter the data in the series as marked in the picture below.
Series name should be entered as the NUMBER OF FAILURES [It can be selected by clicking the cell.]
Series X will be NUMBER OF FAILURES DATA i.e. from G17:G21.
Series Y will be TEMP and data will be from F17:F21.
Click OK.
The following picture shows the chart after clicking OK.

Look at the chart above.

The XY chart shows the correct values in the way we want.

Now we are just left with a few tasks.

Bring the text labels to the left side.

Remove the extra elements present on the chart.

BRINGING THE TEXT LABELS TO THE LEFT SIDE

Let us try to bring the LABELS to the left side of the chart.

Follow the steps.

Click on the LABELS [secondary vertical category axis].
The FORMAT AXIS box will open on the right.
Go to TEXT OPTIONS.
Go to LABELS and from the POSITION DROP DOWN , choose LEFT.

MATCHING THE CATEGORY AND THE VALUE ON THE SAME AXIS

After the categories have gone to the left side, we have one more problem.

The problem is the mismatch of the XY CHART VALUES and the LABELS. Let us try to fix them.

Follow the Steps

Click the LABELS.
Go to FORMAT AXIS OPTION BOX on the right.
Choose TEXT OPTIONS.
Under the AXIS OPTIONS, go to Axis Position and select the option ON TICK MARKS. [ As shown in the picture below].
As soon as we choose this, the bars will coincide with the tick marks.
Now we need to set the XY CHART values so that the first category has the correct value against it.
The current situation of the chart is as follows.
We can see that the values on the ORANGE LINE doesn’t coincide the TEXT LABELS which are the years.

coinciding the tick marks of both the charts and removing the extras

Now , we have almost finalized our chart. So let us fix the final issue of setting the values against the categories.

Follow the steps:

Select the VERTICAL VALUE AXIS [ The range in the left from 0 to 6].

Go to FORMAT AXIS OPTION BOX on the right.

Choose TEXT OPTIONS.

Choose AXIS OPTIONS.

Under AXIS OPTIONS set the minimum value as 1 and Maximum value as 5 which are the temp values as we put in the table. It’ll help us match the values correctly.

The following picture shows the process and the map after making the options.

Select all the extras one by one and press DELETE.
Select the VERTICAL VALUE AXIS, VALUES ON THE TOP, CHART TITLE and press DELETE.
Select the BLUE BARS and RIGHT CLICK.
Choose NO FILL in the FILL OPTION.
The BARS ARE GONE and we are left with the chart we planned.
The following animation shows the process.

FINAL VERTICAL CHART WITH LABELS ON VERTICAL AXIS

After changing the STYLE of the chart we get the final output as follows.

NOTE:

FOR ALL OTHER TASKS LIKE CHANGING THE NAME OF THE CHART, CHANGING THE AXIS , CHANGING THE CHART STYLE ETC. VISIT HERE [HOW TO CREATE A CHART IN EXCEL].

Contents