Leading the way in Microsoft Office Development

Excel

Step 1 - What Can I Enter ?

There are three different types of data that can be entered in cells:

Numerical (right aligned by default) - These are basically numbers or values and includes dates and times which are treated as serial numbers.

Text (left aligned by default) - These are also known as strings or labels. A cell can actually contain as many as 32,000 characters.

Formulas (right aligned by default) - These are formulas which include mathematical operators. All formulas must begin with an equal sign (=).

Everything that is not a number, date, time or formula is considered to be text.

The different types of data are displayed in different positions in your cells.

 Example entering a formula

If you enter more text than can actually be displayed in the cell then the text will either spill over to the adjacent cell (assuming that it is empty) or will be truncated.

When you start typing into a cell two additional buttons will appear on the Formula bar.

 Enter - Accepts the formula or value. This is the same as pressing (Enter).

 Cancel - Removes the formula or cancels your entry.

You can enter data into cells either by using the formula bar or by typing directly into the cells.

When you enter data directly into a cell it will also be displayed in the formula bar.

Step 2 - Entering Numbers

There are a couple of important points to remember when you are entering numbers.

Valid numbers are right aligned by default as opposed to text which is left aligned by default.

Excel only guarantees precision for the first 15 digits (or significant figures).

Any of the following characters can be used to help apply formatting when you are entering numbers: 0-9 , . + - / ( ) % £ $ E e.

The E and the e allow you to express large numbers in a scientific format that is easier to display.

For example 1,234,000,000,000 can be displayed as 1.234E+12.

Step 3 - Entering Text

There may be times when you want a numerical value to be treated as text data.

If you want to store numbers that have more than 15 digits you must format them as text.

Numbers that have been formatted as text cannot be used in formulas and functions.

This can be achieved by preceding the value with a single quote ( ' ) mark.

This single quote mark is an alignment character and tells Excel that the following is text and should be left aligned.

This is exactly the same as formatting a cell containing a value using (Format > Cells)(Number tab) and selecting Text in the Category list.

If you want to enter more than 15 significant figures in your numbers, then they must be entered as text.

Step 4 - Entering data using the Formula Bar

Every time you select a cell the contents are displayed in the formula bar.

Once the contents are displayed you can use normal editing techniques combined with the mouse to select and highlight individual words.

To select a single word you can double click on it with the left mouse button.

You can then either type over it or press the Delete key to remove it.

When you have finished entering your data you can either press the green tick button to confirm the contents or you can press (Enter).

If you want to insert a formula into the cell that contains a function you can use the function button to the immediate left of the formula bar to display the (Insert > Function) dialog box.

 Formula bar displayed at the top

Step 5 - Entering data directly into a Cell

It is possible to enter data directly into a cell without using the formula bar. You must have your (Tools > Options)(Edit tab, Edit directly in cell) checkbox ticked for this to be possible.

You can Double click with the left mouse button while hovering over a cell to edit the contents directly. This will place the cursor at that particular point within the cell contents.

Alternatively you can press (F2) and this will place the cursor (or insertion point) at the end of the value or text string.

Pressing Enter or using the mouse to select another cell will confirm to contents or you can press Escape to cancel the changes.

Remember that once you press Escape the data you entered will be lost. This entry will not appear on the (Edit > Undo) drop-down list.

Excel will allow 1024 characters on a line ??

Step 6 - Entering data directly into a Block of Cells

It can be useful to highlight the cells before you enter data, that way you can use the Enter key to move to the next cell in the selection instead of using the arrow keys to navigate.

To quickly enter data into a range of cells, highlight the cells first.

(Enter) - Moves to the cell below the active cell in the selection or to the top of the next column.

(Shift + Enter) - Moves to the cell above the active cell in the selection or to the bottom of the previous column.

(Tab) - Moves to the cell on the right of the active cell in the next column in the selection or to the start of the next row.

(Shift + Tab) - Moves to the cell on the left of the active cell in the previous column in the selection or to the end of the previous row.

 Example entering data into a block of cells

You can enter the same value into a range of cells by highlighting the cells, entering the value into the first cell and pressing (Ctrl + Enter).

You can quickly copy the contents of the active cell to the selected range by pressing F2 and then (Ctrl + Enter).

Step 7 - Editing data

Your changes can be made either using the formula bar or editing the cells directly and you can change between the two methods at any time.

The easiest way to edit the contents of cells is to do it directly in the cell.

Assuming you have your (Tools > Options)(Edit tab, Edit directly in cell) checkbox ticked you can double click a cell with the left mouse button or you can press F2.

Pressing F2 will place an insertion point at the end of the cell's contents.

Double clicking with the right mouse button will place the insertion point at that particular point within the cells' contents.

If your "Edit directly in cell" option is not ticked you will only be able to edit using the formula bar.

You can use the Backspace and Delete keys to remove characters and make corrections.

If you change your mind after you have made some changes to a cell you can either press Escape to cancel the changes.

Remember that once you press Escape the data you entered will be lost. This entry will not appear on the (Edit > Undo) drop-down list.

Step 8 - Clearing data

You can easily remove the contents of a cell by selecting it and pressing the Delete key.

Using the Delete key only removes the contents of the cells and does not remove any formatting.

Never use the spacebar to clear cells as it actually adds a space to the cell which is then also impossible to find later.

Alternatively you can select (Edit > Clear) to display a sub menu of possible items to remove.

 (Edit > Clear) sub menu

All - The contents, the formatting and any cell comments are all removed.

Formats - Removes just the formats from the selected cells, leaving the contents and comments the same.

Contents - Removes just the contents from the selected cells, leaving the formats and comments the same. This is the same as pressing the Delete key.

Comments - Removes just the comments, leaving the formats and contents the same.

An alternative and sometimes a quicker method of removing formatting is to drag and drop an adjacent cell which has no formatting applied.

Step 9 - Shortcut Keys

(F2) - Edits the active cell, putting the cursor at the end.

(Ctrl + Enter) - Fills the selection with the current value.

(Alt + Enter) - Starts a new line within the active cell.

(Esc) - Cancels the cell entry and restores the original contents.

(Ctrl + Alt + Tab) - Inserts / increments the number of indents used in the active cell.

(Ctrl + ' ) - Enters the formula from the cell directly above into the active cell.

(Ctrl + Shift + 2) - Enters the value from the cell directly above into the active cell.

Step 10 - Things to Remember

If the cell displays "#######" then this means that the column is not wide enough to display the entire number. Just increase the width of the column by selecting (Format > Column > AutoFit Selection). The default setting in Excel is to resize your columns automatically when the number is too large.

If you enter any fraction into a cell they could be interpreted as dates. To avoid this, always precede a fraction with a zero and a space (e.g. 0 1/2)

Be very careful if you use the Precision As Displayed option on the Calculation tab of the (Tools > Options) dialog box. This changes all the underlying values in your worksheet to be the same as their display values. This will change values permanently from having 15 significant figures to whatever format is displayed. This cannot be undone.

You can actually prevent duplicate data or incorrect data from being entered. For more information please refer to the Data Validation section.

It is possible to enter the same data simultaneously into multiple sheets. Just select all the worksheets first.