EXCEL NOTES – Miss Paulin

1) IDENTIFYING TYPES OF ERRORS:

ERROR INDICATOR DESCRIPTION EXAMPLE
Displayed when a column is
not wide enough to display all
####### the numbers in a cell, or if the
cell displays a negative time
and date
Shown when Excel does not
recognise the text in a formula
#NAME?
(if something is spelled
incorrectly, for example).
Shown when a number is
#DIV/0? divided by zero or in a cell
with no value.
Displayed when a cell
reference is not valid. This can
#REF! happen when you delete cells
that other formulas referred
to.
Displayed when the formula
contains cells with different
#VALUE!
data types (letters and
numbers, for example).
Shown when a formula or
function has invalid numerical
values, for example $1,000 will
give this error, because the $
#NUM! is used as an absolute
reference and the comma (,) is
a separator in formulas. In
Excel 2014, this was replaced
with the semicolon (;).

TO MULTIPLY, DIVIDE, ADD OR SUBTRACT IN EXCEL:

 To Multiply, we use an asterisks (*) E.g. = 4*2
 To Divide, we use a slash (/) E.g. = 75/25
 To Add, we use a plus sign (+) E.g. = 71+29
 To Subtract, we use a minus sign (-) E.g. = 15-5
 CONDITIONAL FORMATTING
RULE DESCRIPTION
Allows you to highlight data based on certain
conditions (for example, highlight all cells with
a value greater than 4 000 or less than 100).
Highlight Cells Rules
You can also use this to find values in a range,
highlight cells containing specific text or
highlight cells containing duplicate values.
These rules allow you to find the 10 top or
bottom values, the top and bottom 10% of
Top/Bottom Rules values or values that are over or below
average. This tool is very useful for teachers
who want to track class performance.
Adds a coloured bar to highlight the data in
the cell. The longer the bar, the higher the
Data Bars
value. Data bars are almost like bar graphs for
each individual value.
Colour scales use a gradient to apply
background colour to a cell based on where
Colour Scales
the value of that cell falls in a range. The
darker the colour, the higher the value.
Used to add different symbols, ratings and
indicators based on the values in the cell. For
example, if you wanted to track monthly sales
Icon Sets
of products, you could use arrow indicators to
show where sales went up, down or stayed the
same.

Guided Activity:
In Chapter 8, Activity GAct8.1, I want you to highlight all marks above 90. First, I want you to select
the cells you want to highlight. Next, select the home tab, then select Conditional Formatting and
then select Highlight Cells Rules. Select Greater than. Enter 90 in the dialogue box that appears
and Select Ok.

Guided Activity:
In Chapter 8, Activity GAct8.2, I want you to use your prior knowledge and highlight all text that
contains the sport Soccer using the “Text that Contains” option.
You can also create a new rule by selecting the rule type, colour, format style, values or text.

Independent Activity:
In Chapter 8, Activity 8.1 (Report), follow the below instructions:
1. Apply Conditional Formatting to column G so that all cells showing learners that have
reported bullying:
a. Appear in a light grey fill.
b. Have a green border.
2. Apply Conditional Formatting to column D so that all cells showing learners younger than
17 appear in:
 a. A red fill and, learners older than 17 appear in
b. A green fill with a blue border.
3. Edit the Conditional Formatting Rules for Column E so that all cells showing learners from
the B group appear in:
a. Purple

AUTO-FILL FACILITY
OPTION DESCRIPTION
Copy Cells Copies the value in a cell into the selection.
Fills in the next numbers, days or dates in a
Fill Series
series.
Only Takes the formatting in a cell and fills it in
Fill Formatting the other cells in the selection, for example,
the text colour or a highlight.
Fills in only the values and not the formatting
Fill Without Formatting
from the first few cells
Looks at the data in the cells next to the one
Flash Fill you selected, detects a pattern and then fills
the cells with that pattern.
Fills in the names or dates of days in the range
Fill Days
you selected.
Fills in only the names or dates of weekdays
Fill Weekdays (not Saturday or Sunday) in the range you
selected.
Fills in the month in a date. For example, if you
Fill Months only want a list of the last days of the month,
you will use this option.
Changes the years in a date to be consecutive
years. For example, you can start the list with
Fill Years the date 01/11/2018, use Fill Years and have
the list be 01/11/2019, 01/11/2020,
01/11/2021, etc.

