Monthly Archives: May 2012

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 !

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

Hello friends!

Lets start with a Recap: till now we have talked about what SphInX is and how it is structured. Let us come to the part where we get our hands dirty  ‒ the construction and working of this Pharma KPI tracking dashboard.I’m sure that by now, you must have explored and played around with the dashboard‒ just in case you haven’t, here’s the Dashboard link again.

Before starting the development of any dashboard, it is very important to have an idea of how the final output would look like‒ it’s a different story that it may not turn out to be anything as you imagine it in the beginning, but overall it gives you a starting point. For SphInX, my idea was to create something that would enable the user to view the country-wise sales trend of different molecules in different currencies—Euro and USD for now as it covers most of the geographies of interest from a pharma company point of view. So, I decided to take the country and currency as inputs from the user, based upon which the results would be presented.

You would know that we always follow the three sheet structure when it comes to MS Excel dashboards:

  1. The Data sheet
  2. The Processing sheet
  3. The Display sheet (User Interface)

Lets take them one by one.

The Datasheet

To keep things simple, I chose a pretty small and a simple dataset for SphInX ‒ as we progress, we’ll add complexity to the data. It is a sample IMS sales dataset with value sales for some products in Euros and USD over a period of 10 months for three countries i.e. US, Japan and UK. The dataset is shown in the figure below:

(click image to see a larger image)

Different column headers have been marked in the dataset above and the each row represents a unique entry in the dataset. The first column (labeled 1) has been used to create a key by concatenating the Country and Currency values for every row‒ the value of this key alone helps me in differentiating entries which are different in either one of the parameters of country or currency. To delve deeper into the concept of unique key and data structure, you can visit this section in Dashboard 101 by clicking here.

The data here is first arranged by currency and then at a second level by country (for example, all values for US in Euros are together). This arrangement of data is of prime importance ‒ based on this, we will be extracting the data from this dataset. This arrangement allows extraction of data for a specific country and a specific currency (both selected by the user) in one go, using a single formula (we’ll see how).

 

The Processing Sheet

This is the brain of SphInX dashboard and controls how the data will be extracted‒ it links user inputs to the dataset. For the user inputs: I have chosen to take the country input from the user via a combo-box and the currency input in the form of option buttons. In order to process these inputs, I need them in cells on the processing sheet. I have described the process of retrieving these user inputs in the figure below (For more details, you can visit the Combo-box section of Dashboard 101 by clicking here).

(click image to see a larger image)

The Display Sheet

The user interface or the display sheet of the SphInX is pretty simply structured:

  1. The selectors for country and currency have been provided at the top
  2. Two charts‒ a line chart showing sales and a clustered column chart showing the market share have been added to see the market trend and the data for these charts has been shown in a table
  3. A table to view the mini-profile of molecules selected by the user has also been provided

The illustration below describes the features of the various components of this sheet.

(click image to see a larger image)

The mini-profile section of this sheet has been created using data validation and conditional formatting‒ the user can select the molecule using in-cell drop-downs and its details will automatically appear under the corresponding headers. Also, the legend for the charts has been created using conditional formatting since the number of molecules may change for every country and we would need to only view molecules for a selected country.

At this stage I would encourage you to download the dashboard and explore its formulas and features. If you have any confusion over the formulas that have been used in SphInX, you can visit our  Dashboard 101 section that has detailed explanations for all MS Excel features and formulas that have been employed for creating this dashboard.

I’ll use a different post to explain some of the formulas used  in the dashboard, such as the one used for creating a unique list that we have used in the mini-profile section of the dashboard. In my next post, I’m also going to add some new features to SphInX such as a third user input that would give the dashboard a new dimension; some conditional formatting to highlight  trends in the data and we’ll also touch upon some basic VBA to create simple outputs (at first) in a MS PowerPoint presentation simply, on the click of a button.

Till then, 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!

CompTraX a company profiling tool – small update

Hello friends,

The last couple of weeks have been hectic and we have been busy like bees, uploading the Dashboard 101 tutorials. This has kept me away from the blog—thankfully not for too long—and from the CompTraX updates.

I am back on track now and today, I would like to explain a set of formulas that I used while building the first version of CompTraX

You would have noticed that there is a sub-section on the Display Sheet of CompTraX that displays recent news.

spin button and news section

This section fetches the data from the Database Sheet with the help of a spin button (circled in red above). The cells which hold the relevant data in the database sheet look somewhat like:

