Dynamic charts

Back to the main story. In this post I would like to show you, how we can build a dynamic chart based on some economic figures utilizing a GUI for the users of the table. We will utilize the technique described in this post as well as the functions RANDBETWEEN and HLOOKUP.

Please note, that I will create a spreasheet document in MSXL 2007 using the so called rS1-method, which I learned from Reinhold Scheck, an Excel professional user and author from Germany. If I get the allowance from him, I will post a detailed description of this method in the near future. Meanwhile you can have a look on this document, to see how the method works.

We would like to compare the foreign trade volume to the gross national product of Germany. The economic figures will be created with the RANDBETWEEN fucntion and are not real (just wanted to save the time researching the real economic values). The numbers should simulate the foreign trade volume of Germany compared to the other G8 nations.

Ready? Okay, let's start:

I set up a document with three sheets (Focus1, Base1 and Data1) and on the Data1 sheet a table looking like the one below:

Dispaly Excel tables online >> Excel Jeanie HTML 4

In this table we could now enter references for the foreign trade volume between Germany and the other G8 nations using the RANDBETWEEN fucntion in Excel. This function returns random values in a predefined range. First of all we will set up the range in cells M15 and M16 (the min and max values which the fucntion will use). I entered 10 billions as the lower and 99 billions as the upper threshold.

The next step is to select the range from L12 to R12 with the mouse having L12 as our active cell. Into this cell the following formula is entered:

=RANDBETWEEN($M$15,$M$16)

Attention! If you press ENTER now, the formula will only be used for the active cell (L12) but we want to use it for the whole range so we press CTRL+ENTER on the keyboard which copies the formula with its absolute references to the min and max values into the whole range. If you want to "freeze" the reference to cells M15 and M16 you could press the F4 key on your keyboard immediately after selecting each of the cells. We need this absolute reference because after pressing CTRL+ENTER the formula will be copied and the focus of the input would shift, if we wouldn't have frozen it.

The format I am using here is

#,##0,, "millions"

which makes the figures a little bit more readable. To access the Format Cells dialog, press CTRL+1 on your keyboard and go to the numbers tab. There I selected the last entry on the list on the left: Custom.

Okay the table should now smoething look like this:

Data1

Display Excel tables online >> Excel Jeanie HTML 4

Please, note that your values will be different from mine, because the RANDBETWEEN function will be recalculating the value each time an entry is made in a cell, the spreadsheet document is saved or any other change occurs. I have also added a numbering to row number 10 which we will use later on in our HLOOKUP function.

To avoid the recalculation I will use a quick technique which I have described in this post and paste in the values.

Okay, according to the rS1-method, I will have the data to compare on its own tab, called Base1. The table I have set up there looks as follows:

Base1

Display Excel tables online >> Excel Jeanie HTML 4

Let me explain what will be entered in the cells and what the colors mean. First of all the orange one is named range which is obligatory when using the rS1-method. It will not be used for any calculations in this example but has to be in all documents using this method. The blue cell (K15) will hold a number from 1 to 7 which will be controlled by a scroll bar. If you want to know, how to set up a scroll bar to control this cell, read my post "Roll with it, baby".

Depending on what number will be written in cell K15 (which is controlled by the scroll bar) we will have the according country from our just created table on the Data1 tab. Therefore we will use the HLOOKUP function (to learn more about LOOKUP formulas, see the article series on VLOOKUP in this blog).

The green cells will hold the values for Germany's "fake" gross national product (which we will enter manually) and - with another HLOOKUP - the value for the foreign trade volume.

Let's go with creating the values. Go to cell L12 and enter a "fake" German gross natioanl product. (Funny task, isn't it ) I entered the value of 15,858,392,345 fomatting the cell the same way as in the table on sheet Data1. So cell L12 reads now 15,858 millions.

Next thing we do is to set up the "controller" - our scroll bar. In MSXL 2007 I go to the Developer Tools ribbon and from the Insert button I chose the scroll bar in the upper half of the occuring window. My cursor now changes to a crosshair and I can draw my scroll bar anywhere I want. I will not resize or format it in any way at the moment, because we will shift it to another data sheet later on, the Focus1 data sheet to be precise.

Go on with right-clicking the scroll bar and selecting Format Control from the upcoming context menu. As we have 7 countries to compare the German figures with, enter 1 for the Minimum Value, 7 for the Maximum Value and 1 for the Incremental Change. Now put the cursor into the Cell link text field.

As we will cut and paste this control later on to another data tab (Focus1) we have to make sure, that we got the complete reference in our cell link field including the sheet name of our current worksheet (which is Base1). Instead of entering Base1! manually there is a quick workaround for this. Click the button right to the Cell link text field () and switch over to the Data1 sheet and immediately back to the Base1 sheet. Now click cell K15. The text field should now read

Base1!$K$15

Click the button right of the Cell Link text field again and see if it looks like mine:

Click OK to close the dialog box. If we now click somewhere outside the scroll bar and try to use our scroll bar it should go through values from 1 to 7 in steps of 1.

The next thing we'd like to do in this table is to put the countries according to the selected number in cell K15. Therefore we will use a LOOKUP function, the HLOOKUP:

=HLOOKUP(K15,Data1!L10:R11,2,FALSE)

• =HLOOKUP(K15, - This tells excel that the search criteria is the value in cell K15
• Data1!L10:R11, - This is the matrix we want to look in
• 2, - Hey, MSXL, please return the value in the second row of our matrix
• FALSE) - Please return only exact matches

If we now use our scroll bar, the value in cell K13 should scroll through the seven nations <> Germany. Now we will use this result for another HLOOKUP function:

=HLOOKUP(K13,Data1!L11:R12,2,FALSE)

The elements of this formula are very similar to the one mentioned above. We have only changed the search criteria to the country and the matrix to countries and values. Using the scroll bar again should now not only scroll through the countries but should also return the corresponding values.

We are getting close to the final result. Now create a chart by selecting the area K12 to L13 and insert a bar chart by going to the Insert ribbon and clicking the bar chart button. I chose the first design of the bar chart. From the resulting chart I right-clicked the legend and chose Delete to get rid of it.

Now select the chart and use the Cut and Paste tools to move it to the Focus1 data sheet. Go back to the Base1 sheet, right-click the scroll bar and select Cut from the context menu. Switch over to the Focus1 sheet and paste it below the chart. Format it the way you want it and voilà, you're done. My final Focus1 sheet looks like this:

Not bad, isn't it? Of course this is only a simple example with some fake numbers but imagine, what you could do with this.

If this is useful for you, please leave a comment below and tell me what you think about it or share, what other purposes you could use this for. By the way, this is my first post exceeding 1,500 words

The final MSXL files are here for you to download (MSXL 2003 and 2007): Dynamic Charts (ZIP - 18 kB)

Merry Christmas and a happy new year!

Discuss this blog post in the forums (click the blue button)