Tag Archives: Pharma Dashboard

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

LaunchTraX – A Post-Launch Brand Performance Tracking Tool for the Pharma Industry [2/5]

Hi Guys!!

With this post, I am uploading the first version of Post-Launch Brand Performance Tracking Tool ─ LaunchTraX (Download LaunchTraX_v1.0). Before jumping to the tool, I would like to share a small concept used in the pharmaceutical industry for the launch performance tracking.

Launch Date Standardization

Sales performance tracking generally involves comparing value/volume sales of brands for same time periods (e.g. Year 2011 sales of Drug A vs. Year 2011 sales of other drugs). However, in case of newly launched brands, it would make more sense to compare the sales of brands for equivalent months/quarters of launch, so as to relate the post-launch growth curve of different brands irrespective of their launch dates.

For instance, if I have recently launched a new drug (say in Jan 2011) in the Hypertension market, I can compare my drug’s first-12 month (Jan-11 to Dec-11) performance with one of its competitors (launched in Jul 2007) for the same period i.e. Scenario1 or with the competitor’s first 12-month (Jul-07 to Jun-08) i.e. Scenario2. These equivalent months of launch are represented as M1, M2…., Mn.

Launch Date Standardization - Scenarios

The Scenario2 is usually preferred for new brands to benchmark their post-launch performance with the competitor brands in the same market, thereby helping companies in fine-tuning their strategies. Continue reading

LaunchTraX – A Post-Launch Brand Performance Tracking Tool for the Pharma Industry [1/5]

Hi Guys!!

In this project, I am going to showcase the importance of MS Excel automation in the post-launch tracking of drugs in the Pharmaceutical industry. Although this launch tracking dashboard/tool is going to be Pharma specific, it can be used for other industries as well, with minor tweaks.

Post-marketing and performance optimization of the newly launched drugs are the most important aspects of the drug development cycle in the Pharmaceutical/Biotech industry. It is quite crucial to determine the planned/anticipated effects of all such implemented strategies on a regular basis, so that the companies can make a change in their post-launch strategies (if required).

Despite having well-designed KPIs at the centre of each drug launch, only 2-3% of these newly launched brands excel in today’s pharmaceutical environment. One of the few reasons for this is that companies fail to define and track post-launch success of their brands. Hence, development of launch tracking performance metrics/ dashboards is very important to:

  • provide timely reports to senior management on Sales performance KPIs such as gain/loss in brand share vs. market
  • resolve deviations from already implemented plans in a timely and effective manner
  • fine-tune brand strategies due to potential risks from other competitor brands Continue reading

CompTraX – A Microsoft Excel-based company profiling tool [3/5]

Hello friends,

As mentioned in my last post, I have added a new feature to our CompTraX tool. Download Version-2 of the CompTraX tool (Click here)—so that you can explore it while reading this post.

Now let’s discuss the enhancement that I have made in this version. One of the limitations in Version-1 of the CompTraX tool was the fact that there was limited space to display text in the ‘Company Overview’, ‘Recent News’, and ‘Key Pipeline’ sections (highlighted in red in picture below).

What this meant is that, if one were to write large paragraphs in these sections, all of that would not fit in. This was obviously not acceptable from a user’s point of view—no one likes missing text. So, to overcome this limitation, I came up with a VBA based solution.

The solution itself was a two-step approach:

Step 1: Calculate, with the help of formulas, if the input text is small enough to fit in the space allocated for it. If yes, display the full text and if not, then curtail the text up to a certain limit (say 500 characters) and append a text that prompts the user to click on the cell/Range containing the article (‘click for more’; what we usually see on web pages)

To do this, I used the LEN, LEFT & IF functions. A generic version of the formula  that I have used would be:

IF (LEN(Some_Text)>500,LEFT(Some_Text, 480)&”………click for more”, Some_Text)

[For help on these formulas, visit the Bare Essential Formulas section in Dashboard 101 tutorial]

Step 2: If the user clicks the relevant cell/range (one containing company profiles, news item and pipeline), display a pop up window containing the full text. The pop up window is created using 4 different shapes and some VBA to show/hide them, as needed.

Gif image showing the dashboard in action

You can find the VBA code that goes behind this enhancement in the ‘Display Sheet’ on the VBA interface of the workbook  ( with appropriate comments to make it easy to understand for beginners)

For those of you who are new to VBA, we are soon planning to start a new series called ‘Dashboard 102′ which will cover a lot of basic concepts of VBA.

As always, feel free to contact us at query@crabsheet.com for any queries that you may have.

Keep Excelling !

Introducing Dashboard 101 ― MS Excel skills required for dashboard creation

Howdy Pharmexellians!!

Microsoft Excel dashboards play a significant role in the Healthcare industry, particularly in the lives of people working with the Pharma data. Though Microsoft provides a lot of documentation on the various features of MS Excel, there is hardly any material that could guide someone to create a full-fledged dashboard from the scratch. Moreover, there are several websites that provide online training for dashboard basics, but most of these tutorials are paid. Keeping all this in mind, we decided to prepare a comprehensive (and not to mention FREE) tutorial on the basics of dashboard creation — we call this Dashboard 101.

Before creating this section we brainstormed a lot on the MS Excel skills that one may need for creating his/her very first dashboard. Eventually we shortlisted 10 different skills (shown below) and came up with a tutorial section on each one of them.

10 Steps for Dashboarding

We have tried to be as narrative/descriptive as possible in our tutorials and, by the end of it; we expect that you would be well-equipped to create your own awesome dashboards.

