HIT191 > Spreadsheets: Charting
Charting

Having learnt how to organise and manipulate the data, in this topic you will learn how to represent the data in a graphical form. A picture is after all worth a thousand words.


Develop your skills - Charting

Rate your skills: Do you need to do this topic?
                            Find out by taking this quick skills test.

 

Skills

Stable 2007 textbook
Stable 2003 textbook

SET TEXT
Chapter 8
pp. 275-296
or
Chapter 8
pp. 277-298

Graphing
Excel graphing features are easy to use and offer many options. As you read the chapter try to get a feel for the process and kind of options available rather than trying to remember the details.

TASK 1: Complete the Develop Your Skills 15-17 on pp. 294-296.

Stable 2007 textbook
Stable 2003 textbook

SET TEXT
Chapter 9
pp. 297-310
or
Chapter 9
pp.299-310

Printing
This next chapter discusses the options available when printing spreadsheets. Without these options printed data can be very hard to read. As you learnt in word, presentation is very important for effective communication.

TASK 2: Complete the Develop Your Skills 18-20, pp. 308-310.

Tips & tricks icon Tips & Tricks

Hiding data

Hiding columns or rows is not only useful when you wish to print out part of a spreadsheet but also when you are trying to view it on the screen. Hide the unnecessary columns and then you can see the information you are interested in without having to scroll. Note that to unhide the information you will need to use Format / Column (or Row) / UnHide .

Charting non-adjacent cells (p.288)

Using this technique it is possible to create charts that take data from multiple worksheets. This allows you to organise your data in a way that suits the problem and not worry about having it in an adjacent block to chart.


Putting it to work: Graphs

As you have seen Excel has powerful charting functions and the challenge is how to use these functions to communicate your message.


Good and bad graphs
Good and bad
graphs

http://www.math.yorku.ca/SCS/Gallery/lie-factor.html
Mandated fuel
and
The shrinking
family doctor

Stock graphs
Stock graphs

Good and bad graphs

The page opposite gives some principles you should consider when creating graphs. As you work through the page, concentrate on the features that make some graphs effective while others are blatantly misleading.


Now pick one of the "misleading" graphs from the reading below and graph it fairly in Excel.


Or try this one but start the scale at 0. Does it improve the readability?

 

 

Discussion board
Discussion board
Post your improved graph and a short reflection on your improvements in the discussion forum.

 

Creating deceptive graphs
Tools like Excel allow you to produce a variety of different graph types with great ease and the minimum of effort. You need to be careful that you do not get carried away with the tool and forget the message you are trying to communicate.
  • 3-Dimensional graphs
    A classic example is 3-dimensional graphs. They are easy to create but inherently "unfair". As discussed in the fuel economy and doctor graphs examples above, the area used on a graph to represent a value should be directly proportional to the numerical quantity being represented. In a 3-D graph to get the 3-D effect on a 2-D surface (ie. screen or page) the area used to represent each value have to be distorted.

Spreadsheet
Spreadsheet
(13.5k)

Download the spreasheet opposite which contains some imaginary figures for the weekly cost of groceries in cities around Australia.


Using a 3-dimensional pie graph, produce the following 2 graphs:

    1. The first should put the Darwin figure in the worse possible light, i.e. make Darwin look as expensive as possible.
    2. The second should put the Darwin figures in the best possible light, i.e. make Darwin look as cheap as possible.

You might like to try

    • using different colours (dark colours make sectors look smaller)
    • rotate the pie sectors (right click on the plot area and then click Format Data Series...)
    • the exploded pie graph type.

For both graphs, include a short statement explaining how you achieved the effect.


  • Misleading Scales
    As discussed in the reading, scales that are inconsistent in their graduation or start at non zero numbers can be inherently unfair. Using the spreadsheet from the last exercise, produce the following 3 column graphs:
    1. a fair representation
    2. put Darwin in the best light possible
    3. put Darwin in the worst light possible

 

 


Discussion board
Discussion board
On the discussion forum
  • post your own graphs
  • look at some others and coment on an issue or effect that caught your eye.


What have I learned?

After completing these activities you should:
  1. be able to create charts to visually represent information
  2. understand how charts can be used to give different emphasis to the same sets of data.


Next section --> Macros and more