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.

Basic excel charts

Highlight the data and navigate to the “Insert” ribbon in order to find the Chart options.

 

charts

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.

basic excel charts

 

Clicking into the chart itself activates the chart options ribbon

 

excel charts 5

 

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: –

excel charts 6

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
  • Legend
  • Data labels
  • Data tables

We can also make changes to the axis and add or remove gridlines.

Add a title to an excel chart

 

basic excel charts 7

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.

excle basic charts 8

 

Example of an overlayed chart title

 

charts basic excel 9

 

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.

excels charts the basics 10

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.

Secondary axis

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

 

charts 11

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.

charts 12

Right click the section and navigate to the final option “Format Data series…”

Charts 13

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.

excel charts 14

Using the “Chart Tools” option use the Change chart type and change the second data set to a line graph.

charts 15

This is so much more visually appealing than the previous version.

charts 16

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.

excel chart basics 17