Create Dynamic Range for your Charts

Hi guys recently I got an email where the sender has requested to create chart in such a manner that graph adjusts itself to the changing figures in the table or data. I provide him a solution which look something like the below image.

Download the file from here: DYNAMIC RANGE

Now the real question here comes is, how this was done? For that I have used Name Manager from Formulas tab.  Don't worry I will tell you step by step process to create this chart and dynamic data input range for it.

Suppose I have a table like given in the image below and I want to create a chart for this table in such way that if I enter Budget and Actual sales in column C & D, these values will automatically get reflected in my chart.

Now first I will name my ranges to be plotted in charts. For that click Formulas > Name Manager > New

For Budget column name it as Budget_data or whatever you like and in refers to box at the bottom write the formula as =OFFSET(Sheet1!$C$5,0,0,COUNT(Sheet1!$C$5:$C$16)). Similarly for 2011 data name it as Actual_data and refers to =OFFSET(Sheet1!$D$5,0,0,COUNT(Sheet1!$D$5:$D$16))  have a look at the image below.

Before moving ahead I want to explain what the above formula is doing.  To put it in layman language, OFFSET is helping us to select the range starting from C5 and COUNT function is helping the formula to decide the height of range by counting the number of data entered in the table.

Once we're done naming ranges now we'll create charts and do edits required to make it dynamic.

Select your data to be plotted, here select B4:D15 and click Insert > Charts > Column > Clustered Column.  It will give you a chart appearing as in 1 of image below.

Now right click in your chart and select change series chart type as in 2 of the image.  Select Line chart type.

You can also change the layout of your chart and tittle as done in 3 of the image.

Now in 4 comes the most important part of this trick.  Here we will adjust the data source for our chart and define the two series shown in the chart.

As shown in 4 of the above image, when we click Select Data it will open Select Data Source Box where under Legend Entries (Series) we'll edit Series1 & Series2.  In the following image below we have made edits to Series1 by referring Series name to cell C3 & Series values to "Budget_Data" which we named above.  Similarly for Series2 we referred Series name to cell D3 & Series values to "Actual_Data".

What this will do is make our chart responsive to the data we enter in table range B4:D15.  When we enter figures for any month it will automatically plot the same in the corresponding chart.  And that exactly was our goal as well.

Hope you learned something out of this trick.  "HAPPY LEARNING"

Conditional Lookup using Multi Criteria

Hello readers today's post will be short but I assure you it will help you tackle problem which you may be facing or might face in future. Suppose you want to look up any value based on two criteria, for example in the below image you want to know the price in F8 based on Make & Color in F2 & F5 respectively.

I know the solution to this can be found using filter feature, but being big fan of functions I would like to provide you formula to find it as well.  You'll use a combination of INDEX & MATCH function to get your figure here. The formula in F8 will be as follows:

F8: {=INDEX(C2:C10,MATCH(F2&F5,A2:A10&B2:B10,0))}

This is an array formula and to make it works you'll have to press (Ctrl+Shift+Enter) CSE.

Download sample file: MULTI-CRITERIA LOOKUP

Dynamic Charts with Index & Match Function

Have you ever created a dynamic chart in excel? If yes, Kudos to you!  If not don't worry today you can master that skill in couple of minutes.  I would like to tell you that this is my first post on charts and I am starting a series on charting with this.  I will keep adding more to it time and again for sure.  In this post I'll be talking about how to create dynamic column chart using few amazing but simple functions of excel.  INDEX, MATCH & VALIDATION are three things I am using to create a dynamic chart which will look like the below example.

You can download the example from here! DYNAMIC CHART FILE

I am assuming that you have already downloaded the file from above.  In it you can see my main data is in range A1:E7, but using nested INDEX & MATCH functions I have created a new data for my chart to take in range A10:B16.

