The tool in action:
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:
- 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
- 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:
- A dashboard presents the analyses in a single view and eliminates all navigation issues i.e. moving across sheets
- A dashboards allows filtering of data i.e. the user can slice and dice the data in numerous ways to suit his/her needs
- Comparison between different scenarios is easier by selecting different parameters on the dashboard interface
- 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 !
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:
- Display sheet (User Interface)
- Processing sheet
The section below describes the three sheets individually.
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.
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:
- 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.)
- Objects: Combo Box (Drop down), Option buttons
- 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! :)
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:
- The Data sheet
- The Processing sheet
- The Display sheet (User Interface)
Lets take them one by one.
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:
- The selectors for country and currency have been provided at the top
- 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
- 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 !
As promised, I’m back with some new features for the SphInX‒ click here to download the new version. I’ve added the following functionalities to the dashboard:
- An option to select a specific time period‒ the user can specify the start and end month using combo-boxes and everything in the dashboard would change according to this. To see how this has been done, you can visit the dynamic chart section of Dashboard 101 by clicking here
- Conditional formatting to help the user view the product with the highest sales in the last month of the selected daterange by highlighting the corresponding row. Click here to visit the conditional formatting section of Dashboard 101 page
The snapshot below demonstrates the new functionalities of the dashboard in action.
All the newer functionalities have been added around the time period input that is received by the user. So, the first step to start is to retrieve the user input in excel in the format shown below. I have taken the output of the time period selectors on the ‘Processing Sheet’ and have further used these as inputs to create the final output on the ‘Display Sheet’.
You can view the formulas used and the rules applied to generate the output by downloading the file (click here).
Please feel free to write to us at firstname.lastname@example.org in case you have any questions and we will be happy to address them.
My next post would be on exporting the generated analyses to a Power Point presentation. Till then, keep Excelling!
I’m back with the final version of SphInX. In this post, we will discuss the process of exporting our results on the dashboard, to a MS PowerPoint presentation. Thought this has already been covered by Keshu in the final post of CompTraX, the major difference in this post would be that in CompTraX, we exported images from Excel to PowerPoint whereas in SphInX, we will create editable charts and editable tables in the PowerPoint presentation to represent the analysis (click here to download the dashboard with the PowerPoint template; .zip, .xlsm)
The video below shows the tool in action:
This approach of making editable slides would solve two purposes:
- You can make as many set of selections on the dashboard as you like and then export the results to a PowerPoint presentations to analyze/present them later
- If you find an anomaly in the data, you need not go through the entire process of generating the report again. Since the charts and tables exported to the PowerPoint presentation would be editable, you can make changes directly in the slides
There are two ways of creating editable objects in PowerPoint:
- Create new objects in the template, enter data in them and format them each time the export process is carried out. The line chart representing sales in SphInX has been exported using this methodology
- Keep an object in the template itself and edit it only when the export process is called. The tables in SphInX have been exported using this method. While this approach saves a lot of time, as you don’t have to create the object again and again, it has its own nuances; often fails in case of complex graphs.
The section below describes the two major code segments that have been used to export the data.
Code for creating an editable chart in PowerPoint from Excel using VBA
Set Gph_Shape = New_PPT_File.Slides(2).Shapes.AddOLEObject(ClassName:=”Excel.Chart”, Link:=msoFalse)
Set Gph_Object = Gph_Shape.OLEFormat.Object
Set PPT_chart = Gph_Object.Charts(1)
Set Chart_sheet = Gph_Object.Worksheets(1)
PPT_chart.SetSourceData Source:=Chart_sheet.Range(Chart_sheet.Cells(1, 1), Chart_sheet.Cells(Data_rows, Data_columns + 1))
PPT_chart.ChartType = xlLine
PPT_chart.PlotBy = xlRows
Gph_Shape.Line.BackColor.RGB = DisplaySheet.ChartObjects(“Linechart”).ShapeRange.Line.BackColor.RGB
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = Linechart_Excel.SeriesCollection(i).Border.Weight
.SeriesCollection(i).Border.Color = Linechart_Excel.SeriesCollection(i).Border.Color
.Legend.Position = xlLegendPositionBottom
With .Axes(1, 1)
.NumberFormat = Linechart_Excel.Axes(1, 1).TickLabels.NumberFormat
.Font.Color = Linechart_Excel.Axes(1, 1).TickLabels.Font.Color
.Font.Bold = Linechart_Excel.Axes(1, 1).TickLabels.Font.Bold
.Format.Line.BackColor.RGB = Linechart_Excel.Axes(1, 1).Format.Line.BackColor.RGB
With .Axes(2, 1)
.NumberFormat = Linechart_Excel.Axes(2, 1).TickLabels.NumberFormat
.Font.Color = Linechart_Excel.Axes(2, 1).TickLabels.Font.Color
.Font.Bold = Linechart_Excel.Axes(2, 1).TickLabels.Font.Bold
.Format.Line.BackColor.RGB = Linechart_Excel.Axes(2, 1).Format.Line.BackColor.RGB
.Color = Linechart_Excel.Axes(2, 1).Border.Color
.LineStyle = Linechart_Excel.Axes(2, 1).Border.LineStyle
.Weight = Linechart_Excel.Axes(2, 1).Border.Weight
The above code is responsible for creating and adding data to the chart in PowerPoint‒ the data is picked up from the ‘Display Sheet’ and entered into the chart sheet. It also takes care of the formatting of the chart in PowerPoint‒ the code applies the same formatting to the chart in PowerPoint as it is present in our dashboard. So, as it can be seen in the code, we have taken care of formatting for all series values, the chart axes, the gridlines, etc. For the entire code with comments, you can download the dashboard (link given in the beginning of the post).
Code for creating an editable table in PowerPoint from Excel using VBA
Set DataTable_shp = New_PPT_File.Slides(2).Shapes(“DataTable”)
For Rowcounter = Data_rows To 1 Step -1
For Columncounter = Data_columns + 1 To 1 Step -1
If Rowcounter = 1 Then
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Text = Format(DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter), “mmm-yy”)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Text = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Font.Bold = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter).Font.Bold
If Rowcounter = Max_val_row + 1 Then
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.Fill.BackColor.RGB = RGB(146, 208, 80)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Font.Color = RGB(255, 255, 255)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Font.Color = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter).Font.Color
This code picks up data from the data table in the ‘Display Sheet’ and enters it into an already created table on Slide 1 of the presentation. It also formats the table as it is formatted in the dashboard‒ the text fonts, the background fill, etc. have to be taken care of while exporting the table.
Two loops, one within the other, are used for the data transfer process. Once the data has been transferred, the extra rows and column are deleted from the table (code for this can be found in CreatePPT module of the dashboard).
These are only minor snippets of code, picked up to explain the important parts. You will need to go through the entire code thoroughly to understand it completely. For any queries, you can contact us at email@example.com and we’ll be happy to address them.
This will be all for the SphInX dashboard from my side‒ hope you enjoyed as much as I did. We can further improve it by incorporating your suggestions and feedback. So, please feel free to post them in our comments section.
I’ll be back soon with a new idea, and a new tool. Till then, keep Excelling!