using charts in excel - excel 2007

8/6/2019 Using Charts in Excel - Excel 2007

1/21

University of Bolton

Using Charts In

Microsoft EXCELSchool of the Built Environment and Engineering


2/21

There are several ways of presenting statistical data in chart form, though the intendedinterpretation of the information will generally determine the type of chart to be used.

This workbook attempts to demonstrate some of the chart forms available using thefollowing sections.

1.0 Getting Started

2.0 Making a Chart and Adding Labels

3.0 Formatting Axes

4.0 Changing the appearance of the chart

5.0 Taking the Chart into a WORD Document

6.0 Changing the Type of Chart


3/21

1.0 Getting Started

You need to be in Excel Type in the following data.

Place the cursor into cell A10 (or any of the cells containing a value)

2.0 Making a Chart

Goto Insert Chart and select Column

1. SelectColumn

1. This titlegoes into cellA1

3. Beforeentering 5-14you will needto format the

cell for textin ut.

2. RightJustify thevalues in

each cell

4. Centre thetext in thiscolumn


4/21

Excel will automatically insert the chart above.

You will notice that a new tab has appeared on the ribbon at the top of the screen calledChart Tools, we will use this now to make our chart how we want it.

3.0 Editing a Chart Labels

0%

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

0-4 5-14 15-24 25-44 45-64 65-74 75-84 85+

Female

Male


5/21

You can add Chart Titles byselecting one of thepredetermined options, click

the arrow to view moreoptions. Try this first.

We will now look at adding our titlesusing the Labels section in the

Layout ribbon.


6/21

Type in the Axis TitleAge Group

Repeat the above

process, using the AxesTitles option

Select Title Below Axis forthe horizontal axis


7/21

The spreadsheet should now resemble the following:

Edit the chart Titles further to produce the following:

80%

90%

100%

Male - Female Population Data


8/21

3.0 Formatting Axes

Select the Font option first and then try the Format Axis

1. Place cursor onX-axis and RIGHTCLICK the mouse

2. Select Format Axis1. Select the Font Folder

4. The Font Size

should be 10

1. Select theFont Folder


9/21Your Graph should now look similar to the one below.

1. Have a look atall the options.Axis OptionsNumberFillLine ColourLine StyleShadow

3-D FormatAlignment

Experiment witha few differentstyles andcolours of lines


10/21

4.0 Changing the appearance of the chart

1. Place Cursorover chart areathe press the

RIGHT-HANDmouse button.

2. Select Format

Plot Area.

1. Have a look atall the options.FillBorder ColourBorder StylesShadow3-D Format

Experiment with


11/21

Using the feature described above

Edit the chart so that it resembles the following:

5.0 Taking the Chart into a WORD Document

Open a new Microsoft WORD document.

Return to EXCEL, whilst in the EXCEL document

1. Place Cursor ontoCh A d RIGHT


12/21

The Chart will now be copied to the clipboard.

Return to the WORD document

Position the cursor at the point in the document where the chart is to appear

Goto Paste

The chart should appear in the WORD document at the required location.

6.0 Changing the Type of Chart

0%

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

0-4 5-14 15-2425-4445-6465-7475-84 85+

Precentaga

Males to

Females

Age Group


Female

Male


13/21

The resulting chart will appear as follows

50000

60000


3. Select the line type chart

4. Select OK

Note that theY-axis nowshowsactual


14/21

Produce the following Chart presentations of the same data

0

10000

20000

30000

40000

50000

60000

PrecentagaMalestoFemales

Age Group


Male

Female

40000

60000

80000

100000

Female

lestoFema

les



15/21

EXERCISE

Now attempt the following pie chart:

25-44, 33%

85+, 1%

Craggy Island - Male Population through the age ranges

0-4

5-14

15-24

25-44

45-64

65-74

75-84

85+


16/21

7.0 Presenting Population Data

We will now demonstrate how the following chart is produced; though you may

attempt the chart without instruction if you feeling confident.

Whilst still in EXCEL Take the cursor into cell E4 enter the formula =-1*B4 PressEnter.

(i.e. We want the value9481 to appear in cell E4)

Copy the formula held in E4 into the cell range E5:E11.

9481

21105

16522

50935

39011

10924

5531

1047

8927

19901

15851

47705

38580

12858

9453

60000 40000 20000 0 20000 40000 60000

0-4

5-14

15-24

25-44

45-64

65-74

75-84

85+

Population

AgeBands

Population Data for the Ward of Craggy Island

Female

Male


17/21

Go to InsertChart to receive the chart wizard

1. Select Bar2. Selectthis sub-type

3. Next

1. Selectthe Seriesfolder

2. To furtherdemonstrate howeach series is addedand specified, it is

worth removing anyexisting ones fromthe list.To do this, firstselect each one then


18/21

Addand name a new series:

Specify the Category (X) axis labels

3. =Sheet1!$A$4:$A$11should appear here

4. Left-clickhere to acceptthe entry andreturn to theSource Data

2. With the mouse, select the cellrange A4:A11

T

hisisthe

Xa

xisforthis

type

ofchart

1. Add

2. Left-click hereto go to the

spreadsheet toselect the cellcontaining theword Male

3. Select the cell E3

4. =Sheet1!$E$3should appear here.

5. Left-click

here to acceptthe entry andreturn to theSource Data


19/21

The Source Data dialog box should resemblethe one below:

Using the same approach add a second seriesfor the female population and obtain thesettings below:

When complete select the Next button

Cell C3

Cell rangeC4:C11

Shouldremain Cell

range A4:A11

1. Enter PopulationData for the Ward ofCraggy Island

2. Enter Age Bands

3. Enter Population 4. Next


20/21

The chart should now look something like the one below:

To show Data Labels

Right-click on the blue horizontal bars whichrepresent the the Male population and selectFormat Data Series

Repeat the exercise for the Maroon horizontal barswhich represent the Female population.

Making Negative values positive

Negative values can be made positive within a chart by formatting the numbers so that they appearRED when the number becomes negative. This is done as follows

1. Select the DataLabels folder

2. Select

Value

3. OK


21/21

Page 21 of 21

THE FINISHING TOUCHES

Population Data for the Ward of Craggy Island

21105

50935

39011

8927

19901

15851

47705

38580

12858

9453

3521

16522

9481

10924

5531

1047

60000 40000 20000 0 20000 40000 60000

0-4

5-14

15-24

25-44

45-64

65-74

75-84

85+

AgeBands

Population

Female

Male

Remove theborder

Make the titlesmaller

Underline

the X axistilte

Make the Bar colourred so that it is

consistent with thered data labels andaxis labels (belowzero)

Make the barcolour blue

Make the Female data labels blueso that they are consistent withthe bar colour

Make the axis labels blue sothat when positive, they areconsistent with the bar and

label colours

It may be necessary tomove some of the labelsout so that they dont

obscure the X axislabels

Make all axis labels and data labels a size that improves thegeneral appearance

using charts in excel - excel 2007

Documents