Posts Tagged ‘Excel charts’

Unusual Excel chart

November 12th, 2009

Today we are going to create a little unusual, but quite natty chart in Excel.  Image below shows how it works.Unusual Excel chart

There is no VBA involved and the whole idea is based on the usage of conditional formatting only.

Before we start writing proper formulas controlling the way our chart is going to look, let’s prepare “the stage”. What we need to do is to prepare an area of 100 (10 rows and 10 columns), preferably square, excel cells (unless you have a better idea about how it could look like).

Unusual Excel chart

Our basic “stage” is now set and I am quite happy about cells sizes. The only thing is that everything looks a little boring. And we don’t want to be boring, do we? So let’s add some colors. I decided to set the background color to blue and add white cell borders. Finally, let’s turn the worksheet grid off. Our chart starts to slowly take a final shape.

Unusual chart in Excel

Now, what we need to do is to set conditions for Conditional Formatting, which will turn individual cells on and off, depending on the helper cell (where we keep the amount of cells which need to be coloured).

Let’s select all 100 cells of our chart, go to the Conditional Formatting dialog box and create a new rule.  My first approach to creating a proper rule is:

=OR(ROW($B$11)-ROW()<=INT($M$3/10)-1,AND(ROW($B$11)-ROW()=INT($M$3/10),COLUMN()-COLUMN($B$11)<($M$3/10-INT($M$3/10))*10))

Where $B$11 is the bottom left cell (corner) of our chart and in $M$3 we keep the percentage (number of squares we want to highlight).  You will probably have to adjust it slightly to meet your own needs.

Unusual Excel chart

I decided that I want all the cells fulfilling my condition to be green.

After a few minutes I realized though, that it could be done with a simpler formula (formatting condition):

=((ROW($B$11)-ROW())*10+COLUMN()-COLUMN($B$11)+1)<=$M$3

As before, $B$11 is the bottom left cell (corner) of our chart and $M$3 is the cell with the number of squares we want to highlight.

Until now, our starting point is the bottom left corner of the chart. But what if we wanted to start from the bottom right corner? Or, even better, what if we would like to have a choice?

Unusual Excel chart

All we need to do is to add a list with our choices (to do so I used the Excel validation feature), and to change our formula slightly, which will now look like this:

=((IF($A$5,ROW($B$11),ROW($K$11))-ROW())*10+ABS(IF($A$5,0,COLUMN($K$11))-COLUMN())-IF($A$5,COLUMN($B$11),0)+1)<=$M$3

The cell A5 contains TRUE if “Left2Right” direction is chosen, and FALSE otherwise.

If you have any doubts please download the example file attached and play around with my formulas.

Marcin

Dynamic Bubble Chart in Excel

October 20th, 2009

Dynamic Bubble Chart
One of handy features of Excel is it’s ability to create dynamic ranges which can change their size and position depending on criteria we define. Today I would like to show you how, using this feature, you can add some life to a bubble chart.

The general idea behind bubble charts is, that for each bubble you need to define it’s position (X and Y coordinates) and size. Simple! But, since a picture is worth a thousand words, below you can find a very simple data table and a bubble chart prepared using this data. I hope it explains any questions you might have.

BubbleChart and it's source data table

The first step to add some life to our bubble chart is to prepare a table with source data. In my case, the data table looks like that:

Dynamic Bubble Chart: Step 1 - data table

All data is prepared for years 1999-2005 and for 6 countries. As you might notice (after a quick table analysis) what we have prepared for each case is (mentioned earlier)  X, Y coordinates and size.

I have also decided that my “tail” with historical data will follow the chosen year data for a span of 4 years. Consequently, you will see a maximum of 5 bubbles for each listed country. You can change this number if you like to suit your needs.

Dynamic Bubble Chart - historical data

Through the years we will move using a scrollbar inserted using the Form controls toolbar. In Excel 2007, you can find the Scroll Bar control under the Insert dropdown on the Developer tab.

Form controls - scrollbar

The scrollbar is linked to one of the worksheet cells.  I named this cell “PRZESUNIECIE” (sorry for foreign and probably meaningless names but the text was originally prepared in polish. And of course it’s not the names that matter, what matters is how we use them.)

Scrollbar linked to a worksheet cell

Because I have decided that the maximum number of bubbles is going to be 5, I am going to define 5 data series for my bubble chart.

Dynamic Bubble Chart - Data Source

The main series („Series1” on the picture above) which refers to the “active”, chosen year is defined as follows.

Dynamic Bubble Chart - Edit SeriesToShow_X, ToShow_Y and ToShow_Bubble are properly defined dynamic ranges, so that every time we choose the year, the data used for the chart will automatically slide through the data table.  Always pointing to the position and size of the bubble which should be displayed first.

Dynamic Bubble Chart - sliding through the data table

Dynamic ranges are defined as follows:

ToShow_X
=OFFSET (Sheet1!$D$7;Przesuniecie*3;0;1;7)

ToShow_Y
=OFFSET(Sheet1!$D$7;Przesuniecie*3+1;0;1;7)

ToShow_Bubble
=OFFSET(Sheet1!$D$7;Przesuniecie*3+2;0;1;7)

As I have mentioned earlier „Przesuniecie” is a defined name referring to the cell (A1) linked to the scrollbar.

The cell $D$7 (used in the above formulas) is the first cell of my data table. Please have a look at the picture below for clarity.

Dynamic Bubble Chart

The remaining series (2-5) for the historical data (tail) are defined in a very similar way.

Dynamic Bubble Chart

All dynamic ranges we refer to from the “Edit Series” window have the same structure. Let’s have a look at ranges used by “Series2” (the first bubble of our “tail”)
ToFollow_X1
=IF(Przesuniecie<1;Sheet1!$D$2:$I$2;OFFSET(Sheet1!$D$7;(Przesuniecie-1)*3;0;1;7))

Using the range $D$2:$I$2, we are trying to cheat Excel a little and persuade it to hide bubbles we don’t want (yet) to see on a screen. Picture below should unveil the mystery of our $D$2:$I$2 range :)

Dynamic Bubble Chart

ToFollow_Y1
=IF(Przesuniecie<1;Sheet1!$D$3:$I$3;OFFSET(Sheet1!$D$7;(Przesuniecie-1)*3+1;0;1;7))

ToFollow_Bubble1
=IF(Przesuniecie<1;Sheet1!$D$4:$I$4;OFFSET(Sheet1!$D$7;(Przesuniecie-1)*3+2;0;1;7))

For all other ranges the formula structure is the same, with the only difference shown on the picture below.

Dynamic Bubble Chart

And that’s pretty much it. With all dynamic ranges defined and linked to the graph series what we need to do is to format the graph a little, choose nice bubble colors for each series and …. Enjoy effect of our work.

Marcin