Excel Basics

What is a spreadsheet?

A spreadsheet is the computer equivalent of a paper ledger sheet. It consists of a grid made from columns and rows. It can make number manipulation easier.

Spreadsheets are made up of columns and rows, and their intersections are called cells. We usually refer to each cell by its Column Letter and Row number. Will you now create new spreadsheet with content as shown below?

What data to put into each cell?

There are 3 basic types of data that can be entered:

· Labels – text with no numerical values; e.g. values in Cells A2, A4:A6 above

· Constants – a constant numerical value; e.g. data in Cells B1:C6 above

· Formula – a mathematical equate used for calculations; e.g. Cells D2, D4:D6 above. Note that all formula begins with = (the equal sign). Formula may even involve functions (eg. SUM()).

Copying Formulas

Sometimes when we enter a formula, we need to repeat the same formula for many different cells. In Excel we can use the copy and paste command. The cell locations in the formula are pasted relative to the position we copy them from.

Copy the formula in Cell D2 to D1. What do you get in D1? Why?

Copy the formula in Cell D2 to E1. What do you get E1? Why?

Copy the formula in Cell D4 to D3. What do you get in D3? Why?

What are relative, mixed and absolute cell references?

C2, B3, and C4 in formulas above are relative cell references. Relative cell references are basic cell references that adjust and change when copied.

Sometimes Other times it is necessary to keep the column or the row position (but not both) relative to the new cell location. You do so by placing a $ before the column letter
(i.e. keep the column letter the same) or a $ before the row number (keep the row number the same), but not both. This is called Mixed Reference.

Sometimes it is necessary to keep a certain position that is not relative to the new cell location. This is possible by inserting a $ before the column letter and a $ before the row number. This is called Absolute Reference.

Copy the formula in Cell D6 to D3. What do you get in D3? Why?

Formatting text and numbers

We can dress up a spreadsheet by formatting the text and numbers in its cells.

Select the cell (or group of cells) that we wish to change the formatting and click Ctrl + 1. Notice the Format Cells dialog is shown:

You can use this dialog box to change the number type, alignment, font, etc.

Will you change the format of Cells B1:C6 to Currency with 2 decimal points?

Adjusting column width/row height

If you want to make column wider/narrower, simply move the arrow to the right side of the column label and click and drag the mouse to the right (to make wider) or left (to make narrower.) Let up on the mouse button when the column is wide enough. Or double-click to adjust the width to be just wide enough to accommodate all data content in the column.

NOTE: Sometimes you see ####### in a cell/cells. This just means the column is not wide enough to show the entire number. Simply make it wider.

How about making column narrower so that only ‘ABSOLUTE’ (but not ‘Reference’) is shown?

Next, make it just wide enough so that ‘ABSOLUTE Reference’ can be seen.

Likewise, you can adjust the vertical height of a row by moving the lower edge of the row title (number).

Inserting a column/row

If you need to insert a column into an existing spreadsheet, click on the column label (letter) and choose in Columns from the Insert menu. This will insert a column immediately left of the selected column.

Likewise, click on the row label (number) and choose the Row from the Insert menu. Again this will insert a row before the row you have selected.

Try to add 2 rows before the row that shows ‘ABSOLUTE Reference …’