Independent Activity:
In Chapter 8, Activity 8.2 (Autofill), follow the below instructions:
1. Use Autofill to complete the following columns for each person:
a. Column A: so that we can see how many entries there are to the worksheet.
b. Column E: a unique code is created for every person.
c. Column N: months of the year
d. Column O: days of the week

CELL REFERENCING
In Excel all cell references in are relative references. This means that the reference is based on the
location of the cell. For example, in Figure 8.5, the formula in Cell D2 (=B2*C2) will change to
=B3*C3 if we copy it into Cell D3.
Absolute cell referencing, on the other hand, lets you move a formula around on a worksheet
without changing the cell references. You create an absolute reference by using the dollar sign ($)
in front of the column name, row number or both. To add in the VAT (which is the same 15% for
every item) in the above example, you would use an absolute cell reference which is contained in
cell F1. In this case, you type in 15% in Cell F1 and then use the formula =D2*$F$1 in cell E2 to
work out the total VAT for all the products in row 2.

When coping a formula, insert a $ before the part that must not change. This works both for row
numbers and for column letters, for example:
The $ sign before the row numbers make this
range absolute for the rows, so as you copy
D$2:D$25
the formula down, the range will remain fixed
from D2 to D25.
This is an absolute reference, meaning it will
$F$1 always refer to cell F1 no matter where you
copy the formula.
The $ sign before the column numbers make
this range absolute for the columns, so as you
$D2:$D25
copy the formula down, the range will remain
fixed to column D.

Independent Activity:
In Chapter 8, Activity 8.3 (Club), follow the below instructions:
1. Change the function in:
a. cell H2 so that it can be copied into cells H3:H17 and still give the correct results as
in the current cells.
b. cell F2 so that it can be copied into cells F3:F17 and still give the correct results as in
the current cells.
c. cell L2 so that it can be copied into cells L3:L17 and still give the correct results for all
the years
 2) CALCULATIONS
ROUND FUNCTION
EXAMPLE OF
NUMBER RESULT DESCRIPTION
FUNCTION
Rounds a number to
92.6 =ROUND(A1;0) 93 nearest whole
number.
Rounds number to 1
78.8987 =ROUND(A1;1) 78.9
decimal place.
Rounds number to 2
65.345 =ROUND(A1;2) 65.35
decimal places.
Rounds number to 3
68.5275 =ROUND(A1;3) 68.528
decimal places.
LARGE AND SMALL FUNCTIONS

FUNCTION EXAMPLE OF FUNCTION DESCRIPTION

=LARGE(A1:A4;2)
Determines the nth largest
=LARGE(range;nth) Looking for second largest
number from a given range.
number
=SMALL(A1:A4;3)
Determines the nth smallest
=SMALL(range;nth) Looking for third smallest
number from a given range.
number
POWER FUNCTION

FUNCTION EXAMPLE OF FUNCTION DESCRIPTION

=POWER(A1;2) The POWER function returns
=POWER(range;power) Or the result of a number raised
The number^power to a power.
COUNT FUNCTIONS
FUNCTION WHAT IT DOES EXAMPLES
Counts the number of cells in
COUNT(Range) a range that contains =COUNT(A1:A10)
numbers.
Counts the number of cells in
COUNTA(Range) =COUNTA(B1:B10)
a range that contains letters.
Counts the number of cells in
COUNTBLANK(Range) a range that contains empty =COUNTBLANK(C1:C10)
cells.
Counts the number of cells in =COUNTIF(D1:D10;”Happy”)
COUNTIF(Range;Criteria) a range that meets a certain =COUNTIF(D1:D10;5)
criteria. =COUNTIF([Link]”>=70”)
SUM(Range) Adds all the values in a range. =SUM(E1:E10)
AVERAGE(Range) Calculates average in a range. =AVERAGE(F1:F10)
MIN(Range) Finds Min value in a range. =MIN(G1:G10)
MAX(Range) Finds Max value in a range. =MAX(H1:H10)
Independent Activity:
In Chapter 8, Activity 8.4 (Rain), follow the below instructions:
1. Insert a function in cell G48 to determine the total rainfall for April.
2. Insert a function in cell H49 to determine how many times no rain fell in May.
3. Insert a function in cell K50 to determine the highest rainfall in August.
4. Insert a function in cell M51 to determine the lowest rainfall in October.

