In my experience I have come across numerous people who so wish that their charts are ‘Interactive’. I’m sure many of you would like to select a data point on your chart and let it act as a selection for sort of a drill-down. Doing this, although not impossible in Excel, requires sound understanding of Class Modules and chart events. I have tried to create a small example to illustrate this.

Problem Statement:I have  data for daily site views for USA, Canada and the rest of North America. While there is a top chart that shows the cumulative counts, I would like to select a data point on the chart and see a detailed view for it as below.

Chart Event Example

Chart Event Example

Read the rest of this entry »


” As I develop and serve, be patient. God is not finished with me yet.”

-Jesse Jackson

For those of you who were beginning to wonder if there was ever going to be more activity here, there is good news. Wait, just a little bit longer……….

Quote  —  Posted: April 9, 2014 by MaxFraudy in Others


UntitledWhen working with a hierarchical data, most often we need to view at various levels of hierarchy. One of the ways of getting it done is using Data List Outline feature of Excel. To better demonstrate what I’m talking about, let me take an example.

I have the following data – the first column contains the month name, the second column contains the region name and the last column has the sales.

Raw Data

Raw Data

Read the rest of this entry »

Large Operation Warning: Excel

Posted: September 12, 2013 by Theodoulus in Excel
Tags: ,

We generally see a “Warning Dialogue Box” like:

large operation warning

This is seen when we try to put a formula, value or formatting to a big range (many cells) at a time in Excel worksheet.
So the good part is, we can modify the number of cells for which the dialogue box gets popped-up. And how do we do this:

Simply, Open Excel File -> Goto Excel Options-> Goto Advanced Tab -> In Editing Options, we can find one option like highlighted below:

large operation warning Solution

 

 

We can set any number in the box given there (in Thousands).

Now, if we perform any calculation on the a bigger range than specified, the Warning will be popped-up.

Opening URL @ new tab in Internet Explorer: VBA

Posted: September 11, 2013 by Theodoulus in Excel, General Tips, VBA
Tags:

Problem Statement: Suppose, we have an array of URLs and we need to open them one by one. By looping over all the URLs on the same tab at internet explorer, we need to check the internet explorer is in LOADED or LOADING state. If the explorer is in loading state, it may give an error. And by default, the URL will be opened on the same tab.

So the process of opening URL on the same tab will be lengthy in terms of Coding, and processing time.

Solution:

We can use different tabs of the Internet Explorer so that we would not have to check the READYSTATE and to REFRESH the tab again and again. The example is given below:

Option Explicit

Sub OpenURLOnNewTab()

    Dim lngC As Long
    Dim strUrl  As String
    Dim ieObj   As InternetExplorer

    Set ieObj = New InternetExplorer
    ieObj.Visible = True
    strUrl = "https://usefulgyaan.wordpress.com/"       'We have taken one URL but you can take an array of URLs

    For lngC = 1 To 100
        ieObj.Navigate2 strUrl, 2048            '2048 is to open the URL on the new tab
    Next lngC

End Sub

The Ultimate Correlated SQL Query

Posted: August 23, 2013 by Transformer in MS Access, SQL
Tags: , ,

Problem Statement:  There is a table named SkillSet which has employees’ skill details. We need to find out employees (skill wise) who have the highest proficiency rating. If multiple employees have the highest proficiency rating in some skill then the employee having more experience should be selected.

Employee Skills Proficiency Experience
Thor SQL 9 1
Thor VBA 8 1
Mike VBA 9 1
Mike SQL 6 1
Robert VBA 9 2
Robert C# 6 2
Robert SQL 9 1
Logan C# 9 1
Logan SQL 9 2

Solution:Lets do it in steps. Try to find out skill wise employees having maximum proficiency. Read the rest of this entry »


Although this is not my personal favorite chart, I have seen that many people find it fabulous since it looks good on a dashboard. I think that it takes too much space for the small information that it conveys. But then, that’s just me.

Gauge Chart

Gauge Chart

Read the rest of this entry »


When your Excel dashboard has multiple users, you might want to customise the options available to different sets of users.

Scenario 1: You created a sales dashboard, you might want pricing column hidden from certain people, or you might want only relevant products visible to the suppliers, or you may want to call the same report by different names for different sets of people and so on and so forth. Essentially, you want to control what you want to show to whom.

Scenario 2: You have an Excel report with huge volumes of data of all regions. You want to set your print area such that each region can print only their own data and the header is customised as per their region name. Read the rest of this entry »


While Preparing an Excel Dashboard one needs to handle a lot of hiding and unhiding of Sheets for various users. For Devloper all sheets should be unhidden and for user some of them should visible. We need different views of one workbook for different people developer, user, admin, etc.
We can handle this with using a Custom View property of the workbook with VBA

ActiveWorkbook.CustomViews.Add Viewname, PrintSettings(value True/False), RowColumnSettings(value True/False)

Read the rest of this entry »


There are numerous Project Management tools available in the market which make life easier while managing huge projects. But for simpler projects, you don’t need all that jazz, just a simple project plan template is all that is required. Well, you can use conditional formatting to create simple Gantt Charts. Here’s how:

Step 1: Create your list of tasks and the decide the proposed dates against them. On the top row, put down the dates (one in each column) like this:

Task List

Task List

Read the rest of this entry »