SpreadSheeting Cheat Sheet

This cheat sheet is written for MS Excel 2000 but can be applied to most spreadsheeting packages running in a Windows environment. This guide intended to help users become familiar with only the basics of spreadsheeting.

What is a Spreadsheet?

Spreadsheets are used to organize numeric data, like budgets, financial statements, grade sheets, and sales records. They are able to perform calculations and can also be used to display data in graphs and charts.

Key Features

A spreadsheet consists of workbooks that contain worksheets. In the worksheet there are grids of cells organised in rows and columns. Rows go across and columns go up and down the screen. You enter data into cells, this data can be either text, numeric or formula. Each cell is referenced by its column and row labels (e.g. C2 represents the cell in column C and row 2).

Starting Up

Double-click on the MS Excel 2000 icon. Use the File menu to do the following operations on documents (files) - create a New file, Open an existing file, Close a file, Save a file.

Finishing Up

Choose Exit from the File menu. You will be asked to save the file, giving it a name, if you have not already done so. See Saving, Naming and Retrieving Files below.

To Open a Document

Choose Open from the File menu. You will see a screen showing the current directory and a list of files in that directory. To find a particular file, you have to select the correct drive in the Drives box, and also to select the appropriate directory by clicking on the folder icons in the box above the Drives box. To list all files in a directory, you can type *.* in the File Name box. You then scroll through the files listed until you have found the one you want.

Saving, Naming and Retrieving Files

When you save your files, you need to specify where on your floppy or hard disk so that they can be found again. Just as you need to file documents in a filing cabinet in such a way that you can retrieve them. When you are saving your file on the computer you start by checking that the disk drive is correctly allocated i.e. A: (for your floppy disk) and C: (normally) for the hard disk in your computer. This is equivalent to specifying the correct filing cabinet. You then move on to specifying into which directory you will save the file, i.e. into which drawer in the filing cabinet. Finally, when you are saving a file for the first time, or renaming a file, you will be required to give the file a name. To save a file for the first time, choose Save from the File menu.

To rename a file, select Save As from the File menu. Check that you have the correct drive and directory. Try and give your file a name that is meaningful now and later. Files are classified by three letter extensions. The default extension for MS Excel 2000 files is .xls. This is automatically added if you do not assign an extension to your Excel file. The Save command stores all changes made to the file. It is good practice to save frequently to make sure you do not accidentally lose your work, e.g. through a power failure.

Entering Data

In a spreadsheet data is entered into cells, one at a time. Using your left mouse button click in the cell that you want your data in, and then type your data. Once you have finished entering the data in that cell, press either the <tab> key (takes you to cell on left), the <enter> key (takes you to the cell below) or one of the arrow keys. You are now ready to enter data into another cell.

Moving Around

There are a number of ways to move around in a spreadsheet. You can use your mouse in and click in the cell that you wish to go to. You can also use one or more of the following key combinations:

adds the contents of cells C1 and C2. To enter a formula, click in the cell in which you want to enter the formula. Type '=' (an equal sign). Enter the formula, and press <enter>. You will find more information on the available functions and how to use them by using the Help menu.

Copying

Rather than type the same formulas (or data) over and over again it is possible to copy them. Note when copying formulas, the copied formula will change to reflect relative cells i.e. if the formula in cell A5 is =sum(A1:A4) when copied to B5 will become =sum(B1:B4).

To copy cells, highlight the cells to be copied. From the Edit menu, choose Copy, or use the copy button on the toolbar. Click on the cell where the first copied cell is to be pasted and then from the Edit menu, choose Paste or the paste button on the toolbar. The cells will be copied.

To copy one cell to more than one place, highlight the cell to be pasted. Choose Copy, then highlight the area that this is to be pasted to and choose Paste.

The Fill command allows you to fill up, down, left or right. Highlight the cell to copy and at the same time highlight the cells (up, down, left or right) to copy to. Choose Fill from the Edit menu and then choose the fill direction.

Note: If you need to use the value in one particular cell for many different cells you need to refer to it as an absolute reference and describe it in formula as $A$1.

Formatting Data

Information in cells can be formatted in a number of ways, this includes the type of data (i.e. number, currency, text, date etc), alignment, font and borders.

To format cells, select the relevant cells by clicking in the first cell with your left mouse button and while holding the button down drag your cursor over the appropriate cells.

Choose Cells from the Format menu. Then select the appropriate format type i.e. number, alignment etc. You can format by using the format toolbar (e.g. currency, increase/decrease decimals, borders), but there are more options available in the Format menu.

Further Help

The Help menu has an index that allows you to search for guidance on a specific topic.