Computing Skills

MSExcel - a quick guide to basic functions

To get the best out of this guide, open MSExcel and practise using the functions as you go. The notes are written with respect to Macintosh. There may be slight variation in functions if you are using a PC.

Details on using Excel for statistical functions can be found here.

Setting up a spreadsheet

Open Excel by double-clicking on the Excel program or using the pulldown Microsoft Office menu. A file called Untitled will appear and you can begin typing. (If you wish to work on an existing Excel file, double-click on the filename and Excel will open automatically.) The file is referred to as a Workbook. The columns and rows in the Workbook are identified by letters and numbers respectively and the space where a row and a column intersect is called a cell (e.g. A1 is the cell in the first row and first column). (On a PC this may be referred to as R1C1 - row 1, column 1.)

Entering and editing data

To enter data, click on the cell you wish to use. When you begin typing in a cell, what you type appears in the formula bar at the top of the page. When you push the Enter or Return key, what you have typed goes into the cell. If you have entered a formula, the result of the formula is displayed in the cell.

If you are preparing a table, leave a few rows at the top of the table for a title then set up appropriate column and row headings before entering your data.

To edit the contents of a cell, click on the cell, position the cursor in the formula bar at the top of the page and type. You can highlight a section of the text in a cell and delete or type over the text. To delete the entire contents of a cell, click on the cell and hit the delete key.

Each cell can contain one of three types of information: text (words), values (numbers, data), or formulas. Formulas allow you to perform calculations on the values entered in other cells. The computer decides if you are typing text, a value or a formula, based on what you type.

  • If you type letters, or a mixture of numbers and letters, the computer concludes that you are typing text and left justifies it.
  • If you type a number, the computer interprets it as a number and right justifies it. If you enter a date like 2/6/99 in a cell, the program thinks that you will always want to put a date in that position. If you decide to put an ordinary number like 325 in that cell instead, the computer still tries to format it as a date. To change the format to an ordinary number, use Format, Cells, Number (see below).
  • If you start with an = sign or + sign or - sign, the computer thinks you are typing a formula and tries to evaluate it.
    (N.B. If you want to type a dash (-) in a cell, type a space then the dash, otherwise the program will interpret the dash as the start of a formula and will insert the cell number of the next cell you click on.)

Workbooks and worksheets

A single Excel file of data is called a workbook. This can contain a number of worksheets, which you can view by clicking on the tabs at the bottom of the workbook. When you create a chart, you can choose to have it displayed in a new worksheet. It will be saved with the original data table when you save the file.

Oops

If you accidentally delete something, you can retrieve it by selecting Edit, Undo Clear. In later versions of MSExcel, it is possible to undo every change since your last Save, using the Undo button (blue curved arrow) on the Toolbar.

Column width and row height

Adjust the size of a column or a row by placing the cursor on the right or bottom edge in the spreadsheet frame (where the letters and numbers defining cells are). Drag the edge to give the desired size. Alternatively, highlight the row(s) or column(s) and use Format, Column, Width or Format, Row, Height and type in a value. (Note: the numbers in these windows have a meaning only known to Excel and they do not mean the same thing for height and width. A bit of trial and error will get you to the right size.) You can set the size of many columns or rows by highlighting them all and using the Format menu. Autofit will automatically fit the width or height to fit what you type in a cell.

Formatting cells

To change the formatting of a cell, click in the cell then select Format, Cells from the pulldown menu. Alternatively, for some formatting commands you can click on the appropriate button in the toolbar (e.g. B for Bold). You can change the format of many cells at the same time by highlighting all the cells (or highlighting an entire column or row). (Click once on the column letter or row number on the spreadsheet border to highlight a column or row.)

Some of the functions described below are available as buttons on the Formatting toolbar at the top of the page. If this toolbar is not shown, select View, Toolbars, Formatting from the pulldown menu.

Text

You can change the: font (e.g. Times New Roman, Geneva, Script, Helvetica);
size (e.g. 12 point, 14 point, 16 point);
style (e.g. Bold, Italic, Underlined)

using Format, Cells from the pulldown menu and clicking on the Font folder.

(Warning: If you are used to using Cmd-B or Alt-B for Bold in MSWord, don’t use it in MSExcel as here it means Clear and it will delete the entire contents of selected cells!)

Values

Use Format, Cells and click on the Number folder to change the number of decimal places in values, or choose a certain date, time or currency format. (If Number format is left as General, a calculated value will display many decimal places.)

