6-Spreadsheets

Learning Outcomes

Students should be able to:

• create or modify a spreadsheet from given information (maximum two worksheets);

• use rows and columns appropriately;

• format cells appropriately;

• sort data into numerical, alphabetical and chronological order; and

• filter data.

• use the following built-in functions:

− sum;

− average;

− max;

− min;

− if;

and − count;

• create, replicate and use formulae appropriately;

• demonstrate understanding of the need for data validation;

• apply data validation to cells to carry out range checks and length checks;

• use relative and absolute cell referencing;

• use data in a spreadsheet to create charts;

• use the following chart formats: − bar chart; − pie chart; and − line graph;

• insert an appropriate title;

• insert labels on axes;

• show data values;

• insert appropriate data legends; and

• print formulae, data and charts.

Questions:

What are the key features of a Spreadsheet?

Why are Spreadsheets useful?

How might a business use Spreadsheets?

What is a spreadsheet?

A Spreadsheet is a software application often used to process and display financial or statistical information. It is used by people in many different occupations such as engineers, scientists, teachers, designers and people who run their own business.

It allows us to store numerical data in an organised way and perform analysis and further calculations on that data. It can also be used to create graphs and charts of the data.

Microsoft Excel is a common piece of software we use when creating spreadsheets.

Businesses can use spreadsheets to perform analysis of numerical data (numbers). They are commonly used to calculates profit/loss and analyse sales data.

Formula

A formula is a way a calculation is represented in a cell, using mathematical operators and references to other cells. The formula is entered into a cell, but the display shows the current result of the calculation, which may change as the values in other cells change. All formulae start with an equals sign.

Function

A function is used to do a very specific job within a spreadsheet. Instead of manually creating each of these using a formula we can save time by using appropriate function. We simply enter the formula into the cell we want the answer to be displayed in.

Questions:

What are the benefits of using Formulas?

What could a business use formulas for?

What benefits do Spreadsheets offer as opposed to manual calculations?

Why are graphs used to present numerical data?

Formatting a Spreadsheet

Some key formatting features available in spreadsheet software are set out below.

Charts

Spreadsheets display data in tabular form. Charts and graphs can be used to display data in a more visual way making it easy to read and to spot trends. There are different types of charts you can make.

For example:

• Bar Chart

• Line Chart

• Pie Chart

To create a graph, you first need to select the cells you wish to display. Highlight the area. Tip: Use the control button [Ctrl] to highlight two separate ranges of cells if they are not beside each other. Select the ‘Insert’ tab and choose which chart type you would like to use. You can then use the ‘Chart Tools’ tabs to make changes to the colours, titles, axis, or legend etc.

Charts and graphs can be copied into other software applications such as word processing or presentation software. They can even be linked so charts will automatically update if any changes are made to the original.

Kagan

In Pairs discuss how a business with a spreadsheet on sales could use the following functions.

Sum

Average

Min

Max

Count

Replicating formula

Replication is the process of copying a formula from one cell to another. This saves us time as we do not have to manually do each formula. When we have a table of data we often want to apply the same formula to a range of cells so replicating is very useful

Relative Cell Reference

A relative cell reference is cell reference in a formula that changes depending on its location on a worksheet. For example, if a formula is copied from one cell to another the cell reference in the formula will change automatically. This can save us time when making spreadsheets as we do not have to individually complete every formula.

Absolute Cell Referencing

This is when you want a cell reference in a formula to always stay the same. A dollar sign ($) is used to tell the spreadsheet software to always to use that particular cell. In the example opposite, cell B2 is the cell to be used in all formula. Therefore a dollar sign has entered in front of the letter B and the row number 2 to ensure that cell is always used in the formula even when replicated.

Questions

Make a list of 5 questions based on either the theory or skills covered so far in spreadsheets. You will pair up twice sharing quiz with a partner.

Data Validation

Data validation is used to ensure that data entered by the user is reasonable and sensible. Data validation can be used in a number of ways such as ensuring that data entered is within a particular range, that data is in a particular format or that only certain items in a list can be selected. Data Validation can be found under the ‘Data’ tab in the spreadsheet.

Examples of Data Validation:

• Age must be a whole number which is less than 18

• Exam Result must be between 0 and 100

• Gender must be from a list which consists of Male and Female

