## Learn Excel

## 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 RacesOpen 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 BeansComplete 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 DataWe 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 formattingOpen 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 CashIf 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 ExcelComplete 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 ReferencingWhen 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 SYMBOLSAbsolute 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 FatEnsure 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

## Lesson 5 - If Statements

Tasks: 8 If statements Intro & 9 boy girl passed - If statementNot 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" |