The challenge here is to change the data in range A10:B16.  If you see, you will notice that in cell H1 I have used data validation and it is giving me year for which I want my chart to plot the data. The data range A10:A16 has the name of car manufactures.  Using these two inputs I am pulling out data from main data.  The combination of functions I have used is as follows:

B11=INDEX($B$2:$E$7,MATCH($A11,$A$2:$A$7,0),MATCH($H$1,$B$1:$E$1,0))

Now I have dragged this function till B16 and I have my dynamic data ready with me.  It changes as I change the year in Cell H1.  Using this data series I have now created a chart.  To do it the steps are as follows:

Select your data > Insert > Charts > Column > 2-D Column > Clustered Column

You can now make adjustments as per your presentation requirement and give it a look as you want.  I have removed data labels from this chart and changed the color of each bar as well.  Just keep one thing in mind if you have the nested function mentioned above, rest of the work is simple.  You can use these charts in sales dashboards and many other places.  I use them it to show the performance of my team on weekly basis.

Dear reader, please let me know any feedback you have for me.  You can also give suggestions and topics that you want me to cover in upcoming posts.  Till then "HAPPY LEARNING!"

Two ways LOOKUP formula to extract values from left & right (INDEX & MATCH FUNCTIONS)

I am sure if you’re using Excel at work, then you must have used lookup function (LOOKUP, VLOOKUP & HLOOKUP). There is no doubt that these are most useful & beautiful functions provided in the excel package. But if you haven’t used these function yet, let me take the honor of telling you that a lookup function return a value from a given table by looking up another value in the same table.

Because of our habit of entering data in very common style, VLOOKUP is among the most used function of Excel. But there is a limitation that it cannot go left and with all the LOOKUP functions is that they only look in the left column and return a from a cell to the right. So what I mean by this is LOOKUP function in Excel are useful for one-way lookups, there is function (inbuilt) for two-way lookup.

Today after reading this post you will be able to conquer this limitation, I am sure of that! First I would like you to look at the data given below and visualize it. It a table representing sales figures for ten of my employees in USA. I have named this table as “SalesData”.

Now if you want to know whose region is Minnesota, no lookup function will be useful. This is because, LOOKUP functions only return values to the right and it doesn’t return values from the left side. One transitory solution can be cut the data from column C and insert it in column and use VLOOKUP, but such quick fix are not always possible. Especially when you are not supposed to manipulate the arrangement of data provided.

In this case we will take help of two functions available in Excel namely INDEX & MATCH.

INDEX(reference,row_num,column_num,area_num): This function returns the value of an element in a table or an array, selected by the row and column number indexes. It means this function will provide you result from your data when you supply the row number and column number of the desired result.  The answer will be intersection of supplied row & column number.

MATCH(lookup_value,lookup_array,match_type): This function returns the relative position of an item in an array that matches a specified value in a specified order. It means this function will provide you the number where your lookup value falls in the series.

Now let’s come back to our problem where we have to know the name of the sales person responsible Minnesota region.

The solution will go like this F13:  =INDEX(SalesData[Name],MATCH(D3,SalesData[Region],0)) this will provide the name as “JAMES”

Similarly if you want to know who over achieved his target by highest margin? Solution will be F14: =INDEX(SalesData[Name],MATCH(MAX(SalesData[Difference]),SalesData[Difference],0)) and the name will be “EMMA”.

Now before wrapping the post here I have some homework for you. Please try to find the following using function mentioned above.

• Who has the maximum & minimum target in my team and what were their respective regions? For minimum use MIN function.
• What is the name of person managing Kansas region and how long is he/she with the organization?
• Who has the lowest target among the team?
• Who is the newest member of the team?

Let me know you solutions, you can post them in the comment section!

I hope you like this post and will share this with your friends and colleagues to help us grow the community of excel learners, there are various options you can use to share with people at the end of the post.

Now you also follow me on Twitter, just click on the vertically aligned Twitter radio button to the left of your computer screen to follow!

You can post your feedback as a comment below and I will take a note of it for future. “Happy Learning”

Please download the file from here!