• List of Excel Add-In
• List of Excel Templates
• List of Excel Training
• Back to Free Excel Training Index

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

When Excel performs a calculation it does so in the following order:

• Exponentiation

• M ultiplication and Division

• Subtraction and Addition

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.