Excel Week 4

Charts in Excel

There are simple but important rules to follow when designing a chart. The chart should be simple enough to read quickly.

ChartWizard - easiest way to create a chart. The Wizard guides you step by step through the choices needed to complete your chart.

Chart toolbar - toolbar that contains tools for making changes to and enhancing a chart.

A Drawing toolbar – appears to allow you to insert special objects in your chart.

Chart Type – bar chart, column chart, pie chart, etc

Line chart - chart that plots one or more data series in lines that connect the data points.

Pie and Exploded pie are effective for showing proportions. That means not a lot of information.

Exploded pie chart - pie chart that separates one or more slices of the pie for emphasis

3D pie chart - pie chart that has a dimension for the height of the chart. Can be exploded or non-exploded format.

Column chart - a chart that uses vertical bars to represent data. Most effective way to display actual numbers

A bar chart is a horizontal version of the column chart.

3D column chart - a column chart that has a dimension of depth to the columns.

Stacked columns - chart that displays multiple data series in stacked columns as opposed to side by side.

Refer to the following Excel workbook from now on:

Category Labels (X axis) __ Descriptive category (e.g. / B3:E3)

Data Values (Y axis) __ Quantitative category (e.g. / B7:E7)

Data Range -- Category Labels, Data Values (e.g. / B3:E3, B7:E7)

A chart can be stored as part of a worksheet or in a separate chart sheet still in the same workbook.

To draw a chart:

Select the Data Range

Click the Chart Wizard on the Standard Toolbar

Select the chart type in step 1. Click Next

Confirm (change) the data range in step 2. Click Next

Enter the chart tile and any additional Chart Options in step 3. Click Next.

Choose the chart location as either in the same worksheet or in a separate chart sheet in step 4. Click Finish.

After you have created the chart you can enhance it in several ways. You can change the chart type, add (remove) a legend, add (remove) gridlines, change the formatting of any text in the chart, you can add arrows, text boxes etc by using the Drawing toolbar, etc.

ANATOMY OF A CHART

	Category axis
	Chart Area
	Chart Title
	Value Axis
	Series 1
	Plot Area
	Major gridlines (Scale)
	Data Series (Series 1)
	Data Labels (Show value)
	Legend
	Chart Options (Step 3 of chart wizard)

Whenever the data values in a worksheet changes, it is reflect on the associated charts immediately. For example, update B4 as Chicago and B4 as 400.000.

MULTIPLE SERIES

One of the most important concepts in chart creation is understanding your data.

You need to clearly plan the purpose of your chart.

Also understand that rows versus columns will express information differently.

Series in

¢ Rows

¢ Columns

Clustered Chart – Compares values across categories

Stacked Chart – Compares the contribution of each value to a total across categories

Sample:

Select A3:E6

Click Chart Wizard.

Determine the Chart Type as clustered (side-by-side) column chart. Click next

Select data Series in

Rows: 4 categories and 3 series

Category Labels: B3:E3

Legend Text: A4:A6

Data Series:

B4:E4

B5:E5

B6:E6

This corresponds to Revenue by geographic area

Columns: 3 categories and 4 series

Category Labels: A4:A6

Legend Text: B3:E3

Data Series:

B4:B6

C4:C6

D4:D6

E4:E6

This corresponds to Revenue by product

Default selection rule: Fewer data series than categories.

OLE

Compound document - a document that contains data (objects) from multiple applications.

Embedding object - object that is stored in the compound document.

Embedding - the ability to store objects within the compound document

Linked object - object that is stored in its own file. The compound document maintains a link, which points to the object.

Linking - the ability to store links to objects in the compound document

Object - data created by an application. The object could be a graphic, a chart, a worksheet or a memo for example.

Object Linking and Embedding (OLE) - the means by which you create a compound document. One of the major benefits of the Windows environment

Paste command - command will paste a copy of the object in the compound document

Paste Link command - command will paste a link to the object in the compound document

Paste Special command - command will link or embed an object depending on option checked