Excel
The spreadsheet was revolutionary when first released (VisiCalc - 1980), especially in finance where it was able to perform the work of many clerks.
Last updated
The spreadsheet was revolutionary when first released (VisiCalc - 1980), especially in finance where it was able to perform the work of many clerks.
Last updated
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.
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
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
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.
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
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
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
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
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
