Author Archives: Keshava

Create an awesome responsive chart for your dashboard in 10 easy steps

Hi friends,

Today I will show how you can create a cool excel chart for your dashboards that responds to mouse over – yes, no clicking required. The final chart is going to look like the one below.

Animated Graphs

This can be a very useful technique to show some important data points related to your main series. For example in the chart above I am showing the profit made for each year when the user points his/her mouse over the relevant series.

So, let’s see how this is done Continue reading

A Tool to Find and Replace Words Within PowerPoint Charts

What !! you want me to change the name of a product in 120 charts because I missed to capitalize the first letter? And you want me to do this in the next 30 minutes? Do you have any idea that there is no inbuilt functionality in PowerPoint to do this?

This was the reaction my friend had when his manager asked him to change the product name in all the charts in the 150 slides PowerPoint deck that he just made. Since it was not the first time this had happened to him and he was pretty sure that it would happen many more times again, he reached out to me.

So, I made him a tool that allowed him to specify all the names that he wanted to change and their substitutes on an excel sheet and let the Excel tool take care of the rest. And for the greater good, I decided to share it with you guys. Download the tool here (Find Replace Within PowerPoint Charts tool; xlsm).

Using the tool is a simple two-step process and is explained in the image below.

FandRwithinPPTCharts

I hope your manager never asks you to do so. But if he does, you will find this tool useful :)

The Mathematics of Arrays

In the last post I explained the basic logic of array formulas and how they work. In this post I will be focusing on the mathematics of arrays – so what happens when you subtract one array from another or multiply or divide two arrays? How exactly does it work?

For the purpose of this post I will just be multiplying various arrays but you can basically do any mathematical (subtract, divide, add, etc.) or non-mathematical  (concatenation, find length, etc.) operation in the same fashion – literally anything and everything that you could think of.

Whenever I start with this concept, I get a common question – is this matrices multiplication that we learnt in school – so let me answer this, just in case – and the answer is NO. Array multiplication is a row wise multiplication. So each row of the first array gets multiplied with each row of the second array – in most of the cases.

Now, let’s look at all the possible cases of array multiplication:

Case 1: when both the arrays have the same number of rows and columns

In the image below, I multiply two arrays A & B (essentially range  C6:C10 & E6:E10). When I do this in a cell, I get the result that is displayed in the Output Array below. So, each cell in each row of the array A gets multiplied with the corresponding cell in array B.

Array2

So why did I say “each cell in each row”? To understand this let’s look at the image below: 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

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