Microsoft Excel in one page

| Minerva | BSU Gmail | WMS | Liquid Office | Training | The Hub | Phone Book | 

Finding your way about

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:

Toolbar mysteries

Excel's toolbars work in the same way as those from 'Word'. Follow that link for an overview. Similarly, you can influence the way Office's menus behave for you.

Absolute and relative cell references

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:

Absolute cell references

These will fetch the contents of a particular cell. If you copy the formula to another cell, they'll still fetch the contents of the same cell as before. Use them for things like the following: you might store the VAT rate or Plancks constant in one cell on your spreadsheet, and use absolute cell references to fetch that constant for your calculations. If the VAT rate should change, you can change just that one instance of it and know that your calculations will include the change. (If Plancks constant changes, be very alarmed of course. If you're still there ...)

Relative cell references

Put one of these in a formula, and when you copy the formula to another cell, the cell(s) from which the formula fetches data changes too. So if you have a column of figures, and next to the first cell you put a formula to multiply by three, you can copy that formula to the cells below, and create a series of relative cell references to automatically 'Point' to your original column of figures and create a 'Times table'.

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 ...

Freeze Panes

To keep the labels at the head of a column of figures in view:

• Select the entire row beneath the labels by clicking its number at its left hand end
• Use Excel's menu 'Window/Freeze Panes'
• You'll find you can now scroll your figures, but keep the labels at the heads of columns in sight.

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.

Printing: print column titles on each page

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.

Pasting figures, not formulae

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.

Excel, Calculating Percentages, Averages ...

If you're the sort of person who freezes in terror when you meet either of these, 'unfreeze' yourself and continue ...

Percentages

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:

1. Having selected the new cell and typed the '=' sign to put Excel into 'Formula building mode', select the cell with your first figure in it with a mouse click. That puts the cell reference into your formula.
   
   
2. Type a '*' and then 100 - to multiply your first figure by 100
   
   
3. Type a '/' to tell Excel that you're dividing the result of (2) by your second figure ...
   
   
4. ... and select the cell with your second figure in it and press the 'Enter' key to move on
   
   
5. The cell should now display one of those figures as a percentage of the other.
   
   

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 ...

Averages

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?!

Transposing columns and rows

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:

• Select the area containing your data
• Use the menu 'Edit/Copy'
• Then use the menu 'Edit/Paste special', and check the 'transpose' box ...

Converting Text Case

If you have a spreadsheet with text in the wrong case - and here are the options:

• UPPER CASE
• lower case
• Proper Case

Convert the text case using a formula. One way to do this is:

• Create a second worksheet
• Set up a formula to take the contents of the first worksheet to the second one, doing the conversion on the way.

Let's take this step by step

Open your workbook and create a new worksheet

Use Excel's menu 'Insert/Worksheet' for this, or if there was a spare worksheet created when you set up the workbook, use that.

Name the worksheets

Select a worksheet's name in the worksheet tab, right mouse click it and choose 'Rename' ...

Inspect your original data

It's likely that you'll not want to convert the entire worksheet, perhaps just a block of cells that contain the data who's case you need to alter.

Add a formula to a cell in the second worksheet to replicate the data in the first

Choose a cell who's location corresponds to a cell with data in the first. The formula you need is of the form "=FirstWorksheetName!D57", where the first worksheet is called ...

Edit the formula so it changes the data as desired

• "=Addresses!D57" copies the cell data from cell D57 from a worksheet called 'Addresses' without touching it ...
• "=UPPER(Addresses!D57)" converts lower to upper case
• "=LOWER(Addresses!D57)" converts upper to lower case
• "=PROPER(Addresses!D57)" converts either to lower case with capitals

Copy the formula to cover the cell range with your conversion data

Excel should now be taking the data from the first worksheet and copying it to the second, altering its case as it goes ...

Other problems

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.

Excel for statisticians

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.

Need more?

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.

| Top | Home Page