| 1. Basic Themes in Excel 3. Formatting 4. Working with data |
Note 1: I've always taught formulae in two spreadsheets - a practice one, and a 'base spreadsheet' which allows the student to practise a range of the formulae they've just learnt, for consolidation. You can take advantage of Excel's multiple worksheets and have some examples ready set up to move onto.
Note 2: Not all formulae start '=SUM'. Only adding formulae should start this way. I've seen people use Autosum for the most random things. It's simply because Autosum is the commonest formula when starting out, but it's by no means the only one, and should be discouraged for anything but adding. If you use it for everything, please desist!
Note 3: If you ever find yourself stuck when writing a formula, you can turn it into text by placing ' (apostrophe) just before the = (equals).
How to teach formulae? Absolutely from the bottom up. It's surprising how few people know how to construct one, and it's pretty quick to get going.
Start with writing 100+200 on the board. Ask, if all you had was a calculator, what key would be needed to complete this. (A: 'equals') Point out that in Excel, essentially formulae are exactly the same BUT the equals symbol always goes at the start in Excel. Reinforce this at every opportunity.
Move onto entering '100' into A1 and '200' into A2. How would we add them up? Concentrate on values for now, not cell referencing. Active cell should be where we want the answer to go (i.e. A3): =100+200. At this point it is essential to confirm correctly, not 'click out', for reasons we'll come to shortly. We have two ways to confirm: press the Enter key, or click on the little green tick to the left of the Formula Bar. Reinforce this at the end of every formula: "what do we do to confirm? Press enter or click the tick!"
Once people have completed this formula, point out you can see the answer in the cell, so where is the formula? Yup, in the Formula Bar, and that's why it's got its name. (You think this sounds patronising? You'd be surprised how often, again and again, people are really surprised and fortified by this basic revision of the essentials.) Note: if they get an answer of 'FALSE' they have not used the Shift key for +, and their formula will read '=100=200'.
Ask them to change the value in A1 to 99. Once they confirm (and what do we do to confirm?!) ask them what has happened to the answer (answer: nothing). Ask why not? Ask how we can change the formula so it takes into account anything in A1 and anything in A2 - modify the formula to say =A1+A2 and confirm. Now it doesn't matter what goes in A1 or in A2, the formula in A3 will automatically update. This introduces and reinforces the relevance of cell referencing.
Now enter a column of numbers (I use 10, 20... 80 in B1:B8, using this to reinforce AutoFill). Now ask how we could add these up. =B1+B2+B3+...? Not likely! Introduce them to AutoSum. Trick: Autosum involves NO selecting. Simply choose as your active cell where you want the formula to go, click Σ and confirm. If you are in the habit of selecting, get out of it. In advanced classes, I introduce spreadsheets with at least 5000 rows of data - that's a lot of selecting and a sure way of getting RSI, so don't do it, kiddies!
Now move to cell C1. Ask what will happen if I AutoSum here? After all, there's nothing above it. Ah, it AutoSums to the left. Now if I modify the value in B1, I can see it's impacting on two separate formulae - the one in B9 and the one in C1.
Move back to your 'base spreadsheet' and practice some addition, using standard + or AutoSum.
Back to your 'practice sheet' - enter 10 in E1, 13 in E2. Now we want to multiply them. What do we use for multiplying? * ('star' or 'asterisk'). There are two on a standard keyboard - encourage them to use the one they prefer. Ask them to write the formula in E3.
In F1:F4, enter 10, 2, 4, 5 and ask them to multiply them together in F5. There is no quick way of doing it - if people suggest AutoSum, brackets or colons, then they are using perfectly valid logic, but this helps reinforce the fact that SUM only works for adding, and they'll have to do it 'the long way round' - i.e. =F1*F2*F3*F4.
In H1:H2, enter 44, and 33. Ask them to minus one from the other in H3.
In J1:J2, enter 33, and 11. Ask them to divide one from the other in J3. But what do we use for division? / Note: never back-slash.
Return to the base spreadsheet, and practise some of these formulae. If you have a column of data, you can then demonstrate using AutoFill to copy a formula down.
Again, write on the board what the first formula is (e.g. = B1*C1) and then - without actually entering the formulae on the spreadsheet, just writing it on the board - ask the students what the next one is likely to be (=B2*C2). Repeat this a few times on the board (=B3*C3, =B4*C4) until they can see a pattern forming. Say we can take advantage of this pattern, and use AutoFill to fill the formula all the way down the column. Again, have a few columns where they can practise this.
So far we have only looked at typing formulae in, save for AutoSum. You can also nominate cells using the mouse. It will involve a combination of keyboard and mouse, but can be a lot less frustrating.
Start off by typing '='. Then, using your mouse, click slap bang in the middle of the cell whose reference you want to include in the formula. For instance, if you want to put =F9, type = then click in the middle of F9.
This is why 'clicking out' is to be discouraged - you spend ages setting your formula up, only to ruin it by 'clicking out' - you can't, all you're doing is telling Excel you want to nominate that cell in your formula, and you'll be stuck utterly until you 'press Enter or click the tick'!
It's surprising how few people know about Absolutes, but they are very, very useful.
We've seen that if we use AutoFill to copy a formula down a column, then the
cell references change:
=B1*C1
=B2*C2
=B3*C3 etc.
These are called relative cell references.
However, sometimes we don't want the cell reference to change. This is called an
absolute cell reference.
Say, for instance, you have a spreadsheet listing net product costs. You want create a formula to calculate gross product cost in the next column along, so you multiply each of your net costs with a single cell at the top (let's say J1), which contains your VAT rate. If you created a column of formulae in the standard way, when you AutoFill your formula down it will change from J1 to J2 to J3 and so on. What we need to do is fix the cell reference in place, so it stays J1 all the way down the column.
What are the advantages? Simply, that we only have one cell to change if, in our example here, the VAT rate changes. We don't have to modify the formulae over and over, and nor do we have to have an entire column of exactly the same value.
The trick is to 'anchor' the cell in place, and this is done in two ways - a long way and a quick way. The long way is literally to type - the symbol is $ and we need one before the column ref and one before the row ref - e.g. $J$1. The quick way? This is neat, and your students will love it - immediately after entering the cell reference in your formula, press the F4 key. This will automatically slap the dollar signs in. Note: you must be in a formula for this to work.
Sometimes - rarely - you will find a 'mixed' ref or 'semi-absolute', i.e. $J1 or J$1. The former is fixing only the column part of the ref in place. The latter is only fixing the row ref in place. When you press F4 over and over on your cell ref, it will toggle through all of these varieties and back to a relative ref.
Cells in Excel already have a name - the column-and-row referencing we're all familiar with, such as A1 or J45. Naming cells with your own choice of name has several advantages: 1) you can find that cell or range very quickly; 2) you can use the name in a formula - even if you've named a whole range; 3) you can remember the name of a range a whole heap more easily than you can a cell ref; and 4) you can use a named range to create a hyperlink.
If you create a name, always click somewhere completely different and check it works by following the drop-down from the Name Box. If your name isn't there, you've not created it properly.
Similarly if you see a #NAME? error there's a problem with your named cell/range.
Functions are a fundamental type of formula in Excel. They are SO important! Chances are you know one already - AutoSum (also called SUM or Σ). The most important thing about teaching functions in particular is that there are hundreds of them, all pre-programmed to do specific things, and it's important to let learners find out how they look and behave so they can be self-sufficient and learn from existing uses of functions and from the help file. It's like the difference between teaching someone the alphabet on its own, and teaching someone how to use the alphabet to read.
I start off by asking the students to consider the differences between some
formulae in their spreadsheets already - such as
=A1*B1
=SUM(A1:A10)
Some will spot the words, the brackets, the colon - these are all important. A
colon indicates a range.
I will then explain that the SUM is called a 'function', and will write up on
the board the common elements of all functions, in the following way
= NAME ( arguments )
and say, as I write each element:
Trick: if you type your formulae in lower-case, Excel will convert everything it recognises into capital letters, after you confirm. Therefore if the first of your dedicated function examples is AVERAGE, teach =avg(a1.a3) rather than =AVERAGE(A1:A3). This has three benefits: firstly the avg will throw up the #NAME? error, which is a great way to introduce learners to the importance of getting the right name; secondly it will show them that Excel has recognised their cell refs (the a1 and a3 will have changed to A1 and A3); and lastly it will show them the shortcut for : (colon) which is . (full stop). It's also good practice so you can track down errors very quickly - all the bits that are wrong will be left in lower-case.
You can either teach them to type the whole thing out, or use the AutoSum button. For more advanced functions, use the drop-down next to the AutoSum button (in earlier versions, an fx button). You can also teach the Insert Function tool, which has each argument in a separate panel in the dialog box, but to be honest, that doesn't really teach them to break down and understand functions that have been used in spreadsheets they will encounter in their working life, so is only useful up to a point. You can't use it for nested functions, either.
I run through AVERAGE (making note of how a zero value in the range will skew the answer), MAX, MIN, COUNT and COUNTA. These can all be used with single ranges, though you can introduce the concept of multiple arguments at this point by using the comma to separate - e.g. =MAX(A1:A3) or =MAX(A1,A2,A3).
You can also, at this point, show the quick-access functions in the status bar, which are dead handy.
Next, I move onto simple multi-function arguments, such as COUNTIF and SUMIF. I've seen tutors use PMT and FV for alternatives, which are good because you can modify the arguments. Text functions such as CONCATENATE or LEFT can be used here, but it's trickier than you think.
I always have a section on IF statements (including nesting), and VLOOKUPs, as these are particular types of function which are hard to understand from the Help files alone. Remember that nesting IFs will only go to 7 levels, so beyond that you should be using a lookup.
For me, learning functions took place in two stages - 1) looking at ready-made examples in spreadsheets I used at work that people had already created - learn from someone else's effort! - and 2) once I grew more confident, I was able to know what it was I wanted to achieve, and could browse around to find one that did the trick.
I demonstrate the use of the Help file for learning about functions in this way. For this, go to Insert -> Function and change Most Recently Used to All. This shows a list of all available functions. Allow the users to browse through the list. Giggle at the ones with silly names. Agree that the short syntax and description (in bold, below the main white panel) isn't a busting amount of use, so choose 'Help on this Function' from the bottom left-hand corner of the dialog box. This will bring up the Help file with a detailed description of the function's purpose, syntax and arguments, and a cut'n'pasteable example you can stick into a blank spreadsheet to see it working in action.
From here I tend to show See also which is below the function name at the top of the Help file; this will give a list of related functions which may be more of use. It's handy to know how functions are grouped into Statistical, Text, Date & Time, Math & Trig and so on. You don't have to teach an example from each - you just need to teach syntax and good function 'housekeeping' such as counting brackets, making sure you have all your double-quotes, your commas are in the right place and so on.
You will sometimes find yourself bashing your head against the Excel brick wall. If this is ever the case, walk away - do something else, read a book, ring your mum, chat to your mate in Marketing - anything to take your mind off it! As soon as you return, you will spot that comma out of place, or that missing bracket. C'est la vie! Don't let the stress get to you. You will only ever spot mistakes with fresh eyes. And if you can't 'get out' of the formula because Excel keeps chucking error messages at you, see Note 3 right at the top of the page.
Get someone else to check your formulae, too, if they're particularly important. I spent two hours creating something highly complex, only to discover an hour later (after a great deal of smug playing around) that my 'discount' actually make prices more expensive. Heh. As they say, pride comes before a fall ;-)
What's this? It's something critical that will affect all formulae/functions in Excel.
Write =2+3*4 on the board and invite answers. Most will volunteer '20', occasionally you'll get a '14'. Ask them to enter the formula in Excel and compare the answer (14) with their suggestion (20). Why is Excel giving this? It's not approaching the formulae backwards (good suggestion, though!); eventually someone will twig that it's doing the multiply bit first. Explain about BODMAS, run through the mnemonic together, then ask how we can make =2+3*4 return a value of 20. Ask them to modify their formula accordingly.
The easiest way to teach this is through a quick demo. It's also easiest if you have several workbooks for your students to practise on.
Linking worksheets: use the 'type and click' method rather than getting them to type out the whole 'Sheet1!A1' shebang.
You can do the same for linking workbooks. It's easiest if you open all the workbooks concerned, then arrange them using your Window drop-down. Tiled is usually better than Horizontal. How many will depend on your screen resolution, but for most people using 1024x768 or 1280x1024 a maximum of 4 or 9 tiled is about as far as you can get. In reality, I've only ever had to link 3 together at any one time, anyway.
3D Linking: This is when all of your worksheets are laid out in exactly the same way, with labels and data appearing in the same cells on each sheet. You can then use a function such as SUM.
It can be quite tricky to co-ordinate all this clicking and typing and holding keys down, so give plenty of opportunity to practise. Students always comment on how much more sensible it is to have the 3D linking rather than the first option - good spreadsheet design will conserve a huge amount of effort in the long run. In fact the way I introduce the 3D aspect is by presenting an example of the first type of linking that's so fiddly and so irritating the students can't help but agree that having each sheet exactly the same is the way to go!
You can also use the Paste Link aspect of Paste Special for creating a linking cell.
There's nothing more guaranteed to make people cross than a 'hash error'. They need explaining, and you can either do it as they appear or have a section dedicated to them. I prefer the latter, as not everyone will generate errors at the same time.
You can cover the following quite easily. Have the students enter 'Value' in A1 and 'Message' in B1. In A2 type 30, in A3 type 40.
Others:
Incidentally if you have a formula (such as a LOOKUP) which will return an error in certain circumstances, you may wish to prevent your spreadsheet's users from seeing it and getting in a flap. To get around this, use the ISERROR function, nested in an IF.