snippet of database sheet containg news on pharma company

These cells have different news items in the same cell; separated by a line break (I have added color to different items to differentiate them in the pic). However, the Display Sheet shows the news items one by one on clicking the spin button. So, how is this accomplished?

The key lies in a group of four cells on the Process sing Sheet which looks something like this:

processing sheet

Cell 1: takes input from the data base sheet i.e. the group of news items

Cell 2: takes input from the spin button and helps decide which news item to show. E.g. in the image above the value ‘1’ means—show the first news.

Cell 3: has a formula that adds an identifier to demarcate the start and end of a news item. The formula is:

= SUBSTITUTE (SUBSTITUTE (A44, CHAR (10),”start1start”, B44), CHAR (10),”stop1stop”, B44)

This formula replaces the first instance of a ‘line break’ with the keyword ‘start1start’. Then it again acts on this modified string and replaces the first instance (which would earlier have been the second instance) of ‘line break’ with ‘stop1stop’. So we have a demarcated news item. Something like this:

‘start1start news item stop1stop’

Cell 4: has a formula which extracts this demarcated news item. The formula is explained below:

explanation of formula

It might seem a bit complicated at first look, so, I will try to explain it using another example—see the picture and the maths below.

mathematics

Let’s say cell ‘A44’ has some text value. Formula in Step 3 adds a start and stop position to this string. Say this is stored in cell ‘C44’. Now, Step 4 extracts the demarcated news item from this string. As mentioned earlier, the entire set of formula is dependent on the spin button and with every click of the button the next news item is extracted.

That’s all for this post; hope you find this useful. In the next post I will upload a new version of CompTraX which would have a search interface. So, stay tuned and keep Excelling!

Data Validation in MS Excel‒ Creating in-cell drop downs

In this post I’m going to talk about Data Validation in MS Excel. Dashboards require user inputs, based upon which the output is generated. A good dashboard ensures that the users have an easy option to provide their inputs and at the same time have the data entry restricted to a certain set of allowed values that the dashboard recognizes as input. This ensures that no errors occur while data processing due to the wrong input values entered by the user.

We earlier demonstrated the use of combo-boxes in achieving this purpose (read about combo-boxes by clicking here). In this tutorial we will demonstrate how this task can also be achieved by  using the data validation feature of MS Excel.

How does it work?

Compared to combo-boxes which are require insertion on a sheet, data validation can be applied to cells itself . MS Excel allows the following data validation settings to be applied to cells:Restrict data entry to values in a drop-down list

  • Restrict data entry to a whole number within limits
  • Restrict data entry to a decimal number within limits
  • Restrict data entry to a date within a time frame
  • Restrict data entry to a time within a time frame
  • Restrict data entry to text of a specified length
  • Calculate what is allowed based on the content of another cell
  • Use a formula to calculate what is allowed

We’ll discuss only the ‘Restrict data entry to values in a drop-down list’ method here since we’ll be using it the most. Also, once we explain this, it would be pretty simple for you to explore the rest on your own

The following steps will have to be followed to apply data validation and create an in-cell drop-down (cell ‘I1’ in the picture  below)

 

Step 1: Select the cells in which you want to create a drop-down (cell ‘I1’ here; can be a range of cells)

Step 2: On the MS Excel Ribbon, go to Data→ Data Validation → Data Validation…

Step 3: The Data Validation dialog will appear. In the Settings tab, select List under the Allow parameter and select the source data for this list under Source category. The source data here would be $G$2:$G$6 (you can also use a named range)

Crab Tip: While creating Data Validation you can enter the Input Message to display once the cell has been selected by the user and also, you can specify the Error Message that appears when a user enters a wrong value in the cell.

Step 4: Click on ‘Ok’ and you have created an in-cell drop-down with the source data  $G$2:$G$6. Now when you select the cell, you will see a drop-down from which you can select from the range of values that you specified.

Data validation can not only be used to create drop-downs to replace combo-boxes but also for creating forms and data sheets where you would like to restrict user entries to certain specific values— in fact the later is its primary purpose. This would ensure that the user does not input data that the tool cannot recognize and hence, would reduce the chances of errors.

So, the next time you have to take a large number of inputs from the user (like in case of a Market Research based dashboard), don’t forget to limit the options available for entering the data— you don’t want your users to come back to you every time they misspell an entry.

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.

Database

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!  :)