Other Excel Add-In, Templates and Training
Lesson 6
CALCULATIONS / COMMENTS
DRAWING TOOLBAR / EDIT REPLACE /
DELETE / CLEAR
In this lesson we
complete our session on Formulas and Functions by looking at how Excel
calculates. After this we will take a break from the very important
calculation side of things to look at some of Excels very handy features
that can make setting up, using and editing a spreadsheet much easier.
How Excel Calculates
One of the fundamental things
that you must know about Formulas and Functions is the method in which Excel
performs calculations. We will not go into any great detail in this, but
there are some basics all Excel users need to know.
The main function of Excel is
obviously the number crunching side of things and a good spreadsheet is one that
returns accurate results 100% of the time. So whilst we may have a spreadsheet
that looks very pretty and is formatted to make it look a million dollars, it is
the guts of the spreadsheet, or the nuts and bolts, that make it either a
workable spreadsheet or an unworkable spreadsheet, not the visual appeal.
Operators that Excel Recognises
| Operator | Explanation | Examples |
| +
| Add
| 1+1
or
1+2+3
|
| – |
Subtract
|
5-2
or
3-2-1
|
| *
|
Multiply
|
2*2
or
1*2*3
|
| /
|
Divide
|
4/2
|
| %
| Percent or Percentage
| 50%
|
| ^
| Exponent
| 2^3
(is the same as 2*2*2)
|
| =
|
Equal to
|
A1=0
or
A1=C3
|
|
>
|
Greater than
|
A1>0
or
A1>B2
|
|
<
|
Less than
|
A1<0
or
A1<B2
|
|
>=
|
Greater than or equal to
|
A1>=0
or
C1>=D1
|
|
<=
|
Less than or equal to
|
A1<=0
or
A1<=B1
|
|
<>
|
Not equal to
|
A1<>0
or
A2<>B2
|
| :
| Range operator
| A1:A10
(refers to the cells from A1 to A10)
|
|
,
| Union operator. For combining Cells or Range for calculation.
| SUM(A1:A10,E1:E10)
|
| & |
Concatenate |
"A cat" & "and" & "a dog"
(gives the result of "A cat and a dog") |
When Excel performs a
calculation it does so in the following order:
If a formula contained both a
multiplication and a division operator Excel would
calculate them from left to right. The same would apply for subtraction
and addition. We can change the order in which Excel does its
calculations by closing the relative function in parenthesis. Let's say we had
the formula =10-10*10 the result would be -90 (negative 90). If we
then used =(10-10)*10 the result would be 0 (zero). In other words
we have forced Excel to change its natural order of calculation. Excel is
quite happy to do this.
Some examples of this would
be:
-
=5+5*5+5+5 would result in 40
-
=(5+5)*5+5+5 would result in 60
-
=(5+5)*(5+5)+5 would result in 105
So as you can see, we can
manipulate any formula to calculate in the order we want, simply by placing the
parenthesis in the appropriate places.
We will leave Formulas at this
stage to allow you time to let what we have discussed to date sink in. If there
are any questions you would like to ask or any particular formulas you would
like explained you only need to ask. What we have shown you is what we consider
the least you should know about Excel and formulas. Once you have gone over and
fully understand these lessons on Excels functions and formulas you will have
the foundations on which we can build. You may also discover that you will know
the fundamentals of Excel formulas and functions better than a lot of so called
experienced users!!
Let's now move on to some more
light-hearted features of Excel that can make life that bit easier.
Cell Comments
cell Comments, or notes
as they are often called, were first introduced in Excel 97. They are basically
the equivalent of sticky notes that have become so popular in offices throughout
the world. They allow us to attach a comment to a cell to inform, remind
or explain the content of a cell or range of cells. We must stress,
however that they shouldn't be used too liberally as not only will they loose
their impact but they can cause a file size to increase dramatically. As a rule
of thumb we would recommend using no more than 50 or so per Workbook. You would
have noticed in lessons 4 and 5 that I used cell Comments to help explain the
formulas that resided in the cells they were attached to. As with most
features in Excel, there are numerous ways we can insert a cell Comment,
the method used is entirely up to the user.
To insert a cell Comment,
do one of the following:
-
Right
click and select Insert Comment
-
Go to
Insert>Comment
-
Push
Shift + F2
-
Display
the Reviewing Toolbar. Go to View>Toolbars>Reviewing or right
click on any visible Toolbar and select Reviewing. Once the
Reviewing Toolbar is visible click the first icon on the left (New
comment).
Whichever method we use Excel
will:
-
Insert
the comment into the cell that is active at the time.
-
Place in
the user name for the PC being used at the time.
-
Place the
mouse insertion point within the comment ready for you to type. This is a cell
Comments Edit mode.
The user name of your PC is
determined by Excel under the General page tab of the Options
dialog box. We can change this by going to Tools>Options and selecting
the General page tab and typing whatever we like in the User name
box situated at the bottom.
This will not affect any cell
Comments that have already been inserted only new ones we insert after making
the change.
Let's insert a cell Comment
into any cell on any Worksheet using any of the above methods, we prefer
the right click method. As mentioned before, you will be in Edit
mode so we can simply type any text we like. Once you reach the edge of the
cell Comment, Excel will automatically drop us down to the next line.
This can also be done at any time by pushing Enter. If you keep typing
until you reach the bottom edge of the cell Comment Excel will automatically
push the top line out of sight and continue on.
Once you have finished typing
and click out of the cell, the comment will automatically do what is known as
Hide itself. If the comment is still fully visible you may need to do one or
both of the steps below:
-
Display
the Reviewing Toolbar. Go to View>Toolbars>Reviewing or right
click on any visible Toolbar and select Reviewing. Once the
Reviewing Toolbar is visible click the fifth icon on the left (Hide All
Comments) this is a toggle key.
-
Go to
Tools>Options select the View page tab and ensure the Comment
indicator only check box is checked.
In fact it would be a good
idea to have the Reviewing Toolbar visible while we go through cell
Comments, so show it and then either drag and dock it, or double click the blue
title bar. If you prefer you could also leave it as a floating Toolbar.
You will notice that the cell
containing the cell Comment has a small red triangle in the top right
corner. This is the Comment indicator, or flag as it is sometimes
called. This simply lets us know that there is a comment in the cell. To read
the comment simply hover your mouse pointer over the cell and it will display
the comment. Once you move your mouse pointer away from the cell the comment
will hide itself again.
Many books and other
literature will tell you that you should select the cell to display a cell
Comment and the cell being active is what displays the comment, this not
technically true! A simple way to prove this is to click in the cell directly
below the cell with the cell Comment, move your mouse pointer away from the cell
and use the Up arrow on the keyboard to activate the cell. You will not
see the comment until you hover your mouse pointer over the cell.
Edit A cell Comment
Once we have a comment in a
cell we can Edit it in much the same way as we can format a cell and/or
it's content. This means we can nominate the type of text, the color of the text
and/or the comment itself, its size, its outline and even it's shape.
Most of these can be achieved
via the Format Comment dialog box and are self explanatory, so we will
only explain the little known ones. However, as with any part of Excel if
you would like some detail, you only have to ask!
Ok, the easiest way to Edit
a cell Comment is to click in the cell containing the cell Comment, right click
and select Edit comment. This will put us in Edit mode, exactly as
we were when we first inserted it. The first thing you may notice is the fuzzy
outline around the cell Comment and the eight small white boxes or circles
(depending on which version of Excel you are using). These white boxes are
called the Size handles and are common to all shapes, textboxes, comments
and charts etc. All you need to do is hover your mouse pointer over one of them
until your mouse pointer changes to an up/down arrow, left click, then drag and
release. If you use the Size handles in either corner of the cell Comment
the height and width will change in accordance with each other.
Let's now display the
Format comment dialog box. To do this, double click on the
outer edge, or right click on it and select Format comment.
Either way, Excel will display the Format comment dialog box. On
this you should see eight page tabs and most of these are purely for visual
effect with the exception of Protection and Properties. Protection
is will explain is a later lesson. If there is any aspect of this dialog box you
would like explained just let us know.
There are a few features of
cell Comments that cannot be changed via the Format comment dialog box
and these are the shape and 3d effect. For both of these examples
you will need to use the Drawing Toolbar. So if it is not visible
go to View>Toolbars>Drawing or right click on any toolbar and select
Drawing. By default the Drawing toolbar will dock itself at the
bottom of your screen.
Change the Shape
-
Select a
cell that has a cell Comment, right click and select Edit Comment then select
the outer edge.
-
On the
Drawing Toolbar go to Draw>Change AutoShape.
-
Wave you
mouse pointer over the sub menus to see your choices.
-
Select
any shape.
Now you will have a comment
that has the shape of the AutoShape you chose.
Give a 3d Effect
-
Select a
cell that has a cell Comment, right click and select Edit Comment then
select the outer edge.
-
On the
Drawing Toolbar go to Draw>3-D. This is the last icon on the right
in the shape of a 3-D box. Select any 3-D setting
Now you will have a comment
that has the 3-D effect that you have chosen.
Give a Shadow Setting
-
Select a
cell that has a cell Comment, right click and select Edit Comment then
select the outer edge.
-
On the
Drawing Toolbar go to Draw>Shadow this is the second last icon on
the right.
-
Select
any shadow setting
Now you will have a comment
that has the shadow effect you chose.
That is about it for cell
Comments, but please ask any questions that you may have.
Find and Replace
As with most Microsoft Office
applications Excel has a Find dialog and a Replace dialog. These
make it possible to quickly find a particular piece of text or a value in a
Worksheet or Worksheets.
There are two rules that apply
to both of these dialog boxes and these are:
-
If we
only have a single cell selected they search through the entire Worksheet.
-
If we
have more than one cell selected, they will search only through the selected
range.
The Find dialog box and
Replace dialog box are very closely related. They are more often than not
used in conjunction with each other. Basically, if you opt for Edit you
will be able to use the Find and/or the Replace. Our
recommendation is to forget about the Find box unless you are searching
for text or values that reside in cell Comments. We will use cell
Comments as our first example, but first we feel it is important to point out
that the Look in box within the Find dialog box has three options;
Formulas, Values and Comments. The Formulas and Values options can
give very misleading results, so I recommend not using them. We will
however, show you an alternative later.
The comments option of the
Find dialog box does not have a Replace option and works like this:
-
On a
clean Worksheet, type the simple formula =B1+2 in cell A1.
-
In A2,
type the number 2.
-
In A3,
insert a cell Comment and type the number 2 inside it.
-
In A4,
type the word Cat, push the space bar and then type cat.
-
In A5,
insert another cell Comment and type the number 2.
-
In A6,
type =2+B6
-
With any
single cell selected, either go to Edit>Find or push Ctrl + F
-
Type the
number 2 in the Find What box. You will notice that there is a
Search box that contains the two options By Rows or By
Columns.
Note - These options will have very little bearing on a Worksheet
unless virtually every cell is filled with data, which is very unlikely! Even
then, it will only make the difference of a second or two in the way it
searches
-
Have
Comments selected in the Look In box, then click Find Next
and Excel will take you first to cell A3.
-
Click
Find Next again, and you will be taken to cell A5. Note
- If you keep clicking Find Next, you will toggle between A3 and
A5.
-
What you
need to do from here should you wish to replace the number 2 within the
cell Comment(s) is click Cancel and edit the comment as we discussed
previously.
Let's now use the
Edit>Replace dialog to replace text or values in our cells.
-
Again,
with any single cell selected, go to Edit>Replace or use Ctrl + H.
-
Type the
word cat (in lower case) in the Find What box, then type the word dog
(lower case) in the Replace With box.
-
Check the
Find Entire cells Only box and click Replace. You should get a
message come up telling you that Microsoft Excel cannot find a match. This is
because we do not have the single word cat in a cell on its own, which is what
the Find Entire cells Only option forces it to look for.
-
Deselect
(or uncheck) the Find Entire cells Only checkbox and click the Match
Case checkbox. Now click the Find Next button and we should be
taken straight to cell A4, which contains the two words Cat and
cat. One with the uppercase C and one with a lowercase c.
-
Click the
Replace button and our word cat (with the lowercase c) should be
replaced with the word dog. The word Cat (with an uppercase C) will be left
unchanged, even if you click the Replace button again.
-
Click
Close
We should note here that
clicking the Replace button will only ever replace the text or value in
the current active cell. Clicking the Replace All button will replace all
matching text or values on the entire Worksheet, unless we had more than one
cell or a range of cells selected before we activated the Replace dialog.
Let's now assume we want to
replace the number 2, but only in cell A1, where it is part of our
formula.
-
Select
any cell on your Worksheet other than cell A1.
-
Push
F5 and click Special on the Go To dialog box. This will
display what is known as the Go To Special dialog box.
-
Check the
Formulas option and leave all the sub-options below this, as it does
not matter in this case.
-
Click
OK
-
Now push
Ctrl + H or go to Edit>Replace. Type 2 in the Find What
box, and the number 3 in the Replace With box. Ensure the Find
Entire cells Only option is not checked.
-
Click
Replace All and then Cancel.
-
If you
then click back in cell A1 and look in your formula bar, you should now
see the formula = B1+3. If you click in cell A6 you should see =3+B6
in your formula bar.
The reason the Replace All
only replaced the number 2 in our formulas was simply because we had more
than one cell selected, which is telling Excel to only replace the number 2
in the selected cells.
The Different Methods
of Clearing cell Contents
Normally when working in
Excel, if we want to remove a cell(s) contents we would simply push the
Delete key on our keyboard. This would delete the contents of the cell.
However, it will not delete the formatting of the cell in any way. What this
means is, if we had a cell with a yellow background, blue font and formatted for
currency, and the value $10.00 in the cell. Pushing Delete would
only remove the value 10. All the other cell attributes would remain intact.
Let's try just this so that
you can see what we mean.
-
In cell
A3, type the value 10. Format the cell for currency of any kind,
make it yellow and make the font blue.
-
Click in
cell A3 and push Delete.
-
Now type
any number in the cell and as you will see, you will still have a yellow cell
with blue font and formatted for currency.
-
This
time, with cell A3 selected, go to Edit>Clear>All
-
Now type
any number in cell A3 and all you will have is the number in black
font, with no formatting and no background colour.
-
Click the
Undo button to undo the Clear All action.
-
Ensure
A3 is still your active cell. Go to Edit>Clear>Formats and cell
A3 should now only have the number 10 in the cell without any
formatting of any kind.
While these differences may
appear subtle, they can be very handy should you have a large range of cells
which you have specifically formatted and you only want to clear the contents
and not the formatting or vice versa.
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
Special :
Get the complete training Excel Training for free when you purchase our Technical Indicators now!
|
