Learn Excel

Before You Start

Before you start you will need to Download this file pack for Excel.

1.) Create a new folder in your Documents called "Learn Excel"
2.) Click on the link on the right and download the zip file into that folder
3.) Right click on the folder and click "extract all..."

---

Lesson 1 - Intro to Formulas

The main use for Microsoft Excel is the use of formulas.

Spreadsheets have existed for a very long time and their primary function was to store number data and work out Mathematics equations.

Basic Mathematic equations look like the picture on the right:

In order to enter any equation you need to enter "=" at the start

Some additional Equations are as follows:

=Sum(A1:A4) -- This adds together the sum of A1, A2, A3 and A4.
=Average(A1:A3) -- This finds the Average(mean) of all numbers listed between A1 and A3.


Exercise 1: Food Eating Races
Open and complete the Excel Spreadsheet for Food eating races

Use the sum and average command to fill in the grid.

Then use the Mathematics equations to work out some of the equations in the box below.

---

Lesson 2 - Sorting, Percentages and Formatting

Task 2 - Coloured Beans

Complete the Coloured Jelly Beans Activity

Extra for experts: Try and complete the graph with no help

Additional Formula

=min(A1:A4) -- returns the lowest entry between A1-A4.
=max(A1:A4) -- returns the highest entry between A1-A4

Sorting Data

We can sort data by values by using the data sort function

To do this first select the data you want to sort.

Select the data tab and click Sort.

Task 1 - Games and Beans formula and formatting
Open and complete "Common Wealth Games"

Formatting
We can use font, background colour and borders using the font menu box.

You can Auto-magically adjust the column widths to match the contents of the cells.

Percentages and Cash

If you wish for your values to change from 0.9 to $ 0.90  or 90%

Then you need to hit the $ or % button under number.

---

Lesson - 3 Absolute Referencing

Recap - Open "3 super 14.xlsx" in Excel
Complete the activity and use some of these new formatting tools under the alignment table

They are used to modify text.

Absolute Referencing.

Go to the Math Squares activity on that page

Try to drag one formula i.e. =c2+b3 to complete the task

What goes wrong? Why does the formula not work?

Activity Time:

Open and complete both:
4 Deposit - abs ref.
6 Interest Table + Banks - Absolute Reference

Absolute Referencing vs Relative Referencing

When we are clicking and dragging formulas it normally uses what is called relative referencing. This ensures the formula updates properly each time it is copied to a new cell.

By adding a $ symbol we ensure that the formula stays on the value it is supposed to stay on the same Cell

for example:
=A1  is a relative reference that will change when
=$A1 is relative to the row but forces the A column
=A$1 is relative to the column but keeps the same row
=$A$1 is absolute to A1 it will stay at A1.

QUICK KEY: HIT F4 TO ADD THE DOLLAR SYMBOLS

Absolute referencing is important when you have a percentage or amount such as GST that may change, but that you need to use all the time.

---

Lesson - 4 Graph Representation

Task: Complete 10 Tall buildings, Palmy, and Fat

Ensure every graph has a legend and is fully labelled.

Graphs are important for giving a visual representation of data.

People identify with pictures more and it is easier to map trends and specifics.

To great a graph select the data, then select the type of graph you want to create

---

Break Part Time Test

Take the time to complete the Jack and Jill Assignment

Open 7 Jack and Jill - Assignment

Complete this in one lesson

Go for it

---

Lesson 5 - If Statements

Tasks: 8 If statements Intro & 9 boy girl passed - If statement

Not achieved < 50%
achieved > 50%
Merit > 70%
Excellence > 90%

If statements can be used in Excel, in fact whole games can be programmed in Microsoft Excel.

If statements work very similarly

Here is how they look

=IF(H3 < 3, "you lose", "you win")

Test I.e. is the data in H3 less then three.
Output if condition is true
Output if condition is false

If H3 had the value of 2 it would return:
"you lose"
If H3 had the value of 3 or more:
"you win"

Practice Tests and Practice

Now complete the following

11 Last lot of Practice
12 Athletics and Breakfast


and one of these

13 Weekly expenses and US exports
or
14 Consoles