UNDERSTANDING WORKBOOKS
Microsoft Excel Fundalmenter
appear somewhere in the worksheet. As a
consequence when the Status Bar shows Ready mode, at least one cell in the worksheet will be
highlighted – this is known as the active cell. In the screen above, the active cell is cell A1 –
notice that the column label and the row label also appears coloured to indicate the active cell.
You can have more than one active cell – when this occurs you have what is known as a range
A workbook (as you would expect) is made up of pages known as worksheets. You can have as
many sheets in a workbook as your computer resources can accommodate. As a default, a new
blank workbook normally has 3 worksheets labelled Sheet1, Sheet2, and Sheet3. Of course
these labels are pretty boring and meaningless and can be changed to something more relevant
The Insert Worksheet button here will insert another worksheet into the current workbook
should you need it
1
2
5
3
4
6
Microsoft Excel
ITTraining@sgul.ac.uk Page 2 St. George’s Information Services
NAVIGATING IN A FILE
Arrow
Keys
Move one cell to the right, left, up or down
Tab Move once cell to the right
Ctrl+Home To beginning file
Ctrl+End To end of typed information
Home Beginning of a line
End End of a line
Page Down Down one screen
Page Up Up one screen
F5 To a specific page
Scroll bars
Appear at the right and on the bottom of the screen. You may click
the scroll arrows, drag the scroll box or click the scroll bar to move
through the document.
Microsoft Excel
ITTraining@sgul.ac.uk Page 3 St. George’s Information Services
TYPING TEXT OR NUMBERS INTO A WORKSHEET
Generally when you start a new spreadsheet
project, the first task is to enter some headings
into rows and
columns. To type anything into a
worksheet you need to make the cell into which
you wish to enter the data active. This can be done
in a number of ways but the most common is to
click in it first before typing.
Try This Yourself:
Before you begin ensure
that there is a blank
workbook on the screen…
Click in cell A3 to make
this the active cell, type
Garden Settings and
press
When you press the
next cell down
automatically becomes
the active cell. By the
way, even though the text
looks like it is in cells A3
and B3 it really only is in
cell A3 – since there is
nothing in B3, Excel
allows the spill over to be
displayed giving the
illusion it is in 2 cells…
Type Pool Covers and
press
Repeat the above steps
and enter the remaining
text in column A as shown
Click in cell B2 to make
this the active cell, type
UK and press
When you press the
cell to the right becomes
the active cell…
Enter the remaining text in
row 2 as shown
enter text:
1. Click the cell pointer on the desired cell and
type the required information
2. Press , an arrow key or to
confirm the data entry and to move the cell
pointer to another cell
You don’t have to use or to make
adjacent cells active. You can simply use the
mouse and click in the cells if you want or
even press the arrow keys to move up,
down, left, or right.
1 2
3
4
5
For Your Reference…
To save a new document:
1. Click on the File Tab and select Save As
2. Locate the storage folder in the Navigation
pane
3. Type a File name and click on [Save]
Handy to Know…
In the exercise above we have named the
workbook Garden Department Sales and
filed it in C:\Course Files for Excel 2010.
Each time you start Excel it will most likely
assume you want to file your workbooks in a
folder called Documents which is associated
with the user name you use on the
computer.
Microsoft Excel
ITTraining@sgul.ac.uk Page 4 St. George’s Information Services
TYPING SIMPLE FORMULAS IN A WORKSHEET
The whole idea behind Excel is to get it to
perform calculations. In order for it to do this you
need to type formulas in the worksheet. Usually
these formulas reference existing numbers, or
even other formulas, already in the worksheet
using the cell addresses of these numbers rather
than the actual value in them. Formulas must be
typed beginning with an equal sign (=).
For Your Reference…
To enter a formula:
1. Click the cell pointer on the desired cell and
type the formula commencing with =
2. Press , an arrow key or to
confirm the data entry and to move the cell
pointer to another cell
Handy to Know…
Operators
+ Addition
- Subtraction
* Multiplication
/ Division
Try This Yourself:
Continue using the previous
file with this exercise...
Click in cell B8 to make this
the active cell
Type =B3+B4+B5+B6+B7 and
examine what is happening on
the screen
Press to enter the formula
and move to the next cell
Notice that a calculation has
now been performed. We have
entered a formula in B8 that
says “add the values in B3, B4,
B5, B6,
and B7 and show them
here”…
Ensure that C8 is the active
cell, type =SUM(C3:C7) and
press
This is an alternative type of
formula known as a “function”.
Again a calculation will appear
in the cell…
Click in cell B8 and notice that
the formula you typed appears
in the Formula Bar, while the
result of the calculation
appears in the worksheet
Repeat step 5 with cell C8
Click on the File Tab
and select Save to save the
additions that have been made
2
3
5
6
column F
Examine each of the series
created by the filling process
For Your Reference…
To fill a series:
1. Click on the first cell in the series
2. Drag from the fill handle across as many
columns as required
Handy to Know…
As you drag the fill handle across, a tool tip
appears below the fill pointer displaying the
current value in the series. This is really
handy when you want to end on a particular
month, day or value.
2
3
6
duplicate
worksheets within a workbook. This technique is
ideal for replicating layouts. For example, if you
have a budget workbook that contains data for
several departments, you can create a worksheet
for the first department and then copy it to create
identical worksheets for other departments.
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or open
the file E1324 Worksheet
Techniques_1.xlsx...
Right-click on Sheet1 to
display the worksheet shortcut
menu
Select Move or Copy to
display the Move or Copy
dialog box
Click on Create a copy so it
appears ticked, then click on
[OK]
The new worksheet is named
Sheet1 (2). Let’s create a
“template” from this worksheet
by deleting unwanted data...
Select the range B7:E9, then
press to clear it
Repeat step 4 to clear the
ranges B14:E23, G7:J9 and
G14:J23, then press +
to return to cell A1
Now we can copy this
“template” to create additional
worksheets...
Repeat steps 1 to 3 three
times to create three copies of
the template worksheet – this
time without data
The final worksheet should be
named Sheet1 (5)
1
2
For Your Reference…
To copy a worksheet:
1. Right-click on the worksheet to copy, then
select Move or Copy
2. Click on Create a copy so it appears ticked
3. Click on [OK]
Handy to Know…
You can copy the current worksheet using
the HOME tab by clicking on Format in the
Cells group, then clicking on Move or Copy
Sheet.
The Before sheet options in the Move or
Copy dialog box allow you to position the
copied worksheet where you want.
3
6
Microsoft Excel
ITTraining@sgul.ac.uk Page 8 St. George’s Information Services
RENAMING A WORKSHEET
By default, Excel names worksheets as Sheet1,
Sheet2, Sheet3, etc. These names are fine if you
are not planning to share the workbook, but
changing these to something more relevant
makes it much easier to understand the purpose of
a worksheet. You can also adjust the horizontal
scroll bar to make room for longer, more
meaningful worksheet names.
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or open
the file E1324 Worksheet
Techniques_2.xlsx...
Point to the vertical dots
between the sheet names and
the horizontal scroll bar, as
shown
The pointer will change to a
double-headed arrow...
Click and drag the bar across
to the right, to the end of
column L, then release the
mouse button
Double-click on Sheet1 (5) to
select the worksheet tab name
This will also place it into edit
mode…
Type Comms, then press
Repeat steps 3 and 4 to
rename the other worksheets:
Sheet1 (4) Admin
Sheet1 (3) Shop
Sheet1 (2) IT
Sheet1 Maintenance
1
3
4
5
contain up to 31
characters including spaces, but it is better to
keep it short and succinct.
Microsoft Excel
ITTraining@sgul.ac.uk Page 9 St. George’s Information Services
MOVING OR COPYING A SHEET TO ANOTHER WORKBOOK
You can copy worksheets to other workbooks as
required. For example, you might need to keep
records for six different divisions – rather than
send each division the entire set of records, you
can copy their worksheet to another workbook and
send them their data only. If worksheets exist in the
other workbook, you will need to determine the
order in which to place the copied worksheet.
Try This Yourself:
Same File
Continue using the
previous file with this
exercise, or open the file
E1324 Worksheet
Techniques_6.xlsx...
Click on the Maintenance
worksheet tab
We’ll copy this completed
data to another workbook...
Right-click on the
worksheet tab to display
the shortcut menu, then
click on Move or Copy to
display the Move or Copy
dialog box
Click on the drop arrow for
To book, then select (new
book)
Click on Create a copy so
it appears ticked
This will create a new
workbook as well as
making a copy of the
worksheet...
Click on [OK]
A new workbook will be
created and Maintenance
will be the only worksheet
in the workbook…
Save the new workbook as
Maintenance.xlsx, then
close it
1
2 4
Techniques_7.xlsx...
Click on the Admin
worksheet tab to select the
worksheet
Right-click on the worksheet
tab to display the shortcut
menu, then point to Tab
colour
This will display a palette of
colour options…
Click on Red under
Standard colours to apply
the colour to the tab
Right-click on the
Maintenance worksheet tab
to display the shortcut menu,
click on Tab colour, then
click on Blue under
Standard colours
Notice how the Admin
worksheet tab colour is now
a solid rather than a
gradient…
Repeat either technique to
apply the following colours:
Shop Yellow
IT Green
Click on the Admin
worksheet tab to view the
results
3
4
5
6
Techniques_8.xlsx...
Click on the Admin
worksheet tab, hold down
, then click on the Shop
worksheet tab to select the
first three worksheets
Click in cell A1 to select the
cell
Click on the HOME tab, then
click on Italics in the Font
group
This will italicise the text in
cell A1 on this and all other
worksheets in the group…
Click on the Maintenance
worksheet tab, then the
Shop worksheet tab to see
that the changes have been
applied here
Click on the IT worksheet
tab to see that the changes
have not been applied to
this worksheet
Since this was not part of
the grouped sheets the
changes have not been
applied here. Notice too that
clicking on a tab deselects
the previous grouping
2
3
4
5
formatting and text changes done on a
worksheet in a group will be applied to other
sheets in that grouping.
Microsoft Excel
ITTraining@sgul.ac.uk Page 12 St. George’s Information Services
FREEZING ROWS AND COLUMNS
When you lay out your data in rows and columns,
it is most likely that your headings end up at the
top or to the left of your data. If you have a large
amount of data, you may find that when you
scroll across or down to particular cells, the
headings scroll out of view. This problem can be
resolved by freezing the rows and/or columns that
hold the headings.
Try This Yourself:
Same
File
Continue using the previous file
with this exercise, or open the file
E1324 Worksheet
Techniques_11.xlsx...
Click on the Maintenance
worksheet tab, then spend a few
moments examining the worksheet
Depending on your screen, it is
possible that you won’t be able to
see all of the figures on the screen
at once...
Click in cell B6 to select the cell
Click on the VIEW tab, click on
Freeze Panes in the Window
group, then select Freeze Panes
Thin black lines appear above and
to the left of the selected cell. This
indicates that the areas above and
to the left are frozen...
Scroll to the right until Yearly
Average in column L appears next
to column A
Scroll down until Overheads in row
25 is below row 5
Press + to move to cell
B6 – this is our temporary home
cell, as the cells above and to the
left are frozen
On the VIEW tab, click on Freeze
Panes in the Freeze Panes group,
then click on Unfreeze Panes to
unfreeze the rows and columns
3
4
RANGES
A contiguous range is any group of selected
cells that form either a square or a rectangle. A
single cell that is selected is also considered to
be a range. Ranges can be selected using the
mouse, the keyboard or a combination of the two.
Once selected, you can use the range for input, or
apply formatting, or copy the cells as required.
Try This Yourself:
Open
File
Before starting this exercise you
MUST open the file E705
Ranges_1.xlsx...
Click on cell B7 to select it
Because it is the only cell
selected it is the active cell…
Hold down the key and
click in cell E10
Even though a range has been
selected, the active cell is B7 – it
appears in a different colour and
its contents appear in the
formula bar. You can keep the
range selected and change the
active cell within the range using
the keyboard…
Press several times and
watch the various cells become
active through the selection
Click in cell B7, hold down the
mouse button, and drag down to
cell C10 before releasing the
mouse
The previous selection has
disappeared and the range B7
to C10 is now selected…
Press and to
deselect the selected cells and
return the cell pointer to cell A1
For Your Reference…
To select ranges with the mouse:
