Pegasus‒A VBA based Drag and Drop tool in MS Excel [2/2]

Hello friends!

I apologize for the delay in this post. Firstly, here is the working version of the tool (click here to download).

I’m going to keep this post short and simply going to upload the working version of Pegasus for you to explore. The principle by which the drag and drop feature works in Pegasus is really simple—it activates the built in properties of two list boxes wherein a value from one list box is dragged into another. The trick here is to make the second list box (to which the value is being dragged) transparent so that the user is able to see the formatting on the sheet.

Although the underlying code for the tool is self-explanatory and most of it should be easy to follow, I would like to highlight the following important snippets: Continue reading

Array formulas – The basics

Looking up data from tables is very important when creating dashboards or for that matter even when crunching data for any analysis. While there are already some lookup formulas, that you know of and we have covered in our Dashboard 101 tutorial as well, not all of these formulas are capable of giving you the desired result.

For example let’s look at the data table below. It contains the data for drug samples given by sales reps to physicians. In order from left to right the columns are 1) the visiting reps’ name, 2) the visited physicians’ name, 3) the date of visit and 4) the number of samples given.

Array formula example table

Now if you were to find out, in a single formula, the total number of samples left by reps containing ‘Andrew’ in their name and visit date between 1st Jan and 15th Jan 2012 (both inclusive)– how would you do that?

Well… the answer is array formula. Continue reading

Pegasus‒A VBA based Drag and Drop tool in MS Excel [1/2]

Hello friends..Happy New Year to all of you!

First year in a B-school takes its toll on you. But now, as things have settled down, I’m back.

This time around I’m back with something new—something that will take your MS Excel skills to the next level. We are going to create a drag-and-drop tool which would allow your deliverable to be more interactive. As usual, I’m going to give you a glimpse of what we’re going to develop and follow it with a post explaining how we develop it. And yes, I call it the Pegasus :) .

Our drag-and-drop tool would be a solution for pharma analysts and would help them to analyze the drug pipeline for Pharma companies in a very novel and effective way. We would do a simple two dimensional analysis which would allow the user to view the number of molecules by their mechanism of action that various pharma companies have in their pipeline.

You would ask why to go for such a complicated solution when we can simply use dropdowns to create this solution. Well, my aim here is to demonstrate how to build drag-and drop functionality in MS Excel which will open a whole new range of possibilities for you in the long run. Moreover, it looks and feels amazing!

Just to give you a peek of what we will be learning, I’ve uploaded a video (shown below).

The list below is mentioned just to tell you what all we will be using in to develop this tool: Continue reading

Zoom & filter chart data using scroll bar form control

The scroll bar, in the form control group, is a very effective tool that can be used in dashboards to filter data—specially date values. In this post I will show how it can be used to create a zoom effect on graphs and simultaneously filter data based on dates. You can download the workbook that I created for this post, here (.xlsx – yes no VBA) and the final product looks like this:

Scroll Bar Zooming on Chart

Before I explain what I did and how I did, let’s have a look at the form control scroll bar (I know most of you already know what it is but for those who don’t, I don’t want you to start searching the internet :) ). You can find the Scroll Bar control in MS Excel > Developers Tab > Insert > Form Control > Scroll bar Continue reading

Generating random date values using RANDBETWEEN

Hello friends,

All dashboards need one thing to work – data. However, while building dashboards we very often do not have access to the original data and this compels us to create random data sets. While most of us know how to create numeric data sets (by using the RAND function), I often get a lot of queries on generating random date values. So, I thought to post something on my approach of generating them.

It used to be a bit tricky in excel 2003, especially if one wanted dates to be within a range – which is mostly the case as dashboards usually have data for the relevant 5-6 years and it would not make a lot of sense to get dates that are 100 years old. However, with the Excel 2007 & 2010 RANDBETWEEN function it has become really easy. How?

Excel dates are nothing but numbers – Don’t trust me? Test it yourself – write a date in a cell in Excel, now go to the ‘number’ sub-tab on the home ribbon and change the format to number, surprised!! Well basically this is how Excel stores dates and hence it makes it fairly easy to compared dates.

Now, let’s come back to the problem. First of all, let’s explore the format of the RANDBETWEEN function:

RANDBETWEEN ( bottom, top)

This function requires two inputs - ‘bottom’ and ‘top’. Both the inputs require numeric integer values. Continue reading

Creating a progress bar using MS Excel charts – part 2

The idea behind this post is to offer more examples of MS Excel Chart-based progress bars. To be honest, the possibilities are unlimited—especially if you combine a bit of mathematics with your Excel skills. I designed this (see below) progress bar using a bit of co-ordinate geometry [the co-ordinates of a point at a distance ‘r’ from the centre of a circle is given by X = r cos(theta) and Y= r sin(theta), where theta is the degree in radians. And, just in case, if you are wondering about converting degree into radians in Excel—it is very easy, use the RADIANS formula]. I used a scatter plot chart with two series. Values in the first series are fixed (gray dots)—so they form a circle. Values in the second series (yellow dots), change as the code loops. The numeric value in the center is a WordArt.

ms excel chart based progress bar for definite loop

In all the progress bars till now, I have focused around building them for macros that run/loop for a defined/known number of times. Continue reading

Creating a progress bar using MS Excel charts

While writing complex VBA programs, or programs that take a lot of time to run (e.g. calculating country/region-wise sales from third-party data), it is useful to give the end-user some sense of the task completed/remaining. I am essentially talking about the progress bar that we see in most software, websites and operating systems. Excel does not have a built-in progress bar that one can use for programming purposes. However, there are several ways of creating one. In this post I am going to use an Excel chart to create such a progress bar–please note that this is not the only way or the best way of creating a progress bar, but definitely one of the easy and effective ways. The end product will look like the one shown in the GIF image below:

ms excel chart based process bar

The first step is to create a 2-D bar chart using data for two series (lets say, Total and Current): Continue reading