Lab 3 - Excel Fundamentals PDF

Preview

Summary

notes of lab in internet modelling...

Description

Microsoft Excel Fundamentals Microsoft Excel is one of the most fundamental tools used in both IT and Business. This short intro should give you enough context to get started on ITECH1100 lab tasks. As students at Fed Uni, you have access to a free copy of Microsoft Office. Visit https://federation.edu.au/it-offers and see the Office365 section.

Tour of the Microsoft Excel interface The section at the top is the ribbon, and you can switch between different sets of buttons by clicking Home, Insert, Page Layout, etc.

Click each tab to explore what buttons are available, and then back to Home Below the ribbon is the formula bar.

We’ll be using the formula bar shortly, but first let’s look at the final part we care about today, the spreadsheet or, in Excel terminology, worksheet view:

A spreadsheet is a big two-dimensional grid of cells. You can see a single cell selected in the above screenshot. Cells can be identified by a combination of their column (A,B,C…) and row (1,2,3,...1 048 576). The selected cell in the above screenshot is C2.

Entering data To enter data (numbers, text, dates, other types) into a cell, just select it and start typing. Alternatively, and a good approach when working with formulas, you can click in the formula bar and edit from there as well.

CRICOS Provider No. 00103D

Insert file name here

Page 1 of 5

Try entering the values 1, ‘1, Hello, and 17/3/1967 into various cells and seeing how Excel handles it. What does adding the apostrophe in front of the 1 do?

Basic mathematics with Excel Simple calculations Excel and other spreadsheets are great for doing sequences of calculations, or applying the same calculation to multiple data points at the same time. Try selecting cell A1, and entering the following, either directly in the cell or in the formula bar: = 10 + 10 The equals sign indicates to Excel that it should calculate the value for this cell, rather than just use it as text or a number or other data. In Excel terminology, you just entered a formula (so the formula bar should make a little more sense now). The spaces don’t actually matter and are optional. Press Enter or click another cell to finish. The cell should update to show 20 – but the formula bar will still show the above formula. Other operators you can use besides + are: •

* (an asterix) for multiply

e.g. =3*5

•

/ (a forward slash) for divide

e.g. =1/2

•

- for subtract

e.g. =9-4

•

^ (a caret) for exponentiation.

e.g. =2^8

•

for less than, equal to, and greater than

e.g. =9 5

CRICOS Provider No. 00103D

Insert file name here

Page 3 of 5

This should display TRUE or FALSE, depending on whether A1 is greater than the number 5. Select cell C1 again. Take a look at the bottom corner – there is a small square “handle”. Click and drag that handle down to C6. You can also select a range and choose Fill from the Home tab to perform the same operation.

Notice that Excel has automatically updated the cell references in each formula so that the forumula in cell C2 refers to A2, C3 refers to A3 and so on.

Absolute references What if we want to change our formula from checking against 5 to something else? It would be nice to be able to make it so that we have our threshold in another cell, and refer to that in each cell. •

In cell D1, type the value 5

•

Update C1’s formula to reference cell D1

•

Redo the copy operation above to fill cells C1:C6 with the formula.

•

Look at how amazingly it didn’t work.

The problem here is that Excel is helpfully updating the reference to D1 in the same way it updated the reference to A1. That is, cell C2 is now looking for its threshold in cell D2. We can prevent this behaviour by using absolute references. These are simply a way of telling Excel not to update a particular reference when copying. •

In cell C1, change your reference to cell D1 to D$1

•

Redo the copy operation once more

•

Check that the results are correct

The dollar sign ($) in the above cell reference “locks” the row number when copying. We could have also kept the column from changing by using $D$1, or just the column using $D1. This works on ranges as well. Changing the range A:A to $A:$A would prevent the range being changed on copying.

Named references There is another way to use absolute references, which also helps you keep track of the meaning of each cell. Select cell D1. The formula bar should look something like:

The text box on the left, which currently shows D1, allows you to supply an alternative name or label for this cell. Enter a name such as thresh into this box. You can choose anything you like.

CRICOS Provider No. 00103D

Insert file name here

Page 4 of 5

Now you can use this name instead of D1. •

In cell C1, change your reference to cell D$1 to thresh

•

Redo the copy operation once more

•

Check that the results are correct

Further information Further learning materials are available at https://www.microsoft.com/ennz/learning/course.aspx?cid=55165

CRICOS Provider No. 00103D

Insert file name here

Page 5 of 5...