Need a course on using 'Excel'? If you're enrolled at Bath Spa University a computer based training course is available to you from any networked pc which has sound available. Get in touch, or my notes on this to see how to get yourself going. Also, try this set of online tutorials from Datapig technologies.
Here's a chapter from O'Reilly on pivot tables.
Once you're using Excel, it's worth looking beyond the basics, read on:
These are actually simple. To set up a spreadsheet calculation, you go to a cell and set up a formula. Formulae will contain cell references - pointers from one cell to another. When you've set up a formula, you'll often need to copy it to other cells. When you copy a formula, you can set the cell reference(s) to behave in one of two ways, and both have their uses:
Mark absolute cell references with a dollar sign like this $B$6. Relative ones like this: B6. Once you're happy with absolute and relative cell references, you'll spot that there are two other sorts too: $b6 and b$6 and you'll be able to work out what those do for yourself.
In a formula, change an absolute cell reference to a relative one (and vice versa) as follows: select the cell reference and then hit 'F4' to toggle between - four taps will take you round the complete cycle and include those 'Partial' cases mentioned above ...
To keep the labels at the head of a column of figures in view:
This works in the 'Vertical' direction too: select a column and choose 'Window/Freeze panes and everything to the left of the column stays put.
You need the menu 'File/Page setup' for this. Select that and then in the panel, select the 'Sheet' tab. One of the controls there is 'Rows to repeat at top'. For a single row containing your column headings, just type $1:$1 - or use the 'Cells selection tool' on the right hand end of the control's text field to select the rows that you need.
You might need to copy the values produced by a formula and paste those figures elsewhere. Copy as normal and then choose 'Paste special' from the menus - choose 'Values' from the list that appears.
If you're the sort of person who freezes in terror when you meet either of these, 'unfreeze' yourself and continue ...
If you've two figures on your Excel spreadsheet and you need to know one figure as a percentage of another, you need a formula. Select a new cell and type an 'equals' sign into it.
Excel's now expecting you to build a formula in that cell by selecting other cells in the spreadsheet. You're going to divide your two numbers, one by the other, proceed as follows:
If you left the *100 component out of that process, simply format the cell itself to display its contents as a percentage - use Excel's 'Format/cell' menu for this.
You're finished, but look at the result to check that it's sane - you may have done the calculation 'Upside down' in which case start again but reverse the order of the two figures ...
To calculate the average of several figures, add them and divide by the number of figures. You're done. But do a sanity check on the result in case you aren't. The 'Average' of 2,3,5,6 isn't going to be 16, is it?!
To swap or rotate data between columns and rows, you need to 'Transpose' the data. It's often good to start by creating a worksheet to receive the result. Then:
If you have a spreadsheet with text in the wrong case - and here are the options:
Convert the text case using a formula. One way to do this is:
Let's take this step by step
Spreadsheets are powerful tools - 'Excel' is very capable, and simple tasks are straightforward. Don't be discouraged if things don't work for you at first though - it's easy to tackle too much at once. Aside from a course in spreadsheet work, you'll find right mouse clicks useful to you in discovering good ways to perform tasks.
Statisticians find Excel very capable, but with a few alarming twists. Here's a resource on 'Excel' and statistics from the University of Reading - a 'Google' search for 'excel statistics' reveals many more resources.
If you can supply a useful tip for this page, tell me and I'll add it. If you've come here for something and haven't found it, tell me too and I'll find something to help.
All before you, in this world, is smoke and shadows.All before you, in this world, is smoke and shadows.
Words found on a door lintel in the garden of a house in the Cretan village of Argiroupolis.
The lintel is a fragment from the city state Lappa, which occupied the same site.
Fifteen hundred years later, when you use the web, from time to time you might well feel that the author was on to something. And if you work with particle physics, you'll know he was ...