CALCULATIONS/COMMENTS

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

The text below is from the Excel help file:

Calculation operators in formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

Arithmetic operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator

Meaning

Example

+ (plus sign)

Addition

3+3

– (minus sign)

Subtraction Negation

3–1–1

* (asterisk)

Multiplication

3*3

/ (forward slash)

Division

3/3

% (percent sign)

Percent

20%

^ (caret)

Exponentiation

3^2 (the same as 3*3)

Comparison operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Comparison operator

Meaning

Example

= (equal sign)

Equal to

A1=B1

(greater than sign)

Greater than

A1>B1

< (less than sign)

Less than

A1<B1

>= (greater than or equal to sign)

Greater than or equal to

A1>=B1

<= (less than or equal to sign)

Less than or equal to

A1<=B1

<> (not equal to sign)

Not equal to

A1<>B1

Text concatenation operator

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Text operator

Meaning

Example

& (ampersand)

Connects, or concatenates, two values to produce one continuous text value

"North" & "wind" produce "Northwind"

Reference operators

Combine ranges of cells for calculations with the following operators.

Reference operator

Meaning

Example

: (colon)

Range operator, which produces one reference to all the cells between two references, including the two references

B5:B15

, (comma)

Union operator, which combines multiple references into one reference

SUM(B5:B15,D5:D15)

End of MS Excel Help file

When Excel performs a calculation there are a few things you need to know.  Firstly you have to enter an = (equals) sign first.  This is the trigger to Excel that a calculation is following.  Secondly, Excel calculates 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:

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. 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 the 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 the workbooks in lessons 4 and 5 that we 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:

Whichever method we use Excel will:

The user name of your PC  can be located (and changed if you wish) by selecting the Office button, then Excel Options and in Personalise your Copy of Microsoft Office under the Popular heading you will see User Name.

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 it's faster.  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.

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, and its outline.

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, show the comment right clicking and selecting Show/Hide Comments then select the outer edge of the Comment box until you have a fuzzy outline, then right click on it and select Format comment.  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.

Find and Select

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:

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 we 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:

Let's now use the Replace dialog to replace text or values in our cells.

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.

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.

You can also right click and select Clear Contents.

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.

 

 

 

 
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.