Excel

The spreadsheet was revolutionary when first released (VisiCalc - 1980), especially in finance where it was able to perform the work of many clerks.

Open Microsoft Excel and create a blank Workbook

Set up your split screen with the new Workbook. Save your document to OneDrive with the name ExcelTraining_YOURNAME

The document that you have created is called a workbook. A workbook can be further divided into Sheets.

Adding Numbers

Right click on sheet1 at the bottom of the page and rename it to Adding

Each square of the grid that you see is called a Cell.

Click on cell A1 and type 1. In cell A2 type 2

Lists

Excel makes creating lists of numbers easy if the pattern is obvious.

Highlight both cells that you have written in. Click on the bottom right hand corner of the highlighted region and drag the cells down to A10

Formulae

Excel is so powerful because we can use formulae to reference cells and perform arithmetic on their contents.

In cell B10 write Total. Highlight both B10 and C10 and create a border using the Border Button > All Borders.

Click on C10 and in the equation bar (at the top next to fx) write the following code and then press Enter.

=A1+A2+A3+A4+A5+A6+A7+A8+A9

The Sum & Average Function

The approach above works well for short lists but would quickly get tedious for long lists. Excel has lots of built in formula to help you.

Starting on cell E1, create a list of the first 30 odd numbers by writing 1 in E1, 3 in E2 and dragging the numbers down like you did before.

Create another total box at the bottom but this time, in the formula box type =sum( and then highlight your odd numbers and press Enter.

Create a new set of boxes below total but this time use the average() function to calculate the average of your numbers

Sorting

Click on the + button next to your sheet name at the bottom of the page. Create a new sheet called Sorting

Create two headers at the top of the sheet called Name and Age. Use the formatting tools to add a background colour, make the text bold and centre it.

Now fill five names and ages of your choosing

Highlight cells A1:B6

In the Data tab at the top, click Sort and select Name as the column to sort by

Graphing

Another powerful feature of Excel is its ability to make charts

Create a new sheet called Graphing and create a table of x and y values.

Hightlight your data and from the Insert tab, select a line chart.

We now need to tell Excel to use our first column as the graph's x-values.

Right click on the graph and click Select Data. Click on Horizontal (Category) axis labels and highlight your x column before pressing Enter.

Double click on the graph title and write Excel Training Graph

Add your file to the Firefly task and submit

Project

Task 1: Multiplication Table

We are going to use the power of Excel to create any times table of our choosing.

Create a new Excel workbook called ExcelProject. Label the first sheet TimesTable

The first task is to create a box that we can use to tell Excel which times table to use.

In Cells B2 and C2, create the box shown below, putting a border around both.

We will now create a space to calculate the multiplication table:

Type 1 into C4 and 2 into C5. Highlight both and drag down until you get to 10.

In column D, we want to tell Excel to multiply the number in C2 with the number to its left.

If we just wrote C2 as a reference and dragged down, Excel would change the reference to C3 in the next row etc.

To fix a reference in excel, we put a $ in front of either the number or letter, or both.

In cell D4 type =C4 * C$2 and drag down to row 13

We can now type any number we want into C2 and get the multiplication table

Extension Exercise: Extend the list down to the 100th multiple and add some styling to your sheet

Task 2: Magic Squares

Magic Squares are square grids with a special arrangement of numbers in them. These numbers are special because every row, column and diagonal adds up to the same number. So for the example below, 15 is the magic number.

Create a new sheet in your workbook called 3x3 Magic

The first thing we will do it to create some squares by setting the row and column widths to be the same.

Highlight columns A to F by pressing Shift and highlighting those columns. Right click on the column header and click set width. Set it to 12.

Highlight the first 6 rows and set their height to 60

Starting at B1, fill in the magic square values from the example at the top of this page. Align the text to the centre of the box and add some styling.

Use excel formula to add up each row and column to check if it is magic

Can you find another set of magic numbers?

Extension: Create a new sheet and create a 4x4 magic square

Save your file and attache it to the Firefly task