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.


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.


Displaying Data

Sometimes if a number starts with a negative sign then they are only displayed as a dash if the column width is not wide enough for the whole number. Just F2 the cell or convert it to a formula (prefix with an equals).
If you have used row and column range names to provide a quick way to get parameters into worksheet functions, then "NUM!" is displayed although the correct value is obtained ??


Hiding Data from being Printed

Need to produce a printout where certain cells (not an entire row or column, you could right-click and Hide them) in the print area need to be hidden? One trick is to set the font colour in those cells to white.
Select the cells, (Format > Cells). In the Format Cells dialog box click the Font tab
Click the arrow at the right side of the Color list box and select white from the list
Click OK to close the dialog box and apply your new font colour
Print the worksheet without showing the unwanted cells. After you finish printing, select the cells again and set the colour back to black (or automatic).


Linking a Cell

You can reference a cell in another cell if you copy the cell.
Select the cell you want to link and select (Edit > Paste Special > Paste Link).


Adding Links

You can copy a range of cells and paste a picture onto a worksheet. The picture is then updated automatically with any changes in values or formatting. Hold down the Shift key and select (Edit > Paste Picture Link).


Important

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.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopNext