Archive for August 2009

Home / Archive for August 2009

How to define Range Names?

Excel generally provides more than one ways to perform an action. For defining range names also, it has provided 2 ways: U sing the Insert ...

What are Range Names?

Dhakkanz 8:08 PM Add Comment

Range Names is another of Excel's feature that makes life of its users a lot easier. A Range Name, as the name explains itself, is a Nam...

Autofilters in Excel

Dhakkanz 1:05 PM 2 Comments

This is a recent question that I received from one of our readers, Peter. Question : Can we apply filters on more than one table on the same...

Working out with Data Validation list

Dhakkanz 10:07 PM Add Comment

Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I'll call this ...

Function Columns

Dhakkanz 4:35 PM Add Comment

Columns() This function is used to determine the number of columns in a given reference (the range of cells referred to in the function...

Subscribe to: Posts ( Atom )

Monday, August 31, 2009

How to define Range Names?

Excel generally provides more than one ways to perform an action. For defining range names also, it has provided 2 ways:

Using the Insert –> Name –> Define command in Menu Bar
Using the Name box (adjacent to formula bar) directly

Here are the steps to define Range names by both the above methods:

Using the Menu Bar:

Select the cell / range of cells which you would like to name
Go to Insert –> Name –> Define command
Now you would see a dialogue box (shown below)
On the top text area (where the cursor would be blinking by default) type the name that you would like to give to the range selected
Press Add and then OK

...and you are done!

Using the Name Box:

Select the cell / range of cells which you would like to name
Go to Name Box (as shown below)
Type the name for the range in the Name box and then press Enter

...and you are done!

Now you have learned the usage of and the steps to define range names. But, there are some important points that one should keep in mind while defining the range names.

Sunday, August 30, 2009

What are Range Names?

Range Names is another of Excel's feature that makes life of its users a lot easier.

A Range Name, as the name explains itself, is a Name that is given to a cell or a range of cells. This Name then can be used at other locations to refer to the defined range.

Name ranges can be defined in 2 ways:

Using the Insert –> Name –> Define command in Menu bar
Using the Name box (adjacent to formula bar) directly

Advantages of using Name Ranges:

Range names are used in soft-coding of big and complex models. Not just complex, range names can also further simplify simpler models
They make formulas easier to understand and read. They can add meaning to boring formulas by providing them with names
They are very useful in situtations where VBA coding is used. If a VBA code uses a cell reference and that cell's value need to some other place, the range names can come very handy in such cases

How to define Range Names?

Wednesday, August 19, 2009

Autofilters in Excel

This is a recent question that I received from one of our readers, Peter.

Question: Can we apply filters on more than one table on the same worksheet?

Answer: No, we cannot have more than one Auto-filters on a same sheet. Even if the tables are separated by blank row(s), one cannot have auto filters applied on 2 tables at a same time. This issue has not been covered in Office 2007 also.

The only way, that I think, can make this possible is through VBA coding. Though I can do some basic coding, but have never tried it.

Does anyone interested in trying out this code? Or does anyone know any other way around this issue?

Thursday, August 6, 2009

Working out with Data Validation list

Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I'll call this person as Tom as this person has asked for anonymity of his identity.

As you remember, a cell can be validated for numerous things, text entries, numerical ranges, pre-defined lists. etc. [Refresh your data validation concepts here]. Tom has asked me a question related to data validation for a pre-defined list. He asks - when defining the validation rule for a list, we have to put a range where the list has been defined. Now when you are defining the rule, it asks for the list range and you have to either manually enter the range or you can select the range with the help pf mouse.

At this stage, there is one thing you cannot do, i.e. input the range which falls out of that sheet. What it means is you cannot select any range which is not on the same sheet where the validation is being put.

Tom is correct partially. He is correct when he said that users cannot select the range which is on the other sheet, but it is not true that you cannot enter the range that is not on the same sheet. One can very well enter the range manually and have the list located on any sheet other than where it is being used for validation.

One simply has to mention the full range of the list (the address where the list is located) together with the sheet name and it will work normally. Just remember to put a '=' sign so that the data validation works fine. For an example - I will refer to a list which is on 'sheet2' and I am applying this validation on a cell in 'sheet1'. My formula for this would be:

= 'Sheet1'!A4:A10

There is another way you can do this (and in my opinion this is the best way) - using Range Names.

A Range Name is a name given to a cell or range of cells. One can use this name anywhere in the workbook for any kind of processing without having to worry about the sheet names and the cell ranges. In our case, while using range name, the validation rule should simply refer to the range name given to the list. The list address should instead show this formula (assuming the range name given is 'ListName'):

=ListName

...and that is it!

Range Names are one of the coolest tool that Excel has provided. I have not yet written about it, but will soon do it. Range Names can actually make your life very simple when writing complex formulas and making heavy and dynamic Excel models.

Tuesday, August 4, 2009

Function Columns

Columns()

This function is used to determine the number of columns in a given reference (the range of cells referred to in the function).

Syntax:

= Columns (Reference_range)

Reference_range: This is the range of cells for which the number of columns is to be determined

Examples:

The function simply returns the number of columns within the range which is passed on as reference with the function. For example:

Columns($A$3:$A$3) - result will be: 1 {as there is only 1 column 'A' within the given range}
Columns($A$35:$D$35) - result will be: 4
Columns($A$3:$A$5) - result will be: 1
Columns($A$3:$F$8) - result will be: 6

Application:

Many a times, you have to write a range of formulas (a similar formula over a range of adjacent columns), which should have a number which is dynamic and changes as the column changes. For example - you have to write a formula in cell A5 and the next formula goes in B5, C5, and so on. But in this range of formula, you want one of the parameter to increment by itself. In such a case you can use Columns() function. Let us say that

Here is the way the Columns() function would be used in the above example :

= Original formula (Parameter1, Columns($A$5:A5))

Now, when the above formula will be copied to next cell, i.e. B5, it will change to:

= Original formula (Parameter1, Columns($A$5:B5))

Note that $A$5:A5 changes to $A$5:B5 and this will result in 2 which will be passed on as a parameter to the original formula}