3) ERROR INDICATORS
CIRCULAR REFERENCE
A circular reference error happens when the formula you entered is trying to calculate a value in
the cell you put it in, which means that the formula keeps going in circles.

To fix this problem, Change the incorrect cell reference to the correct one. In this case, change A11
to A10 to get the correct answer.
 #NULL
#NULL! errors happen in one of two cases:
1) When you use an incorrect range operator in a formula, for example, using a semi colon (;)
instead of a colon (:) to separate cell ranges.
2) When you use a space between range references to specify an intersection between two
ranges that do not intersect.
The reference operators in Excel are the:

• Range operator (colon or :) which defines the reference to a range of cells.

• Union operator (comma or ,) that combines to references into one reference.
• Intersection operator (a blank space) that indicates the intersection of two ranges.

As you can see, the range operator (:) has been

left out of the sum.

To fix this problem, You need to check that you are using the correct syntax in the formula. This
means that you must make sure that you are using a colon when you are referencing a range of
cells that follow on from each other (for example A2:A36 or A1:R1) or a comma to reference cells
that need to be combined (for example A1:A25,A36 or A1:L1,M1).

Independent Activity:
In Chapter 8, Activity 8.5 (Data Errors), follow the below instructions:
1. Determine why the #NULL! Error is in all the cells and correct it.
2. Correct the circular references errors.
Independent Revision Activity:
In Chapter 8, Activity 8 Revision Activity, follow the below instructions:
Work in the Travellers worksheet. The worksheet shows the statistics of the arrival and departures
of travellers using the King Shaka airport from 2010 to 2014.
1. Format the cells in row 1 as follows:
a. Merge the text in row 1 from cells A1:I1. (1)
b. Change the row height of row 1 to 57. (1)
c. Change the size of the heading in row 1 to 22 pt. (1)
2. Adjust the column width of rows 2 & 3 so that all the subheadings are clearly visible. (1)
3. Column C has the arrival months of the travellers. Use AutoFill to continue the arrival
months. (1)
4. Apply conditional formatting to column D so that all the cells displaying more than
4000 SA arrivals:
a. Appear in a light blue fill. (1)
b. Have a red border. (2)
5. In cell D54 calculate the number of SA residents that arrived at the Airport. (2)
6. In cell L4 determine the second largest number of foreign travellers that have departed
from the airport. (2)
7. In cell L5 determine the least number of SA residents that have departed from the airport.
(2)
8. Use a function in cell L6 to determine how many visitors overnighted in May. (2)
9. Change only the vertical borders between the headings in row 2 to a reddish line. (2)
Work in the Foreign worksheet.
10. Insert a function in cell L3 to display the average number of ALL Malawian visitors to
South Africa. (3)
11. Insert a function in cell L4 to show how many provinces did not receive Mozambican
visitors who came by air. (2)
12. Use conditional formatting to automatically display from which 8 countries the most
travellers to KwaZulu Natal came. Fill the background of these cells with any background
colour of your choice. (3)
13. Use a function in cell B50 to calculate all the foreign tourists who visited Gauteng. (2)
14. Insert a function in cell L5 that will display how many countries had people visiting North
West. (2)
Total: 30 Marks

4) IF FUNCTIONS AND CHARTS

IF FUNCTION
The IF function is built-in to and is one of the Logical Functions in Excel, which means that it
follows the logic of IF something is true, then do this, else/otherwise do that.

IF (condition, value_if_true, [value_if_false])

 FUNCTION CONDITION VALUE IF TRUE VALUE IF FALSE

