HIT191 - Spreadsheets

Organising Data

Excel provides powerful tools for manipulating data, organising data and performing calculations. This topic will emphasize designing your worksheets well so that you can maximise the benefits of Excel's tools and communicate your message clearly. The subsequent topic then looks at presenting this information using charts.

Develop your skills - Organising Data

RATE
YOUR
SKILLS

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

Skills

SET TEXT
Chapter 6
pp. 233-256
or
Chapter 6
pp. 237-258

More about performing calculations
The next reading introduces some useful features in Excel:

using the AutoFill feature to create a sequence of numbers or labels
understanding the difference between absolute and relative addresses in formulas
using the VLOOKUP function to simplify calculations.

TASK 1: Complete the Develop Your Skills 11 and 12 ,
pp. 255-256.

SET TEXT
Chapter 7
pp. 257-274
or
Chapter 7
pp. 259-276

Organising data
In any reasonably sized worksheet, organising your data is an important consideration. The next reading introduces some useful features such as multiple worksheets, data forms and sorting. As you work through the chapter, consider how you could use these features in a spreadsheet you are working on.

TASK 2: Complete the Develop Your Skills 13 and 14,
pp. 273-274.

Tips & Tricks

Paste Special

Normally when you copy and paste in Excel, the values displayed in the cells are copied. If you wish to copy links or the formulas in the cells, you can by using the Paste Special command.

Quick formula copy (p.254)

This tip will show a very quick and easy way to copy formulas to a group of consecutive cells. This is a particularly useful tip and one you should make sure you understand as it will save you a lot of typing.

Converting addresses between absolute to relative

Use the F4 shortcut key to cycle addresses between absolute and relative.

Putting it to work: Constructing a Spreadsheet

Having learnt the basics of spreadsheets the next challenge is taking a “real world” situation and implementing it in a spreadsheet.

Below are two worked examples:

One creates a worksheet for student marks
The second does a “what-if” analysis for a prospective business

Step by step instructions are given, however if you are feeling confident you may wish to attempt the problems without working through the steps. If so, your solution may be different – the important issue is to recognize the principles of spreadsheet design and be able to justify your implementation.

Student Marks

Sample Data

Student Marks

Read the problem description and have a look at the sample data.

Start Excel and open the data file (don’t forget the data file has a .txt extension) – the Import wizard will start up and you should follow the steps.
Consider the layout of the spreadsheet; it never hurts to have an overview. In practice I suspect most people just dive in and then adjust later but a bit of up front designing and thought may well save you time later.

For this example, no upfront design is provided as it would give away some of the later steps.

Hint

Check your
answers

Do the row calculations i.e. the calculations you do for each row in the spreadsheet. For the moment leave out the assigning the grades.

Need help with the scores?

Check your answer

Check your calculations: This can't be overstated; giving the wrong answer is not only wrong but also often dangerous. There is a tendency to say, "a computer calculated this so it must be right". While it is true the computer won't make a mechanical error, if your formula is entered incorrectly then...

Can you change your spresdheet easily?
For example if the lecturer wants to change the weightings for the test to 5% (from 10%) and the exam to 55% (from 40%) what would need to change?

How easy is this to do?

What happens if you change it in some places and not others? You will end up with inconsistent information.

The problem is that information is being repeated: the weightings appear in the calculation for each student.

Never repeat information: If a number (or text) is being repeated on every row

type the number into a cell
use that cell in the calculations
if you are copying the cell, make sure you use absolute addressing (absolute addressing: see pp. 250-251 in text book).
If the number only appears once it is easy to change. Also having these numbers explicitly stated helps with the readability of the spreadsheet.

Hint

Compare Ideas

Compare Ideas

Fix your spreadsheet to be easy to update.

Need a hint?

Check your answer
Add in the calculation for grade. Do this using a VLOOKUP formula. Not sure about VLOOKUP - see pp. 253.
Add in the summary calculations for minimum, maximum and average. (reference: p. 204)
Sort the data. (reference pp. 260)

Check your answer
Format the spreadsheet for readability and usability. No example is given of this as there is many different ways to do it.

A Challenge: Now try extending your spreadsheet to do this:

The lecturer now asks that you find out which test each student did best on?

This is tricky. To get started, work out which of test 1 and test 2 is higher. Then extend this to test 3.

Business Analysis

Read the problem description. Remember that one of many possible solutions is given below.

Plan your spreadsheet either on paper or in Excel.
- Identify all the numbers given
- Identify the rates of increase
Compare your answers.
Enter these numbers into your spreadsheet.

Remember to ensure that you can change your spreadsheet easily.

Compare your layout.
Work out year 1 profit and loss.

Check your calculations.

Compare your answer.
Work out year 2 figures - this is like the row calculation step above.

Need a hint with the calculations?

Now copy across the formulas for Profit - don't reenter them, use the fill handle!

Compare your answer.
Now copy the table for years 3 to 5 - use the fill handle.

Do your answers look reasonable? Did you remember to use absolute addresses?

Compare your answers.
Finally, format your spreadsheet. In particular consider the number of decimal places you should have for numbers.
Consider what changes you could make to improve the usability and readability of your spreadsheet.

What have I learned?

After completing these activities you should:

be able to design worksheets that include reasonably complicated formulas and calculations
recognise the importance of organising your information and calculations for easy maintenance and communication.

Next section --> Charting