Using Named Ranges

I wanted to write some thing of my own but when I found many things related to this on Internet, so I am posting one of the links here. Please read it and understand it deeply, if you really want to create dynamic pivot tables, charts, data validations etc.

The link below have the information about how to create and make use of ‘Named Ranges’, I like it among all as it is simple with out much zigzag. Correct syntax and detailed information.

http://www.cpearson.com/excel/named.htm

Do let me know your feedbacks and comments

How to create a dynamic Pivot Table in Ms-Excel?

I have come across many times with this question, and some people even say pivot table is not useful because they do not support additional data in the main data table.

But as many other things in excel are not yet fully explored, the same is with this function. Most of the time, we do not get answer to our problems because we always look at it from the same angle. Try to look at any problem from different angles and you will get the solution. The main problem here is not the pivot table but the data that we are using with the pivot table.

So let’s start looking at this thing from different angle… How any pivot table understands what the data is? For this we have to see how we create a pivot table. While creating any pivot table we have option to select cell range. Now this is the place where we define the data source for the pivot table.

There is confusion in our understanding of one word ‘Dynamic’. We see dynamic means changeable, But always remember dynamism will not be possible with out some parts are being constants. This is very much true in our case. To make the pivot table dynamic we need to keep the data source of it constant.

Thinking impossible… let’s see how it is possible. One thing is for sure, by giving cell range as data source will not do as it will be changing whenever you add new data. So what are the other alternatives to select data source? The other alternative is ‘Named Ranges’. Now only giving a name to any range will not make it dynamic at all, but think if you use the offset function while defining the name then….

There are three essential things we need to know to enable us to make it possible. Named Ranges, Pivot Table and Offset Function. If you do not know any of this then, stop here and first get some primary information on these parts then read further.

Let’s see how to make any data table dynamic where just one name can select the entire table of whatever size it is. For creating such a named range we have to some how calculate it’s rows and some times even columns on the fly. This is possible with ‘CountA’ Function.

Logically the entire activity gets performed in the following way.

1. CountA Function gives us no. of rows and if required then no. of colums
2. Offset function selects the range based on the above no.s
3. Named Ranges define range as per above offset function selection
4. Pivote table just refer named range and analyze the data.

Let’s start doing it practically for better understandability.

Create one table in excel, where its header row is 2 and starting column is A. Go to Insert>Name>Define

Type the name of range as Data and in refer to box type =OFFSET(A2,0,0,CountA(A:A),CountA(2:2)) And then click ok

Now as you have created dynamic rage ‘Data’ time to test it, by typing ‘Data’ in Name box it is located just before your formula bar. Add one more row or column and again try selecting same range.

If this test is successful you have to just give this range name while creating pivot table instead of selecting cell range.

Your pivot table is ready to test. Add new row to main data and then just click refresh button on pivot table toolbar and see new row is there.

If you have any comments, feedback please add comment or mail me at rshewade@gmail.com