44221 information systems n.
Skip this Video
Loading SlideShow in 5 Seconds..
44221: Information Systems PowerPoint Presentation
Download Presentation
44221: Information Systems

Loading in 2 Seconds...

play fullscreen
1 / 26

44221: Information Systems - PowerPoint PPT Presentation

  • Uploaded on

44221: Information Systems. Lecture 7 (Week 9) Organising Data & Presenting Information By Ian Perry http://itsy.co.uk/ac/0607/Sem1/44221_IS/. Why use Multiple Worksheets?. ‘Old’ Spreadsheet Software enabled the user to build two-dimensional models of numerical data on a single worksheet.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

44221: Information Systems

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
44221 information systems

44221: Information Systems

Lecture 7 (Week 9)Organising Data &

Presenting Information

By Ian Perry


why use multiple worksheets
Why use Multiple Worksheets?
  • ‘Old’ Spreadsheet Software
    • enabled the user to build two-dimensional models of numerical data on a single worksheet.
    • these two-dimensional models could be quite large (typically 256 columns by 8192 rows), and finding you way around could be difficult.
  • Modern Spreadsheet software has introduced the idea of the ‘Workbook’, containing a number of ‘Worksheets’, which:
    • enables three-dimensional numerical data models to be constructed.
    • chich can ease the organisation of, and navigation around, large numerical data models.
an excel workbook

3 Worksheets by default,

but can easily add more.

An Excel ‘Workbook’

Ü 256 Columns; ‘A’ to ‘IV’ Þ







the qtr1 worksheet

Now have 5 Worksheets

Renamed as: Qtr1, Qtr2,

Qtr3, Qtr4, & YearSum.

The ‘Qtr1’ Worksheet


The other 3 Quarters have exactly same layout, but different Sales & Profit data.

reference the profit total for qtr1

Formula: =Qtr1!C8

Reference the Profit Total for Qtr1


Easiest way to do this is to copy the previous

formula from B4 to C4.

complete the yearly summary


Repeat for other 3 Quarters.

Copy for ‘Profit’

Formula: =sum(B4:B7)

Complete the Yearly Summary
re order the worksheets

‘Click-and-Hold’ on

the Worksheet you wish to move, ‘Drag’ to new position, then let go.

Re-order the Worksheets
  • Why use Charts?
    • can reduce the complexity of a situation.
    • can make it easier to depict trends.
    • can make it easy to highlight significant data.
  • Can be misleading!
    • Something ‘pretty’ on screen can be pretty meaningless!
    • Need to use the ‘correct’ Chart type; i.e. for a specific purpose.
chart type purpose
Chart Type & Purpose?
  • Bar/Column Charts
    • show values of a single continuous variable for multiple separate entities, or for one variable sampled at discreet intervals.
      • Or for multiple continuous variables, so that bar heights can be compared.
  • Composite Bar Charts
    • each bar incorporates several data series so that it is possible tosee how the components contribute to the whole.
chart type purpose1
Chart Type & Purpose?
  • Pie Charts
    • show the relative distribution of a single series of data among the parts that make up the whole.
      • The number of segments should not exceed 10 - in which case a Bar Chart would be more suitable.
  • Line Charts
    • show how two (or more?) continuous variables are related to each other.
      • Especially where these variables change over time.
chart tips
Chart Tips!
  • Don’t have too much data, or data of varying scales, on the same Chart.
  • Use 3D Charts with care as one data series can easily obscure another.
  • Make full use of labels and additional text to ‘explain’ your Charts.
  • Be careful when selecting scales - as you can easily exaggerate minor variations, or hide major ones.
what chart type should we use
What Chart type should we use?
  • Look carefully at the data and decide how they might best be represented.
    • Sales for each of four Quarters:
      • a single continuous variable for multiple separate entities.
      • i.e. a Bar/Column Chart.
    • Sales is made up of Costs and Profit:
      • each bar incorporating several data series so that it is possible to see how the components contribute to the whole.
      • i.e. a Composite Bar Chart.
chart 2 profit costs

Press the ‘Chart Wizard’ button.

Highlight the data.

Chart 2 – Profit & Costs


Select the first column of data, then hold down the ‘Ctrl’ key whist selecting the other two.

this week s workshop
Complete this spreadsheet model;

by adding a new Worksheet (with Formulae referencing other Worksheets), then add some Formulae to summarise the data, and finally create two Charts of the Summary data.

This Week’s Workshop