Moving inside the spreadsheet with only keys
There are several ways to move inside an Excel spreadsheet. You can always
click on the worksheet to the cell or the worksheet that you need. Here is the list of keys
that you can use to move without using the mouse.
Arrow keys |
To move to a cell towards the left, right,
up or down. |
CTRL + Arrow keys or
END + Arrow keys
|
To move from one end of a range of cells to another |
Enter |
To accept the text, the number or the formula that was entered in the
cell. |
Tab |
Moves the cursor a cell to the cell on the right of the active cell. |
Shift + Tab |
Moves the cursor a cell to the left of the active cell. |
Home |
Move the cursor to the first column of the row. |
Ctrl + Home |
Return the active cell to the upper left corner of the current
worksheet. |
PageUp and PageDn |
To move a screen up or down. |
Alt + PageUp and Alt + PageDn |
To move of one screen to the left or to the right. |
Ctrl + PageUp andCTRL + PageDn |
To move to the previous or following worksheet. |
Ctrl + End |
To move in the last cell containing text, a number or a formula. |
F5 orCTRL + T |
To move directly to any cell of the file. |
Blocks
There will be times when you will want to select several cells at one time
in order to change the formatting, to create charts or to take advantage
many other possibilities offered by Excel. The software offers you at least three
ways to select a range of cells.
But before starting, you must pay attention to the shape of the cursor.
The cursor changes shape according to the place where it's located on the worksheet
on even inside a cell. To select cells, the cursor must look like a
big white "+" like this .
You can't make a block when the pointer is in the shape of an arrow or any other shape.
However, you can move a range of cells using that format. Place the pointer on
the border of the cell or of a range of cells. Press and hold the left mouse button and move it to it's new location. You can also copy the content of a
cell if you place the pointer on the dot at the button right corner of the active
cell. The pointer changes to the format of a thin black "+". Using this format,
you can copy the contents of the cell or the range of cells vertically or
horizontally. So, before selecting a range of cells, always make sure that the
cursor is in this format .
1-Use the mouse.
To select a range of cells by using the mouse.
Place
the cursor over the first cell of your block.
Press and hold the left mouse button, move the cursor up to the last cell of
your block.
Release the mouse button.
This is practical when the range of cells is relatively small. But what to
do when you need to make a range of cells that's bigger than the screen? It's
in this situation that using the mouse with the Shift key is more practical.
2-Use the Shift key.
Place
the cursor over the first cell of your block.
Press
the left mouse button.
Move
the cursor over the last cell of your block.
Press
and hold the Shift key and click the last cell of the block.
By doing a block this way, you don't have to keep a finger on the mouse button.
It avoids overshooting the width of the block that you wanted and to go back and fourth until you select the right size.
There is a last method that you should know. Both first possibilities offer
you the possibility to make a continuous block. All the cells between both extremities
of the block will be selected. But what can you do when the cells
that you require are not next to each other? The last method demonstrates how you can make
many blocks of cells with cells that are located everywhere in the worksheet.
3-Use theCTRL key.
Make
your first range of cells by using the mouse or with the Shift key.
Press
and hold theCTRL key and make your second range of cells.
If you
need the other blocks of cells, keep your finger on theCTRL key and
use the mouse to make as many blocks as you need.
This last technique is very usefulare not located next to eachother. You can use it for example to select blocks
that will be the source for a chart. But, it that last box, every block must
represent at least a series of data for the chart, not just a lonely cell.
Data entry
There are three types of data that you can enter into a cell : text, numbers
and formulas. The other objects, such as charts, drawings and maps, "float"
over cells They don't affect the cells under it. To demonstrate the many way
of entering your data, follow the following exercise:
Open
Excel with a new worksheet.
Click
on the A1 cell.
Enter 1 and press the Enter key.
The active cell moved to the A2 cell that's directly below A1.
Enter
the text following "To the right" and press the Tab key or Tabulation key (next to the the Q key to the left of on the first row of keys).
The active cell moved to the right of the last cell which is B2. The
combination of the Shift and Tab keys would move the active cell
back to the left.
Enter
the formula =A1+5 and press the down cursor on your keyboard.
You can also enter data and use the cursors to quickly pass to another cell
and enter more data. The active cell should be the B3 cell.

