Selecting Data

Excel Lesson 3: Part 3 (Creating Advanced Excel Charts)

Please Note: This session builds on, and assumes that you have completed, the previous Excel sessions. If that is not the case, please go back and complete them before returning to this session.

STEP 1: Getting Started

Launch Excel and create a new blank Workbook or open up an existing Workbook. We will use the familiar Car example:

STEP 2: Selecting Data

We will start by selecting the data to display on our charts:

AssignmentTutorOnline

This shows quarterly sales across all 10 regions.

STEP 3: Creating Scatter/Line/Bubble Charts

Select the ‘Scatter Plot with Line’ option. This will produce the graph below.

N. America

China

Pacific Rim

Africa

Asia

Europe

Each of the 10 ‘intervals’ represents one region and each colour a quarter.

If you choose the basic ‘Scatter Plot’ there are no lines, but it uses the same approach. See overleaf.

The third type of chart in this family is the ‘Bubble’ chart. In this case, it is less useful. See below.

The full set of charts are shown together overleaf:

STEP 4: Creating Line & Area Charts

Using the same set of highlighted data, we select the 3-D version of a ‘Line Chart’:

This generates a great three-dimension, very clear chart – shown overleaf.

You can extend this concept by using a 3-D ‘Area Chart’:

For the next few examples – so as to get the correct regional names on the chart axes – I have removed the ‘Sales’ row (R3) and just retained the actual names of the regions. This will be clear when you study the charts below.

STEP 5: Creating Waterfall Charts

Select the ‘Waterfall’ Chart.

Which produces a graph showing the cumulative effect over time of positive and negative values.

It is thus of limited value here.

STEP 6: Creating Funnel Charts

Again, this chart is of limited value to us.

STEP 7: Creating Surface Charts

This is much better for our purposes.

STEP 8: Creating a Pivot Chart

Note: This is not to be confused with a ‘Pivot Table’.

The first step is to select your data set. I want to visualize sales over 10 regions, across all four quarters. Then, under the INSERT menu option, select ‘Pivot Chart’.

You will then see the following box – just click ‘OK’.

This will present:

You can now systematically tell Excel which pieces of data you wish to include on your new pivot chart by ‘checking’ the various boxes down the right-hand side. We start by including the four quarters held under the ‘Period’ column:

I can now start adding each region, one-by-one, starting with Europe:

The pivot table thus only shows European sales for each of the four quarters:

We can now include all 10 regions by ‘checking’ their boxes:

As well as customizing the regions to display, you can control which periods are shown on the chart.

Click the ‘Period’ button:

This will show you a drop-down list of all periods available – ‘check’ the ones you want to display:

I will present sales only for Q1 and Q4:

Click ‘OK’ to generate a new pivot chart that shows sales for all 10 regions but only in Q1 and Q4.

The final technique is to move the ‘Period’ dimension so as to lay the same data out in a different way. I have also only used the Europe and China regions because this display format generates a lot of columns across the top of the chart and they will not fit into the screenshot.

See the image below.

Note the different layouts.

That completes this session.

Now your turn…

Practice Tasks

1. 
   
2. 
   

Launch Excel

1. 
   
2. 
   

Either create a new blank Workbook or open up an existing one.

1. 
   
2. 
   

Select some suitable data and create:

1. 
   
2. 
   
3. 
   

Scatter Plots

1. 
   
2. 
   
3. 
   

Scatter & Line Charts

1. 
   
2. 
   
3. 
   

Bubble Charts

1. 
   
2. 
   
3. 
   

Line & Area Charts

1. 
   
2. 
   
3. 
   

Waterfall Charts

1. 
   
2. 
   
3. 
   

Funnel Charts

1. 
   
2. 
   
3. 
   

Surface Charts

1. 
   
2. 
   
3. 
   

Map Charts

1. 
   
2. 
   
3. 
   

Pivot Charts

1. 
   
2. 
   

Re-save if you wish.