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

Contents

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.

YEARNUMBER OF FAILURES
2015-201620
2016-201716
2017-201812
2018-201916
2019-202010

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

Follow the steps to create one.

FIND PIE CHART BUTTON ON EXCEL
EXCEL:BUTTON LOCATION FOR INSERTING PIE CHARTS

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.
YEARTEMPNUMBER OF FAILURES
2015-2016120
2016-2017216
2017-2018312
2018-2019416
2019-2020510

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.
CREATE A COMBO CHART IN EXCEL
EXCEL:FOLLOW THE STEPS AND CREATE A COMBO CHART
  • 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.
CREATE A COMBO CHART IN EXCEL AND ADD ELEMENT TO CHART
EXCEL:CREATE COMBO CHART AND ADD ELEMENT TO THE CHART

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.
EDIT THE DATA OF THE CHART IN EXCEL
EXCEL: EDIT THE DATA OF THE MAIN SERIES

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.
EXCEL:EDIT THE DATA OF THE XY SCATTER CHART

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.
CHANGE THE DIRECTION OF THE LABELS
EXCEL:CHANGE THE POSITION OF THE LABELS FROM RIGHT TO THE 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.
 
 
CHANGE THE DIRECTION OF THE LABELS
EXCEL:CHANGE THE POSITION OF THE LABELS FROM RIGHT TO THE LEFT

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.
 
CREATE VERTICAL CHARTS IN EXCEL
EXCEL:SET THE MAXIMUM AND MINIMUM LIMITS OF THE VERTICAL VALUE AXIS
  • 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.
CREATE VERTICAL CHART IN EXCEL EASILY
EXCEL:REMOVE THE EXTRAS

FINAL VERTICAL CHART WITH LABELS ON VERTICAL AXIS

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

 
FINAL VERTICAL CHART IN EXCEL
EXCEL:FINAL OUTPUT

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].

 

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]