Place
the cursor on the formula bar.
Enter 4 and press the button
or press the Enter key.
As just you discovered, you may enter the contents of a cell by using
the Enter, Tab and cursors keys and the formula bar.
Formulas
One of the biggest advantages of a spreadsheet is the automation of the calculations.
By entering a formula instead of a number, it's easier to change the model and
get the wanted results faster. Formulas should be used in every time it's possible.
You use a spreadsheet to analyze and interpret the results. Leave the task of
crunching the numbers to the spreadsheet.
Every formulas begins with the +, - or = sign. An Excel
functions always begin with a =.
Ex: =a1+a2 , =sum(a1:z20) , =average(a1:z20) , =vlookup(a1,B1:c20,2)
A mistake that's often committed by beginners using an Excel functions is
to place a space between the name of the function and the first bracket. Ex:
=sum () instead of =sum(). The name of the function and it's first bracket must
be glued together: no spaces. Also, some functions need more than one argument or information to work properly such as the =vlookup function above. A comma is used to separate these parameters.
To see the list of all the Excel functions, depending on the version of Excel that you have.
Press the down arrow beside the AutoSum button .
Select the More Functions option.
OR
From the Insert menu, select the Function option.
It's possible to make a formula that uses the contents of cells that are in
a different worksheet or even a different file! To do this, you must specify the name of the worksheet
with the cell address. An exclamation point (!) is used to separate the
name of the worksheet from the cell address.
Ex: =a1+sheet2!A1 , =sum(sheet1!A1:sheet3!A1)
To really take advantage of a spreadsheet, you must be able to use numbers
coming from other worksheets and even from other files. The next part consists
in explaining how to reach the contents of cells in other worksheets and then
from other spreadsheets. If the file does not contain at least three worksheets,
you can add them by using from the Insert menu the Worksheet option.
Numbers from another worksheet
From
the first worksheet, enter 100 in the A1 cell.
Click on the tab of the second worksheet.
OR
Press
theCTRL and the PgDn (Page Down) keys.
In
this worksheet, enter 200 in the A1 cell.
And now for the formula. It consists in adding the contents of the A1 cells from the first two worksheets of the file into the third worksheet.
Click
on the tab of the third worksheet.
In
the A1 cell, enter the = sign.
This is the start of the formula. It's now necessary to select the needed cells
in the formula.
Click
on the tab of the first worksheet (Sheet1).
From
the first worksheet, Click on the A1 cell.
Press
the key +.
Click
on the tab of the second worksheet (Sheet2).
On
this worksheet, Click on the A1 cell.
Press
the Enter key.
OR
In
the A1 cell of the third worksheet, enter the following formula: =sheet1!
A1+sheet2! A1.
The formula is complete. The result should be 300. In fact, to reach any cell
of the file that's not on the present worksheet, you must first write the name
of the worksheet followed by the exclamation mark and the then address of the
cell. Ex: =Sheet1! A1. There is another way of arriving at the same result.
From
the third worksheet, place the cursor in the A2 cell.
In
the A2 cell, enter the beginning of the formula =sum(
Click
on the tab of the first worksheet (Sheet1).
Click
on the A1 cell.
Press
and hold the Shift key, click the tab of the second worksheet.
To
end the formula, close the bracket ) and press the Enter key.
OR
Enter
the following formula: =sum(sheet1:sheet2! A1).
To
see if the formula works, change the value of the A1 cell of the first
worksheet from 100 to 300.
The new result of the A1 and A2 cells of the third worksheet
should be 500.
All the functions can be also accomplished in "3D". That is by using the
content of cells of other worksheets of the file.
Numbers from another worksheet
Imagine the possibilities if you could also use numbers coming from the other
files! It's possible. This exercise demonstrates how to do it.
Open
a new file.
Enter 100 the A1 cell.
Save
the file under the name test1.xls.
Do
not close the file.
The content of the first file is completed. The second file will come
to look for this number in a few moments. It's easier to carry out this operation
when both files are opened. Excel can open up to 99 files at the same time.
To pass from a file to another, go to the Window menu and to choose the required
file.
From
the File menu, select the New option.
From the Task pane, select
the Blank workbook option.
Press
the OK button.
OR
From
the standard toolbar, press the button.
Place
the cursor in the A1cell.
Press
the key =.
From
the Window menu, select the Test1 file.
OR
For
those using Excel 2000 or a later version, click on the Test1 file on
the taskbar at the bottom of the screen.
Click on the cell that you need.
For
this exercise, select the A1 cell.
Press the Enter key.
OR
Press
the button of
the formula bar.
The formula should be =[ test1.xls ]Sheet1!$A$1.The name
of the file is between brackets, followed by the name of the worksheet, an
exclamation mark and the cell address.
Save
the file under the name Test2.xls.
Close
the Test1.xls and test2.xls files.
Open
once again the Test2.xls file.

Every time you'll open this file, it's going to ask you is you wish to verify
and update the references of the other files. For this example, it's about the
contents of the Test1.xls file. To update the file, you must press the Yes button.
Otherwise, the file will use the same data from the last update.
You must also pay attention where you put the "linked" file. If you
delete it, or move it or rename the reference file, Excel will not be able to
find it.
If you moved or renamed the file, you will have to indicate to Excel the new
location or the new name of the file. After you selected to correct file, press
the OK button to update the link.




You'll find more information about some Excel functions on the
Web page on functions and formulas on this site.
Edit a cell's content
Instead of having to rewrite the contents of a cell, you can change it's contents
to correct "typos" or the formula. There are three ways to change
a cell's content.
You must first place the cursor in the cell to be changed. Then,
Press
the F2 key.
OR
Double-click in the cell to be changed
OR
Click
in the formula bar to change the contents.
The worksheets
It's on the worksheets that you can write down the text, the numbers, the formulas
and the other elements of our model. It's possible that you want to distribute
your model on to several worksheets. Every worksheet has a name that you will
find at the bottom of the screen. By default, they are called Sheet1 , Sheet2
. This next part is going you to demonstrate how to add worksheets, to rename
them, to move them and even to copy a complete worksheet.
Add a worksheet
Even by placing your numbers on the worksheets that are shown, it's very possible
that you need more worksheets. A spreadsheet can contain up to 100 worksheets.
From
the Insert menu, select the Sheet option.
OR
Place
the cursor on the tab of one of the worksheets.
Press
the right mouse button.
From
the context menu, select the Insert option.
Delete a worksheet
For aesthetic needs or any other reason, it's always possible to remove a worksheet
of the file.
Place
the cursor on the worksheet that you want to remove.
From
the Edit menu, select the Delete a sheet option.
Confirm
the deletion by pressing on the OK button.
Rename a worksheet
It's much more practical to help you to find of the information you need when
the names of worksheets are more familiar to you such as "Budget" or "Forecasts"
instead of Sheet1. There are various ways to rename a worksheet.
From
the Format menu, select the options Sheet and Rename.
OR
Double-click
on the tab of the worksheet.
OR
Place
the cursor over the tab of the worksheet.
Press
on the right mouse button
From
the context menu, select the Rename option.
Move a worksheet
Excel offers you also the possibility to quickly move a worksheet in your spreadsheet.
You can move them between other worksheets.
Place
the cursor over the tab of the worksheet to be moved.
Press
and hold the left mouse button, move the tab towards the left or the
right according to your needs.
A small black arrow will indicate you the point of insertion if you
Release the mouse button at this moment.
Copy a worksheet
Excel allows you also to copy all the contents of a working sheet with
a variation of the last technique.
Place
the cursor over the tab of the worksheet to be copied.
While pressing on theCTRL key and on the left mouse button,
move the new worksheet in the wished place.
Copy the contents without creating a new worksheet
Click
the gray box of the upper left corner of the worksheet; between the " A " and
" 1 " headers.
From
the Edit menu, select the Copy option.
Place
the cursor on the worksheet on that you want to copy the data.
Place
the cursor in the cell where you want to insert the data.
From
the Edit menu, select the Paste option.
Split the screen
This option splits the screen into several windows. This is very practical
when you want to compare data that's distant from one to another. For example,
you want to compare this month's numbers with last year's or the forecast
for next year. However, there is twelve months worth of data separates the two. The next
exercise consists of showing you how to put two cells next to another by splitting
the screen.
Open
a new file or place the cursor on a new worksheet.
Place
the cursor in the D6 cell.
From
the Windows menu, select the Split option.

The worksheet is now separated into four parts. It's also possible to separate
the screen only vertically or horizontally. Just above the vertical bar and
to the right of the horizontal bar you
will find the divider bars. You can place them in any place on the worksheet.
You can also change the place any time or even to remove them. Just place the
cursor on the divider bar, press and hold the left mouse button, and "pull"
it in its new location.
Press
the F6 key several times.
The F6 key serves for moving from one area to another. If you press
it several times, you'll notice that the cursor moves in a clockwise fashion.
The Shift and F6 keys "turns" the cursor counter clockwise. Zones
move in a synchronized way as you move horizontally or vertically.
To remove the divider bars
From
the Windows menu, select the Delete the dividers option.
OR
Place
the cursor on the divider bar.
Press and hold the left mouse button, move the bar towards one of the borders
of the screen.
OR
Double-click
on the divider bar to be removed.
Freeze a part of the screen
On a worksheet, it's important to have titles that describe the numbers. It's
important to see these titles at all times. After all, the numbers are used
to describe something. The Excel designers, as well as the other spreadsheet
programs available on the market, recognized this fact. The option to freeze
part of the screen allows you to always have certain area visible on the screen.

Write
the text in the appropriate cells.
Place
the cursor in the B2 cell.
From
the Window menu, select the Freeze shutters option.
Press
several times the PgDn (PageDown) key.
Although you moved on the worksheet, the text that you wrote above and to the
left of the place that you fixed the shutters is always visible. To return to
a normal page.
From
the Windows menu, select the Release shutters option.
|