Leading the way in Microsoft Office Development

Excel

Identifying Cells & Ranges

The location of a cell on a worksheet is given by its column letter and its row number (e.g. "A1"). This is often referred to as the Cell Address.

All the cells on a worksheet are separated by grey lines, known as gridlines and a group of adjacent cells is known as a Range of cells.

There are three types of cell reference operator that can be used to identify cells and ranges. The colon, comma and the space.

Colon ( : ) - This is used to define a range of cells (e.g. "A1:D4"). A range of cells is described in terms of the top left cell and the bottom right cell references separated by a colon.

Comma ( , ) - This is used to define non adjacent cells (e.g. "A1", "B2"). The comma is also known as the union operator.

Space ( ) - This is used to combine the intersection of two blocks of cells (e.g. "A1:D4 B2:C3"). When you use this operator the two blocks of cells must overlap. If not then an error message will appear. The space is also known as the intersection operator.

Multiple cell ranges are commonly known as non adjacent or non contiguous ranges. These are ranges of cells that are not in a nice rectangular block.

Entering data

You can enter three different types of data into cells. This is sometimes expanded to four as some people class Dates & Times as a separate type.

Numerical - These are basically numbers or values. This includes dates and times which are treated as serial numbers. This allows dates and times to be manipulated within formulas such as adding and subtracting dates.

Text - These are basically words or any kind of text data. These are also known as labels or strings. Text entries are considered to be labels because you cannot perform any calculations on them.

Formulas - These all start with an equal sign (=) and allow you to perform mathematical operations.

If you select a cell and start typing you will over-write the contents of that cell.

You can enter (and edit) the contents of cells either by using the formula bar or by using the cell directly.

If you are not able to edit the contents in the cells directly then check your (Tools > Options)(Edit tab, "Edit directly in cell").

Selecting Data

Before you can enter data into a cell you must select the cell first.

There can be a number of cells selected on a worksheet at any one time but there will only ever be one Active Cell at any one time.

The active cell is always surrounded by a black border.

The address of the active cell is always displayed in the "Name Box" to the left of the Formula bar.

When a range of cells is selected, only the upper left cell is considered to be the active cell.

If a range of cells in selected then the active cell is identified as white.

To deselect a cell or range of cells click anywhere outside the range or press any of the arrow keys.

There are a large number of shortcut keys that can help you to select data and probably the most useful is (Ctrl + Shift + 8) which selects the current region.

There is a full list of all the shortcut keys that can be used for selecting data in the Shortcut Keys section.

Moving data

Whenever you copy cells, Excel stores the data in a temporary memory called the clipboard.

The data will stay there until it is replaced by some other data. It will only get replaced with a subsequent Cut or Copy command.

The shortcut keys (Ctrl + C), (Ctrl + X) and (Ctrl + V) correspond to copying, cutting and pasting data respectively and are consistent with other applications.

You can alternatively just press Enter to paste the current entry from the clipboard.

Sorting data

There are a number of ways you can sort and rearrange your data.

You can sort by rows or columns into ascending and descending order.

When you sort by rows the columns remain in the same order and the rows are rearranged.

You can define your own custom sorting sequence regardless of their alphabetical sequence.

It is important to remember that numerical values are sorted ahead of text values.

There are four custom lists that exist by default and these contain days and months. If you sort by a column that contains entries from one of these four custom lists then they will be automatically sorted into the correct chronological order (i.e. Mon, Tue, Wed or Jan, Feb, Mar, etc).

Deleting data

When you want to erase the contents of a cell, use the (Edit > Clear) menu rather than deleting the actual cells.

Alternatively you can press the Delete key.

This will ensure your formulas and number formatting remains intact.

Shortcut Keys

(Ctrl + C) - Copies the current selection to the clipboard (Edit > Copy).

(Ctrl + X) - Cuts the current selection to the clipboard (Edit > Cut).

(Ctrl + V) - Pastes the entry on the clipboard (Edit > Paste). An alternative to this is just to press Enter.

(F4) - Repeats the last action.

Things to Remember

Excel treats numbers and text differently. Values are automatically aligned on the right and displayed in the general number format. Text is automatically aligned on the left.

Copying cells will copy their values, formulas, formatting and comments.

Cells cannot contain more than 32,000 characters.