Recently my sister asked me to plot a bar chart in excel from a table of data. There were 5 different columns but she only wants to choose 2 columns, one going to X- Axis/Axes or Y-Axis/Axes.
Now initially when I heard about this, I thought ohh, its just a piece of cake.. right because excel is known for its charts and it should be easy. But soon I realized its not..here is why..
Before we start that I should show how my table looks like :
Now I have to extract data from Conc column to show as X-Axis and % (percentage) as Y-Axis
By default when you export data from table into charts by going to Insert > Chart and you choose the chart, be it the column or bar chart, it only gives either X axis or Y axis to give the data range. And Excel automatically selects the other axis.
Now this is not what I want, I need to plot both axis. After couple of googling, i found that this cannot be done straight away. I may be wrong but this is how it worked for me…
Go to Insert > Chart and Select from Standard Types select XY (Scatter) go to next button, here it should show Chart Wizard – Step 2 of 4 – Chart Source Data. Here select the 2nd tab that says Series.
Here under series , you will series heading with a small box with Add button, click the Add button which will pop up with these :
Under name you can either type in the name or clicking on the small icon besides will let you select the data range from the excel sheets. You can select X-Axis values by clicking on the icon to select the data range starting from the values (excluding the heading/title) Do the same for Y-Axis values.This can be done by clicking from the top cell down to bottom till the end of the data cell.
Click on next, you should be in Chart Wizard – Step 3 of 4 – Chart Source Data You can give the title for X & Y Axis . Go to legend tab in the same step and I choose to show the legend at the bottom, it sort of gives my graph a better room to show data. You can remove this legend later. Go to next step and click on finish.
Now you should have a Scattered Graph, which is not what we want, therefore, Right Click on the Graph area, which will drop down a menu with some options, select change chart type
Clicking on chart type will give you the Chart Type window and Select the Bar or Column Chart from this window.
Now you should be able to see bar or column layout of your graph with X & Y Axis plotted. That’s it off you go!!