# A3

Document Sample

```					                         Part I
Hands-on Exercises

Part II

Chapter 5: Charts

Manuel Gonçalves
mgoncalves@fe.unl.pt
FEUNL
Overview
   5.1 Introduction
   5.2 Creating Charts with Chart Wizard
   5.3 Working with Chart Options
   5.4 Creating Dynamic Charts
   5.5 Summary

2
Introduction
   Creating a chart using the Chart Wizard

   Customizing charts using various chart options

   Creating dynamic charts

3
Creating Charts with the Chart Wizard
   Charts are used to take data from a table and
transform it into a graphical illustration

   They are useful for displaying data patterns or
results

   To create a chart, we will use the Chart Wizard
– Insert > Chart from menu
–      icon from standard toolbar

4
The Chart Wizard
   There are four main steps in using the Chart Wizard:
–   Step 1:   Chart Type
–   Step 2:   Source Data
–   Step 3:   Chart Options
–   Step 4:   Chart Location

   Chart Type = bar graph, scattered graph, pie chart, etc
   Source Data = data range or various series
   Chart Options = title, axes, legend, gridlines, etc
   Chart Location = object or chart sheet

5
Figure 5.1
   Table of data = Number Sold and Revenue Generated from
various Car Models

   We create two charts: bar graph of Number Sold and pie chart of
Revenue Generated

6
Step 1: Chart Type

   Each chart type has
a set of chart sub-
types.

   First we will select
Column as our chart
type.

   We can now choose
a sub type that is
either a 2D or 3D
– Clustered Column
– Stacked Column
– 100% Stacked
Column

7
Step 2: Source Data
   Source Data can be defined
by a Data Range or with
Series

   Data Range is the main data
table we initially highlighted
to be graphed.
– Rows = comparison is made
among rows of data from the
table
– Columns = comparison is
data from the table

8
Step 2: Source Data (cont’d)
   Series are the rows or
columns of data from the table
which are being compared.

   Series can be
– Removed
– Named

9
Step 3: Chart Options
   Tabs of the Chart Options step include:
–   Titles
–   Axes
–   Gridlines
–   Legend
–   Data Labels
–   Data Table

10
Step 3: Chart Options (cont’d)
   Titles options include chart, category (X) axis, and value (Y) axis titles

11
Step 3: Chart Options (cont’d)
   Axes options include Category (X) and Value (Y) axes
– Automatic = default
– Category = any grouping of axes values
– Time-scale = dates or time units

12
Step 3: Chart Options (cont’d)
   Gridlines options include Category (X) and Value (Y) axes
– Major gridlines
– Minor gridlines

13
Step 3: Chart Options (cont’d)
   Legend options include showing or hiding the legend, and placement of
the legend

14
Step 3: Chart Options (cont’d)
   Data Labels options include showing or hiding data values and labels

15
Step 3: Chart Options (cont’d)
   Data Table options include showing or hiding the data table

16
Step 4: Chart Location
   The chart can be completed as
– A new chart sheet; name is given
– An object in a current sheet; name of sheet is selected

17
Figure 5.6
   From the bar graph we see that the Aco3500 sold the most cars

18
Figure 5.7
   We repeat the Chart Wizard steps to create the pie chart of Revenue
Generated
   We see that the Cam3200 generated the most revenue

19
Working with Chart Options
   After creating a chart using the Chart Wizard, we can return to any of the
Wizard steps by simply right-clicking on the chart.

   The Format Chart Area option also allows us to modify the general
patterns, font, and properties of the chart.
– Format Plot Area
– Format Data Series
– Format Axis

20
Figure 5.9
   Data table shows plant growth based on minerals added or removed
from the ground
Growth    Minerals
-1000      -10
-512       -8
-343       -7
-216       -6
-125       -5
-64       -4
-27       -3
-8       -2
-1       -1
0        0
1        1
8        2
27         3
64         4
125         5
216         6
343         7
512         8
1000       10

21
Figures 5.14 and 5.16
   Create the chart

   Chart Options can be used to switch x and y values for better data
analysis                                          Minerals

1500

1000

500

0
-15    -10   -5           0    5   10   15

-500

-1000

-1500

22
Creating Dynamic Charts
   A chart is linked directly to the Data Range specified in the Source Data
step of the Chart Wizard.

   If any points in this range of data are modified, the chart is automatically
updated to reflect a new corresponding data point.

   There will be three main Excel concepts used to create a dynamic chart:
– Defining names
– OFFSET function
– COUNT function

23
Dynamic Charts (cont)
   Create some range names using the OFFSET and COUNT functions and set the
Series of the chart to these dynamic ranges.
– =OFFSET(initial_data_location, 0, 0, COUNT(entire_column), 1)

   The rows_to_move and columns_to_move parameters are set to 0 because
we are only interested in the column in which our reference_cell ( =
initial_data_location) is located.

   The width is again set to 1, since we are interested only in one column.
   The height parameter is found using the COUNT function.
– The COUNT function will review the entire column of the relative data and count how
many cells have numeric values.
– Thus the height of our range becomes dynamic as the amount of numeric values in
the column increases.

24
Figure 5.17
   Months and Units Sold may be dynamic values

25
Figure 5.18
   The dynamic ranges are created for each column using the OFFSET
and COUNT functions

26
Figure 5.19
   The Series are set using these dynamic ranges

27
Figure 5.20
   The chart is now dynamic

28
Summary
   Excel Charts allow you to illustrate your data in order to perform better
analysis.

   There are four basic steps in the Chart Wizard:
–   Step 1:   Select the Chart Type and options.
–   Step 2:   Verify or change the Data Range and define all Series.
–   Step 3:   Determine the Chart Options.
–   Step 4:   Choose the location of the chart.

   A chart can be modified after it is created by right-clicking on the chart or
different parts of the chart. You can change basic settings as well as
formatting.

   A dynamic chart can be created using the OFFSET and COUNT
functions to create dynamic ranges used as Series in the Source Data.

29

30

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 5 posted: 11/12/2011 language: English pages: 30