Creating basic charts in Excel
Charts are a visual representation of numeric data that allow for easy identification of key numerical values and proportional representation. Basic charts are what most people would refer to as graphs.
How to create a chart in excel
If we use data from a local bake sale as an example we can make a simple chart to visually display the data.
Highlight the data and navigate to the “Insert” ribbon in order to find the Chart options.
Let’s make a bar chart first as this is probably the Chart most people are familiar with.
When we select the “Column” options the fly out menu gives us many presentational options but for this first example I will sue the 2-D column.
Selecting the first option in the 2-D section produces a chart like the below that requires a little tinkering with to improve the presentation.
Clicking into the chart itself activates the chart options ribbon
This “Design” ribbon in chart tools allows us to change the type of chart, say from a bar chart to a pie chart or we can switch the columns and rows to change the layout of the graph as below: –
In basic charts like this the Calories and Number sold data is grouped together so can be compared quickly and easily with similar data sets.
There are numerous pre designed templates that can be selected if a change of colour scheme is required.
The layout options within the Chart tools ribbon is where we can really make some changes to the chart and improve the general presentation.
Under this ribbon we can add: –
- Chart titles
- Axis labels
- Data labels
- Data tables
We can also make changes to the axis and add or remove gridlines.
Add a title to an excel chart
Selecting the Chart title option opens a fly out menu where the options are: –
None – No chart title
Centered Overlay Title – Title is overlayed on top of the graph in the centre of the chart space.
Above chart – The title is added in the border around the chart.
Example of an overlayed chart title
Example of an above chart title
In the above chart title the chart data is compressed slightly in order to fit the title within the chart area.
The chart axis also deserve labels to help the end user identify what the data is represented. Using the “Axis Titles” button on the ribbon we can add titles to either or both axis.
The addition of the chart and axis titles makes the chart itself feel more appealing and user friendly, it is now easy to understand exactly what the data is showing and how the cupcakes compare.
In the big bun sale data it is difficult to see how the numbers sold compare as the main axis stretches from 0 -300 in order to accommodate the calorie counts for each cake.
The addition of a second axis will allow us to compare the numbers sold easier, this is a simple step forward from basic charts.
How to add a second axis in an excel chart
Select the axis that is to be used for the secondary axis, if you are not sure which to use try one and see how it looks, you can always undo and try the other axis if required.
Right click the section and navigate to the final option “Format Data series…”
When the pop up box opens use the radio buttons to select the Secondary Axis option. The secondary axis will then be displayed onto the chart, however at present the data for the second axis is stacked on top of the first data series.
Using the “Chart Tools” option use the Change chart type and change the second data set to a line graph.
This is so much more visually appealing than the previous version.
It is now easy to draw conclusions from the data represented by the chart! Has the strawberry sold least due to it’s high calorie value? Or is it just that the other flavours are more appealing?
Adding a second axis title and moving the titles to the correct location produces an appealing final product.