Ever got High over the Beer 'n' Excel punch??
It’s been long, may be since our school days, that we all are using Excel as a Tool. But have you ever wondered what all super cool stuffs can be done using this commonly available Tool? It’s just as simple as you’re thinking, may be a little tricky but at the end, it’s all fun and bliss!!
Well, you might be thinking the
other way round for sure so let’s not get deeper into it..!!
However, keeping everything aside, let’s have some fun time learning a few cool stuffs that can be done
using Excel & VBA
Well I assume you are are well accustomed with the Charts we use for our Daily Reporting. Also, I assume , you are bored of using the same Blue and Red combination provided by Excel by default.
Let's get rid of usual stuff for some time and try some new cuisine !
Have you ever tried using a picture instead
of a colored bar Chart and a touch screen functionality instead of
clicking on the much used List Box?
If no, then there’s a small Fun
which I have built for you. Hope you will enjoy it!!
So, ever wondered about the consumption of Beer in India?
Well FYI, it's one of the lowest if compared with the other countries.
However to keep up the spirits I have used random numbers to demonstrate the consumption of a list of 20 countries globally across a 5 year span.
I have taken a random list of 20 countries along with their flags in picture form and arranged them in order as seen in picture above and in the downloaded file.
Like I said, I have generated random numbers for all the Countries across the 5 years period. Excel, gives us the privilege of using randbetween** function for generating random numbers. So why not use it for any demonstrations like this?
Fun Phase - Version 1.0
Let's create the magic of "Touch Screen" functionality
Well, apparently it seems like a very high end thing but actually there's not much of an effort which needs to put while implementing the same feature.
So for a similar functionality you need to define your own function in VBA.
Firstly, you need to define the key range which needs to be referenced at the VBA function.
But hang on! Are you familiar with Name Manager in Excel?
If not, here's a brief outline of it -
Name Manager is used for defining a Range / Cell and anything linked to it be it Static / Dynamic. It can be found at the Formulas tab.
So, getting back to the first step, I have defined a range under the name cntry_name and this is the key reference because whenever I am hovering on a particular country, the relevant Country Name would be getting populated at the defined range.
Now, how did I define it?
Go to the Name Manager as seen in the Formulas tab and click on the New button to define the Name along with the Reference like it's shown below.
Now its time for defining the Function as would be used in the VBA. Open the VBA Editor
(Alt + F11) and insert a Module and write the below shown code :
So basically, you just need to create a Public Function, Name it, and also a Range you're going to use in VBA for passing the value at the Name Manager you have defined previously. That's all from the VBA side!!
What I have used here?
Public Function name as cntry_touch
Range as used in VBA as macror
Name Manger for the country reference as cntry_name
Now you go back to the main Excel screen and just beside where you have mentioned about the Country & Flag , you put a formula which would finally call the entire touch screen functionality to the range you have defined through the name manager.
=IFERROR(HYPERLINK(Name of the Public Function defined for VBA("Cell Reference for the Country Name")),"")
Just drag down till the end point (as in till the Last Country Reference you have, like in my case it's 20)
You can now hover on the Red Region (Beside the Flag) to see the change in the Name Manager Reference.
Enough of Touch Screen!!
Fun Phase - Version 2.0
Changing the traditional Blue / Red Bar Stack with a picture of your kind.
For that you need to lookup the yearly data of the country hovered on from the Raw Data section.
You can use any conventional lookup technique. For reference you can see mine in the Excel file attached. I have used a simple VLOOKUP for the reference and kept it at a separate section.
Done with that as well?
So how about displaying the same in the Chart by a Beer Logo??
It's also very simple. Just right click on Format Data Series and go to the
Fill option and change the Picture you want to keep. That's it!!
Now that you've almost covered the majority chunk, a small yet another thing left.
Fun Phase - Version 3.0
What if I want to put the Flag logo beside the Country Name as appearing in the chart?
Anything strikes to you ???
Well, there's a smart yet very much conventional technique of doing so. Although it's not so used extensively but what's the harm in impressing the other at seat when there are ways?
For that, click on Insert tab and insert a Bitmap Image Object.
In case the Image Editor pops up you may close it down.
So positioning it right you just need to give an Indirect Reference to it.
Well, some of you might not know about Indirect Referencing. So let's get into a brief explanation of it too.
Indirect Referencing is generally done using the Indirect Function in Excel. It's generally returns the reference specified by the Text String.
Similarly, here also I have made the individual Name Managers for all the Country wise Flags, and then a separate Name Manager for the Flag reference at the Graph.
Here at the Flag reference Name Manager, I simply referred to the cntry_name using the Indirect Function. So whenever the Country name changes the Flag would also pick the Country Name and based on the name manager I assigned to each country, it will display the Flag.
Confused?
I guess you should see how I have used in the real dashboard for a much purified understanding.
Rest all covered!!
I would love to keep posting more such not-so-conventional dashboards and of course other topics.
Till then keep visiting the blog and also the other posts covered by my fellow mates!!