May 01, 2004

Quick Navigation, Part 2

Last time we looked at Hyperlinks. Here’s something extra I always do for really big files.

I always find the 2 places I most want to go are to the top of the file and the bottom of the file. So I’ve made 2 buttons like below using the oval AutoShape(You can use any shape etc you want to. I’ve also tried to make them more attractive by changing the color and also adding a shadow effect.

Believe it or not, I think I’ll start at the bottom. Scroll down to the very bottom of the file. In the case of the below picture, I’ve selected cell B102 then entered FileBottom in the Name Box.

Scroll back to the Scroll Down button and right click it to select Hyperlink. Select the Place in This Document tab and then the name you entered, FileBottom. From now on pushing the Scroll Down button will get to the bottom of things real fast.

Now, let’s try something else. Open your Visual Basic Editor and paste the following code. (Try pushing Ctrl + F11)

Sub ScrollUp()
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub

Go back to the Excel File and right click the Scroll Up button to select Assign Macro and choose the ScrollUp subroutine you’ve just pasted. You can now use the Scroll Up button to get to the top in a hurry.

One more thing, you’ll notice when you scroll down the bottom that the Scroll Up and Scroll Down buttons are not visible.

No problem. Click the Window Menu and then Freeze Panes. I recommend that you do this just under the titles of the file. This will enable you to use the buttons as well see the titles no matter how far you scroll down.

Of course, you can change any of these settings to suit what’s best for you. For example, you could adapt the ScrollUp macro to go somewhere else besides the top left corner of the book by changing the values of either ActiveWindow.ScrollRow = 1 or ActiveWindow.ScrollColumn = 1.

I hope these tips were useful. It’s time for bed.

April 30, 2004

Quick Navigation, Part 1

A couple of days ago I noticed that Dick Kusleika wrote about hyperlinks on his blog, Daily Dose of Excel (I read it everyday, most definitely recommended!). He gave advice about using hyperlinks and it gave me an idea for this post - quick navigation around your workbooks.

There are quite a few ways to move quickly around a workbook. One of the simplest ways is to enter a cell reference such as H176 into the name box (the little box next to the formula bar with a drop down arrow) and push Enter. Presto! You've been teleported there in a flash!

Great trick, but you have to know your destination in advance, otherwise you're going to have to scroll there anyway.

How to get around this? There is at least one way if you are going to navigate there on a regular basis and don't want to burden yourself with the horrific task of remembering multiple cell references.

Using the Insert Menu, select Name and then Define. At the top enter a name such as "AccountsTotals", then at the bottom enter the cell reference (you can use the little red arrow to get there). You can also go to the cell reference and enter the name directly into the Name Box.

Now, you are ready to get around in a jiffy. Just use the drop down arrow in the Name Box and select where you want to go. You can also use the Goto dialog box. Get there by clicking the Edit menu or try either Ctrl + G or just use the F5 button.

So where do hyperlinks come in? Well, first of all you need to open the Hyperlinks dialog box. The easiest way is to use the right click menu, or you could find it in the Insert menu. (The shortcut keys are Ctrl + K)

Select Place in This Document and enter the cell reference. You can do this manually by entering the cell reference and name of your choice, or you can select an existing name if you've defined one already.

When finished, you should see a link appear. It's blue and underlined but you can change the font color and remove the underline if you want to, it's entirely editable as far as appearance goes.

Okay, that's it for today. More on navigation next time I post.

April 29, 2004


There are different kinds of lookup functions available. VLOOKUP, HLOOKUP, INDEX, MATCH, CHOOSE and many more. But LOOKUP by itself is probably the most simple and easiest to use.

How does it work?
First we need a reference cell and at least 2 lists to make a comparison (cross-reference). In the regular LOOKUP function the 2 lists should be vertical.

Say I wanted a quick reference to the days in a month.

As you can see I used reference numbers from 1-12 fro the months of the year in cells A2:A13 and the days in the month in cells C2:C13. (Cell B2:B13 show the month's names just to make it easier to understand. Although I can use text for reference in LOOKUP, I prefer to use numbers because it is so much faster)

For February, I've entered the month's reference number shown in cell A3 into cell A16. Then in cell B16, I've entered the following formula =LOOKUP(A16,A2:A13,C2:C13).
This means that the number I've entered in cell A16 will be searched for in list A2:A13, and then cross-referenced in list C2:C13 to give me a value of 28.

But what about leap years? Okay, I can use another LOOKUP function for this too, by adding a new list in cells D2:D13 and entered this formula into cell C16, =LOOKUP(A16,A2:A13,D2:D13).

Yesterday I used logic formulas to choose alternatives between different customers to calculate their shipping time. With just 2 customers, it was starting to get quite difficult to avoid potential errors in calculation. Now let's apply LOOKUP to the task.

Say my customers have now increased. I have a total of 10 customers and I use 2 different modes of shipping for each of them. (It doesn't matter whether it is by courier, truck, air or sea. The important thing is there are 2 modes available for each customer)

I've entered the customer's reference numbers in cells A2:A11, customer's names in cells B2:B11, and days required for either shipment mode 1 or 2 in cells C2:C11 and D2:D11 respectively.

I enter the customer's reference number in cell A14 and the shipment mode in cell B14.
In cell C14, I have entered this formula, =IF(B14=1,LOOKUP(A14,A2:A11,C2:C11),LOOKUP(A14,A2:A11,D2:D11)).

It means to check the shipment mode in cell B14 and then cross-reference the list A2:A11 with list C2:C11 or list D2:D11 depending on the shipment mode. Then in cell D14 I enter the shipment date, and enter =C14+D14 in cell E14 to give me the arrival date.

Somewhat complex, but trying to do all of this with only logic formulas by themselves would involve a lot more work. LOOKUP does it all for me.

More support from the very top!

I've just been listed on The Spreadsheet Page which is by the man himself, John Walkenbach (aka J-Walk)

This is indeed an honor. Thank you very much John. I'll try to behave myself on your blog :)

April 28, 2004

More on Logic Formulas
I covered quite a bit of territory yesterday and thought some revision might be in order.

Okay, let's use IF, AND and OR for some potential real life applications.

I've mentioned that I use Excel for logistics. To calculate shipping time, I could just use a calendar and count the days, but I could also use Excel to do it for me. Let's consider time required for shipments to arrive at two different customer's locations, one is domestic and the other is overseas.

Customer A (Domestic)
Courier = 2 days (Overnight Courier)
Truck = 3 days

Customer B (Overseas)
Air = 3 days
Sea = 10 days

I enter the following information in the appropriate cells.
Customer Name in cell A1
Shipment Mode in cell B1
Shipment Date in cell C1

Here's a possible formula
=IF(AND(A1="Customer A",B1="Courier"),C1+2,IF(AND(A1="Customer B",B1="Sea"),C1+10,C1+3))

What does this mean?
If I ship to Customer A and I am shipping by courier, I add 2 days to the shipment date in C1.
If this is not the case, then I move on to the second IF formula which says, if I ship to Customer B and I am shipping by Sea then I add 10 days to the shipment date. The third alternative means that I am neither shipping to Customer A by courier or Customer B by sea, so I add 3 days for either shipment by Truck or Air.

Here's the catch. What happens if I enter Customer A into cell A1 and Sea into cell B1 or Customer B into cell A1 and Courier into cell B1? In this case, the above formula will give me an incorrect answer so here is a solution. (Shipping time for truck or air is the same, so it won?ft matter if you mix them up in this case)

To keep things simple, let's say that I had entered the above formula into cell D1 and the next formula into cell E1.

=IF(OR(AND(A1="Customer A",B1="Sea"),AND(A1="Customer B",B1="Courier")),"Please re-select Customer Name or Shipment Mode",D1)

This will have the effect of telling me to re-select the Customer Name or Shipment Mode if I make a mistake or telling me the correctly calculated shipping time calculated in cell D1.

Alternatively I could combine both formulas to be entered in cell D1 like this.
=IF(OR(AND(A1="Customer A",B1="Sea"),AND(A1="Customer B",B1="Courier")),"Please re-select Customer Name or Shipment Mode",IF(AND(A1="Customer A",B1="Courier"),C1+2,IF(AND(A1="Customer B",B1="Sea"),C1+10,C1+3)))

Either way will do the job. But what if there are more than 2 customers or other forms of transport? To accommodate these extra criteria, you could just keep making increasingly elaborate formulas but remember there is a limit as to how many times you can use IF, AND or OR functions.

You could try spreading the formula out to 2 or more cells like above in cells D1 and E1, each cell calculating a part of the overall formula.

Or you could use something like a LOOKUP function. This will simplify things a great deal. I'll be looking at LOOKUP the next time I post.

With friends like these...

Special thanks to Masaru Kaji (aka Colo) and Dick Kusleika, both MVPs who have shown their support for my modest entry into the greater world of Excel.

Thanks guys. With friends like you, I wish I had started sooner.

Colo's Excel Junk Room

Daily Dose of Excel

The Excel Maniacs, Colo's Diary

April 27, 2004

Logic Formulas
To the best of my knowledge there are just 6 logic functions within Excel. But these 6 functions should be all that you will ever need.


I would like to have a look at the first four of these functions. (To be honest, I don't think the TRUE and FALSE functions are particularly useful by themselves. Also please note that you can combine these functions to make custom made functions such as NOR by using NOT and OR, or NAND using NOT and AND)

This is definitely one of the most useful functions in Excel. It basically works like this, =IF(criteria is true, TRUE, FALSE). For example, if in cell A1 you have the number 10, =IF(A1=10,TRUE,FALSE) will give you TRUE. If I changed the number in A1 to 9, the same function will give me FALSE because A1 does not equal 10.

In addition to the equal sign (=), you can also use greater than (>), less than (<), or make combinations such as <=, >=, and <>. Text is also recognized so formulas such as =IF(A1="ten",TRUE,FALSE) will give me TRUE if the A1 cell had been entered as 'ten' instead of just 10. Upper case or lower case is not affected, you could even write TeN or tEn and still get the same result. Note that numbers, cell references, TRUE and FALSE do not require quotation marks.

You can also change the TRUE and FALSE response to something like =IF(A1="","Please enter something","Thanks"). The double quotation marks with nothing between them mean that the A1 cell is blank.

Finally, you can use equations and other functions combined such as =IF(A1=100,A1+20,IF(A1>100,A1+30,"Less than 100")).

This is an example of a nested IF formula, you can use up to seven IFs together in the same formula, just don't forget to add extra brackets.

You can use this formula by itself to get TRUE or FALSE. It's a multicriteria function, you can add up to 30 criteria that must all be correct to get TRUE. Here's an example - =AND(A1=12,B1="John") will give you TRUE only if both 12 and 'John' are entered into cells A1 and B1.

This is another multicriteria function (up to 30 criteria just like AND). Use this to choose at least one criteria. For example, =OR(A1=25,A1=40,B1=75) will give you TRUE if at least one of the criteria are met. Notice that I used cell A1 twice. You can multi-query the same cell with OR but not AND. There is no way that a single cell can have more than one value at the same time.

NOT seems to be applicable to just one criteria at a time. No problem, you can use it together with IF, AND or OR, such as in =NOT(OR(A1=32,B1=60,C1="Overdue",D1=E1+F1)) or perhaps =IF(AND(A1>24,OR(NOT(B1>100),C1=TRUE),D1="Paid"),"Great!","Try again").

Complex? Practice makes perfect :)

April 25, 2004

More Custom Formatting

I could probably write a book on Custom Formatting and still not cover everything :)

I see that the options offered on my Excel 2003 are becoming more diverse but here are some simple formats that you should know - adapt them to what best suits you. Right click the cell(s) you want to change and select the Format Cells dialog box, then choose Number, Custom.

Numbers & Currency
0 a general number format
0.00 a number with 2 decimal places (add or delete zeros for more or less decimal places)
$0.00 dollar currency with 2 decimal places
#,##0 a number with commas added for every thousand, million, billion...
$#,##0.00 dollar currency with 2 decimal places and commas added for every thousand, million, billion...
#,##0, will round your number to thousands
#,##0,, will round your number to millions

# ?/? simple fraction rounded up to 1 denominator
# ?/?? simple fraction rounded up to 2 denominators
# ?/??? simple fraction rounded up to 3 denominators
# ?/???? simple fraction rounded up to 4 denominators (This appears to be the limit for fractions, I don't know the limit for decimals but I can tell you it's a lot!)
# #/4 fraction divided into quarters
# #/16 fraction divided into 16ths
# #/100 fraction divided into 100ths

Dates and Time
Dates (see my previous post)
h:mm:ss hours, minutes and seconds
h:mm AM/PM hours and minutes with AM or PM

% Just add a percentage mark to a number format (decimal numbers or commas are okay)

Very useful. You can use this to enter almost anything and what you enter will be what is displayed. Note: You'll have to change to another format in many cases such as entering formulas otherwise they will not work.

@ You can use this to add whatever word you enter to text that is already entered into your format. For example, "This is a "@ will give you 'This is a boat', if you enter 'boat' into the cell. You can enter text before and after the @ mark. (Use double quotation marks for formatting, single quotation marks are just to show my examples)

Colors and Equations
You can do simple conditional formatting by entering colors or equations into square brackets. For example, [Red][=<100] will turn the font red for any number less than or equal to 100, but might cause the value to disappear if over 100. I suggest using a semicolon and using a formula such as [Red][=<100];[Black]. Combinations are also possible, such as [Red][=<100];[Blue][=>200] which means red font for any number equal or less than 100 and blue font for any number equal to or more than 200. (You might find that Excel adds it's General format as an automatic anti-error feature, this usually works to your advantage)

Okay, that's it for now. There is another simple way to make custom formats, I'll cover this sometime in the future.

This page is powered by Blogger. Isn't yours?