FUNCTIONAL ICT LEVEL 2

Advanced Excell

Data types

Find and replace

Home Tab

Sorting and filtering

Sorting Ascending and Descending

Filtering Select data and filter – creates a drop down menu

Sorting and filtering

Searching on one fieldAny field in a table of data can be searched.<=18 is an example of a search criterion. ‘<=’ is called anoperator. There are lots of operators you can use in a search on their own or in combination

Searching

search on more than one field

Searching

use wild cards in a search

Creating a drop down list

Creating a drop down list

Formulas

Formulas

Formulas – Look Up

Formulas – VLook Up

Conditional Formatting

Select cells – Home tab – Conditional formatting- select formatting

Charts

Bar chartsColumn and bar charts are useful if you want to compare

different values, such as sales each month or different answers to a survey question.

Pie chartsPie charts are good for showing proportions or percentages.

Line graphsLine graphs are used to show values that are always changing.

For example, you could use a line graph to show the temperature over a year.

Charts

Select data – if more than on column , ctrl and additional columns. If you select the column name, it will be populated on the chart as well.

Insert - chart typeOr

Insert chart – select data tab – select data on spreadsheet (with column name)

Relative referencing

In a spreadsheet you can refer to another cell in a formula by using a 'cell reference' such as A1. This refers to column A, row 1. The simplest formula would look like =A1

Absolute referencing

In a spreadsheet you can refer to another cell in a formula by using a 'cell reference' such as A1. This refers to column A, row 1. The simplest formula would look like =A1

But spreadsheets allow you to drag that formula down or across or even copy it to another cell. Normally the formula you end up with in each cell is different. For example the next cell down would have =B1

If you want to stick to the same cell, regardless of how you drag or copy it then you use an 'Absolute cell reference' like this =$A$1

The $ tells the spreadsheet not to alter the formula as you drag or copy it to another cell.

Excel

Task Services (15 mins)Open the spreadsheet called novices 1. Format the invoice sheet so that it is clear to read2. Use a formulae to calculate the total cost of the

services, using sheet 2 “services” to get the costs. 3. Use a formula calculate total services, VAT at 20%

and Total 4. Use the vlookup function to populate the

description and rate5. Use a formula calculate total services, VAT at 20%

and Total

Email

Remember that you do not need access !

Demo