The two types of validation you can be asked to use in your unit 1 exam are range check and length check.

A Range Check is used to check that a numerical value entered is within a certain range. For example if the most hours an employee can work in a week is 40. We can use a range check on that group of cells to ensure the value entered is between 0 and 40. This helps reduce the likelihood of data entry errors.

A Length Check is a check used to ensure the text/value entered is of a set length. For example all customer reference numbers might be 6 characters long e.g. P43535. If a customer reference number is entered which is not 6 characters long it will be rejected.

To add validation, highlight the group of cells you want to add validation to and then click on the data option from the top menu. In the data tools section select the option for data validation.

Use the settings tab shown below to customise your validation method. You can also customise the input message and error alert message.

IF Statements (Conditional Checks)

An IF can be used to check the value in a certain cell and output one of two messages depending on whether a condition is met. For example we can use it to check a student's test score and output pass/fail if it is over 60%. Note the use of an absolute cell reference on cell B6 as we want to be able to replicate the formula and always reference B6. To do this we put a $ before the B and the 6 as shown below. When checking a value we can use > for greater than and < for less than. We can also use equal to check if the values are the same. In the example we are checking if the value in B3 is greater than the value in B6, if it is "Yes" is displayed. If it is not "No" is displayed. Notice how in this example we are using the pass mark of 60 in cell B6 and so have to use an absolute cell reference.

Sorting / Filtering

Sorting allows us to rearrange the data in a Spreadsheet based on a certain value. For example if we had a spreadsheet with studnet names, test scores and date of birth we could sort them on their names, score, or age.

When sorting we can sort in ascending order (starting at A or 1 working upwards) or descending (startng at the highest value and working downwards. This can be useful and can make it easier to interpret the data.

Filtering allows us to only show certain data, lets say we added the students class or gender to our spreadsheet we can filter the data to focus on a particular class or gender. This can be very useful when dealing with large volumes of data.

Macro

A macro is a small program created to perform a repetitive task. It allows the user to perform a task by using the macro rather than having to enter the individual instructions. If the task is likely to be used many times it is much easier and more user friendly to use a macro. It is a set of commands/instructions recorded together that can be activated by a mouse click or pressing a key. In simple terms it is a short cut to a task. A macro is created by recording the tasks to be carried out. It is then named and saved. You can also write a macro using a script editor. We can create a macro on a spreadsheet to print a table or chart out.

Spreadsheet Practical Task 1

Download the Spreadsheet from Teams and save in a suitable area.

Complete the following activities on the payments worksheet...

Add the title "Sales Information" using merge cell at the top of the table from cells A2 to J2.
In cell G2 Add the label Total.
In cell H2 add the label Average.
In cell I2 add the label Highest.
In cell J2 Add the label Lowest.
Use formula/functions to calculate the total, average, highest and lowest for each customer.
Format the Date of Birth Column to display all dates consistently.
In Cell C13 add the title Total.
Use a formula/function to calculate the total for each month.
Use Formatting to improve the apearence of the spreadsheet.
Create a range of graphs to show the information in a useful way to the managers of the business.
Add the value of euro as 1.12 in an appropriate cell. Using an absolute cell reference convert your total in £'s to total in Euro.
Use validation to improve the accuracy of data. Put a length check on the Parent ID column and a range check on the Sales for April, May and June to ensure the value entered is between £300 and £1000.
Create a validation check to ensure a name is entered for each customer.
Insert a conditional check which will display either "10 % Discount" or "No Discount" if the average is greater than £600 .
Insert a conditional check which will display "Bonus Draw" for all customers with a highest value equal to £800.
Add a label "Number of customers" into an appropriate cell, use the count function to display the number of customers in the cell beside it.
Add a column to contain the gender of each customer, place it between Name and Date of Birth. Enter the gender for the customers using M for male and F for Female.

19. Filter the data to show only the male customer sale information. Print out the results.

20. Filter the data to show only the female customer sale information. Print out the results.

19. Use sort to sort the spreadsheet in ascending order based on the average column.

20. Use sort to sort the spreadsheet in descending order based on their name.

21. Print out your spreadsheet in data view.

22. Print out your spreadsheet in formula view.

Practical Skills Recap Video

spreadsheet practical 1.wmv

Keywords

Page updated

Report abuse