LIS 505 - Microsoft Excel introduction

Microsoft Excel is a widely used and powerful spreadsheet package. It normally creates .xls files, called workbooks, each of which can contain multiple related spreadsheets. Simple spreadsheet packages, like the one included in Microsoft Works, save each spreadsheet in a separate file. By default, Excel starts with an empty workbook containing three spreadsheets. In the lab exercise, you work with only one spreadsheet (Sheet1), although you will notice that the other two (Sheet2 and Sheet3) are also there.

Within a sheet, cells are identified by column and row; each column is identified by a letter code, and each row is identified by a number code.

A cell can be empty or contain a text string, a number, or a formula. Normally, Excel tries to recognize which is which by the contents of what you enter; for example, a string of numerals is treated as a number. You can control this by selecting "Cells" from the "Format" menu and selecting a different category. Doing this after entering the data can sometims have somewhat bizarre results; for example, if you type in 31 Dec 03 and then change the category to Text, you end up with 37986.

Formulas all start with the equals sign ("="). A cell that contains a formula normally displays the value of the formula. To see the formula itself, you can select the cell and look at the Formula Bar above the spreadsheet. Changing the category of the cell to Text has no immediate effect (the value is still displayed); but, if you type the formula in again, it will be displayed exactly as typed.

You can chooose to have certain cells locked while others are unlocked. This can be useful for protecting against making unwanted changes, for example, to formulas, while allowing changes to other values, such as budget amounts.

You have some control over the appearance of cells, including alignment, borders, fonts, and shading. For example, you can turn text in a heading row to an angle to make it easier to read. You have no control over formatting of individual parts of a cell's text, however; for example, you could not specify that one of two words was to be in italics and another not.

Split panes are useful for larger spreadsheets, since they allow you to continue to see the row or column headings even when you scroll far down or to the right.

There are a few options that allow part of a spreadsheet to act like a small database table; the most commonly used of these is probably the sorting option.

Excel can be used for a number of common statistical operations. Simple descriptive statistical functions such as median() are quite easy to apply. Inferential statistical operations, such as doing a chi-square test, require a bit more setting up.

You can generate simple charts, such as bar charts, fairly easily using the Chart Wizard. Once created, however, a chart will usually need some modification; for example, the default colors may not show up well in black and white and substituting patterns may be advisable, or you may want to change the default gray background to something more suitable, such as white. If you think a particular combination will prove useful in future, you can save it for later use as a user-defined chart.

Excel is less good at producing more sophisticated kinds of charts. It is capable of making a simple scatterplot, a kind of chart sometimes used to show correlation between two variables (for example, scientists' number of publications and rating of their work by colleagues). On the other hand, it cannot make a labeled scatterplot (for example, add the names of the individual scientists).


Last updated August 26, 2003.
This page maintained by Prof. Tim Craven
E-mail (text/plain only):
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7