2
Session Two: Doing Useful
Work with Excel
Only those who have the patience to do simple things perfectly ever
acquire the skill to do difficult things easily.
Unknown authors
Now that you’ve mastered the basics you are ready to do really useful
work with this amazing tool. In this session you will learn to use all of
Excel’s basic features properly. This will put you way ahead of anybody
that hasn’t been formally trained in Excel best practice. You’ll be doing
simple things, but you’ll be doing them perfectly!
Even after years of daily use many users are unable to properly use
Excel’s fundamental features. They often reach their goal, but get there in
a very inefficient way simply because they were never taught how to do
things correctly. By the end of this session you’ll be astonished with how
well you are working with Excel. What originally seemed like a baffling
array of little colored buttons will suddenly all begin to make sense.
Session Objectives
By the end of this session you will be able to:
Enter text and numbers into a worksheet
Create a new workbook and view two workbooks at the same time
Use AutoSum to quickly calculate totals
Select a range of cells and understand Smart Tags
Enter data into a range and copy data across a range
Select adjacent and non‐adjacent rows and columns
Select non‐contiguous cell ranges and view summary information
Re‐size rows and columns
Use AutoSum to sum a non‐contiguous range
Use AutoSum to quickly calculate averages
Create your own formulas
Resize a column and create functions using Formula AutoComplete
Use AutoFill for text and numeric series
Use AutoFill to adjust formulas
Use AutoFill Options
Speed up your AutoFills and create a custom fill series
Use the zoom control
Print out a worksheet
© 2008 The Smart Method Ltd
57
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-1: Enter text and
numbers into a worksheet
Excel beginners tend to reach for the mouse far too often. One of the keys
to productivity with Excel is to avoid using the mouse when entering
data. In this lesson we’ll quickly populate a worksheet without using the
mouse at all.
1
Open the Sample file: First Quarter Sales and Profit.
2
Notice the difference between values and text.
tip
Entering numbers as
text
Sometimes you need Excel to
recognize a number as text.
Cells can contain values or text. Values can be numbers, dates or
formulas (more on formulas later).
If you type an apostrophe (‘)
first, Excel won’t display the
apostrophe but will format the
cell as text. You’ll notice that
the number is then left justified
to reflect this.
Excel usually does a great job of recognizing when there are values
in a cell and when there is text. The giveaway is that text is always
(by default) left aligned in the cell and values are right aligned.
Look at the numbers on this worksheet. Notice how they are all
right aligned. This lets you know that Excel has correctly
recognized them as values and will happily perform mathematical
operations using them.
When a number is formatted as
text you cannot perform any
mathematical calculation with it.
3
Save a value into a cell.
1.
note
Type the value 42000 into cell B5. Notice that the mouse cursor
is still flashing in the cell.
Very few Excel users will make
use of the Cancel
and Enter
At this stage the value has not been saved into the cell.
buttons on the formula
If you change your mind, you can still undo the value by
pressing the key at the top left of your keyboard or by
bar.
The Enter button is the only
method that will save a value
into a cell without moving the
cursor away from the cell.
clicking the Cancel button
Bar.
2.
This is very useful during macro
recording – an expert skill that is
outside the scope of this book
but is covered in the Excel Expert
Skills book in this series.
58
Decide that you want to keep this value in the cell by either
pressing the , or an key on the
keyboard, or by clicking the Confirm button
Formula Bar.
4
First Quarter Sales and
Profit
on left hand side of the Formula
on the
Enter a column of data without using the mouse.
When you enter data into a column there’s no need to use the
mouse. Press the key after each entry and the active cell
moves to the cell beneath. Try this now with the following January
sales data:
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
important
1.
Type 18,000 into cell B6
2.
Press the key to move to cell B7.
The many ways of
entering numbers
3.
Do the same to enter the relevant values into the next two cells.
Negative numbers
‐123.56
(123.56)
Currency prefixes
Excel is quite happy for you to
prefix a number with a currency
symbol. The currency symbol it
will accept depends upon how
you defined the regional options
in your operating system.
5
Enter a row of data without using the mouse.
When you enter a row of data you also don’t have to use the
mouse.
$123.56 (works in USA)
£123.56 (works in UK)
1.
Click in Cell C5.
2.
Type 39,000 and then press the key on your keyboard.
The Tab key is on the left hand side of the keyboard above the
key. Notice how pressing the key saves the
value into the cell and then moves one cell to the right.
See Lesson 4‐3: Format numbers
using built‐in number formats for
an easy method of setting $, £
and € currency prefixes
whatever your locale.
3.
Type 43,000 in cell D5 and press the key.
Commas
4.
You magically move to cell C6 as Excel assumes you want to
begin entering the next row.
12,234,567.78
Fractions
6
Complete the table without using the mouse.
By using the or key in the right place you should be
able to complete the table now without using the mouse:
You must leave a space between
a number and a fraction for this
to work.
6 1/4
(six, space, one, /, four)
The above will appear on the
worksheet as 6.25 after you
enter it in this way.
0 1/4
(zero, space, one,/,four)
The above will appear on the
worksheet as 0.25. The leading
zero is needed to prevent Excel
from thinking that you are
entering a date.
7
8
Change the text in cell B3 to January.
1.
Double‐click cell B3. Notice that there is now a flashing cursor
in the cell.
2.
Type uary on the keyboard to change Jan to January.
3.
Press the key.
Change the text in cell B3 back to Jan using the formula bar.
Click once in cell B3 and then change the text in the formula bar
(see sidebar).
9
© 2008 The Smart Method Ltd
Save your work as First Quarter Sales and Profit-2.
59
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-2: Create a new
workbook and view two
workbooks at the same time
1
tip
The easiest way to create a new workbook is to simply open Excel.
Excel helpfully creates a workbook, unimaginatively named
Book1. If you already have a workbook open called Book1, the new
workbook will be called Book2... and so on.
Other way of creating
a new workbook
Use the keyboard shortcut
+.
Add a button to the Quick
Access Toolbar.
Create a new workbook by opening Excel
Open Excel now and see this in action. Notice that Book1 – Microsoft
Excel is displayed on the Title Bar.
See more details of how this is
done in: Lesson 1‐10: Customize
the Quick Access Toolbar and
preview the printout.
note
What are templates?
The vast majority of users
know nothing about templates
and simply base every
worksheet upon the Blank
Workbook default template
supplied by Microsoft.
2
Create another new workbook.
You wouldn’t want to have to open and close Excel every time you
needed a new workbook.
1.
Click the Office button
at the top left of the screen and
click the New button on the dialog:
2.
The New Workbook dialog is displayed:
3.
Double‐Click the Blank Workbook template. A new blank
workbook called Book2 is displayed in the workbook window.
The Blank Workbook template
has no information in the
worksheet itself, but contains
all of the Excel Option settings
such as the default font size
and type.
Templates can also contain
anything that a worksheet can
contain and are used to store
worksheet frameworks to give
you a flying start when you
find that you often create very
similar worksheet layouts.
If you explore the New
Workbook dialog a little more
you’ll see that there are
hundreds of pre‐built templates
provided by Microsoft.
Later in this book, in
Lesson 3 12: Create a template
you’ll learn how to create your
own template library to
personalize the appearance of
your workbooks.
60
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
You could be forgiven for thinking that nothing has happened but
you can see that the Title Bar now says: Book2 – Microsoft Excel,
showing that you are now looking at a different workbook.
note
Finding a workbook
when many are open
An alternative way to quickly
find a workbook when many
are open is to click
ViewWindowSwitch
Windows.
See the What are templates sidebar for more information about
templates.
3
Use the buttons on the taskbar to move between workbooks.
You’ll see two buttons on the taskbar at the bottom of the screen:
This presents you with a list of
all open workbooks.
Click on the buttons to show each workbook in the worksheet
window. The only difference you will see is the Title Bar changing
from Book1 to Book2 because both workbooks are empty.
See the sidebar for other methods of switching windows.
4
Show both Book1 and Book2 in the worksheet window at the
same time.
This skill often draws a gasp of amazement from my students.
Believe it or not, most Excel users don’t know how to do this.
You can also use the
+ keyboard
shortcut to cycle through all
open workbooks.
1.
Click ViewWindowArrange All. The Arrange Windows
dialog is displayed.
2.
Choose the Horizontal arrangement and click the OK button.
Both workbooks are now shown within the workbook window:
Notice that as you click each workbook window the title bar lights
up and the Close/Minimize/Restore Down buttons appear, to show
that this is the active workbook.
5
Close Book2 and Maximize Book1 to restore the display to a
single workbook.
If you’ve forgotten how to do this, refer back to: Lesson 1‐3:
Understand the Application and Workbook windows.
© 2008 The Smart Method Ltd
61
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-3: Use AutoSum to
quickly calculate totals
anecdote
I ran an Excel course for a small
company in London a couple of
years ago.
The boss had sent his two office
staff to learn a little more about
Excel.
Before the course began I asked
the delegates how long they had
been using Excel. They told me
that they’d been using it for two
years to do all of their office
reports.
Excel’s AutoSum feature is a really useful and fast way to add the values
in a range of cells together.
1
Open First Quarter Sales and Profit-2 from your sample files
folder (if it isn’t already open).
2
In cell A9 Type the word Total followed by the key.
The cursor moves to the right and is now in cell B9:
3
Click HomeEditing
(this is the Autosum button).
When I showed them AutoSum
they gasped in delight. “This
will save us hours” they told
me.
I was curious how they had
been doing their reports before.
Believe it or not, they had been
adding up all of the figures in
each column with a calculator
and then manually typing the
totals at the bottom of each
column.
In this case the boss had given
them Excel as he had heard it
was very good. Unfortunately
he had not initially seen the
need to train the staff in its use.
Something interesting has happened to the worksheet:
With no training it seemed quite
logical to them to use it like a
word processor and the boss
had still been delighted that his
staff were using such impressive
technology.
Excel has placed a marquee around the number range that
AutoSum has guessed we want to work with. The pattern of dots
that marks the boundary of the marquee is called the marching ants
(that really is the technical term for them)!
The marching ants surround all of the numbers in the column
above, up to the first blank cell or text cell (in this case, up to the
word Jan).
First Quarter Sales and
Profit-2
62
=Sum(B4:B8) is your first glimpse of an Excel Formula. Formulas
always begin with an equals sign. This formula is using the SUM
function to compute the Sum (or total) of the values in cells B4 to
B8.
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
tip
4
Press the key or click the AutoSum button
more to display the total January sales:
5
Type the word Total in cell E3 and press the key
once.
once
Entering an AutoSum
using only the
keyboard
You can also execute an
AutoSum using the keyboard
shortcut:
+.
The cursor moves down one row and is now in cell E4.
6
Use AutoSum to calculate sales for January..
1.
Click HomeEditingAutoSum.
This time AutoSum correctly guesses that you want to sum the
values to the left of cell E4:
2.
7
© 2008 The Smart Method Ltd
Press the key, or click the AutoSum button once more.
Save your work as First Quarter Sales and Profit-3.
63
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-4: Select a range of
cells and understand Smart
Tags
Sometimes you don’t want to add all of the values in a column. You only
want to add a selection of cells that you define yourself.
This lesson shows you how to do this with a different AutoSum
technique.
1
Open First Quarter Sales and Profit-3 from your sample files
folder (if it isn’t already open).
2
Observe the formula behind the value in cell B9.
Click on cell B9 or move to it with the arrow keys on your
keyboard.
Look at the formula bar at the top of the screen. Notice that the cell
displays the value of a calculation and the formula bar shows the
formula used to calculate the value:
3
Delete the contents of cell B9.
The easiest way to delete the contents of a cell is to press the
key on your keyboard but you can also right‐click the cell
and then click Clear Contents from the shortcut menu.
4
Change the word Total in cell A9 to USA Sales and press the
key once.
You don’t have to delete the word Total before typing USA Sales.
When you click in a cell and immediately begin to type, the
existing cell contents are replaced.
The cursor moves to cell B9.
5
Select cells B4:B5 with your mouse.
When the mouse cursor hovers over a cell there are three possible
cursor shapes:
First Quarter Sales and
Profit-3
64
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
Cursor
note
What it does
Selecting cells with
the keyboard
The white cross (Select) cursor appears when
you hover over the centre of the cell. You can
then click and drag to select a range of cells.
To select cells with the keyboard
hold down the key and
then use the keys to
select the range needed.
The black cross (AutoFill) cursor appears
when you hover over the bottom right‐hand
corner of a cell. We’ll be covering AutoFill
later in this session.
The four headed arrow (Move) cursor appears
when you hover over one of the black edges of
the cell (but not the bottom right corner).
note
Selecting a large
range of cells with the
-click
technique
Beginners often have difficulty selecting cells and end up moving
them or AutoFilling them by mistake.
Position the mouse at the centre of cell B4 so that you see the White
Cross (select) cursor. When you see the white cross, hold down the
left mouse button and drag down to cell B5.
If you need to select a very large
range of cells it is sometimes
useful to use this technique:
1.
Click the cell in the top left
corner of the required
range.
2.
Use the scroll bars to move
to the bottom right corner
of the required range.
3.
Hold down the key.
4.
Click in the bottom right
corner of the required
range.
You have now selected cells B4 and B5 (in Excel speak we say that
you have selected the range B4:B5).
6
Click the Autosum button.
USA sales are shown in cell B9. Notice the small green triangle at
the top left of B9. This is Excel’s way of saying: “I think you may
have made a mistake”.
Select cell B9 and you will see an Exclamation Mark icon. This is
called a Smart Tag.
7
Hover over the Smart Tag.
A tip box pops up telling you what Excel thinks you may have
done wrong (see below). Of course, in this case, everything is fine.
The Smart Tag thinks that perhaps we didn’t want just the USA
sales – but the Smart Tag is mistaken!
8
Examine the remedial actions suggested by the Smart Tag.
Click the drop‐down arrow next to the exclamation mark icon. A
list of possible remedial actions is displayed. In this case you can
choose Ignore Error to remove the green triangle from the corner of
the cell.
9
© 2008 The Smart Method Ltd
Save your work as First Quarter Sales and Profit-4.
65
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-5: Enter data into a
range and copy data across a
range
Now that you have mastered the technique of selecting cells, you can use
it to speed up data entry.
When you select a range of cells prior to entering data, Excel knows that
all data entered belongs in that range. Several key combinations are then
available to greatly speed up data entry.
1
2
Open a new workbook and save it as Data Range Test.
3
Type London.
Select cells B2:D4.
The text appears in Cell B2, the top left cell in the range selected.
4
Press the Key.
The cursor moves to cell B3 as it normally would.
5
Type Paris followed by the key.
The cursor moves to cell B4 as it normally would.
6
Type New York followed by the key.
This time something new happens. The cursor doesn’t move to cell
B5 as you might expect but jumps to cell C2.
7
Type 150,000 followed by the key.
The value appears in C2 and Excel moves down the column again
to cell C3.
66
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
8
Press the key without entering a value to leave C3
blank.
Excel moves down the column to cell C4.
9
Type 225,000 followed by the key.
The cursor jumps to cell D2.
10
11
Press + twice to change your mind about
leaving Paris blank.
1.
Press + to move backwards to New York sales.
2.
Press + a second time and you are back to the
Paris cell.
Type 180,000 followed by the key.
moves you across the range, to cell D3.
You can now appreciate how to use this technique of ,
, + and + to save a lot of time
when entering a whole table of data.
12
13
Select cells D2:D4.
Type 50% but don’t press the or keys.
The challenge this time is to place the same value into cells D3 and
D4 without having to type the value two more times.
14
Press +.
The value is replicated into all of the other cells in the range.
© 2008 The Smart Method Ltd
67
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-6: Select adjacent
and non-adjacent rows and
columns
1
Open First Quarter Sales and Profit-4 from your sample files
folder (if it isn’t already open).
2
Select all of column A
Hover over the letter A at the top of the column. The button lights
up and the mouse cursor changes to a black down arrow:
Click to select the entire column. The column becomes slightly
shaded and a black line surrounds all of the cells.
3
Click HomeFontBold to bold face the column.
Because the whole column was selected, all of the values become
bold faced.
4
Click HomeFontBold once more to change the type back
to normal.
5
Select all of row 4.
To select a row, hover over the number on the left hand side of the
row. The button lights up and the mouse cursor changes to a black
arrow pointing across the row:
Click to select the row.
6
Select columns B and C.
Hover over the letter at the top of column B until you see the black
down arrow. When you see the arrow, click and drag to the right
to select both columns.
First Quarter Sales and
Profit-4
68
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
7
Select rows 6 and 7.
Hover over the number at the left of row 6 until you see the black
arrow pointing across the row. When you see the arrow, click and
drag down to row 7 to select both rows.
8
Select columns A, B, C, D and E.
Sometimes you will need to select a large number of adjacent
columns or rows. You could drag across them but it is often easier
to use the following technique:
1.
Select Column A.
2.
Hold down the key.
3.
Select Column E.
Columns A to E are selected.
9
Select rows 4 and 6.
Perhaps you need to perform an operation on two non‐adjacent
rows. To select rows 4 and 6 you need to:
© 2008 The Smart Method Ltd
1.
Select row 4.
2.
Hold down the key on the keyboard.
3.
Select Row 6.
69
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-7: Select noncontiguous cell ranges and
view summary information
Contiguous is a very impressive word! It simply means a range of cells
that is split across two blocks of cells in different parts of the worksheet.
Non‐contiguous ranges can be selected using both the mouse and
keyboard. The keyboard method may seem a little involved at first but
you’ll find it much faster once you have the hang of it.
1
Open First Quarter Sales and Profit-4 from your sample files
folder (if it isn’t already open).
2
Select the contiguous range B4:D8 with the keyboard.
When you need to select a contiguous range with the keyboard
here’s how it’s done:
1.
Use the arrow keys on the keyboard to navigate to cell B4
2.
Hold down the key on the keyboard
3.
Still holding the key down, use the arrow keys on the
keyboard to navigate to cell D8
The contiguous range B4:D8 is selected.
3
Select the non-contiguous range B4:B8,D4:D8 using the
mouse.
1.
Select the range B4:B8.
2.
Hold down the key and select the range D4:D8.
The non‐contiguous range B4:B8,D4:D8 is selected:
First Quarter Sales and
Profit-4
70
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
4
Select the same non-contiguous range with the keyboard.
This is a little more involved than using the simple
+ method used earlier.
Here’s how it’s done:
1.
Use the arrow keys on the keyboard to navigate to cell B4
2.
Press the key (it is on the very top row of your keyboard)
3.
Use the arrow keys to navigate to cell B8
4.
Press +
5.
Use the arrow keys to navigate to cell D4
6.
Press
7.
Use the arrow keys to navigate to cell D8
8.
Press + one last time
The non‐contiguous range B4:B8,D4:D8 is selected:
5
Obtain a total sale figures for January and March using the
status bar.
The status bar contains summary information for the currently
selected range.
Look at the bottom right of your screen. You can see the average
sales and total sales (sum of sales) for January and March:
6
View the Maximum and Minimum sales for January and
March using the status bar.
Right‐click the status bar and click Maximum and Minimum on the
pop‐up menu (see sidebar).
The status bar now also displays Maximum and Minimum values.
7
© 2008 The Smart Method Ltd
Close the workbook without saving.
71
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-8: AutoSelect a
range of cells
When data is arranged in a block (as it is in the Sales and Profit Report) it
is referred to as a range (in previous versions of Excel it used to be called
a list).
You will often want to select a row or column of cells within a range, or
even the entire range.
You can select ranges by using any of the techniques covered so far but
this could be very time consuming if the range encompassed thousands
of rows and columns.
In this lesson you’ll learn how to select range rows, range columns and
entire ranges with a few clicks of the mouse.
1
Open Sales Report from your sample files folder.
This report contains a range of cells. The range is the block of cells
from A3 to E19.
2
Select all cells within the range to the right of cell A7.
1.
Click in cell A7 to make it the active cell.
2.
Hover over the right hand border of cell A7 until you see the
four headed arrow cursor shape.
3.
When you see this cursor shape hold down the key
and double‐click.
All cells to the right of A7, but within the range, are selected.
Sales Report
72
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
3
note
Select all cells within the range except the header row.
1.
Click in cell A4 to make it the active cell.
2.
Hover over the right hand border of cell A4 until you see the
four headed arrow cursor shape.
3.
When you see this cursor shape hold down the key
and double‐click.
Other ways to use
AutoSelect
Using the keyboard.
Here’s how you would select
the entire range in the Weekly
Sales Report (including the
header row) using the keyboard
method.
All cells to the right of cell A4, but within the range, are
selected.
Make cell A3 the active cell by
navigating to it with the
keys.
1.
4.
Hover over the bottom border of the selected range until you
see the four headed arrow cursor shape.
5.
When you see this cursor shape hold down the key
and double‐click.
Press:
++
Cells A3:A19 are selected.
2.
Release all keys and press:
The entire range (except the header row) is selected.
++
The entire range (including
the header row) is selected.
From the Ribbon.
The Ribbon method isn’t as
powerful as the other methods
but does provide a way to select
the current range (described as
the region in the dialog).
Make sure that the active cell is
within the range.
1.
Click
HomeEditing
Find & Select
GoTo Special...
The GoTo Special dialog is
displayed.
2.
Click the Current Region
option button and then
click the OK button.
The entire range (including the
header row) is selected.
© 2008 The Smart Method Ltd
You can also use this technique to select cells to the left of the
active cell or above the active cell.
4
Close the workbook without saving.
73
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-9: Re-size rows and
columns
1
Open First Quarter Sales and Profit-4 from your sample files
folder (if it isn’t already open).
Notice that columns B, C, D and E are far too wide for their
contents. It would be useful to make them narrower to keep the
worksheet compact.
2
Re-size column B so that it is just wide enough to contain the
January sales figures.
Hover over the line separating the letters A and B until you see the
re‐size cursor shape:
When you see this shape, keep the mouse still and click and drag
to the left. Column B will re‐size as you drag. Make it narrower so
that the values just fit in the column. Notice that the column width
in points and pixels are displayed as you drag (one point = 1/72
inch).
note
Why are you calling
the pound sign a
hash?
You like potato and I like potahto,
You like tomato and I like tomahto;
Potato, potahto, tomato, tomahto!
Letʹs call the whole thing off!
3
Song lyric by George Gershwin,
American Composer (1898‐1937)
Notice that when the column isn’t wide enough to contain the
contents, hash signs are shown instead of values (if you’re used to
hashes being called pound signs or number signs see the sidebar).
In the USA and Canada the
hash symbol is called the
pound sign or the number
sign.
In different USA/Canada
regions the single symbol has
different names because it can
be used to denote a number (as
in contestant #5) or as a weight
(as in 3# of butter).
Throughout this book I will
refer to the # as a hash because
that is the term used in most
other English speaking
countries.
First Quarter Sales and
Profit-4
74
Re-size column B so that it is too narrow to contain the
January sales figures.
4
Automatically re-size column B so that it is a perfect fit for
the widest cell in the column.
Hover over the line separating the letters A and B until you see the
re‐size cursor shape:
When you see this shape, double‐click to automatically re‐size
column B.
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
note
5
Other ways to re-size
rows and columns
Automatically re-size every column in the worksheet in one
operation.
1.
Select every cell in the workbook by clicking the select all
button in the top left corner of the worksheet (you can also
select all by pressing +):
2.
Hover over the intersection of any two columns until you see
You can also re‐size rows and
columns using the Ribbon.
Click HomeCellsFormat.
A drop‐down menu appears.
the re‐size cursor shape
and then double‐click.
Every column is now perfectly sized.
Notice that Auto‐resize has done its job rather too well. Column A
is now wide enough to accommodate all of the text in cell A1.
You can use the Row Height and
Column Width settings to set the
row or column to a specific
number of points (a point is
1/72 of an inch).
6
Automatically re-size column A so that it is only wide enough
to contain the longest city name (Los Angeles).
You can also use the AutoFit
Row Height and AutoFit Column
Width options to automatically
size the row or column (you
achieved this more efficiently
with a double‐click in the
lesson).
Default Width... often confuses
as it doesn’t appear to work.
This is because it re‐sets all
columns to default width except
those that have already been re‐
sized.
note
2.
Click HomeCellsFormatAutoFit Column Width.
Notice that the text has spilled over from cell A1 into the adjoining
columns B, C, D and E. This always happens when a cell contains
text and the adjoining cells are empty.
7
Manually size row 3 so that it is about twice as tall as the
other rows.
Do this in exactly the same way you re‐sized the column but, this
time, hover between the intersection of rows 3 and 4 until you see
the re‐size cursor shape, and then drag downwards.
Sometimes you will want to
make several columns exactly
the same width.
© 2008 The Smart Method Ltd
Select cells A4:A9.
This time the column is automatically sized so that it is wide
enough to contain all of the text in the selected cells.
Making several
columns or rows the
same size.
To do this, select the multiple
columns and then click and
drag the intersection of any of
the selected columns. This will
make each of the columns
exactly the same width.
1.
8
Auto-resize row 3 so that it is the same size as the other
rows again.
75
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-10: Use AutoSum to
sum a non-contiguous range
In the previous lesson, you learned how to view the sum of January and
March sales using the status bar. But how can you put that value onto the
worksheet?
Now that you have the hang of selecting non‐contiguous ranges you can
use this in conjunction with your AutoSum skills to create a formula that
will calculate the total of a non‐contiguous range.
1
Open First Quarter Sales and Profit-4 from your sample files
folder (if it isn’t already open).
2
Enter the text Jan/Mar Sales in cell A10 and press the
key.
The active cell moves to cell B10.
3
4
Re-size column A so that it is wide enough to contain the
text.
1.
Hover over the line separating the letters A and B until
you see the re‐size cursor shape:
2.
When you see this shape, keep the mouse still and click
and drag to the right. Column A will re‐size as you drag.
Make it wider so that the words Jan/Mar Sales comfortably
fit in the column:
Click HomeEditing
(the AutoSum button).
An AutoSum appears in cell B10 but it isn’t anything like what we
want yet. It guesses that we simply want to repeat the value in the
USA Sales cell.
First Quarter Sales and
Profit-4
76
5
Select the range B4:B8 with the mouse.
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
6
Hold down the key and select the range D4:D8 with
the mouse.
Notice that the non‐contiguous range B4:B8,D4:D8 is shown in the
AutoSum’s formula:
© 2008 The Smart Method Ltd
7
Press the key or click the AutoSum button
again to view the sales for January and March in cell B10.
8
Save your work as First Quarter Sales and Profit-5.
77
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-11: Use AutoSum to
quickly calculate averages
Excel’s AutoSum feature isn’t only restricted to addition. It is also able to
compute averages and maximum/minimum values.
In this lesson we’ll use AutoSum to calculate the average sales for each
month.
1
Open First Quarter Sales and Profit 5 from your sample files
folder (If it isn’t already open).
2
Delete cells E3:E4.
Select cells E3 and E4 and press the key on your
keyboard.
3
Type the word Average in cell E3 and press the
key.
The cursor moves to cell E4:
4
Use AutoSum to create a formula that will show the Average
New York sales.
1.
Click HomeEditingAutoSum Drop down arrow (see
sidebar).
A drop down menu is displayed showing all of the different
ways in which AutoSum can operate upon a range of cells:
First Quarter Sales and
Profit-5
78
2.
Click Average.
3.
Excel generates an Average function and inserts the cell range
B4:D4. This is exactly what we want:
4.
Press the key or click the AutoSum button
to see the average sales for New York:
again
5
Type the word Maximum in Cell F3 and then press the
key.
6
Use AutoSum to create a formula in cell F4 that will show the
Maximum New York Sales for this period.
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
1.
Place an AutoSum in cell F4 but this time, choose Max from the
drop‐down menu.
This time we have a small problem. AutoSum is including the
average value (23,333) in the calculation.
2.
Select cells B4:D4 with the mouse
The marquee corrects and the average value in cell E4 is no
longer included.
Notice that the Max function is now working with the range
B4:D4.
3.
tip
Another way to bring back the
blue box showing a range is to
click the range in the Formula
bar.
7
Change the words USA Sales in cell A9 back to Sales and
press the key.
8
Press the key on the keyboard (or double-click cell B9)
to bring back the marquee (shown as a blue box).
9
Adjust the marquee using click and drag so that all offices
are included in the Sales total.
Notice that there is a small blue spot on each corner of the range.
These are called sizing handles.
10
© 2008 The Smart Method Ltd
Press the key or click the AutoSum button
once
more to see the maximum sales the New York office managed
during the first quarter of the year:
1.
Hover the mouse cursor over the bottom right (or bottom left)
sizing handle until the cursor shape changes to a double
headed arrow. It is really important that you see the double
headed arrow and not the four headed arrow or white cross.
2.
When you see the double headed arrow click and drag with
the mouse down to cell B8.
3.
Press the key or click the AutoSum button again.
Save your work as First Quarter Sales and Profit-6.
79
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-12: Create your own
formulas
The AutoSum tool is very useful for quickly inserting Sum(), Average(),
Count(), Max() and Min() formulas into cells. Many Excel users never get
any further with their formulas than this.
In this session we’ll create our own formulas without the use of
AutoSum. You’ll be amazed at how easy it is.
1
Open First Quarter Sales and Profit-6 from your sample files
folder (if it isn’t already open).
2
Select cells A10:B10 and press the key once.
The previous contents of cells A10:B10 are removed.
3
4
Type the word Costs into cell A11 and Profit into cell A12.
5
Enter a formula into cell B12 to compute the profit made in
January.
Type the value 83,000 into cell B11 and press the
key to move down to cell B12.
1.
Type: = B9‐B11 into cell B12
2.
Press the key.
The profit for January is displayed:
important
Formulas automatically
recalculate whenever any of the
cells in the formula change.
For example: If you were to
change the Paris sales to 45,000
this would cause cell B9 to re‐
calculate to 139,000. Because B9
has changed this would, in
turn, cause the profit to re‐
calculate to 56,000.
6
Enter the formula again using a better technique.
The method that you have just used to enter the formula works
just fine but it isn’t the best method. Sooner or later you will make
a mistake. For example you could easily type B8:B11 resulting in
an incorrect answer.
To eliminate such errors you should always select cell references
visually rather than simply type them in. You can visually select
cells using either the mouse or the keyboard. First we’ll use the
mouse method.
First Quarter Sales and
Profit-6
80
1.
Click in cell B12 and press the key on the keyboard to
clear the old formula.
2.
Press the equals key on the keyboard.
3.
Click once on the value 129,000 in cell B9.
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
4.
Press the minus key on the keyboard.
5.
Click once on the value 83,000 in cell B11.
6.
Press the key on the keyboard.
If you followed the above steps carefully you will see that you
have created the same formula but with a much lower possibility
of making a mistake.
7
Enter the formula again using the visual keyboard technique.
The very best Excel experts hardly use the mouse at all. You waste
valuable seconds every time you reach for the mouse.
Here’s the expert technique of visual selection via keyboard:
8
1.
Use the arrow keys to navigate to cell B12 and then press the
key on the keyboard to clear the old formula.
2.
Press the key on the keyboard.
3.
Press the key three times to move to cell B9.
4.
Press the key on the keyboard.
5.
Press the key once to move to cell B11.
6.
Press the key on the keyboard.
Enter a formula that uses the multiplication operator.
This employer is very generous and pays the staff ten percent of all
profits as an incentive bonus.
In cell A13 type the words 10% Bonus and then press the
key on the keyboard to move to cell B13.
9
important
Enter a formula that uses the multiplication operator.
The multiplication operator is not an X as you might expect but an
asterisk (*). The other Excel operators are shown in the sidebar.
The Excel Operators
Name
Example
+
Addition
1+2
You need to press + to enter an asterisk. If you are using
a full size keyboard with a numeric keypad at the right‐hand side
you can also use the numeric keyboard’s key.
‐
Subtraction
7‐5
Whichever key you use you’ll still see an asterisk in the formula.
*
Multiplication
6*3
/
Division
15/5
%
Percent
25%
Use either the Mouse click technique or the visual keyboard technique
to enter the formula shown below into cell B13 and then press the
key to see how much bonus was earned:
^
Exponentiation
4^2
10
© 2008 The Smart Method Ltd
Save your work as First Quarter Sales and Profit-7.
81
Learn Excel 2007 Essential Skills with The Smart Method
trivia
The feature that Excel uses to
help you out with function calls
first made an appearance in
Visual Basic 5 back in 1996 and
had the wonderful name:
IntelliSense. The Excel
implementation is called
Formula AutoComplete.
In 1996 I was working in
Switzerland developing a ski‐
school management
application. I was extremely
impressed by how easy
IntelliSense made my job. I
didn’t have to remember
hundreds of function names
any more.
I was even slightly worried that
just about anybody could now
program (but, of course, I was
wrong).
Recently I was in London being
driven by a Hackney cab driver
(Hackney cab drivers know
every shortcut and back street
in London). He was worried
that Satellite Navigation would
now mean that anybody could
become a London cabbie.
Lesson 2-13: Create functions
using Formula AutoComplete
1
Open First Quarter Sales and Profit-7 from your sample files
folder (if it isn’t already open).
2
Type the words USA Sales into cell A15 and European
Sales into cell A16.
Notice that the text European Sales spills over into column B
because column A isn’t wide enough to contain it.
3
Re-size column A so that it is wide enough for the words
European Sales to fit within the column.
You learned how to do this in: Lesson 2‐9: Re‐size rows and columns.
4
Click into cell B15 and type =S into the cell.
Something amazing happens:
Of course, he really had
nothing to worry about either!
A list drops down showing every function in the Excel function
library beginning with S. This is a great new feature for Excel 2007
that Microsoft call Formula AutoComplete (if AutoComplete didn’t
display as expected see the sidebar).
note
Enabling and
disabling
AutoComplete.
You’ve already encountered the Sum(), Average() and Max()
functions courtesy of AutoSum.
You may be pleased (or dismayed) to know that there are over 300
functions in the Excel function library. The good news is that most
untrained Excel users only ever get to understand Sum() and
Average()!
As with so many other features,
Microsoft allows you to turn
this very useful feature off.
You’d never want to do this but
you may work on a machine
that has had Formula
AutoComplete switched off
and you need to turn it on
again.
When you typed =S Excel listed all functions beginning with S.
5
Notice that the list now only shows functions beginning with SU
and look… there’s the SUM() function we need three down in the
list.
Click OfficeExcel
OptionsFormulas and make
sure that the Formula
AutoComplete box is checked.
You could simply click on it with the mouse but let’s behave like
an Excel pro and do it with the keyboard.
6
First Quarter Sales and
Profit-7
82
Continue typing: =SU.
Press the key twice to move the cursor over
the SUM function.
The Sum function now has a tip telling you what the function does:
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
7
Display detailed information about the SUM() function.
The tip tells you a little about the SUM() function but to get the full
story press the key while SUM is still highlighted in the
dropdown list:
The Excel help system opens showing detailed help for the SUM()
function.
Read the help text if you are interested and then close the help
window.
Notice that Excel has now left you with only =SU in the cell.
Complete the formula by typing M(
Notice that a little box has appeared beneath the function call. This
is the Syntax (see sidebar for more information).
note
The syntax box
The Syntax box tells you what
arguments (sometimes called
parameters) the function needs.
The first argument has no
square brackets meaning that
you can’t leave it out. The
argument in square brackets is
optional.
For such a simple function as
SUM() the syntax box is hardly
needed but later we’ll discover
functions that require several
arguments and then the syntax
box will be invaluable.
8
If you want to be a real pro you should select them with the
keyboard. To do this:
9
1.
Press the key repeatedly until you reach cell B4.
2.
Hold down the key and press the key
once to select cells B4:B5.
Type a closing bracket to complete the formula and then
press the key.
The total USA sales are displayed in cell B15.
10
11
© 2008 The Smart Method Ltd
Select the cells that you need to sum (cells B4:B5) with the
mouse or keyboard.
Use the same technique to create a SUM() function in cell
B16 to show the total European sales (cells B6:B8).
1.
Click in cell B16.
2.
Type = SU.
3.
Press the key twice to move the cursor over
the SUM function.
4.
Press the key to automatically enter the SUM function
into cell B16.
5.
Select the range B6:B8.
6.
Type the closing bracket followed by the key. The
formula should now be: =SUM(B6:B8).
Save your work as First Quarter Sales and Profit 8.
83
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-14: Use AutoFill for
text and numeric series
1
Open First Quarter Sales and Profit-8 from your sample files
folder (if it isn’t already open).
2
Delete the text Feb and Mar from cells C3:D3.
Select cells C3:D3 and then press the key on your
keyboard.
note
3
Notice that there is a black border around the cell and a black spot
on the bottom right‐hand corner. This is the AutoFill handle. If you
don’t see it, refer to the sidebar.
If you don’t see the
AutoFill handle
somebody has
switched it off
It’s almost certain that AutoFill
will be enabled on any
computer that you work on. It
is such a useful feature that you
wouldn’t want to disable it.
Make B3 the active cell.
4
Hover over the AutoFill handle with your mouse until the
cursor shape changes to a black cross.
Many of my students have great difficulty with this when they try
it for the first time.
If you don’t see the AutoFill
handle (the black spot on the
bottom right hand corner of the
active cell) it’s because
somebody has switched
AutoFill off.
Bring it back like this:
You don’t want the four‐headed arrow:
the cell.
You don’t want the white cross:
cell.
You want the black cross:
Click OfficeExcel
OptionsAdvanced
In the first section (Editing
Options) check the box next to
Enable fill handle and cell drag
and drop.
5
– that would move
– that would select the
– the AutoFill cursor.
When the black cross cursor is visible hold down the mouse
button and drag your mouse to the right to AutoFill the other
months: Feb and Mar.
Notice the tip that appears as you drag, previewing the month that
will appear in each cell.
First Quarter Sales and
Profit-8
84
6
In cell A18 type Monday and AutoFill down to cell A24 to
show the days of the week.
7
In cell B18 type the number 1 and in cell B19 type the
number 2.
8
Select cells B18 and B19.
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
tip
You don’t have to enter two
numbers if you want to create a
numeric range.
9
AutoFill down to B24 to create sequential numbers:
10
11
In cell C18 type 9 and in cell C19 type 18.
12
AutoFill down to B24 to create the nine times table.
13
Use AutoFill to create sequential dates.
If you simply type a number
into a cell and then AutoFill
while holding down the
key you will automatically
generate sequential numbers.
14
Select cells C18 and C19.
1.
Type 01‐Jan‐08 into cell D18.
2.
Type 02‐Jan‐08 into cell D19.
3.
Select Cells D18:D19.
4.
AutoFill down to D24 to create sequential dates.
Use AutoFill to quickly copy values.
Sometimes you will want to duplicate the value from one cell into
many others to the right of, left of, beneath, or above the active cell.
When a cell containing text is the active cell and it isn’t defined as a
fill series (the built‐in fill series are days of the week and months of
the year), AutoFill will simply duplicate the contents of the cell:
Type the text Adjusted into cell E18 and then AutoFill it down as
far as cell E24.
The same text is now shown in each of the cells:
15
© 2008 The Smart Method Ltd
Save your work as First Quarter Sales and Profit-9.
85
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-15: Use AutoFill to
adjust formulas
AutoFill can save you a lot of time when extending or copying text and
number ranges. But the story’s not over yet.
AutoFill’s ability to copy and adjust formulas is one of the most powerful
tools in Excel’s impressive armory.
1
Open First Quarter Sales and Profit 9 from your sample files
folder (If it isn’t already open).
2
Consider the formula in cell B9.
Click onto cell B9 and view the formula displayed in the formula
bar (the formula bar is at the top right of the screen grab below).
The formula is =SUM(B4:B8). AutoSum created it for us in:
Lesson 2‐3: Use AutoSum to quickly calculate totals. The formula uses
the SUM() function to add together the values in the range B4:B8.
Think about the formula that would work in cell C9 (the total sales
for February). It would be: =SUM(C4:C8). Similarly the formula
that would work in cell D9 (the total sales for March) would be
=SUM(D4:D8).
As we move to the right all that is needed is to increment the letter
for each cell reference in the formula and we’ll get the right answer
every time.
Now AutoFill is very clever and realizes this. When we AutoFill a
cell containing a formula to the right, AutoFill increments the
letters in each cell reference.
Most of the time that is exactly what we want.
3
AutoFill cell B9 to the right as far as cell D9.
You may see a row of hashes in Cell C9. This is because the value
is too wide to fit in the cell. If this is the case, AutoFit the column
using the skill learned in: Lesson 2‐9: Re‐size rows and columns.
First Quarter Sales and
Profit-9
86
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
The correct answers for Feb and Mar sales are shown on the
worksheet. Click on the Feb total cell (C9) and look at the formula
in the formula bar.
You can see that AutoFill has done its work perfectly, creating a
sum of the values in cells C4:C8. Our five branches have sold
exactly the same amount in both January and February, but a little
less in March.
4
Consider the formula in cell E4.
Click onto cell E4 and view the formula displayed in the formula
bar.
The formula is =AVERAGE(B4:D4). AutoSum created it for us in:
Lesson 2‐11: Use AutoSum to quickly calculate averages.
Think about the formula that would work in cell E5 (the total sales
for Los Angeles). It would be: =Average(B5:D5). Similarly the
formula that would work in cell E6 (the total sales for London )
would be =Average(B6:D6).
As we move downward all that is needed is to increment the
number for each cell reference in the formula.
© 2008 The Smart Method Ltd
5
AutoFill cell E4 down to E8 to see the Average sales for
each branch.
6
AutoFill cell F4 down to F8 to view the maximum sales for
each branch.
7
Save your work as First Quarter Sales and Profit-10.
87
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-16: Use AutoFill
options
Sometimes AutoFill begins to misbehave and actually gets in the way of
efficient work by wrongly anticipating what you need.
1
Open First Quarter Sales and Profit-10 from your sample
files folder (If it isn’t already open).
2
3
In cell F18 type the date 1-Jan-2009.
AutoFill down as far as cell F24.
The cells are populated with sequential dates:
4
5
In cell G18 type the date 31-Mar-2009.
AutoFill down as far as cell G24.
At some time you’ll need to add transaction dates to a worksheet
and will have four or five entries with the same date.
AutoFill is perfect for eliminating the need to re‐type the date for
each transaction, but its insistence upon incrementing the date
every time could be very frustrating.
Fortunately we can change the default behavior.
6
Click the Auto Fill Options Smart Tag
corner of the filled cells.
at the bottom right
First Quarter Sales and
Profit-10
88
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
7
Click Copy Cells to tell AutoFill not to increment the date.
The purpose of the Fill Formatting options will be clear later in the
course when you have completed: Session Four: Making Your
Worksheets Look Professional.
Here’s what the other options will do:
Copy Cells
Fill Series
Fill Days
Fill Weekdays
Fill Months
Fill Years
This is what
we just did.
The default
for dates that
include the
day.
The date
increments
by one day
at a time.
Because 3rd April
2009 is a Friday
the weekend
days are omitted
and the series
jumps from 3rd
April to 6th April.
Normally this would
show the same day
number for each
month. In this
example, there are
only 30 days in two of
the months so 30th is
shown instead of 31st.
The same
calendar day
is shown for
each
subsequent
year.
The first cell is
copied to the
other cells.
The date
increments
by one day at
a time.
8
Access fill options using right-click AutoFill.
1.
Click again on cell F18 to make it the active cell.
2.
AutoFill once more but, this time, hold down the right mouse
button.
When you release the mouse button you are presented with
the AutoFill options (see sidebar).
This method is preferred to the Smart Tag method because it is
faster (one click instead of two)!
9
Change back to Fill Series or Fill Days.
In this example Fill Series and Fill Days produce exactly the same
result.
10
© 2008 The Smart Method Ltd
Save your work as First Quarter Sales and Profit-11.
89
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-17: Speed up your
AutoFills and create a custom
fill series
In this lesson we’re going to learn some AutoFill techniques that will
massively speed up your efficient use of the AutoFill feature.
1
Open First Quarter Sales and Profit-11 (if it isn’t already
open).
2
3
Delete the contents of cells G18:G24.
4
Double click the AutoFill handle to automatically fill cells
G19:G24.
Type 31-Mar-09 into cell G18.
This is a real master tip!
Hover over the AutoFill handle (the black spot at the bottom right
hand corner). When you are sure that you have the correct black
cross cursor shape, double click to automatically fill down.
5
Delete all of the dates from cells G19:G24 leaving only the
date 31-Mar-09 in cell G18.
6
Hold down the key and AutoFill down as far as G24
by dragging the AutoFill handle down with the mouse.
Because you held the key down AutoFill simply copies the
cell instead of creating a series of values.
First Quarter Sales and
Profit-11
90
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
This is even faster than using the right‐click method when you
want to prevent the date incrementing.
7
Click OfficeExcel OptionsPopular.
8
In the Popular category under Top options for working with
Excel click the gray Edit Custom Lists... button.
The Custom Lists dialog appears.
9
Click in the List entries window and add four custom list
entries: North, South, East and West.
10
Click the OK button.
11
Type North in any cell and AutoFill down.
As you AutoFill the custom list entries appear in the worksheet.
© 2008 The Smart Method Ltd
12
Delete the North, South, East, West… cells from the
worksheet.
13
Save your work as First Quarter Sales and Profit-12.
91
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-18: Use the zoom
control
Zooming is used to magnify or reduce the worksheet. If you have a lot of
rows in a worksheet and have good eyes you might want to zoom out
sometimes to see more of the worksheet on one screen.
1
Open First Quarter Sales and Profit-12 from your sample
files folder (if it isn’t already open).
2
Zoom in and out of a worksheet using the mouse wheel.
The fastest way to zoom a worksheet is by using the mouse.
Most mice these days have a wheel in the middle of the buttons. To
zoom using this wheel hold down the key on the keyboard
and roll the wheel to zoom in and out.
3
Zoom in and out of a worksheet using the zoom control.
The zoom control is at the bottom right of your screen,
Click and drag on the zoom control slider to zoom in and out of
your worksheet. You can also zoom by clicking the plus and minus
buttons on either side of the Zoom control.
4
Select cells A3:D9.
We’re going to use the zoom dialog to make this selection
completely fill the screen.
5
Click the left hand side of the zoom bar.
The zoom dialog is displayed.
First Quarter Sales and
Profit-12
92
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
6
Select the Fit Selection option button and click the OK
button.
The worksheet is zoomed so that the selected cells completely fill
the screen.
7
Zoom back to 100% using the Ribbon.
You’ll probably find the zoom bar to be the quickest and most
convenient way to zoom, but the View Ribbon also has a Zoom
group containing three buttons:
Use the 100% button to restore the screen to normal.
© 2008 The Smart Method Ltd
93
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 2-19: Print out a
worksheet
We aren’t going to explore every option for preparing and printing a
worksheet in this lesson. Printing is such a huge subject that we devote a
whole session to it in: Session Seven: Printing Your Work.
This lesson only aims to teach you the bare minimum skills you need to
put your work onto paper.
1
Open First Quarter Sales and Profit-12 from your sample
files folder (if it isn’t already open).
2
Click the Office button at the top left of the screen
3
Hover the mouse cursor over the Print button at the left of
the dialog.
4
Click Print Preview from the menu at the right hand side of
the dialog.
5
A preview of the printout is shown on screen:
.
First Quarter Sales and
Profit-12
94
www.LearnMicrosoftExcel.com
Session Two: Doing Useful Work with Excel
6
Click on the page to see the zoom feature working.
Each time you click on the page the page zooms in and out.
7
Click the Print button
screen.
at the top left of the Print Preview
If you don’t see this icon at the top of the Print Preview window it
is because you have hidden the Ribbon. Unhide it by double‐
clicking the Print Preview Ribbon tab.
The print options dialog appears:
8
Click the OK button to print your work.
For this simple worksheet there’s no need to change any of the
options but you can probably figure out what most of them are for.
© 2008 The Smart Method Ltd
95
Session 2: Exercise
1
Open a new workbook.
2
Use AutoFill to put the three months Jan, Feb, and Mar into cells A4:A6.
3
Using only the keyboard add the following data:
4
Use AutoSum to compute London’s total profit for Jan/Feb/Mar in cell B7.
5
Use AutoSum to compute the average January profit in cell E4.
6
Use AutoFill to extend the London total in cell B7, to the Paris and New York totals in cells C7
and D7.
7
Use AutoFill to extend the January average profit in cell E4, to the February and March average
profits in cells E5 and E6.
8
Select all of Column A and all of Column E (at the same time) and bold face the values in them.
9
Select row 3 and row 7 (at the same time) and bold face the values in them.
10
Select cells B4:B6 and cells D4:D6 at the same time and then read the total London and New York
sales figure for Jan, Feb and March from the summary information displayed on the status bar.
11
Select cells B4:D6 and zoom the selection so that these cells fill the screen.
12
Save your work as Exercise2‐End.
© 2008 The Smart Method Ltd
97
Session 2: Exercise answers
These are the four questions that most students find the most difficult to remember:
Q 10
Q8
Q 4 and 5
Q2
1. Hover over the
centre of cell B4 so that
you see the white
cross cursor shape.
1. Click on the column
A header.
1. Click in cell B7.
1. Type Jan into cell
A4 and press the
key.
2. Click and drag
down to cell B6.
3. Hold down the
key.
4. Hover over cell D4
until you see the white
cross cursor shape.
2. Click HomeEditing
Autosum (Q4).
2. Click cell A4 once
to make it the active
cell.
2. Hold down the
key.
3. Click on the column
E header.
4. Click
HomeFontBold.
OR
Click HomeEditing
AutosumAverage (Q5).
5. Click and drag
down to cell D6.
This was covered in:
Lesson 2‐7: Select non‐
contiguous cell ranges
and view summary
information.
3. Hover the mouse
cursor over the
bottom right corner
of the cell until you
see the black cross
cursor shape.
4. Click and drag
down to cell A6.
This was covered in:
Lesson 2‐6: Select
adjacent and non‐
adjacent rows and
columns.
3. Either press the key
or click the Autosum button
again.
This was covered
in: Lesson 2‐14: Use
AutoFill for text and
numeric series.
This was covered in: Lesson 2‐3:
Use AutoSum to quickly calculate
totals.
If you have difficulty with the other questions, here are the lessons that cover the relevant skills:
1
Refer to: Lesson 2‐2: Create a new workbook and view two workbooks at the same time.
3
Refer to: Lesson 2‐1: Enter text and numbers into a worksheet.
6
Refer to: Lesson 2‐15: Use AutoFill to adjust formulas.
7
Refer to: Lesson 2‐15: Use AutoFill to adjust formulas.
9
Refer to: Lesson 2‐6: Select adjacent and non‐adjacent rows and columns.
11
Refer to: Lesson 2‐18: Use the zoom control.
12
Refer to: Lesson 1‐5: Save a workbook.
© 2008 The Smart Method Ltd
99
Learn Excel 2007 Essential Skills with The Smart Method
End piece
Please provide some feedback
Thank you for completing my Free Excel 2007 course.
Perhaps now you will wish to teach others what you have learned yourself. At The Smart Method we use
this course to teach our classroom Excel courses. Feel free to print this course out and use it in any way you
wish to spread a greater understanding of Excel 2007.
If the course has helped you I don’t want your money but I DO want your feedback.
There’s a facility at the web site www.LearnMicrosoftExcel.com where you can pass on any comments you
may have or just Email me at feedback@learnMicrosoftExcel.com.
You’ll really brighten up my day if you just say “Hi, thanks for the course” or something like that.
You’ll make my entire week though, if you tell me what was wrong with the course. I really want to know
about typos, inaccuracies or anything at all that you found confusing or difficult. All of your feedback will be
incorporated into future revisions of the course so, with your help, it will get better and better.
Looking forward to hearing from you.
My books
If you want to improve your Excel skills further (and there’s a huge amount more to learn) I have two Excel
books in print (or shortly to be in print if you are reading this prior to October 2008).
They are both available from amazon.com, amazon.co.uk and any bookshop in the world. There’s a link to
the right Amazon page on the website and the current status if either is not yet published.
My first book includes the two sessions you have completed along with five more. It is called Learn Excel
2007 Essential Skills with The Smart Method. The second book: Learn Excel 2007 Expert Skills with The Smart
Method covers all of the skills you need to be a true expert.
100
www.LearnMicrosoftExcel.com
Microsoft Excel 2007 Tutorial
Microsoft Excel is a useful tool in any business environment.
The biggest change from the 2003 to 2007 office suite is the migration from drop down menus to
what are called ribbons. This is challenging at first, but it is a much better way of organizing
different functions.
The Basics
Opening a File
Opening a CD-Rom – Insert the CD-Rom in your external drive. Click on “My Computer” and go
to the disc drive to locate your CD-Rom by its name. Double click on the CD-Rom to open it and
locate the files you need.
Opening Excel – When you want to work independently on a spreadsheet you need to open the
Excel spreadsheet. Go to “Programs” and open Excel, which you can recognize by the symbol X.
Spread Sheet Layout
Spreadsheets have rows that go horizontally across the page, and columns that go vertically across
the page. The Columns are labeled by letters, such as A, B, C, etc. The rows are numbered 1, 2, 3,
and so on. The combination of the column and row, or letter and number, describes a cell. For
example, A1 describes column A row 1.
Understanding this cell designation is very important. To enter formulas, we click into the
appropriate cells. The formula expressed as column/row designations will then appear in the
formula bar, which is labeled fx, is directly below the toolbars.
When using Excel it is important to think about the format. Typically you will want to start with a
Title in the first row. You will want to then label both the rows and the columns of data you will
enter. If you look before there is an example of potential labels.
Columns and Rows
Cell Adjustment: When your Excel spreadsheet appears, the column widths are standard size.
These can easily be adjusted larger or smaller in order for you to customize your sheet and
accommodate your data. If you enter a large number, or one with several decimal points, and the
1
column is not wide enough to accommodate it, the sign ###### will appear. The machine
continues to know what the value is even when the ##### appears and you cannot read it. By
widening the column, the full number will appear. There are two ways to adjust cells: auto-adjusting
and manually adjusting cells.
Manual adjustment: To manually adjust the cell width, drag the cursor to either the split
between letters or numbers. The arrow cursor will change to a line with an arrow on each side.
When the cursor changes click and drag to the desired length.
Auto-adjustment: To auto-adjust the cells place the cursor over the break between either
the number or the letters and double click.
Deleting a Column or Row
You may from time to time want to completely delete a row or a column. Highlight the column (or
row) by clicking on its letter (or number). Then go to the Edit command on your toolbar and click
Delete. You can delete part of a row or column, but be careful because you can upset the symmetry
of the rest of your spreadsheet. If you highlight a section of your spreadsheet and then click
Edit/Delete, the machine will ask you if you want to shift your cells, or eliminate an entire row. If
you make a mistake, immediately go to Edit and click Undo Delete. If you simply want to remove
data from some cells and not actually remove the cell from the spreadsheet, go to Edit and click
Clear.
Inserting a Column or Row
Highlight where you want to make “the insert.” If you want to insert an entire column (or row),
again click on the letter (or number) where you want the new column (or row) to be inserted and ten
go to Insert and click on what you want inserted. Again, adding individual cells, or groups of cells,
as opposed to adding entire columns or rows, can throw off the symmetry of the rest of the data in
your spreadsheet so be careful when making such changes. If you have made an insert error,
immediately go to Edit/Undo Insert or you can also later use the Edit/Delete function to remove
the unwanted column, rows or cells.
** Note: if there are pound signs in a cell where there should be numbers the cell needs to be made
larger.
2
By simply making the cell larger (4.71 pixels to 5.86 pixels) the number appears.
Functions
Microsoft Excel has many different mathematical, statistical, and other useful functions. This is
what sets Excel apart from a simple word processing software package (MS Word). Functions are
entered into a cell and calculated data on the worksheet. The functions will update automatically if
any of the data the function uses to calculate changes.
All functions must begin with the equals sign “=” in order to be effective. If the equals sign is not
used it will not calculate the formula/function.
Formula Writing
Operation
Symbol
To Add
+
To subtract
-
To multiply
*
To divide
/
Note: any time you use an asterisk your
computer will think you want to multiply. If
you want to make a note try making it bold or
italicizing
Any time you use the slash for ivied your
numbers your computer will think you want
to divide so if you try to enter a date as 1/11
it may not be accepted unless properly
formatted.
Formulas
Excel does simple formulas: addition, subtraction, multiplication, division just by simply using the
symbols +, -, *, /. An example of this is shown below.
To enter this equation simply:
1. Select the cell desired by clicking on
it with your mouse.
2. Enter “=”
3. Enter 5 + 5
4. Finally press “enter”
After pressing enter the “=5+5” will
disappear and 10 will appear. To edit
this double click on the cell and you will
see what is shown to the left
You may also enter formulas in the area boxed in above. To do this simple click on the cell and
then click on the white space of that text box.
3
Addition, subtraction, multiplication, and division are not that useful like shown above; it would
much easier to use a calculator. However, excel does many things a calculator cannot do. Below is
an example of the “sum” function. The sum function use is to take a list of data and add them
together.
The steps to enter this function are as followed:
1. Enter “=” followed by sum
2. Then click and drag with the mouse on the desired cells
Microsoft automatically inserts the first
parenthesis in the equation “=sum(A1:A6.” The
second parenthesis is not required; however, it
is good practice to always enter in the ending
parenthesis because it gets you in the habit.
After hitting enter the “=sum(A1:A6)” will
disappear and the answer will be in the box.
Notice the notation for entering the data the
letter of the cell is first followed by the number
“A1.” Then a colon “:” separates the last cell in
the list.
This can be typed in manually if desired instead
of clicking and dragging with the mouse.
Once the basic format of entering in functions is learned other functions are just as easy to create.
Average: the average function “=average” sums data and divides by the number of individual pieces
of data (mean).
4
Error Messages:
If a word or even letter is entered in a cell where you want a formula performed, the term
“#VALUE!” will appear, indication that the machine cannot perform the function requested
because it does not know how to real that cell.
Formatting the Numbers
To enter data in a cell, click on the cell, type in the number and press Enter. When a number is
entered on the spreadsheet it is automatically formatted as General. You can designate specific
numbers by entering your desired format for $00.00 or %, for example, but it is recommended that
for formatting you use the Format function, found at the top of the page. Go to Format and click
on the first line, Cells. You then can select the format that you want. Currency, for example, will
enable you to select if you want to show cents, or not (number of decimal places), if you want losses
to be highlighted in red, etc. Using the format function, dates, for example can be expressed many
ways, to your best liking.
If you want to select a particular format for more than a single cell, you can do so by clicking your
cursor on the first cell you want to format, holding down the left mouse button and then dragging it
to cover the additional cells in the rows and/or columns that you would like to be similarly
formatted. The entire area you want formatted will be highlighted. Go to Format – Cells and select
the format you want, and the entire highlighted area will be formatted as you have directed.
Percentages: Percentage calculations are frequently made when doing retailing math and it is
imprtant that you understand how to enter % figures on your spreadsheet.
When doing any % calculation, you must understand that a percent needs to be expressed as a
decimal, i.e., 4% is expressed as .04 in order to do the math. When entering percentages onto an
unformatted spreadsheet, numbers similarly need to be entering percentages onto an unformated
spreadsheet, numbers similarly need to be expressed as decimals. If, however, a cellhas been
previously formatted as %, when you enter the number you enter it as a whole number and the
machine will know how to handle it as a percent. For example, if previously formatted, by entering
4, the machine will automatically convert it to 4%. If the cell was not formatted and you enter 4, and
later format it as a %, the machine will conver it to 400%.
Short-cut: left click and drag over the data you wish to
re-format. Next right-click on the area you selected
and select format cells.
5
The first tab that appears in the menu is number; on the right-hand side there is a list of different
formatting options. When you select an option different than general there are other options on
formatting the cell.
Rounding: You may choose a certain format, limiting the number of decimal points or cents from a
number. Even if the number shown in the cell is rounded off, however, the machine continues to
know what that unrounded number will be used and the final answer may be slightly different than
you would have expected, based on your (or your textbook) doing the arithmetic with a fully
rounded number.
Ribbon: select the data you wish to format (as in above) and click on the home tab on the ribbon.
In the cells box is a drop down menu labeled format select this and drag the mouse down to the
bottom and select format cells.
6
Merging cells: To merge cells there are three options: the first option is to use the shortcut, the
second is to use the ribbon, and the third is to use the formatting cells menu.
Short-cut: select the cells you wish to merge together by clicking and dragging. Next right click in
that selected area and in the top menu is a picture in the lower left corner click on this picture to
merge the cells.
Ribbon: select the cells you wish to merge by clicking and dragging.
Then on the home tab in the ribbon select the merge icon.
Formatting Menu: first select the cells you wish to merge. Then bring up the formatting menu as
shown above in cell formatting. Select the alignment tab on the top, and then check the merge
box.
7
Copying Formulas
Under Edit, the Copy and Paste features can be very useful. You can copy a formula in one section
of your spreadsheet and transfer or paste it somewhere else on the sheet.
Click on the formula you want to copy. A black box will appear around it. Go to Edit and select
Copy. Then go to the cell you wish to paste the formula into and click on it. Go back up to Edit and
select paste.
A shorter way to do this is to right click on the cell you wish to copy, scroll down to copy and click.
Then right click on the cell you wish to paste
it into and select paste.
Worksheet Formatting:
Renaming worksheets: at the bottom of
the worksheet there are different tabs (default
named as sheet 1, sheet 2, sheet 3). To
rename these tabs place your cursor over the
tab and right-click. A menu will appear, select
rename. The menu will disappear and the tab
will be selected and ready to rename.
Another alternative to renaming a
worksheet is to
simply double click on the tab. After double clicking step 2 above will show up and you will be able
to rename your worksheet.
Moving worksheets: To move a worksheet simply click and drag the tab. As you move the tab an
arrow will appear in the spot it will be placed.
Copying Worksheets
Once you have developed a worksheet you may want to reuse it time and again. Having a fully
formatted sheet which contains all the necessary formulas may save time as you edit and change
parts of the spreadsheet. You can make a copy of the entire page and then reenter values that will be
factored into your already existent formulas.
Click on the entire area that you wish to copy. A black box will appear around it. Go to Edit and
click Copy. Then go to the cell where you want to paste the worksheet and click paste. You can copy
8
to a space on your current spreadsheet, move to another sheet, or open an entirely new Excel
spreadsheet.
Color Coding Tabs/worksheets: right click on the tab to select it. Next Left click on that same
tab. Select the tab color.
Freeze panes: Freezing panes is useful if you have a lot of data and wish to see certain data no
matter how far down/over you scroll. Under the view tab on the ribbon there is a freeze panes
menu.
There are three options to freeze panes:
1. You can freeze the top row.
9
2. You can freeze the first column
3. You can customize which cells you freeze by clicking on the cell just under and/or just
to the right of the rows columns you wish to freeze.
***Notice the pictures to the left of the descriptions show where the panes will freeze
Saving
Be sure to save your work frequently!
Printing
Printing column/row numbers and letters: Excel does not print the cell letters and numbers as
shown on excel by default. To do this you simple click on the page layout ribbon and click on the
box with print next to it under headings.
Printing Gridlines. In Excel 2007, gridlines don't appear on a printed worksheet or workbook by
default. This article explains how you can make the gridlines appear in your printed worksheets.
1.
Select the worksheet or worksheets that you want to print.
To select
Do this
A single sheet
Click the sheet tab.
If you don't see the tab that you want, click the tab scrolling buttons to display the tab, and then click the tab.
Two or more
adjacent sheets
Click the tab for the first sheet. Then hold down SHIFT while you click the tab for the last sheet that you want
to select.
Two or more
nonadjacent
sheets
Click the tab for the first sheet. Then hold down CTRL while you click the tabs of the other sheets that you want
to select.
All sheets in a
workbook
Right-click a sheet tab, and then click Select All Sheets on the shortcut menu (shortcut menu: A menu that
shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press
SHIFT+F10.).
10
Tip When multiple worksheets are selected, [Group] appears in the title bar at the top of the worksheet. To
cancel a selection of multiple worksheets in a workbook, click any unselected worksheet. If no unselected
sheet is visible, right-click the tab of a selected sheet, and then click Ungroup Sheets on the shortcut
menu.
2.
On the Page Layout tab, in the Sheet Options group, select the Print check box under Gridlines.
NOTE
If the check boxes in the Sheet Options group appear dimmed, it may be because you have a
chart, image, or other object selected on the worksheet. If you cancel that selection, you'll be able to use
the check boxes.
3.
Click Microsoft Office Button
, and then click Print.
Keyboard shortcut You can also press CTRL+P.
4.
In the Print dialog box, click OK.
After you select the Print check box, you may want to take the following steps:
Preview the gridlines. To see how the gridlines will print, press CTRL+F2 to open the Print Preview
window. Gridlines are designed to print only around actual data in a worksheet. If you want to print gridlines
around empty cells as well, you must set the print area (print area: One or more ranges of cells that you
designate to print when you don't want to print the entire worksheet. If a worksheet includes a print area,
only the print area is printed.) to include those cells. Alternatively, you can apply borders around the cells
instead. To learn more about these options, see Define or clear a print area on a worksheet or Apply or
remove cell borders on a worksheet.
Troubleshoot printing issues with gridlines. If gridlines don't show up when you print your
worksheet, or if you can't see them in the Print Preview window, make sure that the Draft quality check box
is not selected. The Draft quality check box appears on Sheet tab in the Page Setup dialog box. To
quickly access the Page Setup dialog box, press ALT+P, S, P.
11
If gridlines still don't print successfully, there may be an issue with your printer driver. In this case, you can
try downloading the latest driver from the printer manufacturer's Web site. As a last resort, you can apply
borders around the cells that you want to print. For more information, see Apply or remove cell borders on a
worksheet.
Printing Formulas. In Microsoft Excel 2007, it is easier to show and print formulas than in
previous versions of Microsoft Excel. Use the following instructions to show and print formulas:
1. Click on the Formulas tab:
12
2. Click on Show Formulas
Note: Once you have clicked on Show Formulas, any formula that has been typed in the Excel file
will appear. You will notice that the formatting of the cells has changed (i.e. the cell width is quite a
bit larger). In order to print the formulas on as few of pages as possible, you can shorten the cell
width. Just be sure that you can still see the entire formula. Also, you may want to consider printing
the formula page using Landscape orientation instead of the default Portrait.
Once you have adjusted the cell widths, Print Preview the page prior to printing. This is a good
habit to get into. By doing a Print Preview, you can see how many pages will be printed. So, for
example if you think you can fit the formulas on one page, and the print preview is showing two
pages, then you can adjust the formatting to get the formulas to fit on one page.
When you are done viewing and printing the formulas, simply click on the Show Formulas
button again, and the formulas will go away (leaving only the values). You will notice that the
formatting of the cells has changed again (i.e. the cell widths are smaller). You will need to adjust the
cell widths to ensure that all necessary information is visible.
Find
Find function: to use the find function press control + f. Then simply type in whatever you are
looking for. (Be away the find is case sensitive)
13
Inserting a Header/Footer
Header/Footer: to insert a header and footer click on the insert tab on the ribbon and find the
header/footer tab.
Text will appear at the top and bottom of each page: “click to add header” “click to add footer.”
Click those and type what the header/footer. If you have clicked in the header or footer boxes the
ribbon above will change to a special tab called design. In the Design tab there are header/footer
editing options.
14
1
Session One: Basic Skills
A bad beginning makes a bad ending.
Euripides, Aegeus (484 BC ‐ 406 BC).
Even if you are a seasoned Excel user, I urge you to take Euripides’
advice and complete this session. You’ll fly through it if you already
know most of the skills covered.
In my classes I often teach professionals who have used Excel for over ten
years and they always get some nugget of fantastically useful information
from this session.
In this session I teach you the absolute basics you need before you can
start to do useful work with Excel 2007.
I don’t assume that you have any previous exposure to Excel (in any
version) so I have to include some very basic skills.
If you’re moving to Excel 2007 from an earlier version this session will
de‐mystify Microsoft’s new fluent user interface (Ribbon).
Session Objectives
By the end of this session you will be able to:
Start Excel and check your program version
Maximize, minimize, re‐size, move and close the Excel window
Understand the Application and Workbook windows
Open and navigate a workbook
Save a workbook
Pin a document and understand file organization
View, add, rename, delete and navigate worksheet tabs
Use the Ribbon
Understand Ribbon components
Customize the Quick Access Toolbar and preview the printout
Use the Mini Toolbar, Key Tips and keyboard shortcuts
Understand Views
Use full screen view
Use the help system
© 2008 The Smart Method Ltd
23
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 1-1: Start Excel and
check your program version
If you are using Windows XP the procedure is almost the same as
described here. You should be able to figure out the differences!
1
Click the Windows Start Button
your screen.
2
Click the All Programs item at the bottom of the pop-up
menu.
3
4
Scroll down the list and click on the Microsoft Office item.
at the bottom left of
Pin Microsoft Office Excel 2007 to the start menu.
You’ll be using Excel a lot so it makes sense to pin it to the start
menu. This will make it appear at the top of the list in future so
you’ll be able to start Excel with just two clicks.
To pin to the start menu point to Microsoft Office Excel 2007 in the
list, right‐click your mouse and then choose Pin to Start Menu from
the shortcut menu.
5
Left-click Microsoft Office Excel 2007 to start the application.
Excel starts and is displayed on the screen:
6
Check the program version.
Click the Microsoft Office button
then click the Excel Options button
of the dialog.
at the top left of the screen and
at the bottom right
Click Resources from the left hand sidebar and you’ll see the version
number at the bottom of the dialog:
24
www.LearnMicrosoftExcel.com
Session One: Basic Skills
important
In this example the Excel version is:
Service Packs
When Excel 2007 was first
released it had many bugs (as
do all computer programs of
any size).
Microsoft is very pro‐active at
fixing bugs that are found and
always release these fixes free
of charge as a service pack.
Service packs normally only fix
bugs found in the original
program, but Microsoft
sometimes take things a little
further by including new, or at
least enhanced, features with
their service packs.
The important part is the SP1 denoting that you have service pack one
installed. If this says SP2, SP3 or an even larger number this is also fine. It
simply means that Microsoft have fixed even more bugs in their product
since this book was published.
If you do not see any reference to SPn, you have an early, un‐patched
version of Excel 2007 and you should update it (the update is free) via the
Internet.
7
Check the Operating System version.
Click the About Button next to about Microsoft Excel 2007. A dialog is
displayed with information about your copy of Excel.
I had many emails from readers
of my book: Learn Access VBA
2003 with the Smart Method
suggesting that some of the
examples didn’t work for them.
In almost every case the
problem was that they were
using an out‐of‐date version of
the application or the operating
system. After applying the
relevant service packs the
problems vanished.
For this reason it is important
that you make sure your
software is up to date.
note
What if I’m unable to
upgrade my operating
system or I’m using a
different operating
system?
Click the System Info… button. The Operating System (OS) Name and
Version will then be visible at the top right of the dialog:
Vista
Windows XP
Sometimes your corporate
policy will not allow you to
upgrade your operating system
or application service pack.
In this case you can still use this
book but be aware that it is
possible that some of the
features described may not
work as expected (or may not
work at all).
Once again, it doesn’t matter if you have Vista running a later service
pack than SP1 (or Windows XP running a later service pack than SP2).
If you’re running Vista or Windows XP, any edition will suffice (it
doesn’t have to be the Ultimate or Professional edition).
8
© 2008 The Smart Method Ltd
Click the close button
on the top right corner of each
dialog to return to the Excel screen.
25
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 1-2: Maximize,
minimize, re-size, move and
close the Excel window
The great successful men of the world have used their imaginations,
they think ahead and create their mental picture, and then go to work
materializing that picture in all its details, filling in here, adding a
little there, altering this a bit and that bit, but steadily building,
steadily building.
Robert Collier, American motivational author, ( 1885‐1950)
Now that Excel is open you are confronted with a dazzling array of
buttons, switches and other artifacts.
For now let’s explore the big picture by looking at how the Excel window
can be sized and moved. The details will come later.
1
Understand the Maximize, Minimize and Restore Down
buttons
At the top right corner of the Excel window you’ll see three
buttons.
The buttons that you see will depend upon how the Excel window
was left last time the application closed down. Normally the Excel
screen is Maximized to fill the screeen and you’ll see:
But if you had reduced the size of the Excel window so that it
didn’t fill the screen you’ll see this instead:
Try clicking the Maximize, Minimize and Restore Down buttons but
be careful to click the group of three buttons at the very top of the
26
www.LearnMicrosoftExcel.com
Session One: Basic Skills
window and not the similar group beneath (we’ll be visiting those
in the next lesson).
Maximize makes the Excel window completely fill the screen.
Minimize reduces Excel to a button on the bottom task bar.
Click this button again to restore the window to its previous size.
Restore Down makes the Excel window smaller allowing you to
re‐size the window.
2
Re-size the window
After clicking the Restore down button you are able to re‐size the
window. Hover over either the side of the window, or a corner of
the window, with your mouse cursor. The cursor shape will
change to a double‐headed arrow.
When you see either cursor shape, hold down the left mouse
button and move the mouse (this is called click and drag) to re‐size
the window.
Clicking and dragging a corner allows you to change both the
height and width of the window.
Clicking an edge allows you to change only one dimension.
3
Move the window.
Click and drag the Title Bar (the bar at the very top of the window)
to move the Excel Application window around the screen.
note
Other ways to close
down Excel
There are three expert ways of
closing Excel that will save a
second or two of your time.
1.
Double click the Office
button at the top left of
your screen.
2.
Press the + keys
on your keyboard. We’ll be
learning more about
keyboard shortcuts later in
this session.
3.
Press the + keys
on your keyboard.
© 2008 The Smart Method Ltd
4
Close Excel
There are two common ways to close down Excel and three lesser
used methods (see sidebar). Most people will use one of the
following methods:
Click on the Close button
window.
at the top right of the Excel application
OR
Click on the Office button
at the top left of the screen and then
click on the Exit Excel button at the bottom right of the dialog.
27
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 1-3: Understand the
Application and Workbook
windows
Excel can be thought of as a frame (the Application window) containing
one or more Workbook windows. Most of the time you will work with one
Application window containing one Workbook window.
The screen grab above shows the normal way of working. The Workbook
window is maximized within the Application window.
If the Workbook (that’s the lower group of three buttons) Restore Down
button is clicked the workbook window floats within the frame provided
by the Application window.
1
28
Close Excel down and re-start it again.
www.LearnMicrosoftExcel.com
Session One: Basic Skills
note
2
What’s the point of
having a floating
worksheet window?
Click the Workbook’s Restore Down button (that’s the one in
the lower group of three buttons).
The workbook floats within the Application window:
At this stage in your journey of
discovery, none at all.
You need to know how to
maximize, minimize and
restore down the workbook
window because you might do
it by accident and need to fix
things up again.
In Lesson 2‐2: Create a new
workbook and view two workbooks
at the same time, you’ll see how
Excel is able to display more
than one Workbook window
within the Application
window. This allows you to
view two worksheets at the
same time.
Notice that the workbook’s Maximize, Minimize and Restore Down
buttons are now on the top right corner of the floating window.
3
Click the Workbook’s Maximize button.
The workbook completely fills the Application window:
4
Understand the two close buttons.
You’ve probably noticed that there are two close buttons on screen.
One for the Application window, and one for the Workbook
window. How these buttons work is quite quirky.
Application close button
Closes both the application and workbook windows when
only one workbook is open, or no workbooks are open.
Closes only the active workbook (that’s the one that is
currently on display), when more than one workbook is open.
Workbook close button
Closes the workbook but always leave the application window
open.
© 2008 The Smart Method Ltd
29
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 1-4: Open and navigate
a workbook
important
Organizing your
sample files folder!
Excel uses the analogy of a book that has many pages. In Excel
terminology we use the term: Workbook for the book and Worksheet for
each of the pages. We’ll be learning more about worksheets later in this
session in Lesson 1‐7: View, add, rename, delete and navigate worksheet tabs.
1
Download the sample files (if you haven’t already done so).
1.
Open your web browser and type in the URL:
www.learnmicrosoftexcel.com
When you complete a lesson
that involves a sample file that
is changed, you will be
instructed to save the file with a
postfix.
2.
Click the download link on the top left of the home page.
3.
Download the sample files.
By the time you’ve completed
the course you’ll have sample
files such as:
Sales
Sales‐1
Sales‐2
Sales‐3 ... etc
The first file is a sample file that
you downloaded, and the
others (with the number
postfix) are interim versions as
you complete each lesson.
2
Open Excel and then click on the Office button
of the screen.
The sample file set includes the
sample file and all interim
versions.
2.
If your work‐in‐progress
becomes unusable (for
example after a system
crash) you can continue
without starting at the
beginning again.
at the top left
Select Open from the drop down menu. Navigate to the folder
containing the sample files and double click The Wealth of Nations
to open the sample workbook.
The interim versions are
provided for two reasons:
1.
Open the sample workbook: The Wealth of Nations.
If you prefer to use the keyboard, another way to open a workbook
is to hold down the key and then press the key.
3
Go to Cell ZZ3 using the Name box.
If a lesson doesn’t seem to
give the results described,
you can load the example
to give some clues about
what has gone wrong.
It is a good idea to place the
sample files in a different folder
to your saved work. If you
don’t do this you’ll be over‐
writing the sample interim files
(such as Sales‐1, Sales‐2 etc)
with your own finished work.
Excel uses the letter of the column and the number of the row to
identify cells. This is called the cell address. In the above example
the cell address of the active cell is B3.
The Wealth of Nations
30
www.LearnMicrosoftExcel.com
Session One: Basic Skills
In Excel 2007 there are a little over a million rows and a little over
sixteen thousand columns. You may wonder how it is possible to
name all of these columns with only 26 letters in the alphabet.
note
Excel 2007 is big!
When Excel runs out of letters it starts using two: X,Y,Z and then
AA, AB, AC etc. But even two letters is not enough. When Excel
reaches column ZZ it starts using three letters: ZX, ZY, ZZ and
then AAA, AAB, AAC etc.
ʺSpace is big ‐ really big ‐ you
just wonʹt believe how vastly,
hugely mind‐bogglingly big it
is. You may think itʹs a long
way down the road to the
chemist, but thatʹs just peanuts
to space”.
The currently selected cell is called the Active Cell and has a black
line around it. The Active Cell’s address is always displayed in the
Name Box and its contents are displayed in the Formula Bar.
Douglas Adams, The
Hitchhikers’ Guide to the
Galaxy
We can also use the Name Box to move to a specific cell.
To see this in action, type ZZ3 into the Name Box and then press the
key. You are teleported to cell ZZ3:
A compelling reason to
upgrade from Excel 2003 to the
new 2007 version is the sheer
size of worksheets that can now
be manipulated.
Excel versions 97‐2003 were
limited to 65,536 rows and 256
columns.
Many of my clients have
needed to overcome the “65,000
row problem” in earlier Excel
versions. Until now that meant
moving to Access (Microsoft’s
database management
application).
Excel 2007 worksheets can have
up to 1,048,576 rows and 16,585
columns.
Put into more understandable
terms.
You could create a single
worksheet with the name of
every human being on the
planet and you wouldn’t run
out of cells (in fact, you
wouldn’t have even used half
of your worksheet).
4
5
6
Return to Cell A1 by pressing +.
Go to the end of the worksheet by pressing +.
Use the Scroll Bars.
There are two scroll bars for Excel’s workbook window.
The vertical scroll bar runs from top to bottom of the worksheet
window and allows you to quickly move up and down the
worksheet.
The horizontal scroll bar is at the bottom right hand side of the
workbook window and allows you to move to the left and right in
wide worksheets.
Here’s how the scroll bars work:
If you filled every cell and
printed out the worksheet it
would be 3.25 miles (5.24
kilometres) long and 320
yards (293 Metres) wide –
that’s a lot of paper.
Believe it or not, some of my
clients aren’t even happy with
this and are now running into
the “million row problem”!
© 2008 The Smart Method Ltd
31
Learn Excel 2007 Essential Skills with The Smart Method
Lesson 1-5: Save a workbook
note
1
Save the workbook
When you are editing a workbook, the changes you make are only
held in the computer’s memory. If there is a power cut or your
computer crashes you will lose any work that has been done since
the last save.
You can also use the +
keyboard shortcut to save your
work.
For this reason you should get into the habit of regularly saving
your work.
Even though we haven’t changed this worksheet, let’s save it now
by clicking the Save button on the Quick Access Toolbar at the top
left of the screen.
2
Save the workbook with a different name.
Click the Office button and then hover over the Save As item in the
drop‐down dialog.
You can see that it is possible to save your file in many different
formats.
3
Understand file formats.
Excel Workbook (the Open XML format)
Until very recently every program stored its information on the
hard disk in a completely different way. These incompatible
formats are called binary formats. This made it very difficult to
write applications that could be used together.
The Wealth of Nations
32
All of this has changed with the new file format: Office Open XML.
www.LearnMicrosoftExcel.com
Session One: Basic Skills
Microsoft have published exactly how this format works and given
it away free to the world’s developer community. This allows
other programs to easily work with Excel workbook files. For
example, Apple’s iPhone already supports Office Open XML Email
attachments.
note
How users of earlier
office versions can
read your OpenXML
files
Unfortunately the future has to co‐exist with the past and there are
a lot of people in the world using the older versions of Office (97,
2000, 2002 and 2003). If you save your files in the new Open XML
format only people running Office 2007 will be able to read them
(but see the sidebar for a potential solution to this problem).
Mic...
Purchase answer to see full
attachment