Draw charts in excel according to the table
The visual way of representation makes any information easier for perception. Graphs and charts are one of the ways to present reports, plans, figures and other business materials. They are irreplaceable analytic tools.
There are several ways to build a graph in Excel off a table. Each of them has its advantages and drawbacks, depending on the task. Let's view them one by one.
Simplest charts of variance
A graph is needed when you have to highlight the variance of your data. Let's start with the simplest chart for demonstration of events in different time periods.
Let's assume we have the data for the company's net business income in 5 year:
| Year | Net profit * |
| 2010 | 13742 |
| 2011 | 11786 |
| 2012 | 6045 |
| 2013 | 7234 |
| 2014 | 15605 |
* The numbers are conventional, assumed for training purposes.
Go to the «INSERT» tab, which suggests several chart types.
Click «Insert Line Chart». Select its type in the pop-up box. When you hover the pointer over a chart type, it will show you a hint: for what type of data this graph is best suitable.
Select it – copy this table containing the data – paste it in the chart area. You will get the following example:
We don't need the straight horizontal (blue) line. Just select and delete it. As we have only one curve, delete the legend (to the right of the graph) as well. To make the information clearer, give titles to the markers. Click on the graph to activate the auxiliary panel. Go to the tab «CHART TOOLS»-«DESIGN»-«Add Chart Element»-«Data Labels» and choose the position of numbers. In the sample it is on the right.
Improve the image by giving titles to the axes. Go to «Add Chart Element»-«Axis Titles»-«Primary Vertical»:
Instead of the sequence number of the reporting year, we need the year itself. Select the horizontal axis value. Go to «Axis Titles»-«Primary Horizontal».
The heading can be deleted, moved into the graph area or placed above it. You can change the style, apply a fill color, etc. All the actions are available on the «Chart Title» tab. It's demonstrated on the image below:
Instead of the sequence number of the reporting year, we need the year itself:
This can be the graph's final form. Or you can select a fill color, choose a different font, move the chart to a different sheet («CHART TOOLS»-«DESIGN»-«Move Chart Location»).
Charts with two or more curves
Let's assume we need to demonstrate not only the net business income, but the value of assets as well. The amount of data has increased:
| Year | Net profit | Asset value |
| 2010 | 13742 | 67486 |
| 2011 | 11786 | 76785 |
| 2012 | 6045 | 75620 |
| 2013 | 7234 | 77800 |
| 2014 | 15605 | 83523 |
The graph building principle remains the same, though. However, this time it's reasonable to leave the legend in its place, as we have two curves.
Adding the secondary axis
How to add one more (secondary) axis? If the units of measurement are the same, use the above instruction. If you need to demonstrate data of different types, you will need a secondary axis.
Start off with building a graph as if the units of measurement were the same.
Select the axis for which you want to add a secondary one. Right-click – «Format Data Series» – «SERIES OPTION» - «Secondary Axis».
Behold the second axis that has emerged in the graph and adapted itself to the data of the curve.
This is just one of the ways. Another one is to change the chart type.
Right-click on the line that needs an additional axis. Select «Change Series Chart Type».
Choose the type for the second row of data. In the sample, it's a bar chart.
Just a few clicks – and the secondary axis for the different type of measurement is done.
Building a charts of function in Excel
All the work consists of two stages:
- Creating the table containing the data.
- Building the graph.
Example:
y=x(√x – 2). Increment – 0.3.
Build the table. The first column is the X value. Use formulas. The first cell's value is 1. The value of the second one is = (the first cell's name) + 0,3. Grab the bottom right corner of the cell containing the formula and drag it downward as much as needs.
In the Y column, enter the formula for calculating the function. In our example, it's: =A2*(IMSQRT(A2)-2). Hit «Enter». Excel will calculate the value. Reproduce the formula across the whole column (by dragging the cell's bottom right corner). The table containing the data is ready.
Go to a new sheet (or you can use the current one – place the cursor in an empty cell). «INSERT»-«Insert Scatter (X, Y)»-«Scatter with Smooth Lines». Choose a type. Right-click on the chart area – «Select Data».
Select the X values (the first column). Click «Add», which will open the «Edit Series» menu. Enter the row title – the function. The X values are in the first column of the data table. The Y values are in the second.
Hit OK and behold the result.
Overlaying and combining charts
Building two graphs in Excel isn't difficult. Let's combine two graphs of function within one area in Excel. We will add Z=X(√x – 3) to the previous one. The table containing the data:
Select the data and insert it in the chart area. If something goes wrong (wrong row titles, wrong depiction of numbers of the axis), edit it using the «Select Data» tab.
Here are our two graphs of function within one area.
Dependency charts
The data in one column (row) depends on the data in the other column (row).
A dependency graph for two columns is built in Excel as follows:
Conditions:
А = f (E); В = f (E); С = f (E); D = f (E).
Choose the chart type: «Scatter with Smooth Lines and Markers».
Select the data – «Add».
The row's name is A. The X values are the A values. The Y values are the E values. «Add» again. The row's name is B. The Y values are the data in the E column. Do the similar for the entire table.
You can also build doughnut and Gantt charts, bar and bubble charts, stock market charts, etc. Excel offers a variety of opportunities, which are sufficient for representing various types of data visually.