=IF(D2>10;15;10) D2>10 15 10
=IF(A2>=40;”PASS”;”FAIL”) A2>=40 “PASS” “FAIL”
=IF(D2>10;C2*0.15;C2*0.1) D2>10 C2*0.15 C2*0.1
=IF(C3=”PRINCIPAL”;”GUEST”;””) C3=”PRINCIPAL” “GUEST” “”

=IF(B2<1000; MAX(A2:A10);
BR<1000 MAX(A2:A10) AVERAGE(A2:A10)
AVERAGE(A2:A10))

Independent Activity:
In Chapter 8, Activity 9.1 (Learners), follow the below instructions:
Work in the Members worksheet. An email must be sent to every person who does NOT have a cell
phone number (Column G).
1. Insert a function in cell I3 to determine if an email needs to be sent. If an email must be
sent, “Yes” must be displayed, otherwise “No” needs to be displayed. Copy this function to
cells I4:I101
2. Every member who joined in 1998 (Column C) qualify for a discount in membership fees.
Insert a function in cell J3 to determine which members qualify for a discount. If a member
qualifies, “Qualify” must be displayed, otherwise “Not qualify” needs to be displayed. Copy
this function to cells J4:J101
3. Members who paid on the 25th day or later (Column E) will be fined for late payment. Insert
a function in cell K3 to determine if a member must be fined. If the entry shows the day 25
or later, “Fined” must be displayed, otherwise “No Fine” needs to be displayed. Copy this
function to cells K4:K101
 SUMIF FUNCTION
The SUMIF function lets you sum in one range based on a specific criterion you look for in another
range. This means that you can use the values in a range to modify the values in another range.

Guided Activity:
In Chapter 9, Activity GAct9.3, I want you to Calculate the total sales of Vodacom airtime only. In
cell D2, enter the formula =SUMIF(B2:B10,”Vodacom”,C2:C10). Press Enter to see the result.
Using our data from the previous example, we want to calculate all CellC data sales. Step 1: Select
cell D3. Step 2: Select the Function Builder icon next to the Formulas bar. Step 3: Type a brief
description of what you want to do, select a category or select a function to find the function you
want to use. Step 4: Select the function you want to use (SUMIF) and then select OK. Step 5: Begin
filling in the function arguments and then select OK. To select the range, you can return to the
sheet by selecting on the arrow next to the Input bar. Select the range and press enter to return to
the Function Builder.

Independent Activity:
In Chapter 9, Activity Act9.2 (Sales), I want you to follow the instructions below:
1. Insert a function in cell I3 to determine the total value (Column H) of all the products sold
by the 7Digital Store (in column A). Remember to format the answer as Currency.
2. Insert a function in cell I6 to determine the total value (Column H) of all the MP3s (in
column A) sold. Remember to format the answer as Currency.
3. Insert a function in cell I9 to determine the total value (Column H) of all the products sold
by the artist Green Day (in column D). Remember to format the answer as Currency.
4. Insert a function in cell I12 to determine the total value (Column H) of all the products sold
by the Label EMI UK (in column E). Remember to format the answer as Currency.
5. Insert a function in cell I15 to determine the total value (Column H) of all the DVDs sold (in
column B). Remember to format the answer as Currency.

COUNTIF
The COUNTIF function counts the number of cells that meet a criterion; for example, to count the
number of times a particular city appears in a customer list. In its simplest form, COUNTIF says:
=COUNTIF(Where do you want to look?, What do you want to look for?).
For example: =COUNTIF(A2:A5,”London”)

Independent Activity:
In Chapter 9, Activity Act9.3 (Sales), I want you to follow the instructions below:
1. Insert a function in cell E3 to determine how many sales were made by Williams (in column
A).
2. Insert a function in cell E6 to determine how many sales were made by Jones (in column A).
3. Insert a function in cell E9 to determine how many sales were made in Qtr. 2 (in column D)
 RELATIONAL OPERATORS

OPERATOR OPERATOR
Greater than. Used to determine if one value is
>
larger than another.
Less than. Used to determine if one value is
<
lower than another.
Less than or equal to. Used to determine if one
<=
value is less than or the same as another value.
Greater than or equal to. Used to determine if
>=
one value is more than or the same as
Not equal to. Used to show that two values are
<>
not equal.