You can cause a number to be displayed exactly as typed by selecting Format, Cells, Number and choosing Text. This allows leading zeros (as in Northern Territory postcodes) to be displayed. (In most number formats a leading zero is not displayed.)

Borders

When preparing a spreadsheet that will be printed as a table, put borders around appropriate cells. You can do this automatically with the borders button on the formatting toolbar or by selecting Format, Cells, Borders and clicking on the border style and then onto the cell template in the Borders window.

Alignment

You can change the vertical (top, bottom, centred) or horizontal (left, right, centred) alignment of the cell contents. You can merge the contents of adjacent cells, or "word wrap" the contents of a cell, so that text is confined to the width of the cell - the height of the cell (and the row it is in) is adjusted to fit. Read through the appropriate sections of the Help menu for details on all these functions.

A useful formatting function for organising your text is the Page break, which allows you to choose where in your text a new page begins.

Calculations (Formulas)

If you want the spreadsheet to perform a calculation you must begin with an = sign. If you don't, the computer interprets the combination of numbers, letters and symbols as text.

Formulas can contain numbers, cell addresses, mathematical operations, logarithmic and trigonometric functions, and a host of other functions. The mathematical operations include addition (+), subtraction (-), multiplication (*), division (/), and powers (^). (Use the number keypad at the right of the keyboard. For a power symbol (^) hit Shift - 6, using the number at the top of the keyboard.)

excel sample

Formulas can contain numbers, cell addresses, mathematical operations, logarithmic and trigonometric functions, and a host of other functions. The mathematical operations include addition (+), subtraction (-), multiplication (*), division (/), and powers (^). (Use the number keypad at the right of the keyboard. For a power symbol (^) hit Shift - 6, using the number at the top of the keyboard.)

e.g. Say you want the program to calculate the square of the difference between the numbers in two other cells. Type =, followed by (. Click on the first number, type -, click on the second number, then type )^2, then click on the tick next to the formula bar.

i.e. In cell C1, type =(A1-B1)^2, and when you click on the tick, the answer will be displayed in cell C1. If you have columns of data on which you want to perform the same function, you can copy and paste cell C1 down column C and the program will change the formula to suit the cells in the appropriate row. (It will also adjust your calculators if you insert or delete rows.)

Experiment with these functions by creating the following spreadsheet and insert appropriate calculators to produce values in columns 2, 3 and 4.
Note: To type cm2, type cm2, highlight the 2 then select
Format, Cells, Font, Superscript.

Surface area:volume ratio

Cube side length
(cm)

Surface Area
= (side length)^2 x 6
(cm2)

Volume
= (side length)^3
(cm3)

S.A./Volume

1

     

2

     

3

     

Show Me Quicktime Movie (160Kb)
Have a go (MS Excel required)

Some other functions available are listed in the table below. Please note that function names and cell addresses are not case-sensitive.

Examples of Excel Functions

Function

Example

Result

=sum

=sum(B1:D1)

Sums the values in row 1, Colum B to D

=average

=average(C3:C8)

Displays the average (mean) of non-empty cells in column C, row 3 to 8)

=count

=count(F5:F9)

Displays the number of cells containing values in column F, row 5 to 9

=counta

=counta(F5:K5)

Displays the number of cells containing values or text in row 5, columns F to K

=max

=max(C5:Z5)

Displays the maximum value in the given range

=min

=min(D4:D100)

Displays the minimum value in the given range

=abs

=abs(H2)

Displays the absolute value of the value in cell H2

=sqrt

=sqrt(A1)

Displays the square root of the value in cell A1

Using the Help menu

With a bit of searching the Excel Help menu will eventually provide an explanation and example of any particular function. Use the pulldown Help menu, select Contents and Index and browse for the topic you want or type in the topic you need. Click on Show Topics then double-click on the appropriate topic in the lower box (if the topic you want is highlighted you can simply click on the Goto button). Alternatively, you can choose Microsoft Excel Help, type a question in the box, then select Search. You will be given a choice of index items that (hopefully!) match your query. When you click on a topic, a page of information will appear. Highlighted words on the page are links to other related topics. To close the Help window and return to your spreadsheet, click in the box at the top left of the Help window.

If you merely want to find a particular function, use Insert, Function. You can narrow the list of functions by choosing a Category (e.g. Statistical, Financial, Logical). Functions are displayed in alphabetical order. When one is selected, it is pasted into your spreadsheet and you need only change the cell ranges.