The scope of this section will not only be limited to dashboard creation, as it will also help all our readers in developing a robust understanding of the MS Excel tools that can be used extensively in the field of data analytics and automation. Furthermore, the Dashboard 101 series will act as a handy reference for your MS Excel/dashboarding needs, as you will see in almost all our future posts/projects.

Don’t forget to subscribe to our posts, so that you do not miss any of the dashboard tips and tricks that we post on our blog.

Feel free to contact us at query@crabsheet.com for any queries/ideas that you may have.

Keep Excelling!

SphInX ‒ A pharma KPI tracking dashboard [2/5]

Hello readers!

Ready to impress your boss with the new MS Excel dashboard‒ just download the SphInX Dashboard and you’re set to go.

In this post, I’m going to give a detailed description of the structure of the dashboard and an overview of the components involved. I’ll describe the working and creation part in detail in the posts that will follow.

As always, we have divided the have followed a three sheet structure:

  1. Display sheet (User Interface)
  2. Processing sheet
  3. Database

The section below describes the three sheets individually.

Display sheet

This sheet forms the most important part of the dashboard‒ it is the sole point of interaction between the user and the underlying dataset. It doesn’t matter if you have analyzed your data to derive an extraordinary insight‒ it won’t sell if you’re unable to present it well.


Since SphInX is a KPI reporting dashboard across countries, I’ve created the selectors for only Country and currency selection to keep it simple but as we go on, we will keep on increasing the number of  visualization options available to the user. The analyses have  been restricted to 1) brand-wise sales 2) market share 3) molecule profiles.

Processing Sheet

This sheet forms the link between the display sheet and the data sheet. This is where all the calculations for the dashboard are performed. While making this sheet, we ensured that all the calculations are dynamic i.e. they change on changing the selections on the display sheet.


The database can be one sheet or multiple sheets depending upon the nature of the data being represented.  We have used two sheets with different types of data:

1)      Sheet 1 – IMS sales data sheet: This sheet has the sales data for different products across three countries (to know more about IMS data, visit their website)

2)      Sheet 2- Molecule profile sheet: This sheet has a mini-profile(Molecule name, Indication, Price, etc.)  for the different  molecules covered in the sales sheet

Working of the tool:

We’ll be using a number of formulas, objects and tricks in making the dashboard. They have been listed below and their detailed explanation can be found in Dashboard 101 section:

  1.  Formulas: OFFSET, MATCH, COUNTIF, INDEX, ISNA, ISERROR (We’ll be using these formulas in combination with each other and will also be develop array formulas to perform tasks that are usually done programmatically using VBA.)
  2. Objects: Combo Box (Drop down), Option buttons
  3. Tricks: Named range, Dynamic chart, Conditional formatting, Array formulas

We’ll detail on all of these in our next post. Till then you can play with the dashboard and explore its various components.

Keep Excelling!  :)

SphInX ‒ A pharma KPI tracking dashboard [1/5]

Hi everyone!

You probably are already aware of what MS Excel means to us CRABS‒ if not, I can tell you (and I’m not exaggerating here) that it made all of us outstanding performers at our workplace in a highly competitive environment  (& got us the highest appraisals :D). All of this became possible after we learned the true power of Excel and then used it to develop some revolutionary solutions for our Clients. Our aim here is to demonstrate the power of MS Excel and consequently, empowering our readers with the skill set to use it at their workplace.

I still remember the day I started working on my first project that involved IMS sales data. For those of you who do not know, IMS is a company that tracks sales and prescription data for practically every marketed drug on the planet among many other things.  I was handed over a simple dataset (sales data for a few products across different geographies) by my manager and asked to report KPIs for that market. When I saw the data, I asked myself—what shoud I do:

  1. Make many graphs and tables one by one from the dataset and place them on different sheets to represent the market scenario (which was expected from me) or
  2. Should I could make an interactive dashboard that would present the user with certain selection options to see the entire market scenario/analysis/graphs in a one page via a User Interface(UI).

Do I really need to tell you which road I took?

But I think it might be good if I discussed the  reasons why I chose the dashboard approach, and they were:

  1. A dashboard presents the analyses in a single view and eliminates all navigation issues i.e. moving across sheets
  2. A dashboards allows filtering of data i.e. the user can slice and dice the data in numerous ways  to suit his/her needs
  3. Comparison between different scenarios is easier by selecting different parameters on the dashboard interface
  4. It is way cooler, and the appraisal cycle was closer :)

I faced a lot of difficulty in assembling the required knowledge for creating that first IMS data based dashboard. So, in this post I’m going to talk about the process of creating a KPI reporting dashboard and everything associated with it‒ we’ll cover the process exhaustively from the beginning to the end in the following phases:

Phase I:    Data cleaning and processing—will make the raw data ready for calculations and then apply formulas to get the desired field values

Phase II:   Finalizing and creating some KPI reporting graphs that capture the entire market scenario

PhaseIII: Designing the dashboard and adding some really cool features (which I’ll reveal as we progress)

And we will call this the SphInX project.

Though this dashboard is going to be specific to IMS data, it can be used to represent/analyze the sales data of any industry with minor tweaks.

Oh yes! There’s one minor detail I missed— we’re going to create this dashboard using only formulas and no VBA. The reason for this is that through this project  I want to unveil the power of Excel formulas (from basic to advanced) and demonstrate how they can be used by the pharma and other industries. Moreover, formulas are inbuilt into Excel and run much faster for a medium sized dataset than a VBA code.

Stay tuned for the next update on SphInX.

Till then, keep Excelling !