Excel online training course

excel lesson 3, Range

Using range

Many of Excels formulas depend on making a reference to a selected group of cells.
A group of selected cells in Excel is called a "Range".
For example:
We can have a column which keeps track of the sales made by our representatives, and add them to sum up a total of all sales.
This is one example of how we can use "Ranges" in Excel.

We can select one Range or mulitple Ranges.
Multiple Ranges can be next to each other, over each other or appart from each other.
When we specify a Range, every Range is identified by a top left cell and a bottom right cell, divided by a colon.
For example: to select a range from C1 to C5, we write in our formula C1:C5.
To select multiple ranges we need to seperate them with a comma.
For example: C1:C5,E1:E5

When only one range is selected a black border will appear around the selected cells.
These selected cells will become black in Excel 97 and blue or purple in XP, 2000 and 2003.
The active cell in the range stays white, but can also be found in the black border.

The range stays selected until you click somewhere in the worksheet or push one of the arrow buttons.

Selecting ranges

You can select a range by using your keyboard, mouse or a combination of both.
To select a Range with your keyboard, you select the first cell, hold down the "Shift" key on your keyboard, and move the cursor with the arrows on your keyboard.
To select a Range with your mouse, you click on the first cell and drag the cursor to the last cell of the Range.
You can also speed up this process a bit by combining these two.
We click on the top left cell, hold down the "Shift" key, and next we click on the bottom right cell.

Selecting non adjacent ranges

You can select multiple non adjacent Ranges by holding down the "Ctrl" key.
This can be usefull when we want to make the text bold in the bottom row and the left row for example.

Inserting data into selected range

Once we selected our Range its easy to insert data.
Our active cell in our Range is white, it means we can enter data into this cell.
We use our "Tab" key to jump to the cell on the right, and we use the "Enter" key to jump to a cell below.
When we hold down the "Shift" key when we use the functions explained above, we will jump in the opposite direction.
Please remember that the cursor will never jump outside the selected Range, once you have reached the utmost right cell the cursor will jump to the next row.
Once you reached the bottom right cell the cursor will jump back to the top left cell.
And this will stay this way as long as the Range is selected.

Using autocomplete

Autocomplete is a usefull tool in Excell which automaticly enters repeating information.
For example:
We can easily make Excel enter the days of the week, months of the year or which ever repeating information.
Enter monday in the first cell, select the autofill button on the bottomright of the cell and drag the cell to the right or down.
Excel will automaticly add the following days of the week.

The same thing can be done for numbers.
For example:
Type 2 into the first cell, 4 in the cell below it, next select both cells and drag the autofiller down, now Excell will automaticly fill the cells with a interval of 2 in the next cells.

If Excel does not recognise the information you have typed, for example the name "John", then Excel will copy that name over the entire Range that you selected with the autofiller.
This can be usefull when we want to have some cells with the same information in it.

In Excel 2003 and XP we can set what we want to have inside the cells when we drag the autofill bar with the right mousebutton.
After you are done with the dragging choose "Series".
A new window will be opened.
Make your choice here and give in some values if you want.
When your done click on "Ok"