Guided Activity:
In Chapter 9, Activity GAct9.5, I want you to follow the instructions below:
Mr Tshabalala wants to see if his students may play on the soccer team. They may only play if they
have less than 20 demerit points.
1. Step 1: Mr Tshabalala enters the formula =IF(C2>20,”No”,”Yes”) in cell D2. This says that if
the student has more than 20 demerit points, they may not play on the soccer team.
2. Step 2: He presses Enter to view the result.
3. Step 3: He then copies the formula down to check the rest of the data.

Guided Activity:
In Chapter 9, Activity GAct9.6, I want you to follow the instructions below:
A school administrator wants to check how many Grade 10 learners can be classified as teenagers
(younger than 16) or young adults (16 years old and older). He takes the class list of all Grade 10
learners in his school and does the following:
1. Step 1: In cell C2, he enters the formula =IF(B2>=16,”Young adult”, “Teenager”). This states
that if the learner is older than or is 16, they are classified as a young adult.
2. Step 2: She presses Enter to see the result.
3. Step 3: She copies the formula down to check the rest of the data

Independent Activity:
In Chapter 9, Activity Act9.4 (Dublin), I want you to follow the instructions below:
1. The organisers of St Patrick’s Day want to know with which currencies they will be dealing
with, whether visitors will contribute pound sterling, dollars or Yen.
a. Insert a function in cell G3 to determine which visitors in Column F are from the UK.
If they are from the UK the word “Pounds” must be displayed, otherwise “No
Pounds” must be displayed. Copy this function to the cells in Column G for all
visitors.
b. Insert a function in cell H6 to determine which visitors in Column F are from the USA.
If they are from the USA the word “Dollars” must be displayed, otherwise “No
 Dollars” must be displayed. Copy this function to the cells in Column H for all
visitors.
c. Insert a function in cell I4 to determine which visitors in Column F are from the
Japan. If they are from the Japan the word “Yen” must be displayed, otherwise “No
Yen” must be displayed. Copy this function to the cells in Column G for all visitors.
Work in the Summary worksheet
d. Insert a function in cell C2 to display “True” if the total number of visitors per city
(Column B) is 4 visitors or more. Otherwise, the function must display “False”. Copy
the function to indicate for all cities’ visitors up to cell B21.
e. Insert a function in cell D2 to display “True” if the total number of visitors per city
(Column B) is less than 8. Otherwise, the function must display “False”. Copy the
function to indicate for all cities’ visitors up to cell B21

EXTRA CALCULATIONS
OPERATOR FUNCTION EXAMPLE
Returns a random real number
RAND between o and 1 (decimals) e.g. =RAND()
0.24366
Returns a random whole number
RANDBETWEEN
between the two whole numbers =RANDBETWEEN(1,100)
(BOTTOM, TOP)
you specify.

5. CHARTS

CREATING CHARTS
Charts can only be created when the data is in the sheet and is sorted in a logical way. In Excel,
charts are under the Insert tab in the Illustrations group.

Guided Activity:
In Chapter 9, Activity GAct9.7, I want you to follow the instructions below:
1. Step 1: Select data for the chart.

2. Step 2: Select the Insert tab.

3. Step 3: Select Recommended Charts.
4. Step 4: Select a chart on the Recommended Charts tab to preview the chart.
5. Step 5: Select the chart that will work best for the data.
6. Step 6: Select OK.
Independent Activity:
In Chapter 9, Activity Act9.5 (Species) and work in the Spec worksheet, I want you to follow the
instructions below:
1. Create a pie chart in a new worksheet called Pie Chart to display the severity percentages
for all Vertebrates (Column
a. Add the title “% of Endangered Vertebrates” roughly in the middle of the chart.
b. Add the legend at the bottom.
c. You must use data labels for each piece of the pie chart.
d. The “Birds” section must be separated from the rest of the pie.
2. Create a column graph in a new worksheet called Column Chart to display the number of
threatened species per year (2008 – 2014) for all Invertebrates (Column A).
a. Add an appropriate title to the chart.
b. Add the legend at the top.
c. You must use data labels for the chart

FORMATTING AND EDITING CHARTS AND GRAPHS

Guided Activity:
In Chapter 9, Activity Act9.5 (Species) and work in the Pie Graph worksheet, I want you to follow
the instructions below:
1. Step 1: Right-click on the chart element you want to format. This could be the axes, data
series or titles, for example

2. Step 2: Select Format [chart element]. In this case, it’s the axis.
3. Step 3: The Format pane opens on the right of the screen with options for the element.
In the Format pane:
You can format the chart element using the following tools:

• Fill & Line lets you change the fill colour and border type for each element.
• Effects lets you add image effects (like shadows or a glow) to the element.
• Size & Properties lets you change the width, height and scale of the element as well as assign it
certain properties.
On the Design tab, you can:

• Add new chart elements.

• Change the layout using Quick Layout.
• Change the colours and styles.
• Use the Data group, you can switch the rows and columns and change the data range.
• Change the chart type and move the chart to another sheet or tab in the workbook.

Using the Format tab, you can:

• Format the element you have selected.

• Insert shapes and change the styles of those shapes.
• Add WordArt and change the styles.
• Rearrange the chart’s position in the spreadsheet.
• Change the size of the chart

To link a Title to a cell, you can:

• Step 1: In the chart, select the Chart Title box.

• Step 2: Type = and select the cell that contains the text you want to use as a chart title.
• Step 3: Press Enter.
To update a chart with new data or more data, you can:

• Step 1: Right-click on the chart, and then choose Select Data.

Revision Activity:
In Chapter 9, Activity Act 9 Revision Activity, I want you to follow the instructions below:
Ace Travel has stored a list of their clients in a spreadsheet. Work in the Clients_Kliënte worksheet.
1. Format the worksheet as follows:
a. Centre the text in row 1. (1)
b. Use a light blue fill colour for row 1. (1)
c. Change the height of row 1 to 30pt. (1)
d. Wrap the contents of cell A3. The “Birds” section must be separated from the rest of
the pie. (1)
2. Use conditional formatting on column E to automatically highlight all males in a yellow fill
colour and red text colour. (3)
3. Insert a function in cell K8 to determine the total number of trips made by clients. (2)
4. Insert a function in cell K9 to determine how many clients are NOT international travellers.
(3)
5. Create a pie chart to display the percentage breakdown of each category of traveller status.
Use the data in cells J11:K13 to create the chart as follows:
a. Add the following chart title above the chart (bold & 14pt): Traveller Status
b. Display the percentage for each category of traveller status.
c. Display the chart in a new worksheet and rename it to: Travellers
d. Fill the category that has the highest percentage with any gradient colour of your
choice. (8)
Work in the Bookings_Besprekings worksheet.
6. The rate per day is stored in column H. Insert a formula in cell D5 to determine the total
accommodation cost for the duration of the stay for Bertha Hood. (2)
7. Insert a function in cell J7 to determine the third lowest rate per day. (2)
8. Insert a function in cell J10 to determine the total number of days (column C) that clients
will spend at hotels or lodges in Kenya. (4)
9. A booking reference in column A that is indicated with an ‘X’ indicates an international
hotel or lodge. A booking reference that is indicated with a ‘D’ indicates a domestic hotel or
lodge. Insert a function in cell E10 to return the value ‘TRUE’ if the hotel or lodge in row 10
is international and the value ‘FALSE’ if the hotel or lodge in row 10 is domestic. (4)
Total: 32

6. WORKING WITH SPREADSHEETS

HEADER AND FOOTER

To insert a header or footer:

• Navigate to the bottom right hand side of the workbook. Select the Page Layout view
command. The worksheet will then be shown in the Page Layout view.
• Select the header or footer that you want to format. In this example we will modify the header
at the top of the page.
• The Header & Footer tools tab will appear in the Excel ribbon. From here, you can add page
numbers, dates, workbook names and so on.
 IMPORT/EXPORT DATA
IMPORTING DATA FROM ACCESS:
When you are importing data from Access, you may need to take information from more than one
table. Excel allows you to select the tables in an Access database when you are importing the data.
To see how to import data from Access to Excel, read through the following.
1. Step 1: Open a blank workbook.
2. Step 2: Select the Data tab.

4. Step 4: Select the Access file you would like to import data from and select Open. Access
files end in *.accdb.

5. Step 5: Use the Select Table dialogue box to choose which tables you want to import.

6. Step 6: Choose how you want to view the data in the Import Data window. In this example,
we selected Table.
 7. Step 7: Select OK and wait for the data to be loaded. This may take a few seconds,
depending on the size of the database.
8. Step 8: As you can see, the data has been imported as tables on separate worksheets.

IMPORTING DATA FROM A TEXT FILE:

1. Step 1: Open a blank workbook.
2. Step 2: Select the Data tab.
4. Step 4: Select the text file, [Link], to import data from and select Import. Text files end
in *.txt

5. Step 5: Use the Text Import Wizard to select the format that best describes the data. In this
case, we selected Comma Delimited and made sure that My data has headers has been
ticked. Select Next.

6. Step 6: Select the delimiters of the data. Here it is Comma. Select Next

7. Step 7: Select the format for the data. You can select each column and choose the Data
Format for that column.
 8. Once you are done, select Finish. Step 8: The Import Data dialogue box will open. Select
how you want to view the data. In this case, Table was automatically selected. You can also
choose where you want the data to start in the worksheet. We chose cell A1.

Select OK when you are done.

9. Step 9: Check that the data has imported correctly and
that the columns are correct

EXPORTING TO CSV:
To export the data in an Excel spreadsheet to CSV, you simply need to use the Save As function.
You can select the file name and edit as well as the type of document you want to save it as.

Independent Activity:
In Chapter 10, Activity Act10.1 (Species) and work in the Imp worksheet, I want you to follow the
instructions below:
1. Import the data from Import(Text document) into the worksheet Imp.
2. Save and close the spreadsheet.
 MANIPULATING WORKSHEETS
MOVING AND COPYING DATA:
Step 1: Select the cells you want to move.
Step 2: Move the mouse cursor to the border of the selection and wait for it to become the
move pointer.

Step 3: Drag the selection to another part of the worksheet.

PASTE SPECIAL:
Step 1: Select the data you want to copy. As you can see, there are functions in these cells.

2: Copy the data using the Copy command in the Home ribbon or by
pressing Ctrl+C.
Step 3: Right-click in the cell you want to move the data to. An Options dialogue box will open.
Step 4: Select the Paste Values icon.

Step 5: The data will be pasted as values only. As you can see, when the data is
selected, the functions have been removed.

VIEWING GRIDLINES AND FREEZING PANES:

Step 1: Select the View tab.
Step 2: Select Freeze Panes in the Window group.
Step 3: Select the type of pane you want to freeze.
Step 3a: To freeze the top row, select Freeze Top
Row.
FREEZE PANES:
To freeze a selection of rows:
Select the row directly below the one you want to
freeze. Select Freeze Panes.
To freeze a selection of columns:
Select the column directly after the one you want to freeze. Select Freeze Panes

PROTECTING THE WORKBOOK:

Step 1: Select the Review tab.
Step 2: Select Protect Workbook.
Step 3: Enter a password in the Password box.
Step 4: Tick the boxes to allow users to do certain things. If you
want to make the spreadsheet read-only, untick all the boxes.
Step 5: Select OK. Retype the password and select OK again.
PROTECTING CERTAIN INFORMATION IN
WORKBOOK:
Step 1: Select the cells you want to protect.
Step 2: Select the arrow next to Alignment on the Home
tab to open the Format Cells dialogue box.
Step 3: In the Protection tab, tick the Locked check box and
then select OK.
Step 4: Protect the worksheet or workbook.

PRINT OPTIONS:
PRINT OPTION DESCRIPTION
Print Active Sheets Print only the sheet(s) that
you have selected to print.
Print Entire Workbook Prints all the sheets
containing data in a
workbook.
Print Selection Prints only the cells, rows or
columns you have selected.

You can also manipulate the layout of a worksheet to make the data easier to read when printed
and to save paper. For example, if you have a lot of data on a single
worksheet, you can choose how the data will be printed to use fewer
pages by:

• Changing the orientation. Landscape orientation, which means

the page has the long edge on the top and bottom, can fit
more data on a line.
• Changing how many columns are on a printed page. You can
do this using the scaling options in the Print menu.
• Setting the width of the margins. Narrower margins mean that
there is more space in the main body of the page for the data
to fit on to.
• Use Scaling, fit to one page in Page Setup.
• Drag the page break lines as you please in the Page Break View.

Independent Activity:
In Chapter 10, Activity Act10.2 (Rivers) and work in the River worksheet, I want you to follow the
instructions below:
1. Protect columns A-C to stop users from entering text.
2. Use 1234 as a password.
3. Change the page layout to ensure that all the data will fit on one page without adjusting
the font size and column widths.
4. Save and close the workbook
LINKING CELLS AND FORMULAS BETWEEN SHEETS:
Step 1: Select the cell you want to add the
function to.
Step 2: Start by typing “=” and then the
function’s name in the formula bar, in this case
we’re using AVERAGE.
Step 3: Select the tab of the worksheet you
want to reference.
Step 4: Select the range of cells or the single
cell you want to reference. Note that the sheet
name is followed by an exclamation mark (!).
Step 5: Close the bracket and press Enter. Go
back to the first sheet to see the result.

Independent Activity:
In Chapter 10, Activity Act10.3 (Sales Data), I want you to follow the instructions below:
A local Pokémon GO! community group has been hosting their special events at a nearby zoo.
They sell tickets for R45. They have captured data for each of the events they sold online tickets
for. They want to see how many tickets they have sold on average since the first event, how much
money they made at each event and which event had the highest number of ticket sales.
1. In column C of each sheet (Chikorita Day, Beldum Day and Cyndaquil Day) calculate how
much each person paid for their tickets for each event.
2. Calculate the total number of tickets sold and the total sales for each event. This will be
done in:
a. Cells B59 and C59 respectively for Chikorita Day
b. Cells B74 and C74 respectively for Beldum Day
c. Cells B39 and C39 respectively for Cyndaquil Day
3. 3. On the sheet called Ticket totals, calculate for each event
a. the number of tickets sold (B2:B4)
b. the average number of tickets sold (C2:C4)
c. The average sales (D2:D4); by linking the calculations for each event to their
respective cells on the sheet Ticket totals.

Revision Activity:
In Chapter 10, Activity Act Chapter 10 Revision Activity, I want you to follow the instructions below:
The details of the blood donors at the school are stored in this spreadsheet. Work in the
DonorsInfo worksheet.
1. Format the headings in A1:G1 with a red fill and the font colour to be white. (2)
2. Change the format of the heading in cell I2 to resemble the image below: (2)

3. Change the font of rows 1 & 2 to Comic Sans MS. (1)

4. Apply a suitable formatting in column H so that all cells with donors who weights below
60KG are displayed with a light blue fill. (3)
5. Use a suitable formatting in row 1 and 2 to lock the headings so that they are kept visible
when you scroll through the rest of the worksheet. (2)
6. Learners that donate more than 30 litres of blood are seen as “Peer Promoters” and those
less than 30 litres seen as “Beginners”. The words ‘“Peer Promoter” and “Beginner” should
appear in column F based on the litres of blood donated in column I. In cell F26, use a
function to determine category for Liyema Frans. (3)
Work in the Save-a-ton worksheet.
7. General statistics are normally needed of the learners that are taking part in the Save-a-Ton.
a. In cell I5, use a function to calculate the number of girls that took part. (2)
b. In cell I6 insert a function to determine the number of male adults. (2)
8. Use the totals for the Male and Female in J1:K2 to create a graph similar to the one below.

• NOTE:
• The graph must be a column chart.
• Display a centred overlay chart title: “We support Blood Donation”.
• Format the column for female to a stack filled with a picture [Link].
• The vertical axis must have a rotated title that reads “Number of Adults”.
• Legend must be displayed on the left side.
• Data labels must be displayed inside base. (8)
9. Move the chart/graph to a new worksheet within the existing spreadsheet. Rename the
new sheet “Chart”. (2)
10. Work in the Import worksheet. Import the data from the [Link] file into the Import
worksheet. (3)
Total: 30