Create a bar graph

In Excel, not only can we keep and calculate data, but we can also create graphs very simply.
Let us look at how to create graphs, starting with the most simple, bar graphs.

Example of use) How to create a bar graph

The table below shows the number of stationary (HB pencils) sold at each branch store and manufacturer. We will try to create a graph based on this data.

 

(1) Highlight data to make a graph out of. Include column title (manufacturers) and row title (branch area).

 

(2) Click the ‘Insert’ tab on the top left, and select the graph button shown below (※Bar graph button).

 

(3) Click on the top left graph type. The graph will be created and displayed automatically.
(※Drag (do not click) the cursor to the graph types and we can see samples of that graph)

Change the graph title

The graph created will have the title ‘Chart Title’. To change the title, follow the steps below.

 

Example of use) How to change the graph title

(1) Click on the word ‘Chart Title’ and type title of preference.

 

(2)The title has been successfully changed.

 

Change the axis title, horizontal and vertical

Now Let us add an axis title to make it clear what the numbers represent.

 

Example of use 1) Add an axis title

(1) Add a vertical axis title as follows:

① Click on graph, and select [Chart Tools] – ‘Design’.
② Select the following:

[Add Chart Element] on upper left → [Axis Titles] → [Primary Vertical]

A label called ‘Axis Title’ should appear on the left side of the graph.

 

Example of use 2) Change the axis title

(1) To change the title of the vertical axis, click on the axis just added.

 

(2) Type ‘Number of pencils’.

 

Example of use 3) Rotate axis title

We can rotate the axis title. We do not rotate axis titles usually, but here we will look at 2 types of rotation as examples.

(1) Begin formatting the axis title with the following steps:

① Click on the vertical axis.
② Select [Chart Tools] – ‘Format’.
③ Select ‘Format Selection’.

 

(2) “Format Axis Title” will appear on the right hand side of Excel, so rotate the axis title with the following steps:

① Click on ‘Text Options’.
② Click on the right (‘Text Box’) from the 3 diagrams.

 

(3) Click on the opposite triangle▼ from [Text direction] and choose ‘Stacked’ or ‘Horizontal’.

(4) Check to see if the direction of the axis title is changed.

Switch row/column

The graph created above shows ‘the number of pencils sold for each branch based on each manufacturer‘. Let us try to change this so the graph will show ‘the number of pencils sold based on each branch‘. The difference is how we look at the original table, based on ‘column’ or ‘row’. We call this ‘Switching rows/columns‘ in Excel.

 

Example of use) Switch row/column of graph

(1) Click on graph, and select [Chart Tools] – ‘Design’. Select ‘Switch Row/Column’.

 

(2) The ‘row and column’ of the graph has been changed and the graph is now based on each branch.

 

Change the range of graph data

If we realize after creating a graph that a certain piece of data is unnecessary or would like to add data, we can select the necessary range of data without having to undue everything and start from scratch.

 

Example of use) Change the range of graph data

It seems that data of Idaho branch is no longer necessary. Let us try to take that part off from the graph.

(1) Click on graph, and select [Chart Tools] – ‘Design’. Select ‘Select Data’.

 

(2) When the dialog box appears, follow the steps below:

① Click on the open space in [Chart data range], and highlight range that excludes Idaho branch, cell range [B3:F6] using a mouse.
② Click [OK] to complete.

 

(3) The graph no longer has the information from Idaho branch.

 

Add/remove grid lines to/from the graph

Let us now add ‘grid lines’ to our graph.

 

Example of use 1) Add a minor grid line

(1) We can add a minor grid line with the following steps:

① Click on graph, and select [Chart Tools] – ‘Design’.
② Select the following from the menu:

Select [Add Chart Element] → [Gridlines] → [Primary Minor Horizontal].

 

(2) The grid lines have been added, allowing us to evaluate the graph in more detail.

 

Example of use 2) Remove minor grid lines

We can easily remove the grid lines by following the steps below.

 

(1) Click on graph, and select [Chart Tools] – ‘Format’.
→ Select ‘Vertical (Value) Axis Minor Gridlines’ from the opposite triangle▼.

 

(2) With step (1) the minor grid line has been selected, so press [DELETE] on keyboard to delete.
(※This applies to other graph factors as well)

 

Changing the grid line width, maximum and minimum value

The graph grid line is now set up as ‘a line for every 50 (pencils) and the maximum as 500 (pencils)’. We can change this easily.

 

Example of use) Changing the grid line width, maximum and minimum value

(1) Click on graph, and select [Chart Tools] – ‘Format’.
→ Select ‘Vertical (Value) Axis’ from the opposite triangle ▼.

 

(2) Click ‘Format Selection’.

 

(3) From ‘Format Axis’, change the following settings:

Example)
Minimum → 0.0
Maximum → 600.0
Major   → 100.0
Minor      → 50.0

 

(4) As set up in (3), the grid line width is now 100, maximum is 600, and the minor grid line is 50.

 

How to create a graph with 2 axis

The graph result might be too complicated when data itself is complex and has a lot of information.
One of the examples for this is when the values are too separated.

We added the total in the table below. Let us create a graph based on this data.

 

Outcome of graph)

Because the values for the total are too big, the graph has been merged and is difficult to see.
Furthermore, displaying the total value next to each of the manufacturers seems odd as well.

 

Example of use 1) Add a secondary axis

To make this graph more pleasant to see, we will show the values of the ‘total’ on the right grid line, and will change the graph type to a ‘line graph’. We call this a ‘combination chart’ in Excel.

 

(1) Click on the bars of the ‘total’.

 

(2) Select [Chart Tools] – ‘Format’. Select ‘Format Selection’.

 

(3) From ‘Format Data Series’, select ‘Secondary Axis’.

 

(4) Check that there are 2 axis on each side of the graph. The total value now is referred to the right axis.

 

Example of use 2) Change the graph type of the secondary axis

The bars of the ‘total’ is overlapped, and we cannot see the other bars.
Therefore, now we will change only the data of the ‘total’ to a line graph.

 

(1) Click on the bar of the ‘total’.

 

(2) Select the diagram shown below (※line graph) from the ‘Insert’ tab.

 

(3) Choose the upper left graph.

 

(4) Only the values of the ‘total’ is now a line graph, and the combination chart now looks much better.