Other Excel Add-In, Templates and Training
Lesson 1D
Worksheets
All Workbooks
must contain at least one Worksheet. The maximum number of Worksheets a Workbook
can contain is limited only by the available memory of the PC. As a default,
Excel places three blank Worksheets in front of you. Each one of these has the
default name Sheet1, Sheet2, Sheet3 at the bottom. By default all
Worksheets are identical in that they contain Rows, Columns and
Cells (all discussed below).
NOTE: You can
change the default of three blank Worksheets by going to Tools>Options/General
and changing Sheets in new workbook: by typing in the number you
require.
All Worksheets
contains 256 columns and 65,536 rows. Each separate rectangle within the
Worksheet is called a Cell. You will notice letters across the top of
your Worksheet. These are the names of the columns and go from A through
to IV. The numbers down the left hand side of your sheet of graph paper
symbolise row headings and go from 1 through to 65,536.
Inserting
Worksheets
Adding new
Worksheets to a Workbook can be done in a number of ways. The most common
are:
There is another
way that is not exactly inserting a new Worksheet, but rather making a copy of
an existing Worksheet.
To do this,
select the Sheet you want to copy then left click on the name tab and holding
down the Ctrl key (bottom left hand side of your keyboard), then drag it
to one side, then release. This is a great time saver when you have spent hours
setting up a Worksheet with all the formatting you want. You will notice that
Excel will place a number after the copied sheets name in brackets. This is
because we cannot have two Worksheets of the same name in the same Workbook.
Name and Renaming Worksheets is covered later.
Deleting Worksheets
The logical
opposite to inserting Worksheets is Deleting them. This can be done in any of
the following ways.
-
Right click on
the Worksheets name tab and select Delete
-
Go to
Edit>Delete..... the Delete dialogue box will appear with
options, select your option and click OK to verify your request.
Whichever method
we use, Excel will display a warning message letting us know that the deletion
cannot be undone. This is not technically true as we could always close our
Workbook WITHOUT saving and then re-open the Workbook, but of course this means
we would loose any other changes we had made.
Naming Worksheets
We can also name
our Worksheets with meaningful names (which is a good idea!). The only
restrictions we have with Worksheet names is:
-
They cannot
exceed 31 characters.
-
They cannot
contain the following characters: \ / ? * [ ].
-
The name cannot
be the same as another Worksheet in the Workbook.
While it is not
totally necessary, try to AVOID using spaces between words. For example, if you
wanted to name a Worksheet Bills Hours use BillsHours instead! The
reason for this is that some of Excels formulas have slight problems when they
reference Worksheets that contain spaces.
To name a
Worksheet we can do so with any of the most methods below:
-
Double click
the Worksheet Name tab. This will allow you to now type the name. Once
finished, push Enter or click any cell.
-
Right click on
the Worksheet Name tab and select Rename from the shortcut menu.
Then type the name and hit Enter or click somewhere else on your
Worksheet.
-
Go to
Format>Sheet>Rename type the chosen name and hit Enter or
click somewhere else on your Worksheet.
Moving Worksheets
At times we may
need or wish to move a Worksheet to another position in the Workbook or even to
another Workbook. We have already looked at how we can make a copy of a
Worksheet, select the Sheet you want to copy then left click on the name tab and
holding down the Ctrl key drag it to one side, then release. If we
do not want to copy but rather move the entire Worksheet to another
position in the Workbook we can simply left click the Worksheet Name tab, then
holding down the mouse button drag it to it's new position and release.
The other method is to use the Move or the Copy dialogue box. This
can be accessed in two ways.
It makes no
difference which method you use! You will see on this dialogue box a To
book: box with a small drop arrow to the right. The selection in this box
will determine where our sheet will be moved or copied to. The default
name in this box will be the active Workbook name (the current Workbook we are
in). There will also always be another choice, if we click the drop arrow.
This is (new book), if we choose this option Excel will automatically
create a new Workbook for the sheet to be moved or copied to. Within this box
will also be the names of any other open Workbooks, should there be any.
Directly below
the To book: box is the Before sheet: box. Our selection in this
box will decide which position our sheet will be moved or copied to. Last of all
is the Create a copy checkbox. If we check this box Excel will
create a copy of our moved Worksheet. In other words our original sheet will
remain where it is and a copy of it will be moved to our chosen location.
If we leave it unchecked (which is the default) the Worksheet will be moved and
not copied. The best way (as with most of excel) is to jump straight in and try
out different options - you cannot do any harm!
Let’s now have a
look at the cells on our Worksheet in more detail.
Cells
These are without
doubt the very backbone of Excel. On each Worksheet there are 16777216 cells.
These are divided into 65,536 Rows and 256 Columns, as I mentioned earlier.
The number of Rows and Columns in a Worksheet are fixed, which means we cannot
add more and we cannot take any away, so as a consequence the number of cells
are also fixed. This should never become a problem, as we can add a lot of
Worksheets if we need to, each with 16777216 cells, so this should give us
plenty! A cell is defined as the intersection of where a column and row
meet, just like the coordinates on a road map.
The method that
Excel uses to reference these cells (as a default) is called the A1 style
reference. When we refer to a particular cell we use it's cell Address (or
location) to nominate the cell. The method Excel uses to reference cells is a
very simple, but highly effective method, often referred to as the grid pattern
method, exactly the same as a road map.
Click in cell C6.
It should be defined as your active cell, or the cell in which you are clicked,
by the border of the cell or rectangle being different in some way, either
bolded, or maybe a different colour. Now have a look at the column heading
letter. It should be defined in some way, either bolded, or highlighted in
a colour, it may even look pushed in like a button. Now look at the row
number on the left of your screen which should also be defined in some way.
This is telling you your cell reference. Now look above the column heading
A and you will see the reference C6 in what is known as the Name box
(discussed below).
In the situation
above we have selected one cell only. We can select many more cells at one time
if we wish. When we do so, we have selected what is known as Range of cells. A
range can be one cell, or many cells.
To select a range
of cells we simply select any one cell (position the cursor so a cross appears),
then holding down our left mouse button, drag in the direction we wish to
select. It is important to note here that although we have more than one cell
selected (a range) there is only one Active cell and this will always be
the first cell we select. There is no limit to the number of cells we can select
on a Worksheet.
To select all
cells on your worksheet either push Ctrl + A or the Select all button, (the
empty grey rectangle in the upper-left corner of a Worksheet where the row and
column headings meet). We can also select entire columns or rows by simply left
clicking on the column letter or row number.
We mentioned
above while looking at Toolbars that there is another shortcut menu in Excel,
the right click menu. The right click menu is also known as the
context-sensitive menu. This means that it will display options that are
sensitive to the area in which you are clicked. To see this simply select
any cell and (you guessed it!) right click. You will see a list of common
options available in the area you are clicked in. We won't go into any detail on
these at this stage but we most certainly will in later lessons.
While we are on
the subject of the right click, you will notice as we go into Excel deeper that
it has many of these right click menus. As stated above, the menu that we are
presented with is wholly dependent on the area of Excel we are in at the time.
Moving About the Worksheet
There are many
ways that can be used to move around a Worksheet and the method used is purely
user preference.
At the far right
of your Worksheet window is what is known as a Vertical scroll bar.
This bar has an upward pointing arrow at the top of it and a downward pointing
arrow at the bottom of it. You can vertically scroll up and down through
your document in any one of the common following ways (there are more!):
-
Place your left
mouse button on the downward (or upwards) pointing arrow and click once to
scroll slowly through your document. This will move you down one row at a
time.
-
Hold the left
mouse button down on the downward (or upwards) pointing arrow to move more
quickly through the document
-
Hold your left
mouse button down and drag the grey sliding button between the black upward
and downwards arrows. Notice when you select the last option a yellow box with
the row number will appear. This is because you will not move to the location
until you release the mouse button.
-
Select any cell
and then drag down. You may notice that doing this will display a small yellow
box that informs you of how many rows you have selected and how many columns.
This will only appear after the active cell has scrolled from view. You can
also do the same by selecting any cell and holding down the Down arrow on your
keyboard while holding down your Shift key.
-
Use the Page Up
and Page Down keys on your keyboard.
To get quickly
back to the top of your Worksheet, push Ctrl + Home. This will always
take you to cell A1.
At the bottom of
your Worksheet window to the right is your Horizontal scroll Bar. The use
of this is the same as with the Vertical scroll bar, except of course you move
horizontally and you cannot use Page Up and Page Down.
If you happen to
know the address of the cell you wish to go to we can use the Go To
dialogue box.
What has popped
up in front of your now is the Go To dialogue box. Note that your cursor
is flashing in the Reference section of the box. This is where you type
the cell reference that you wish to Go to. The big box (Go To) is where Excel
stores the last references used. If the reference is in here you would simply
select it and click OK
Type in HS3156
in the Reference box and select OK (or hit Enter on your keyboard
if you prefer). Excel will take us straight to the cell reference we typed.
To get back to
the top left of the Worksheet, push Ctrl + Home. The Ctrl + Home method
is simply a short cut key for typing A1 in the Reference box of
the Go To dialog box.
While the Go
To dialogue box can be handy for moving straight to any cell, there is an
easier way! To see what I mean left click in the Name box (left of
the Formula bar), type any cell address and push Enter.
You are no doubt
wondering why this is called the Name box? We will cover this in detail later, but not
in this lesson.
Moving Through the Worksheets
The only area of
navigation we need to look at now is the Worksheets themselves. This can be done
in one of two ways. The most popular and probably the simplest method is to
simply left click on the sheet name tab of the sheet you want. This will
automatically activate the appropriate sheet.
The second method
is probably best suited to when you have a lot of worksheets in the same
Workbook.
You will notice
to the far left of the sheet name tabs, you have four arrows. Place your mouse
pointer over any one of these and then right click. You will see a pop up menu
containing the names of all the Worksheets within the Workbook. You simply
select the one you want with your left mouse button. The four arrows to the left
of the sheet name tab are called Tab Scrolling Buttons. These four arrow
buttons are to allow you to scroll through the Worksheet name tabs should your
workbook contain more Worksheets than are currently visible. If you click the
arrow with the vertical line after it, you will scroll so that your far right
hand Worksheet name tab is visible. If you click the arrow without the vertical
line, you will basically scroll one Worksheet tab at a time.
Exiting
There are two
ways to exit Microsoft Excel and close the program down. These are:
Select either of
these options to close down Excel. If we wished to Exit Excel and we were
working in a Workbook that we have made changes to, Excel will ask us (via a
message box) if we wish to save the changes we have made. If we wished to Exit
Excel and we had a Workbook that we had not made any changes to, no message will
appear and Excel will Exit without saving.
Summary
So in summary, we
have discussed the three most important aspects of Excel, these are:
-
The Workbook
-
The Worksheets
and
-
The cells
We have looked at
the definition of each of these aspects in detail. We have also covered:
We have looked at
the many ways of moving through the cells within your Worksheet, using a
multitude of options, including the Go To option located under the Edit menu
item.
We have looked at
our Excel screen in detail, the toolbars and how to dock them and the formula
bar. We learnt how to
get help on a specific menu item by using the Shift + F1 option. And finally, we
learnt how to start and Exit Excel in the correct way.
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
|
