The Excel dashboard about Real Madrid Tenth Champions League win 2013/14

Hello Excel fans. Today I would like to mix two of my passions. As you all know I love Excel, but I also love football and am a supporter of Real Madrid. Although it is best to have the full story so that everybody understands. My whole family are Atletico Madrid supporters, in fact I was born as an Atletico fan and I remember going to the Vicente Calderon when I was little, but when I was 5 years or so I changed my team and I became a fan of Real. Why? Because my favourite player was playing for Atletico and then he signed for Real, and I followed him.......   Who is he? The incredible Hugo Sanchez. I was so passionate about how he played and his somersaults as goal celebration, and besides, I also played football with the number 9. Anyway, so today I consider myself a fan of Real Madrid but I still have much appreciation to Atletico. So, back to Excel, I have decided to create a small tribute to Real Madrid for the achievement of the Champions League win last year: the Tenth European Cup, and everything will be done in Excel. So even if you are not Real Madrid supporter or do not even like football, check the dashboard and I am sure you will find it interesting.

First let's see how it looks:

Welcome to Malta - a dashboard in Excel about this beautiful island

Hello everyone. I guess most of you do not know that I live in Malta. I live in this beautiful island since September 2014. To celebrate my adventure and try to convince my friends to come and visit, I have created a dashboard in Excel about the island. The dashboard runs smoothly in Excel 2007, 2010 and 2013. Other versions have not been tested.

And what is a dashboard?

Selecting a range from many using the CHOOSE function

A while ago I wrote about the how to convert long NESTED IF formulas to a simple formula with the CHOOSE function. Today we are going to see what else we can achieve by using the CHOOSE function.

We can fetch a range from a selection of ranges

How do we do that?

Let's see by looking at one example:

Say that we have 4 ranges, {A1:A10}, {B1:B10}, {C1:C10} and {D1:D10}, and that depending on a condition or a selection by the user we want to use one of them, for example, we want to sum up the range.

=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

Convert long NESTED IF formulas to a simple formula with the CHOOSE function

Anyone who has used Excel for a while will have found on more than one occasion long formulas that use the IF function inside another IF that in turn is inside another IF. This is normally called NESTED IF.

An easy example of NESTED IF would be:

= IF (B1 = 1, "Apple", IF (B1 = 2, "Orange", IF (B1 = 3 "Pera", "Grape")))


Obviously instead of the name of the fruits we could write a formula, or a cell.

= IF (B1 = 1, A1, IF (B1 = 2, A1-C1, IF (B1 = 3, A1 * D1, A1 ^ 2)))