Category Archives: Hints and Tips

Excel Functions and Formulas Hints and Tips

Logical testing in Excel: A basic primer

In this article, I want to take you through the basics of logical testing in Microsoft Excel.   Logical testing forms the basis of many complex spreadsheet models, and it’s also a large part of some of the more powerful Excel functions.  A good grasp of logical testing and how it works can help you out no end as you learn more about formulas and functions.

So, what do I mean by logical testing?

A logical test is essentially a question that you ‘ask’ Excel.  In fact, it’s not really a question at all, it’s more of a bold statement which Excel then evaluates and either agrees with, or argues with us about.  Let’s use an example.

Is 5 equal to 10?  The answer is obviously no.  Here’s how to ask the same question to Excel.  In any cell, type in the following:-

=5=10

And press the Enter key.  The cell will now display a FALSE result, confirming that 5 is indeed not equal to 10.  The logical test (that is, the question ‘is 5 equal to 10’) is posed to Excel in terms of a simple formula.  In this case our formula states that 5 is equal to 10 and, when we press the Enter key, Excel either agrees with our statement, or tells us straight that it’s a pack of lies.

A couple more examples:-

Is 10 less than 20?

Formula:  =10<20
Result: TRUE

Is the value in cell C5 equal to the value in cell D5?

Formula:  =C5=D5
Result:  FALSE

And that’s it, you’ve just learned the basics of logical testing.  I know it’s pretty simple stuff, but I recently did a training course on Excel and a few of the attendees weren’t too clear on what was happening, so I took them through a similar spoken tutorial which seemed to help.  These fundamentals are used all of the time in advanced spreadsheets, and an understanding of how and why they work can really help when using some of the more complex Excel functions.

 

Excel Hints and Tips Interface

Listing all names and ranges in Excel #2

Usually, if I write a piece of utility code I check online at some point to see if it hasn’t already been done.  It’s always good to see other ways of doing something.  I try to always write the code myself first as a challenge.

Anyway, yesterday after posting my code about using VBA to list names in Excel I checked and actually found that Excel already has a way to do this built in:-

Click Insert > Name > Paste… and you get this handy little dialog.

Clicking on the ‘Paste List’ button on this form does exactly the same as yesterday’s code, barring making a new worksheet and adding bold and underlined headers!

I guess you do learn something new everyday!

Excel Hints and Tips

Excel: Picture Links in Excel 2003

I was reading Chandoo’s article on Picture Links in Excel and was quite impressed, as I hadn’t come across them before.  The one problem however was that I couldn’t find out how to paste a picture link in Excel 2003!

I’ve found out now, however, and it’s really simple, so I’ll share with you:-

  1. Copy a selection of data from Excel for which you want to link as an image
  2. Hold down the shift key and click the Edit Menu and ‘Paste Picture Link’ will appear in the list.  Using this you can create picture links to your hearts content!

The one thing I find really hard to understand, however is why Microsoft have hidden the feature away in this way

Excel Hints and Tips Tips Visual Basic For Applications (VBA)

Using “Is Not Intersect” in VBA code

I’ve been writing some code to capture the event when a particular cell is changed in one of my Excel Worksheets and, using the power of the interpipes found a simple solution to do this.  It looks something like this in code:-

Private Sub Worksheet_Change(ByVal Target As Range)
	If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
		MsgBox "Hello"
	End If
End Sub

Whilst this works perfectly and does what I want it to do I didn’t understand why I should use ‘If Not Intersect….Is Nothing’ when it would be simpler to use ‘If Intersect(Target, Range(“A1:A10”) > 0’.

I soon found out when I changed something outside of the target range (ie A1:A10) and Excel threw an error at me.  Wondering why I had a bit of a browse on the internet and discovered the reason.  The Intersect method returns a range object, but if there is no range found it will simply return an answer of nothing, which causes the error.  Using this code ensures that, if nothing is returned, the IF statement can just end with no errors returned to Excel.  Nice.

It’s probably not the most interesting post, but I thought I’d document it in case you or I ever come across it again.  A full (and much more detailed) examination of the Intersect method in VBA can be found here.